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:
- REPAIR TABLE : http://dev.mysql.com/doc/refman/5.1/en/repair-table.html
- InnoDB recovery : http://dev.mysql.com/doc/refman/5.1/en/forcing-innodb-recovery.html
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!!Google+