How to Recover InnoDB Corruption for MySQL

19 April 2015 2:20 PM Database, Linux ,

How to Recover InnoDB Corruption for MySQL

Assume you’re running MySQL with Innodb tables and you’ve got crappy hardware, driver bug, kernel bug, unlucky power failure or some rare MySQL bug and some pages in Innodb tablespace got corrupted. In such cases Innodb will typically print something like this:

InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 7.
InnoDB: You may have to recover from a backup.
080703 23:46:16 InnoDB: Page dump in ascii and hex (16384 bytes):
… A LOT OF HEX AND BINARY DATA…
080703 23:46:16 InnoDB: Page checksum 587461377, prior-to-4.0.14-form checksum 772331632
InnoDB: stored checksum 2287785129, prior-to-4.0.14-form stored checksum 772331632
InnoDB: Page lsn 24 1487506025, low 4 bytes of lsn at page end 1487506025
InnoDB: Page number (if stored to page already) 7,
InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 6353
InnoDB: Page may be an index page where index id is 0 25556
InnoDB: (index “PRIMARY” of table “test”.”test”)
InnoDB: Database page corruption on disk or a failed

and crash with assertion failure.
So what can you do to recover such a table ?

if errors persist with innodb_force_recovery in place, then you are going to have to proceed with a dump and restore.

Typically, this will require ‘innodb_force_recovery’ to be configured to 3 or 4, and as stated previously, if any higher is required it has an increased chance of corrupting the data you are attempting to recover.

Once you have restarted the MySQL server with ‘innodb_force_recovery’ in place, you have to dump the corrupted databases.

Single Database

mysqldump database_name > database_name.sql

All Databases

mysqldump --all-databases > all_the_bases.sql

Once you have the corrupt databases dumped successfully, stop MySQL completely and move the ib* files from /var/lib/mysql/, out of the directory. **Ensure that you do not delete them, for just-in-case scenarios.**

mkdir /var/lib/old_innodb_data/;
mv /var/lib/mysql/ib* /var/lib/old_innodb_data;

Once the ib* files have been moved from /var/lib/mysql/, remove ‘innodb_force_recovery’ from /etc/my.cnf and start MySQL back up. Check the logs to ensure that no errors were experienced upon starting the service.

If MySQL starts properly, dump all of the recovered data back into their respective database, or if you dumped ALL of the MySQL databases then just:

mysql < all_the_bases.sql

Once this has been completed, run a database repair to simply ensure that everything is intact:

mysqlcheck --all-databases --repair

I would suggest a final MySQL restart and following the logs to be thorough, but at this point the data should be recovered and issues resolved.