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:
- Install MySQL and MySQL Administrator on my home PC.
- Copy the appropriate table files to the MySQL data directory.
- Use MySQL Administrator to make a backup in SQL format.
- 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
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
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
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
Thank you VERY MUCH! You saved my site!
Posted by: WeBaRTer | October 28, 2009 1:51 AM