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.

Share this post

Comments (13)

  • Yves Trudeau


    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.

    June 3, 2011 at 12:00 am
  • Peter Zaitsev


    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

    June 3, 2011 at 12:00 am
  • Vitaly Tskhovrebov

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

    June 3, 2011 at 12:00 am
  • Aleksandr Kuzminsky

    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

    June 3, 2011 at 12:00 am
  • php forms

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

    June 3, 2011 at 12:00 am
  • Krishna

    Thanks, for very informative post.

    Thanks a lot.

    June 3, 2011 at 12:00 am
  • Justin Rovang

    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?

    June 3, 2011 at 12:00 am
  • Eva

    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.

    September 1, 2012 at 5:59 pm
  • Thyagarajan

    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!


    November 26, 2013 at 6:50 am
  • Yves Trudeau

    @Thyagarajan, Awesome, I am happy it helped you

    November 26, 2013 at 9:59 am
  • Nazri

    MySQL dies and I’ve moved the ibdata1 and other 2 ib* to other location. After restarting the mysql, the database inaccessible. any idea how to resolve this issue?


    April 21, 2015 at 3:29 pm
  • mouhcine

    hi Yves

    apparently im now in the same situation… i will coment you the scenario:

    we have got space issue so after some changes ibdata1 wa either corrupted or just changed .. so basically the situation is like we dont have ibdata1 and yes we have innodb_file_per_table running…

    in mysql i can visualize all the tables … but in mysql logs says tables dosent exist…

    so do you think there is a hope?

    best regards

    June 9, 2015 at 10:41 am
  • Dwarakanath

    Hi, I recently got my mysql db corrupted while trying to upgrade to a newer version.

    For the old version all I did was removing the windows services using mysqld –remove . after that I’m not able to re-create the services and restart mysql with my db. Is there anyway you can help me recover the DDLs? I don’t care about data but schema, definitely yes.


    September 17, 2015 at 5:15 am

Comments are closed.

Use Percona's Technical Forum to ask an