MySQL 5.0: Recovering Crashed/Corrupted InnoDB Database

in

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:

MySQL error code 2006<

If this is repeated event, you need to further investigation. 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 is related with the following errors. You can do simple command “OPTIMIZE TABLE tablename” and check if you see:

  • 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 does corrupt and it deserves recovery.

Log hint for a corrupted innodb database<

As you can see that the log also suggest solution for checking where corruption occurs and provide links to online resource containing more information about steps to recovery. However, if it doesn’t really help, you can check some recovery scenario 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 queries. If there is no more error, recovery process was successful.
    You can also check your log to make sure that the recovery process was completed.InnoDB recovery log<
  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< and they (probably) will help you.