Restore MySQL Database from ibdata and .frm Files

Last week I was presented with a problem that involved restoring a MySQL database for a client using only the /data folder from an original MySQL installation. The solution turned out to be rather simple, but that didn’t stop me from racking my brain for a few hours. Thus, I thought I’d share my experience in hopes of helping others that may run into this.

*NOTE* The MySQL database I was tasked with restoring was associated with a WordPress installation. My guess is that this is irrelevant, though.

*ALSO NOTE* This article assumes you are comfortable with software solutions such as MAMP and/or XAMPP, as well as phpMyAdmin.

I made the decision early on to solve this problem locally, (I find that working directly from a web server usually just gets me into trouble).  I use MAMP on a regular basis and as such have quite a few MySQL databases associated with that installation. Rather than fooling with this at all I opted to use XAMPP on a Windows XP SP2 machine. Either one of these solutions will work, as they essentially provide the same services. I chose to avoid using MAMP on my machine because of everything I have invested into that application, but that doesn’t mean you have to.

The first thing you’ll want to do is install a fresh copy of either MAMP, (Mac only), or XAMPP. Then create an empty database using phpMyAdmin with the same name as your original database, (the one you’re trying to restore). For example, if your previous database was called ‘wordpress,’ in phpMyAdmin, (under the ‘Create new database’ text field), you would enter ‘wordpress.’ This will create a folder named wordpress in /Applications/MAMP/db/mysql if you’re using MAMP, or C:\xampp\mysql if you’re using XAMPP. At this point you’ll want to turn off the MySQL service. Now, copy the contents of, (not the entire folder), your mysql database folder that contains the .frm files to the new location. The next step is to copy the ibdata1 file to the MySQL folder in either XAMPP or MAMP, then start the MySQL service again. Now you should be able to locate your database tables in phpMyAdmin.

At this point, (assuming you don’t want to keep the data locally on your machine), you’ll want to export the database using phpMyAdmin. Be sure to check Add DROP TABLE, choose SQL as the export type, and check the ‘Save as file’ checkbox. The .sql file that will be generated can easily be imported into another instance of phpMyAdmin. The new database doesn’t need to have the same name as the previous database, either. I was unable to use my previous database name because the site I was working on had been moved to a shared hosting server, (where someone had already chosen the name of my previous database), and everything worked fine.

Also keep in mind that this can be accomplished whether or not your server is running phpMyAdmin. There are plenty of alternatives available that will allow you to accomplish the same tasks, and there’s always the command line. The most important step is to simply copy the contents of the /data folder and the ibdata1 file. Hope this helps!

Leave a comment if you have any questions!

Filed under: Technology, Web Development by James Barclay | Social tagging: >

20 Responses to “Restore MySQL Database from ibdata and .frm Files”

  1. User1, on November 26th, 2010 at 12:21 am Said:

    It does not help at all. It could have been clearer of the instructions are bulleted format.

  2. Rajesh, on November 27th, 2010 at 12:33 am Said:

    THANKS A LOT, THE GOD’S PARTNER

  3. James Barclay, on December 16th, 2010 at 12:39 am Said:

    James,

    Could you perhaps give me an idea of the trouble you’re running into? This may be helpful for me as I could use that information to improve the post. Thanks.

  4. Joaquin, on December 17th, 2010 at 10:47 am Said:

    you ar a genious you saved me

  5. DmitryC, on January 3rd, 2011 at 9:48 am Said:

    So simple. Great. It’s work
    Thanks

  6. John, on February 8th, 2011 at 5:35 am Said:

    Hello there,
    I had a huge problem.
    I reinstalled the xampp into my server this week.
    At the time of reinstalling I backed up the old xampp folder.
    But when I start the new xampp control panel and start mysql,
    I can’t find the old .frm files.
    Please help me.
    I am currently a deep deep trouble.

    Thanks in advance

  7. Anthony, on February 10th, 2011 at 7:22 pm Said:

    I did the steps above, but it only restored my MyIsam tables… The table count next to the DB name is correct, but I don’t see any of the InnoDB tables…. What to do!!?

  8. brion, on June 11th, 2011 at 11:54 pm Said:

    Wow, you saved my life. By the looks of it a load other people too.
    Brilliant.

  9. JohnJ, on September 29th, 2011 at 7:05 am Said:

    i only have the frm files is it still posible to do this?

  10. Scott, on October 22nd, 2011 at 4:18 pm Said:

    I had a database called “Website” and it said there were all 17 tables in it, but when you click it there were 0 even though all 17 tables were in the directory as .frm.

    I did what you instructed to do and the same thing happened as when I started, also, now my mysql server will not start. Any ideas how to get it to start again or why it stopped? Also any ideas why I am having the problem with the database?

  11. James Barclay, on October 29th, 2011 at 1:16 am Said:

    Scott,

    Can you provide me with a bit more information regarding the problem you’re experiencing? It’s difficult to troubleshoot this type of issue when I don’t know exactly what is happening on your end.

    Feel free to contact me via email, just fill out the form at http://everythingisgray.com/contact-me/.

    Thank you.

  12. Gareth Williams, on November 6th, 2011 at 1:42 pm Said:

    Hi James/Scott
    Similar problem here; I tried to restore my frm files for a Magento DB but now all I see is 2 tables despite 327 frm files! …did you find a solution or reason for this?

  13. Gareth Williams, on November 7th, 2011 at 8:28 am Said:

    Worked this out.In an effort to sort another issue we had upgraded MAMP to 2.0 – the frm files from our old MAMP 1.9 weren’t compatible it would seem! Thanks

  14. Denis, on December 14th, 2011 at 3:21 pm Said:

    THanks this advice really help to me

  15. Lisarte Barbosa, on June 9th, 2012 at 9:08 am Said:

    I’m using xampp and for some reason phpmyadmin see that a database has 54 tables when I click on in the database is empty. The frm files are there and that database is able to be add new tables and get vivible. Any Idea to solve this issue? (mac osx) thanks

  16. Craig, on October 26th, 2012 at 6:58 pm Said:

    Cannot thank you enough! Sincerely – Thankyou. I had a dodgy hardrive that had two months worth of work on it and it failed, I managed to recover the mysql file but as I failed to previously backup an sql export file it was proving a real nightmare. Your advice proved to be perfect.

  17. James Barclay, on October 28th, 2012 at 4:37 am Said:

    You’re very welcome!

  18. Gitahi Ng'ang'a, on March 15th, 2013 at 3:10 pm Said:

    I followed your exact steps to the letter but I am still unable to restart the MySQL service. Any ideas why? I’ve tried both replacing and not replacing the .opt files in the schema folders.

  19. Paul, on May 18th, 2013 at 2:22 am Said:

    Life saver, thanks!

  20. James Barclay, on May 19th, 2013 at 1:45 am Said:

    You’re welcome!

Leave a Comment

 
« «