Earlier before we blogged about ways to recover corrupt MySQL database with TwinDB Data Recovery Toolkit. However learning curve of the toolkit is rather steep. It takes time to get familiar with InnoDB internals and the tool. And let’s be honest nobody gets prepared for data loss – people who prepare for disaster recovery take and verify backups. So when an accident happens precious time has to be spent on learning and scripting.

At TwinDB we automate everything. With data recovery automation is not straightforward, but we did it. In the post I will show how to recover corrupted MySQL database in the fastest possible way.

MySQL Data Recovery Portal

MySQL Data Recovery Portal is a web interface to TwinDB …

Sometimes corruption is not the true corruption. Corruption in compressed InnoDB tables may be a false positive.

Compressed InnoDB table may hit false checksum verification failure. The bug (http://bugs.mysql.com/bug.php?id=73689) reveals itself in the error log as follows:

2014-10-18 08:26:31 7fb114254700 InnoDB: Compressed page type (17855); stored checksum in field1 0; calculated checksums for field1: crc32 4289414559, innodb 0, none 3735928559; page LSN 24332465308430; page number (if stored to page already) 60727; space id (if stored to page already) 448
InnoDB: Page may be an index page where index id is 516

InnoDB complains that a stored checksum is zero. If you look closely it’s suspicious that calculated checksum is zero too.

Every InnoDB page stores a checksum in first four bytes. When InnoDB reads a page it compares the checksum, stored in …

The unDROP for InnoDB tool can be used to recover corrupt MySQL database. In this post we will show how to repair MySQL database if its files became corrupted and even innodb_force_recovery=6 doesn’t help.

The corruption of InnoDB tablespace may be caused by many reasons. A dying hard drive can write garbage, thus page checksum will be wrong. InnoDB then reports to the error log:

InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 4.

MySQL is well know for poor start-up script. A simple upgrade procedure may end up with two mysqld processes writing to the same tablespace. That leads to the corruption too. Sometimes power reset corrupts not only InnoDB files, but file system becomes unusable for the operating system.

InnoDB …

InnoDB provides no means to repair corrupted table space. Once a table got corrupt the only way to repair MySQL files is to start it with innodb_force_recovery={4,5,6} in hope that you can dump the table, so you can rebuild the table space from scratch. At least this is what the manual says. But let’s take a closer look at InnoDB corruption. In some case you can repair InnoDB table space much faster.

UPDATE: If corruption is in PRIMARY index check post Recover Corrupt MySQL Database

What InnoDB provides to repair tablespace corruption

InnoDB doesn’t let you repair the table space, but you can rebuild secondary indexes with ALTER TABLE DROP/ADD KEY. That may be very useful in case corruption malformed pages where secondary index is stored.

So, before starting a whole story with innodb_force_recovery, dump, drop, create and reload …