MySQL Backup

How to Recover InnoDB MySQL Table Data from ibdata and .frm Files

This tutorial explains how to restore MySQL tables when all or some of the tables are lost, or when MySQL fails to load table data.

One of the reason for this to happen is when the table data is corrupted.

In this particular scenario, when you connect to the MySQL database server, you cannot see one more tables, as they are missing.

Under this scenario, the MySQL log file contained the following messages:

The method explained below will work only for InnoDB database.

Note: Before you do anything, take a backup of all the MySQL files and database in the current condition, and keep it somewhere safe.

To restore the table data you have make sure that data directory and its contents are intact. In my case it was fine.

  • Ibdata1 – This file is the InnoDB system table space, which contains multiple InnoDB tables and associated indexes.
  • *.frm – Holds metadata information for all MySQL tables. These files are located inside the folder of the corresponding MySQL database. (for example, inside “bugs” directory)
  • ib_logfile* – All data changes are written into these log files. This is similar to the archive logs concepts that we find in other RDBMS databases.

Copy the Files

To restore the data from the above files, first stop the MySQL server.



Copy the ibdata files, and the database schema folder to some other directory. We will use this to restore our Mysql database. In this case, we’ll copy it to the /tmp directory. The name of the database scheme in this example is bugs.

Start the MySQL server:

On a related note, for a typical MySQL database backup and restore, you should use the mysqldump command.

Restore the Data

Next, restore the table data as explained below.

In the my.cnf configuration file, set the value of the following parameter to the current size of the ib_logfile0 file. In the following example, I’ve set it to 48M, as that is the size I see for the ib_logfile0 file when I did “ls -lh ib_logfile0″

Please note that both the ib_logfile0 and ib_logfile1 file size will be the same.

Copy the previous ibdata files to respective position, inside mysql data directory.

Create an empty folder inside data directory with the same name as the database schema name that you are trying to restore, and copy the previous .frm files inside this folder as shown below:

Finally, restart the MySQL server.

Now you have MySQL server running with the restored tables. Don’t forget to grant appropriate privileges for the clients to connect to the MySQL database.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.