Methods of parsing MYD, MYI, and FRM files

Question

I've read several posts on this forum and elsewhere but still can't read some MYD/MYI/FRM files that contain data that I want to import into SQL Server.

• I stopped the MySQL service at the client location and copied all the files (the same way we did on SQL Server).
• I installed the latest MySQL ODBC Connector, MySQL Engine and MySQL Workbench on my Windows 10 PC. The engine is running and the ODBC connector has been configured with username and password. Using Workbench, I logged in successfully.
• I created a database called MyTest, which created a C:\ProgramData\MySQL\MySQL Server 8.0\Data\mytest folder.
• I stopped the MySQL service and copied MyData.MYD, MyData.MYI and MyData.FRM to the mytest folder.
• I restarted the MySQL service.

However, if I run the "Select * from mytest.MyData" query in Workbench or using the MySQL 8.0 command line client, it will keep prompting that the table mytest.MyData does not exist. Now, I read that it might be appropriate to run the CHOWN command, but I don't know if the syntax would be different in a Windows system and if this command could be executed directly from the command prompt.

Any help would be greatly appreciated. Thanks!

Answer 1

Older versions of MySQL allowed you to do this in MyISAM tables, but MySQL 8.0 introduced a new "data dictionary" implementation, which is an internally maintained metadata database and has a preference for InnoDB over MyISAM. Therefore, I believe the casual way of moving files around no longer works as well as it once did. To move a table from one instance to another, you should perform the export and import steps correctly.

For InnoDB, you can use Transportable tablespaces. At least this way you don't have to do a full data import, but it does require a few more steps than just copying the files.

P.S.: I haven't used MyISAM in years. I prefer my database to support ACID properties, and MyISAM does not support any ACID properties.