How to Recover MySQL Data from InnoDB

Unfortunately sometimes you need to recover MySQL data from ibdata1. There are many reasons why you might get corrupted InnoDB files that cannot automatically be restored by the mysqld daemon.

Scenario

We assume that your scenario is like the following:

1. You have a backup of your ibdata1, ib_logfile0 and ib_logfile1
2. You also have a backup of your database folder with .frm files
3. You would like to restore this backup into a MySQL server that's already in production

Before we start, just one piece of advice: Stop crying, your data isn't lost.

Restore the Data Backup You Have

First of all, restore your data on another MySQL server so as not to interrupt the services running in your normal production environment. Restore the data into the normal MySQL data directory. For our scenario we use /var/lib/mysql.

Be careful to get the right permissions and owners on all your data files. Your MySQL user should own the files and the group should also be assigned to MySQL.

Before you continue you need to find the size of your InnoDB log files. Simply run ls -l to find this. The output will look something like:

-rw-rw---- 1 mysql mysql 5242880 Jun 25 11:30 ib_logfile0
-rw-rw---- 1 mysql mysql 5242880 Jun 25 11:30 ib_logfile1

Start Up MySQL in Rescue Mode

Some simple steps will start up the MySQL daemon in rescue mode:

1. From your Unix shell, switch to the mysql user:

   su mysql

2. Start up the mysqld process with the log file size and innodb_force_recovery as parameters:

   /usr/sbin/mysqld --innodb_log_file_size=5242880 --innodb_force_recovery=6

If everything goes fine you should get output like this:

InnoDB: The user has set SRV_FORCE_NO_LOG_REDO on
InnoDB: Skipping log redo
070625 11:59:36 InnoDB: Started; log sequence number 0 0
InnoDB: !!! innodb_force_recovery is set to 6 !!!
070625 11:59:36 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.0.18' socket: '/var/lib/mysql/mysql.sock' port: 3306 SUSE MySQL

Get Your Data

The last simple but most important step is to get your data:

1. Open a new shell to the server where your MySQL daemon is running in recovery mode.

2. Run a normal mysqldump of your database:

   mysqldump -u root -p database > database.sql

If you get a message like the following, you have corrupted InnoDB log files:

Got error: 1146: Table 'database.table' doesn't exist when using LOCK TABLES

What you can do to resolve this is keep the ib_logfile0 file from your most current backup while restoring all the other files from an older backup. This isn't a fail-proof solution, but worth a try.

Restore Your Data

Now you can copy your SQL dump to your production server and simply restore the data from your MySQL dump file:

mysql -u root -p database < database.sql