August 27, 2014

A recovery trivia or how to recover from a lost ibdata1 file

A few day ago, a customer came to Percona needing to recover data. Basically, while doing a transfer from one SAN to another, something went wrong and they lost the ibdata1 file, where all the table meta-data is stored. Fortunately, they were running with innodb_file_per_table so the data itself was available. What they could provide us was:

  • all the tables ibd files (Nearly 200 of them, 40 GB of data)
  • the schema in sql format
  • No remote access

Their first question was “Is it possible to recover the data?”

The answer was yes since I was sure to at least be able to recover the data with the Innodb recovery tool of Aleksandr Kuzminsky a colleague on the Percona European team of consultants. In the past, I used this tool a few times and, although it is awesome to dig for data, it is time consuming and the perspective of recovering by hand nearly 200 tables was not really exciting. I needed something faster and more automatic so, after reading this post from Chris Calendar, I decided to use the following strategy.

  1. Start with a clean MySQL install so that Innodb tablespace id is set to 1
  2. Import the schema in MyISAM format to avoid screwing up the tablespace id
  3. Record the tablespace id of all the tables provided by the customer and but the info in a MyISAM table
  4. Advance the Innodb tablespace id by creating fake Innodb tables
  5. Once the Innodb tablespace id is minus one of a customer table
    1. alter the table definition from MyISAM to Innodb
    2. Discard the tablespace
    3. Replace the table ibd file with the one provided by the customer
    4. Import the tablespace
    5. At this point I should have access to the table but, although MySQL was not complaining, the data was not available. An “alter table tablename engine=InnoDB;” would solve the problem but then, the tablespace id would increase by one, annoying. I decided, also for portability, to do “alter table tablename engine=MyISAM;”
  6. Script all that

In order to get a decent disk space and good internet connectivity, I fired up an Ubuntu 10.04 EC2 instance, installed MySQL and provided the customer the credentials to upload their files. Then, I proceed.

Loading the schema

The MyISAM table recording the space ids of the tables

Filling up the table with table names and space ids
The challenge was now to fill up the table. It is fairly easy to find the space id in a .ibd file.

In the above example, the table space id is “00 11″ as stored in bytes 0×24 and 0×25 (actually, 0×22 and 0×23 are also for tablespace id but always 00 in this case). To load the data was just some Bash scripting:

Recovering the tables
With all this in place, recovery the data was just another Bash script:

I did a backup of /var/lib/mysql before the first run to make sure I could restart if needed, which of course, I did a few times. And that, recovered all except one table. For that one, I had to use the Innodb recovery tools but even then, I had a hard time. I pulled in Aleksandr and he basically reversed engineer the table structure and found that the schema the customer provided (likely from dev or staging) contained an extra column. With a modified schema, the recovery completed. The customer was able to download the tables in MyISAM format and then he just convert them back to InnoDB.

About Yves Trudeau

Yves is a Principal Consultant at Percona, specializing in technologies such as MySQL Cluster, Pacemaker and DRBD. He was previously a senior consultant for MySQL and Sun Microsystems. He holds a Ph.D. in Experimental Physics.

Comments

  1. Yves Trudeau says:

    Peter,

    As soon as a table space was recovered, I converted the table to MyISAM so the problem with the insert buffer is avoided. As for the LSN, I _think_ the import tablespace statement takes care of that.

  2. Peter Zaitsev says:

    Yves,

    Nice post. I assume though such connecting should have some limitations. How does Innodb “feel” about LSN which is much higher in the pages compared to current LSN ? Did you get any messages in error log files. Also you might have some data in the insert buffer stored in main tablespace and as such could cause corruption if it was not empty. I believe unpurged index entries could also cause the problems. As such I think this is good as recovery approach but I would not use such tables directly but rather dump them with mysqldump and load back to be on the safe side

  3. Vitaly Tskhovrebov says:

    Does this mean, that it’s better to store database with table per file option?

  4. Aleksandr Kuzminsky says:

    Justin
    ,
    Since 4.1 space id is stored at 0×22-0×25 position.
    Before it was first 4 bytes of the page header (See table “InnoDB Page Header” in http://www.mysqlperformanceblog.com/2011/05/13/connecting-orphaned-ibd-files/).

    More details you can find on http://forge.mysql.com/wiki/MySQL_Internals_InnoDB and http://www.slideshare.net/akuzminsky/recovery-of-lost-or-corrupted-inno-db-tablesmysql-uc-2010

  5. php forms says:

    You used unconventional approach, the result is simple and working well. Good article.

  6. Krishna says:

    Thanks, for very informative post.

    Thanks a lot.

  7. Justin Rovang says:

    Very nice article, something gritty and in depth!

    Is 0×22-0×25 consistent across versions of MySQL?
    Do you have a resource you could share about the metadata for the data files?

  8. Eva says:

    How can I restore a partitioned mysql InnoDB table from just the .ibd files of the form TableName#P#pname.ibd ? Chris Calendar’s article here http://www.chriscalender.com/?p=28 works for non-partitioned tables with a single .ibd file, but the “discard” and “import” steps result in a “storage engine doesn’t have this option” error for partitioned tables.

    Wes Filter in one of the comments at the link above suggests a manual procedure to import one partition at a time, but that did not work for me. If I try to follow his approach by creating a non-partitioned table, moving the first .ibd file renamed as TableName.ibd, and doing an import, the import succeeds, but there are zero rows in the table. The subsequent suggested step of “add back in the partition”, which I tried as “alter table TableName partition by range … (partition pname1 values less than (…) ENGINE=InnoDB)”shockingly replaces the TableName.ibd file (corresponding to the first partition) with a fresh TableName#P#pname1.ibd of a trivial size. I lost one partition’s worth of data trying this. I have about 150 partitions to recover.

    Any advice on how to recover the data from the .ibd files? Thanks.

  9. Thyagarajan says:

    Hi Yves,

    Wanted to drop you a thank you note for this brilliant article. I was having the same situation of recovering 112 innodb tables and this was the perfect solution that was available. It just worked!

    Regards,
    Dellit.

  10. @Thyagarajan, Awesome, I am happy it helped you

Speak Your Mind

*