Restore MySQL Database from ibdata and .frm Files

Here’s a simple way to restoring a MySQL database using only the /data folder from an original MySQL installation. Rather than risking damaging the existing server, the database must be restored on a development or test machine NOT the actual server.

Restore the database on a development or test machine NOT the actual server.
This article assumes you are comfortable with software solutions such as XAMPP and phpMyAdmin.

  1. Find a test or development machine with Windows XP SP2 or higher.
  2. Download and install a fresh copy of XAMPP.
  3. 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 “C:\xampp\mysql” in XAMPP.
  4. At this point you’ll want to turn off the MySQL service.
  5. Now, copy the contents of your mysql database folder (not the entire folder) that contains the .frm files to the new location.
  6. The next step is to copy the ibdata1 file to the MySQL folder in XAMPP
  7. Start the MySQL service again.
  8. Now you should be able to locate your database tables in phpMyAdmin.
  9. At this point you’ll want to export the database using phpMyAdmin.
  10. Be sure to check Add DROP TABLE, choose SQL as the export type, and check the ‘Save as file’ checkbox.
  11. 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. Sometimes, you may be unable to use the previous database name because the site may have been moved to a shared hosting server, (with a pre-defined database name).

This example uses XAMPP and phpMyAdmin. However, there are many other 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.

No comments:

Post a Comment