MySQL 5.0: Recovering Crashed/Corrupted InnoDB Database

restore crashed InnoDBIf 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.

7 Responses to “MySQL 5.0: Recovering Crashed/Corrupted InnoDB Database”


  • A big thank you for this information. Saved my night! :) Had a corrupt table that couldn’t be deleted.

  • @Patrick
    It’s glad to know that this information helps you :)

  • Thanks very much for these tips! Saved my database.

  • thanks man! this really saved my bacon tonight. i was starting to sweat from the thought of loosing a 2 million record database.

    my mysql database had crashed and wouldn’t restart. Here is the exact process i used and it works a treat.

    0) update /etc/my.conf set force recovery to level 6 and start mysqld
    1) export db tables sql text via phpmyadmin (approx 190MB & Added DROP TABLE/VIEW/PROCEDURE/FUNCTION on export)
    2) stop mysql server
    3) move ibdata and idblog files to from /var/lib/mysql to a temp directory
    4) update /etc/my.conf and set force recovery to ’4′
    5) start mysqld
    6) stop mysqld
    7) update /etc/my.conf and disable line using ‘#’ set force recovery to 4
    8) start mysqld
    9) login to db via phpmyadmin
    10) increase phpmyadmin script upload limits in php.ini
    11) restart apache
    12) import full 190MB sql dump (took 3 timeouts but worked eventually after about 15 minutes)

    again nice one!!

  • hi, matt i have the same problem that you….

    i’m running in windows 7

    i try to follow your steps but i don’t have phpmyadmin instead of that i used mysql administrator

    i try to make step 1 but i cant access to the database because the mysql service its down

    do you know how can i get in to mysqld and run step 1

  • hi mate !
    I tried the steps above but everytime I try to backup using command prompt
    mysqldump -uroot -padmin dbname > bkup.sql it shows error >>
    Error 2013 : lost connection to mysql server during dumping table “menuitem” at row 12144

    I tried all the tricks by using innodb_force_recover =1 … then 2 then 3 upto 6 but all showed same error . It actually crashes on menuitem table and my crucial data is actually inside this table only.

    Log file has these entries >>

    InnoDB: Database page corruption on disk or a failed
    InnoDB: file read of page 1376.
    InnoDB: You may have to recover from a backup.
    130826 23:11:17 InnoDB: Page dump in ascii and hex (16384 bytes):
    len 16384; hex 9a368dd4000005600000055dffffffff000000000968b99c45bf000000000000000000000000001539e98057308200ad394b00050000005400000000000000000000000000000000016b000025000000000000008088d40000000000f90201000200246 @ ,@ ®Gáz ®+@-² p7ñ/Õ3ã1/(f%²”þ J – â . z Æ ^ ª ö B Ž c)u h® ;InnoDB: End of page dump
    130826 23:11:17 InnoDB: Page checksum 3685904661, prior-to-4.0.14-form checksum 3451815378
    InnoDB: stored checksum 2587266516, prior-to-4.0.14-form stored checksum 695534868
    InnoDB: Page lsn 0 157858204, low 4 bytes of lsn at page end 157855250
    InnoDB: Page number (if stored to page already) 1376,
    InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 0
    InnoDB: Page may be an index page where index id is 0 363
    InnoDB: (index PRIMARY of table restaurent/menuitem)
    InnoDB: Database page corruption on disk or a failed
    InnoDB: file read of page 1376.
    InnoDB: You may have to recover from a backup.

    PLEASE SUGGEST !!!!

  • I need urgent help, I made big mistake, mysql was not starting so I just took data / database name folder back up and nothing else. reinstalled mysql now when I’m copy paster DATA/DATABASE folder innodb tables DOES NOT EXIT. I do not have old backup / copy of all other files such as ibdata1, ib_logfile0 etc; I just have data folder files. Please let me know how do I recover all lost data, thanks in advance for help

Leave a Reply

CAPTCHA Image
*