MainDoctor WhoMusicSoftware
Main Page

Alden Bates' Weblog

Feigning normality since 1973

Converting FRM/MYD/MYI database files to SQL

Filed in: Website Management.

As I mentioned last night, I had to restore some database files. Unfortunately the backup I'd downloaded was in a tarred/gzip file with each database table represented by three files with the extensions frm, myd and myi. After googling a bit (dear Google: please don't bother giving me "Supplemental result" listings if the target page is gone and you don't have a cache copy of it. That's just dumb.) I found that these were raw SQL database files.

The only way to restore them is appeared to be to copy them directly to MySQL's data directory - the problem being that I didn't actually have access to the MySQL data directory on the web server in question. You would think, given that phpMyAdmin had given me these files as a backup that it would have an easy way to restore them, but if it does, I couldn't find it.

The solution ended up being:

  1. Install MySQL and MySQL Administrator on my home PC.
  2. Copy the appropriate table files to the MySQL data directory.
  3. Use MySQL Administrator to make a backup in SQL format.
  4. Use the SQL script from the backup to restore the table on the web site.

Once the tables were restored, I used a couple of SQL statements to copy the data across to the appropriate table, and all was hunky dory.

Posted December 9, 2005 9:37 PM

Comments

Thank you VERY MUCH! You saved my site!

Posted by: WeBaRTer | October 28, 2009 1:51 AM

Glad to hear this helped :)

Posted by: Alden | October 28, 2009 6:56 AM

Hi,


I have accidently deleted ‘mysql’ database directory, which is created by default (for my system - C:\Program Files\MySQL\MySQL Server 5.1\data). Rest all databases are there. ibdata1 is also safe.

Is there any way to restore it? Does anything inside it (‘mysql’ directory) changes? I think it does, because in another machine (which also has MySQL installed), I can see few file’s timestamps that are different from other files such as user.MYI, db.MYI, db.MYD, & user.MYD

Any idea how I can restore 'mysql' database directory?


Thanks & Regards,
Gaurav Chhabra

Posted by: Gaurav Chhabra | February 4, 2010 2:58 AM

omg i used easyphp and did what u said its now working like charm u saved my site really really thanks !

Posted by: Burak | March 8, 2010 10:27 AM

Hi I can t seem to find the Mysql Administrator to install can someone give me the direct link please?

Posted by: Stephanie | March 27, 2010 4:45 AM

MySQL Administrator seems to have been replaced by MySQL Workbench, which ought to do the same thing. You will also need SQL Server itself.

Posted by: Alden | March 27, 2010 9:13 AM

Hi, I use xampp on my laptop. Two days ago it crashed but i still have access to the HD. So i reinstalled xampp and copied the /mysql/data/ folder from the old folder to the new xampp folder. But it seems there are only .frm files in there. The result is that the data is not copied properly. Do u might have a solution for this?

thanks

Posted by: Boris | April 29, 2011 1:25 AM

nvm, i forgot to copy the files in /data/ like ib_logfile0 etc.

Posted by: Boris | April 29, 2011 1:47 AM

Thank u very much.helped me a lot.

Posted by: Vinayaka M Pai | October 1, 2011 6:18 PM

I am struggling with this i have installed mysql server and workbench on an old pc, but i don't know where to go from here. I uploaded the files to the data directory but nothing appeared in the data base.
Can someone help me please :(

Posted by: Luke | November 12, 2011 3:33 AM

Thank you very much.

Posted by: abdul ehat | May 29, 2013 9:44 AM

I copied all the table files to the MySQL data directory. But its showing some table does not exist. When i checked the table having only .FRM extension are not working. Other tables which are having .MYD &.MYI & .FRM extensions are working fine. Means table having only .FRM file is not working.

Please suggest me what to do.
THANKS in advance

Posted by: wedphp | July 4, 2013 10:43 PM

i have only .frm file how to get data base values

Posted by: rama | March 6, 2015 10:28 PM

Post a comment





Tetrap.com Site Map