Recovering data from MySQL InnoDB files

Databases are usually not something that you play around with. Especially not when they are over 700G in size and can make-or-break a huge company. However, it might be clever to use hexedit on the data files once in while!

I recently found myself in a situation where some critical data in one of the databases was replaced with a common value. This is what happens if you do an update statement without the where clause, which is a pretty common mistake if you write the query directly on the command prompt. This particular database more than 700GB in total, however, the table in question was less than 10MB. The sheer size meant that a complete recovery from backup would take ages.

Since the number of records that were modified was way above what was possible to correct manually, we immediately began unpacking the backup. Those of you who have tried to unpack a tar.gz that should be around 700G when done knows that it will take time. A lot of time. Time we did not have since the shop was completely shut down until we could recover the lost data.

Luckily, we had converted this particular database to a file_per_table setup with InnoDB. With this option, MySQL creates one table description file and one data file for each table in the database instead of putting it all in a single file. To speed up the recovery, some ideas about replacing just the table/data file in the running instance with the backup was discusses. However, we did not know how MySQL would react and feared that it could potentially corrupt the hot standby slave.

Since we had to wait for the unpacking of the backup anyway, I surfed around a bit and found Chris On MySQL which describes how you can hack the data files to import a single table into MySQL from the InnoDB files. The procedure is more or less like:

1. Get hold of the table files you need.
2. Create a temporary database with the table you need. Get the exact CREATE TABLE statement and recreate the table.
3. Drop the tablespace by issuing ALTER TABLE temptable DISCARD TABLESPACE
4. Copy the table to MySQL’s data directory
5. Set innodb_force_recovery to a sane value between 1-6 where 1 is the least intrusive
6. Import the old data file into the database: ALTER TABLE temptable IMPORT TABLESPACE
7. Dump the necessary data from the recovered table(s).

Step number 5 will give you an error from the MySQL database engine: “ERROR 1030 (HY000): Got error -1 from storage engine“. In the error log for MySQL you will find the following:

081511 15:23:40  InnoDB: Error: tablespace id in file
'.blah.ibd' is 195, but in the InnoDB data dictionary it is 1.

Basically, this means that the table you have do not have the right tablespace id. Now, you can fix that in two ways:

1. Create enough tables in the temporary database so that that you reach the number below the tablespace id (tablespace id – 1) in the data file. The number will then be correct when you try to import the tablespace again.
2. Use hexedit to modified the header of the InnoDB file so that it thinks that it has ID number 1.

To shorten the recovery time, we fired up MySQL and waited for the right files to appear in the unzip stream. The idea was that the two files we were looking for would show up before some of the really large files. Surely enough, after a long time the two files showed up. We quickly fired up MySQL and tried method number 1 – creating a lot of tables to get the table space ID right. After setting innodb_force_recovery=1, we tried to import the tablespace. However, MySQL crashed repeatedly with error messages that was less than positive. At some point it started to throw out sig6 messages regardless of the operation even though we had incremented innodb_force_recovery to its highest value: 6.

Since we still were waiting for the unzip, we decided to give hexedit a try. After looking a bit at the numbers, we found which values that should be changed to 1. Loading MySQL with innodb_force_recovery set to 6 and importing the tablespace went file. Of course, a lot of complaining, but at least it did not crash. The best thing: I was able to dump the necessary data with mysqldump. Crisis avoided and recovery time shortened with an hour or so. It has been a while since hexedit saved my day!

Chris On MySQL and Alexandr have a write-up of how you do the hexedit modifications on their blogs. No point in repeating it here, you can see the InnoDB header format on Alexandr’s page. Thanks to both guys for providing invaluable insight.