1. Recovering A Corrupt MySQL Database

Recovering A Corrupt MySQL Database

Corrupt Database

UPDATE: We announced online data recovery from corrupt databases.

Check it out on https://recovery.twindb.com/

The unDROP for InnoDB tool can be used to recover corrupt MySQL databases. In this post, we will show how to repair a MySQL database if its files got 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:

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

InnoDB is very strict when it works with pages. If checksum doesn’t match or some field in the header carries and unexpected value, InnoDB wisely prefers to crash to avoid further corruption.

The manual suggests to start MySQL with innodb_force_recovery option. The purpose of this option is to let user dump their data. There are no means to repair the tablespace. Users must drop the tablespace, create a new one and load the data back.

innodb_force_recovery accepts values from one to six. The higher value, the more tests InnoDB disables.

In this post, we will assume that MySQL cannot start even with innodb_force_recovery=6.

The recovery toolkit works directly with the InnoDB files, it can read records from the InnoDB page. If some part of the page is damaged, it will just skip that piece and continue to read the records further in the page.

So, let’s corrupt some InnoDB file and recover the table.

InnoDB Corruption

For the sake of simplicity we will overwrite part of a .ibd file in the area with user data.
In real life, the corruption may be at any place of index PRIMARY.
In the middle of the PRIMARY index of the sakila.actor table, we will rewrite the data with 128 ‘A’ characters:

Corrupted InnoDB Table Crashes MySQL

When MySQL reads a page with the user data, checksum is wrong and the server crashes.

Before the crash, MySQL writes to the error log what exactly went wrong and dumps the faulty page:

Recovering InnoDB Table Corruption

When you see corruption in the InnoDB tablespace. the first thing to try is to start MySQL with the innodb_force_recovery option. It makes sense to try all values starting from one to six.

We assume that MySQL doesn’t start even with innodb_force_recovery=6 or it does start, but any SELECT crashes it.

The recovery plan is the following:

  • Split corrupted InnoDB tablespace into pages; sort the pages by type and index_id.
  • Fetch records from the PRIMARY index of the table.
  • DROP the corrupted table and create a new one.
  • Load records back into MySQL.

We would need to parse two tablespaces: ibdata1 and actor.ibd (since option innodb_file_per_table=ON). The InnoDB dictionary is stored in ibdata1, we need it to know the index_id of the PRIMARY index of the sakila.actor table.

Split Corrupted InnoDB Tablespace

Now actor.ibd‘s turn

Recover InnoDB Dictionary

We need to know the index_id of the PRIMARY index of the sakila.actor table. Learn more about InnoDB dictionary. Now, we’ll just get index_id of sakila.actor:

So, the index_id of the PRIMARY index of the sakila.actor table is 15, the fifth column in the dump.

Recover Records From The PRIMARY Index Of The Table

c_parser reads InnoDB pages, matches them with a given table structure, and dumps records in a tab-separated values format.

Opposite to InnoDB, when c_parser hits corrupted area it skips it and continues reading the page.

Let’s read the records from index_id 15, which is the PRIMARY index according to the dictionary.

We have identified some valid records, but certainly there’s also some “garbage”. Pay attention to the recovered records before “Nick Wahlberg”. Definitely, there should be records of “Penelope Guiness”, since we haven’t overwritten that data in the actor.ibd file.

Filters

We can improve recovery quality by applying filters on possible values of certain fields. There are 200 records on the original table, but the first two “garbage” records have some weird identifiers (30064 and 19713). We know that the actor identifier should be in the range of [1..300]. Therefore, we tell the parser to match that condition. For this purpose, we add a hint in the comments of the actor.sql file that defines actor table. This comment should be in a special format for parser to recognize it. Listing of the part of actor.sql file (note a comma after the comment!):

After applying the filter, recognized records look much better:

You see, the record for “Penelope Guiness” is already here. The only issue remaining is the invalid record 6-“AAAAA”-“AAAAAAAAA”. This happens because the record appeared to have actor_id of 6, which is what we expected. Ideally, the dump shouldn’t have any junk records, so you may try to add more filters on other fields.
Or, we can delete these records in the database manually later.

DROP Corrupted Tables And Create New One

As soon as we have dumps of all tables we need to create a new instance of MySQL.

If it’s a single table corruption it makes sense to try innodb_force_recovery=6 to DROP the table.

If MySQL can’t even start, try to move the corrupt actor.ibd elsewhere. In the recovery mode, after DROP TABLE actor MySQL removes the record from the dictionary. Remove actor.frm if it still remains.

The point is to get a clean up & running MySQL, ready to import the table dump.

Once MySQL is ready create an empty table actor:

Load Records Back Into MySQL

Then, we load information from the recovered dump:

The final step is to know how much data we’ve lost due to corruption.
The c_parser provides count of expected and actually found records.
In the beginning of each page it gives number of expected records:

Which means 200 records are expected, but the list of records is broken (thus, Records list: Invalid).

In the end of each page it gives a summary of what was actually found

Have a question? Ask the experts!

Previous Post Next Post