How to recover MySQL database from .myd, .myi, .frm files

Question

How to restore one of my MySQL databases from .myd, .myi, .frm files?

Answer 1

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)

Note that permissions (GRANT, etc.) are part of the mysql database. So they won't be restored along with the tables; you may need to run the appropriate GRANT statements to create users, give access, etc. (Restoring the mysql database is possible, but you need to be careful with MySQL versions and any needed runs of the mysql_upgrade utility.)

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]

Answer 2

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.

Answer 3

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.

Answer 4

Simple! Create a dummy database (say abc)

Copy all these .myd, .myi, .frm files to mysql\data\abc wherein mysql\data\ is the place where .myd, .myi, .frm for all databases are stored.

Then go to phpMyadmin, go to db abc and you find your database.

Answer 5

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.

Answer 6

I found a solution for converting the files to a .sql file (you can then import the .sql file to a server and recover the database), without needing to access the /var directory, therefore you do not need to be a server admin to do this either.

It does require XAMPP or MAMP installed on your computer.

• After you have installed XAMPP, navigate to the install directory (Usually C:\XAMPP), and the the sub-directory mysql\data. The full path should be C:\XAMPP\mysql\data

• Inside you will see folders of any other databases you have created. Copy & Paste the folder full of .myd, .myi and .frm files into there. The path to that folder should be

  C:\XAMPP\mysql\data\foldername\.mydfiles

• Then visit localhost/phpmyadmin in a browser. Select the database you have just pasted into the mysql\data folder, and click on Export in the navigation bar. Chooses the export it as a .sql file. It will then pop up asking where the save the file

And that is it! You (should) now have a .sql file containing the database that was originally .myd, .myi and .frm files. You can then import it to another server through phpMyAdmin by creating a new database and pressing 'Import' in the navigation bar, then following the steps to import it

Answer 7

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 |
+------------------+-------+----------+----------+

Answer 8

You can copy the files into an appropriately named subdirectory directory of the data folder as long as it is the EXACT same version of mySQL and you have retained all of the associated files in that directory. If you don't have all the files, I'm pretty sure you're going to have issues.

Answer 9

The above description wasn't sufficient to get things working for me (probably dense or lazy) so I created this script once I found the answer to help me in the future. Hope it helps others

vim fixperms.sh 

#!/bin/sh
for D in `find . -type d`
do
        echo $D;
        chown -R mysql:mysql $D;
        chmod -R 660 $D;
        chown mysql:mysql $D;
        chmod 700 $D;
done
echo Dont forget to restart mysql: /etc/init.d/mysqld restart;

Answer 10

http://forums.devshed.com/mysql-help-4/mysql-installation-problems-197509.html

It says to rename the ib_* files. I have done it and it gave me back the db.

Answer 11

For those that have Windows XP and have MySQL server 5.5 installed - the location for the database is C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.5\data, unless you changed the location within the MySql Workbench installation GUI.