Repairing a badly hurt MySQL database

| 2 Comments | 3 minutes read

One of our MySQL databases (version 5.1.49-1ubuntu8.1, under Ubuntu 10.10) had two corrupted tables, with the following errors when we tried to access their content:

ERROR 1033 (HY000): /usr/sbin/mysqld: Incorrect information in file: './database/sp_account.frm'
ERROR 1033 (HY000): /usr/sbin/mysqld: Incorrect information in file: './database/sp_screen.frm'

Since there were a lot of updates on the database, restoring a backup was not an option, so we tried the standard recovery tools provided by MySQL:

Unfortunately, these two procedures ended up with no successful results 🙁

So we dug more on the Internet, and we found this very interesting article : http://blogs.skysql.com/2011/05/innodb-data-recovery-success-story.html [internet archive]

In this article, the authors refers to the Percona InnoDB Data Recovery Tool which allows recovering lost data by extracting rows from MySQL raw files.

We strictly followed the documentation, and finally managed to recover our data in order to restore them in our corrupted tables.

Here are the steps that we followed. First, we downloaded the source code of the tool onto our MySQL server; then, we built it.

The next step consists of extracting the pages using this command:

page_parser -5 -f /var/lib/mysql/ibdata1

It creates a directory which contains several subdirectories with a lot of files (in our case “pages-1328713071/FIL_PAGE_INDEX/”).

Then, the tricky part is to enable the InnoDB Tablespace Monitor by using this MySQL command:

CREATE TABLE innodb_table_monitor (id int) ENGINE=InnoDB;

With this monitor enabled, we had to look at the MySQL error log (/var/log/mysql/error.log) in order to locate the page index of our corrupted table:

TABLE: name database/sp_account, id 0 85, columns 23, indexes 2, appr.rows 220
...
INDEX: name PRIMARY, id 0 215, fields 1/22, uniq 1, type 3
root page 401, appr.key vals 220, leaf pages 3, size pages 4
...

In this output, it shows that we have to look at the page located in the 0-215 subdirectory of our extracted pages (ie pages-1328713071/FIL_PAGE_INDEX/0-215/).

Then, we had to merge the pages files into one:

find pages-1328713071/FIL_PAGE_INDEX/0-215/ -type f -name '*.page' | sort -n | xargs cat > merged_file

Then, the tool-set needs the definition of the corrupted table.  For that, we used the provided Perl script which creates a .h file:

./create_defs.pl --host=localhost --user=XXX --password=YYY --db=database --table=sp_account > include/table_defs.h

At this point, we hit a bug, because the “BIT” SQL type was not managed by the Perl script (so, we created and submitted a patch to the authors).

Then, when the .h was generated, we had to rebuild the tool-set by firing a “make” command.

The build process compiles a “constraints_parser” executable file. This program will be used to decode the MySQL data file and to print the data as text output:

./constraints_parser -5 -f merged_file > merged_file_data.txt

The file contains the lost data from our table:

...
sp_screen       18      2       11      62      64      63      "#000000"       NULL    "2011-11-03 18:30:25"   0    0    0    "2011-11-03 18:49:58"   "page7" 0       6
sp_screen       19      1       11      65      67      66      "#000000"       NULL    "2011-11-03 18:53:52"   0    0    0    "2011-11-03 18:54:17"   "page8" 0       7
...

And we just had to reimport these data into our table!!

Hurray! Cheers to the Percona guys, you saved my day!!

Nicolas Deverge Author: Nicolas Deverge

Artisan-développeur tendance maniaque de l'organisation

Ma phrase : "T'as pensé à déplacer ton Post-It sur le Scrum board ?"
Ma définition d'ekito : "Un melting pot de personnalités et de compétences"
Mes hashtags : #agile #leanstartup #playframework #mqtt #arduino"

Like it?  Share  it!

Share Button

2 Comments

  1. Hello. Great job. I did not imagine this. This is a excellent story. Thanks!

  2. Thanks man! You solved my problem. I just have converted to innodb and works. Thanks!

What do  You  think? Write a comment!

Leave a Reply

Required fields are marked *.


CommentLuv badge

This site uses Akismet to reduce spam. Learn how your comment data is processed.