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

Question

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

Recognize

Though I answered this, it really belongs on Serverfault. – Trochaic

@Recognize you should really accept the most voted answer. – Attainable

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]

Trochaic

Would this really work without adding the appropriate entries to the information_schema table? I mean MySQL needs to know to look for these files right? – Hillside

The information_schema tables don't actually exist, they're only views into internal database state. See dev.mysql.com/doc/refman/5.0/en/information-schema.html – Inman

Wow, I felt dirty, but dropping the whole directory from what I think was a MySQL4 install into my MySQL5.1 just magically recreated the tables. No restart or anything (on windows). – Disafforest

It work you just need to remeber to run (for every table): check table sometable; and then run repair (only if needed): repair table sometable; – Adaliah

This worked great. I installed WAMP locally and dropped these into \wamp\bin\mysql\test\ and then fired up PHPMyAdmin and there were the tables I needed to browse! – Caulescent

I can not use repair because it says Table XX doesn't exist. It seems it doesn't detect it even I can see it in the object browser. Any solution for it? – Giraldo

Can it be because i dont have the .myd file? – Giraldo

@Giraldo the MyISAM .myd file is where the data is stored. You need it. – Trochaic

This worked great! I had put the files in place, but mysql wasn't "seeing" them until I changed ownership to "mysql:mysql". – Gustie

I copied the files, and changed ownership, but also had to GRANT access to the copied database to a user before I could access it. – Armanda

@Armanda the permissions are stored in the mysql database, so if you didn't restore that as well, they won't be restored. Note that there are additional considerations for restoring the mysql database w/r/t MySQL versions. Added to the answer. – Trochaic

Just because the MYI and MYD files exist, that doesn't guarantee it's MyISAM. – Materialize

The install command is really good for doing mv, chown, and chmod in a single command. These worked for me:sudo install -g mysql -o mysql -m 660 bugs_fulltext.frm /var/lib/mysql/bugs/bugs_fulltext.frm && sudo install -g mysql -o mysql -m 660 bugs_fulltext.MYD /var/lib/mysql/bugs/bugs_fulltext.MYD && sudo install -g mysql -o mysql -m 660 bugs_fulltext.MYI /var/lib/mysql/bugs/bugs_fulltext.MYI – Iamb

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.

Eichman

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.

Resonate

Followed these steps (after everything else had failed) and used the innodb_force_recovery = 4 level (not sure that was needed in this case). Thank goodness! – Shivery

FYI: ibdata1 is InnoDB, not MyISAM. – Trochaic

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.

Garnett

the shortest and precise answer – Lyle

The best way to recover the data... I installed WAMP, then created a new database, copy files in the new database directory C:\WAMP64\bin\mysql\mysqlxx\data\newdatabase open phpmyadmin and your new database, you will see the data – Bobsled

Yes, I have also used this way with XAMPP. Install XAMPP, create the new empty database with the same name, copy these files into the database folder (frm,myd, ibdata...). Then start PhpMyAdmin on localhost, list database and export dump file. And import on live server. Simply works like a charm. – Indic

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.

Bonne

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

Skied

This solution works beautifully in Windows, thanks. – Kithara

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

Geoid

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.

Ceram

If I don't have the EXACT same version of MySQL, what do I do? – Pb

Answer 9

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.

Anthocyanin

Answer 10

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;

Czarra

Answer 11

1. Find your datadir by the query that SHOW VARIABLES WHERE Variable_Name LIKE "%dir" ;

2. Create a new folder and put the .myd, .myi, .frm files into it.

3. Check the tables.

Panama

Answer 12

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.

Fretwell

The question is about recovering from specific file types, not where those files can be found on Windows XP MySQL 5.5. – Riband