Alden Bates' Weblog: Converting FRM/MYD/MYI database files to SQL

Ads by Project Wonderful! Your ad here, right now: $0

Home : December 2005

« Upgrading to eFiction 2 (the revenge) | Main | Outing scumbags »

Converting FRM/MYD/MYI database files to SQL

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

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

Alden Bates' Weblog

Feigning normality since 1973

Converting FRM/MYD/MYI database files to SQL

Comments

Post a comment


	Home : December 2005 Alden Bates' Weblog Feigning normality since 1973 « Upgrading to eFiction 2 (the revenge) \| Main \| Outing scumbags » 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 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 Post a comment Name: Email Address: URL: Remember personal info? Comments: (you may use HTML tags for style)
Tetrap.com Site Map