If you are a frequent user of transactional database feature in MySQL, you must be familiar with InnoDB storage engine. Along with BDB, InnoDB provides transaction safe database within MySQL environment.

Nobody wants bad thing happens although it often does. Sometimes, power outage or internal system malfunction or even human error makes the database crashed or corrupted. This article will provide steps taken to recover corrupted InnoDB database based on real crash course experience within *NIX environment.

Identifying Corrupted Database

Yesterday, all thing went great on your website until you saw query error on some of your pages. If you are sure that you configured MySQL pretty well, you need to be cautious when you see this error message: ERROR 2006 (HY000): MySQL server has gone away. This can imply that your database is corrupted because MySQL thread dies even for simple query depicted below:

If this is a repeated event, you need to further investigate. First resource is your log. Assuming you followed standard installation, MySQL log file can be found at /var/log/mysqld.log.

Quick tips: this error may also be related with the following errors. You can do simple command “OPTIMIZE TABLE tablename” and check if you see any of the following errors returned:

• ERROR 1030 (HY000): Got error -1 from storage engine
  This means that data has not been recovered from previous database crash
• ERROR 1030 (HY000): Got error 28 from storage engine
  This means that there is no space left on device.

If you see the first error, you may need to read the rest of this article. For the second error, releasing some occupied spaces in the hard drive may suffice, followed by table optimization.

The Log Always Gives Hints

First thing first, get information from what’s written in the log. You can use UNIX command tail to output the last log records and analyze what the log says. If you see error message like “Ending processing because of a corrupt database page” (see detailed below), you may conclude that the database is indeed corrupt and it deserves recovery.

As you can see, the log also suggests a default solution, which is to check where corruption occurs by executing the command CHECK TABLE, and provides a link to the online resource containing more information about the necessary steps to be undertaken to help guide the recovery. However, if it doesn’t really help, you can check some recovery scenarios explained below.

Recovery Scenarios

Let’s divide recovery process into two groups: easy and intensive. The first one is when you have backup (either binary or sql dump) and the latter is when you don’t have any backup at all.

For the easy recovery with binary backup, you can just simply follow these steps:

1. Edit my.cnf (usually located in /etc/my.cnf) and add this line into [mysqld] section
   

   innodb_force_recovery=4

   With value set to 4, no background process is allowed to modify the database so that we can safely do the recovery process. For more information about this configuration directive, please refer to online resource provided by MySQL

2. Stop mysqld (e.g: /etc/init.d/mysqld stop) and move your backup to mysql data folder. Binary backup that has to be moved consists of ibdata, ib_logfile0, ib_logfile1, and your database directory.
3. Start mysqld and check all tables. Run some arbitrary queries. If there is no more error, recovery process has been successful.
   You can also check your log to make sure that the recovery process was completed.
4. Comment the line you made in #1, and restart mysqld
5. Optimize all tables and done

If you, by any chance, forgot to create backup, you should do more work as the following:

1. Edit my.cnf (usually located in /etc/my.cnf) and add this line into [mysqld] section
   

   innodb_force_recovery=4

2. Restart mysql (e.g: /etc/init.d/mysqld restart)
3. Your MySQL log should tell you which table is corrupt. You can dump the table using “SELECT … INTO OUTFILE …” from mysql console or using tool like mysqldump
   • Example using MySQL query (you also need to dump the schema later)
     

     mysql> SELECT * FROM corrupted_table INTO OUTFILE ‘/path/to/file’

   • Example using mysqldump
     

     root# mysqldump –opt -u uname -p dbname corrupted_tablename > /path/to/out.sql

4. Go back to my.cnf and comment the line you wrote from step #1
5. Go intoMySQL console and drop the corrupted table
   

   mysql> DROP TABLE corrupted_tablename;

6. Import data dump, for sql dump you can use:
   

   root# mysql -u uname -p < /path/to/out.sql

7. Go back into mysql console, check and optimize all tables
8. Done

If you still have problems, you can contact the guys in MySQL mailing lists to get some help from other who might have had similar experience.

Data Backup is Essential

The recovery steps explained in this article is not bulletproof. There can be circumstances when data is lost despite recovery attempts. By the time the data is loss, it’s already too late and cost a lot to the business. It is necessary to backup your MySQL data regularly. You can backup manually, for example using cron job that runs mysqldump every certain interval (daily, weekly), or also use a backup tool. One of the backup tools you can give a try is Handy Backup.

Use the coupon code BACKUP10 to get 10% discount for Handy Backup Professional with MySQL Backup Plugin when purchasing the product.