1. Recover Corrupt MySQL Database

Recover Corrupt MySQL Database

Corrupt Database

UPDATE: We announced online data recovery from corrupt databases.

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

UPDATE (01/01/2017): We stopped further development of undrop-for-innodb and do not support its open source versions.

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:

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 is very strict when it works with pages. If checksum doesn’t match or some field in the header carries 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. The user must drop the tablespace, create 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 MySQL can not start even with innodb_force_recovery=6.

The recovery toolkit works directly with 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 sake of simplicity we will overwrite a part of .ibd file in the area with user data.
In real life the corruption may be at any place of index PRIMARY.
At the middle of the PRIMARY index of table sakila.actor we will rewrite the data with 128 characters ‘A’:

Corrupted InnoDB table crashes MySQL

When MySQL reads a page with user data check sum is wrong and the server crashes.

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

Recovering InnoDB Table Corruption

When you see a corruption in the InnoDB tablespace the first thing to try is to start MySQL with 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 starts, but any SELECT crashes it.

The recovery plan is following

  • Split corrupted InnoDB tablespace into pages; sort the pages by type and index_id
  • Fetch records from PRIMARY index of the table
  • DROP corrupted table and create 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 index_id of the PRIMARY index of table sakila.actor.

Split corrupted InnoDB tablespace

Now actor.ibd‘s turn

Recover InnoDB dictionary

We need to know index_id of the PRIMARY index of table sakila.actor. See more about InnoDB dictionary. Now we will just get index_id of sakila.actor:

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

Recover records from PRIMARY index of the table

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

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

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

We have identify some valid records, but certainly there is also some “garbage”. Pay attention at the recovered records before “Nick Wahlberg”. Definitely there should be a records of “Penelope Guiness”, since we have not overwritten that data in the actor.ibd file.

Filters

We can improve the 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 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 actor.sql file that defines actor table. This comment should be in special format for parser to recognize them. Listing of the part of actor.sql file(note a comma after the comment!):

After applying the filter, recognized records have much better look:

You see, the record for “Penelope Guiness” is already here. The only issue remained – invalid record 6-“AAAAA”-“AAAAAAAAA”. This happens because the record appeared to have actor_id of 6, that corresponds to our expectations. Ideally, the dump must have not junk records, so you may try to add more filters on other fields.
Or, we can delete this records in the database manually later.

DROP corrupted table and create new one

As soon as we have dumps of all tables we need to create 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 will remove a record from the dictionary. Remove actor.frm if it still remains.

The point is to get 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 will load information from recovered dump:

The final step is to know how much data we’ve lost due to the corruption.
The c_parser provides count of expected and actually found records.
In the beginning of each page it give 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
  • Rustem Garifullin

    i use this instruction. in result sql file i obtain errors like

    — #####CannotOpen_./0000000000002021.page;
    — print_field_value_with_external(): open(): No such file or directory

    How i can fix it. these files don’t exist after stream_parser.

    Some data respored but not at all.

  • Santiago

    i want the program! noww please

  • Jason Kerner

    If we have the page files exracted from ibdata1, whats the next step to try and parse those back into a usable file to re-import? Can it be scripted?