CODEWITHSUNDEEP
mysqlrecovery
chandrajeet
chandrajeet

Reputation: 8963

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

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

Upvotes: 203

Views: 379868

Answers (12)

Soner from The Ottoman Empire
Soner from The Ottoman Empire

Reputation: 20901

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

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

  3. Check the tables.

Upvotes: 0

Kaspar Lee
Kaspar Lee

Reputation: 5596

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

Upvotes: 12

derobert
derobert

Reputation: 51137

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]

Upvotes: 182

dny238
dny238

Reputation: 61

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;

Upvotes: 2

alnel
alnel

Reputation: 251

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.

Upvotes: 25

Vishal
Vishal

Reputation: 229

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.

Upvotes: 22

Demon
Demon

Reputation: 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.

Upvotes: -2

James
James

Reputation: 21

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.

Upvotes: 2

mcardellg
mcardellg

Reputation: 271

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.

Upvotes: 27

Brent
Brent

Reputation: 23702

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.

Upvotes: 15

Elzo Valugi
Elzo Valugi

Reputation: 27866

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

Upvotes: 7

cgp
cgp

Reputation: 41381

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.

Upvotes: 5

Related Questions