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 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.

 

Log hint for a corrupted innodb database

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.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 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.

10 thoughts on “MySQL 5.0: Recovering Crashed/Corrupted InnoDB Database

  1. matt

    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!!

    Reply
  2. Daniel Q

    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

    Reply
  3. aditya

    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 !!!!

    Reply
  4. Viki

    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

    Reply
  5. Smily Myra

    Thanks for the information, got the same error twice in a day , and able to fix because of you 🙂

    Reply
  6. TimothyOntiveros

    If I think, it is quite possible that the path to the datadir is not changed. And if you Engin default InnoDB then copy files frm will not save you, because they contain only a description of the structure. The data itself is in ibdata, and they unfortunately lost.
    Read more about your problem here: http://www.filerepairforum.com/forum/microsoft/microsoft-aa/sql-server
    Try some data recovery program for MySQL, the benefit of the network a lot of them. Try this for example (this program is not free, but I do not trust free software, if you do not like it can look others in the network): http://www.mysql.recoverytoolbox.com/
    Good luck I hope my answer was useful to you!

    Reply
  7. prabhu

    Hi,
    When i tried to restart with innodb_force_recovery it doesn’t work , again show error code 107 server could started , stopped unexpectedly.Help plz

    Reply

Leave a Reply

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