MySql Innodb corruption recovery

Question

I have a MySql database, just containing Innodb tables, that seems to have corrupted itself.

It replicates to another server and it has corrupted that one too!

The symptom is that I can restart the server but as soon as I try to connect to it, it crashes.

The log file talks about a possible index page corruption to a specific table and that fits in terms of the table being accessed when the problem occurred.

Following the InnoDB recovery guidelines in the MySQL manual, I have set the innodb_force_recovery option to 6 which lets me restart the database and dump the tables.

Anything less than 6 and it will not stay up.

My questions are:

• How do I know the extent of the innodb corruption?
• Is the only safe option to recreate the innodb files from scratch (I assume via a re-install) or is there a smarter way to do this?
• Can I trust the table dumps or should I go back to the most recent backup (also a table dump) and accept the data loss?
• If it is an index page corruption, can I rebuild the primary index of the affected table and then restart the DB without the innodb_force_recovery option?

Answer 1

In general case once InnoDB tablespace is corrupt you need to re-create tablespaces from scratch. This is the safest way especially if you're not familiar with InnoDB files/format.

In some cases InnoDB writes to the error log file information about corrupted page number and index it belongs to. For example,

InnoDB: Page may be an index page where index id is 2575
InnoDB: (index "idx_actor_last_name" of table "sakila"."actor")
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 4.

InnoDB crashed at that page but it doesn't mean other pages are OK. To estimate how badly a tablespace is corrupt innochecksum might help. Note however it's often misleading. innochecksum checks individual pages, but not the tablespace structure. For example, all-zero page is valid in innochecksum's opinion while the page should have contained some data.

To check that all tablespaces are good you can read data from all indexes (PRIMARY and all secondary) of all tables. For example, to check sakila.actor you need two selects:

SELECT * FROM actor FORCE INDEX ('PRIMARY')
SELECT * FROM actor FORCE INDEX ('idx_actor_last_name')

If you suspect that only a secondary index is corrupt you may drop and re-create it. I describe this case in https://twindb.com/repair-corrupted-innodb-table-with-corruption-in-secondary-index/ .

If PRIMARY index is corrupt you may want to dump the table drop it and reload again. I.e. you don't reload whole database, just one table.