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.
- Start with a clean MySQL install so that Innodb tablespace id is set to 1
- Import the schema in MyISAM format to avoid screwing up the tablespace id
- Record the tablespace id of all the tables provided by the customer and but the info in a MyISAM table
- Advance the Innodb tablespace id by creating fake Innodb tables
- Once the Innodb tablespace id is minus one of a customer table
- alter the table definition from MyISAM to Innodb
- Discard the tablespace
- Replace the table ibd file with the one provided by the customer
- Import the tablespace
- 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;”
- 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
1 2 | root@domU:/mnt/tables# perl -p -i -e 's/ENGINE=InnoDB/ENGINE=MyISAM/g' schema.sql root@domU:/mnt/tables# cat schema.sql | mysql -u root test |
The MyISAM table recording the space ids of the tables
1 2 3 4 5 6 7 8 9 | mysql> show create table test.tablesG *************************** 1. row *************************** Table: tables Create Table: CREATE TABLE `tables` ( `name` varchar(100) DEFAULT NULL, `spaceid` int(11) NOT NULL, PRIMARY KEY (`spaceid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 1 row in set (0.00 sec) |
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.
1 2 3 4 5 | #root@domU:/mnt/tables# hexdump profile_data.ibd -C | head -4 #00000000 62 bd c3 19 00 00 00 00 ff ff ff ff ff ff ff ff |b...............| #00000010 00 00 00 54 86 9a 91 93 45 bf 00 00 00 00 00 00 |...T....E.......| #00000020 00 00 00 00 00 11 00 00 00 11 80 39 00 00 00 02 |...........9....| #00000030 4f 00 00 02 47 c0 00 00 00 00 00 00 00 13 00 00 |O...G...........| |
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:
1 | # for t in `ls *.ibd`; do tname=`echo $t | cut -d'.' -f1`; spaceid=`hexdump -C $t | head -3 | tail -1 | awk '{print $6$7}'`; mysql -u root -e "insert into tables (name,spaceid) values ('$tname',conv('$spaceid',16,10))" test; done |
Recovering the tables
With all this in place, recovery the data was just another Bash script:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 | #!/bin/bash CURRENT_ID=1 mysql -u root "create database if not exists filler;" while [ 1 ] do CURRENT_SPACEID=`mysql -u root -B -N -e "select spaceid from test.tables where spaceid >= $CURRENT_ID order by spaceid limit 1;"` if [ "a$CURRENT_SPACEID" == "a" ]; then exit fi CURRENT_TABLENAME=`mysql -u root -B -N -e "select name from test.tables where spaceid = $CURRENT_SPACEID;"` echo "doing $CURRENT_TABLENAME $CURRENT_SPACEID" # DO WE NEED TO SKIP SOME ID while [ "$CURRENT_ID" -lt "$CURRENT_SPACEID" ] do echo "skipping spaceID $CURRENT_ID" mysql -u root -e "create table space_$CURRENT_ID (id int) engine=Innodb;" filler let CURRENT_ID+=1 done exit echo "creating the table" mysql -u root -e "create table recover.$CURRENT_TABLENAME like test.$CURRENT_TABLENAME;" sleep 1 echo "switching to Innodb " mysql -u root -e "alter table recover.$CURRENT_TABLENAME engine=Innodb;" sleep 1 echo "discarding tablespace " mysql -u root -e "alter table recover.$CURRENT_TABLENAME discard tablespace;" sleep 1 echo "moving the datafile in place" cp /mnt/tables/$CURRENT_TABLENAME.ibd /var/lib/mysql/recover/ chown mysql.mysql /var/lib/mysql/recover/$CURRENT_TABLENAME.ibd sleep 1 echo "importing tablespace" mysql -u root -e "alter table recover.$CURRENT_TABLENAME import tablespace;" sleep 1 echo "switching to MyISAM" mysql -u root -e "alter table recover.$CURRENT_TABLENAME engine=MyISAM;flush tables;" sleep 1 let CURRENT_ID+=1 done |
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.
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.
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
Does this mean, that it’s better to store database with table per file option?
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
You used unconventional approach, the result is simple and working well. Good article.
Thanks, for very informative post.
Thanks a lot.
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?
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.
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.
@Thyagarajan, Awesome, I am happy it helped you
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?
Thanks.
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
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.
Regards,
Dwarakanath