How to recover mysql db from .myd, .myi, .frm files

Thanks to http://stackoverflow.com/questions/879176/how-to-recover-mysql-db-from-myd-myi-frm-files

If these are MyISAM tables, then plopping the .FRM, .MYD, and .MYI files into a database directory (e.g.,/var/lib/mysql/dbname) will make that table available. It doesn’t have to be the same database as they came from, the same server, the same MySQL version, or the same architecture. You may also need to change ownership for the folder (e.g ‘chown -R mysql:mysql /var/lib/mysql/dbname’)

Actually, you probably just need the .FRM (table structure) and .MYD (table data), but you’ll have to repair table to rebuild the .MYI (indexes).

The only constraint is that if you’re downgrading, you’d best check the release notes (and probably run repair table). Newer MySQL versions add features, of course.

[Although it should be obvious, if you mix and match tables, the integrity of relationships between those tables is your problem; MySQL won’t care, but your application and your users may. Also, this method does not work at all for InnoDB tables. Only MyISAM, but considering the files you have, you have MyISAM]

Note that if you want to rebuild the MYI file then the correct use of REPAIR TABLE is:

REPAIR TABLE sometable USE_FRM;

Otherwise you will probably just get another error.

One thing to note:

The .FRM file has your table structure in it, and is specific to your MySQL version.

The .MYD file is NOT specific to version, at least not minor versions.

The .MYI file is specific, but can be left out and regenerated with REPAIR TABLE like the other answers say.

The point of this answer is to let you know that if you have a schema dump of your tables, then you can use that to generate the table structure, then replace those .MYD files with your backups, delete the MYI files, and repair them all. This way you can restore your backups to another MySQL version, or move your database altogether without using mysqldump. I’ve found this super helpful when moving large databases.

I think .myi you can repair from inside mysql.

If you see these type of error messages from MySQL: Database failed to execute query (query) 1016: Can’t open file: ‘sometable.MYI’. (errno: 145) Error Msg: 1034: Incorrect key file for table: ‘sometable’. Try to repair it thenb you probably have a crashed or corrupt table.

You can check and repair the table from a mysql prompt like this:

check table sometable;
+------------------+-------+----------+----------------------------+
| Table | Op | Msg_type | Msg_text | 
+------------------+-------+----------+----------------------------+ 
| yourdb.sometable | check | warning | Table is marked as crashed | 
| yourdb.sometable | check | status | OK | 
+------------------+-------+----------+----------------------------+ 

repair table sometable;
+------------------+--------+----------+----------+ 
| Table | Op | Msg_type | Msg_text | 
+------------------+--------+----------+----------+ 
| yourdb.sometable | repair | status | OK | 
+------------------+--------+----------+----------+

and now your table should be fine:

check table sometable;
+------------------+-------+----------+----------+ 
| Table | Op | Msg_type | Msg_text |
+------------------+-------+----------+----------+ 
| yourdb.sometable | check | status | OK |
+------------------+-------+----------+----------+

 

I just discovered to solution for this. I am using MySQL 5.1 or 5.6 on Windows 7.

  1. Copy the .frm file and ibdata1 from the old file which was located on “C:\Program Data\MySQL\MSQLServer5.1\Data”
  2. Stop the SQL server instance in the current SQL instance
  3. Go to the datafolder located at “C:\Program Data\MySQL\MSQLServer5.1\Data”
  4. Paste the ibdata1 and the folder of your database which contains the .frm file from the file you want to recover.
  5. Start the MySQL instance.

No need to locate the .MYI and .MYD file for this recovery.