Recover orphan innodb database from ibd file

Server Management Service

Are you trying to recover the orphan InnoDB database from the ibd file?

You can follow the steps detailed in this guide.

If you're a MySQL DBA, you're probably taking backups of your data. Imagine a situation: you lost all of your data, but you do have the .ibd files and the rest of the "default" files associated with InnoDB (ibdata1 and the log files assuming you ran InnoDB).

How do you recover your data? Is it even possible?

Here at Ibmi Media, as part of our Server Management Services, we regularly help our Customers to perform InnoDB related queries.

In this context, we shall look into how to recover the orphan InnoDB database from the .ibd file.

What triggers Orphan InnoDB database incident ?

Orphan InnoDB database incident happens:

1. If a user accidentally removes the ibdata1 file. (mostly in /var/lib/mysql/ibdata1).

2. If ibdata file is corrupted.

3. Enabling "innodb_file_per_table" in my.cnf beforehand

4. The database folder (/var/lib/mysql/database_name/) and files inside the folder remains untouched.

By default, if the "innodb_file_per_table" is enabled then the InnoDB database is fully dependent on the ibdata1 file.

Note: Losing ibdata1 without "innodb_file_per_table" enabled basically means that we have lost everything.

When "innodb_file_per_table" is enabled in my.cnf, we can see 2 files format in the database folder:

.frm file (internal data dictionary) and .ibd file (database structure information)

If ibdata1 is accidentally removed then the InnoDB database will refuse to start due to its inability to refer to the correct data block in ibdata.

So we need to make the .ibd file and ibdata1 sync again.

How to recover orphan InnoDB database from ibd file ?

Here are the steps to sync the .ibd file and ibdata1:

1. First, we need to recreate the table structure exactly the same as the corrupted one, which is stored in the .frm file.

We need to restart the MySQL service to recreate ibdata1, and then copy the database folder to another directory as a backup:

$ cp -axvRfp /var/lib/mysql/database_name /home/backup/

2. Next, we login to MySQL using the terminal by running the below command:

# mysql -uroot -p

3. Then we create a dummy database with the same name:

mysql> create database database_name;
Query OK, 1 row affected (0.00 sec)

4. After that, we connect to the dummy database:

mysql> use database_name

5. Then we create a dummy table with the same name as the corrupted table, with random structure, and enable InnoDB engine:

mysql> create table table_name (id int) engine=innodb;
Query OK, 0 rows affected (0.01 sec)

6. We describe the table to check its structure.

mysql> desc table_name;

7. Now, we stop the MySQL service, and copy the .frm file from the backup directory to /var/lib/mysql/database_name/ to replace the .frm file inside. For that, we run the below commands:

/etc/init.d/mysql stop
cp -ap /home/backup/database_name/table_name.frm /var/lib/mysql/database_name/

8. We start the MySQL service, connect to the database, and flush tables:

/etc/init.d/mysql start
mysql -uroot -p
mysql> use database_name
Database changed
flush tables;

9. Then we describe table again and we will be able to see the losing structure of the table:

desc table_name;

10. However, the table cannot work without a proper CREATE command by identifying each structure. So to review the CREATE query of the corrupted table, we run the below command:

show create table table_name;

11. The query can now be referring to easily.

So we COPY the create table line, drop the dummy table and recreate the table with the recovered structure query:

mysql > drop table table_name;

12. Now to recover the ibd file, we need a innodb recovery tools from here. We download the tools, extract it, and install it:

cd /usr/local
wget http://www.ipfusions.com/setup/percona-data-recovery-tool-for-innodb-0.5.tar.gz
tar xvfz percona-data-recovery-tool-for-innodb-0.5.tar.gz
cd percona-data-recovery-tool-for-innodb-0.5
cd mysql-source
./configure
cd ..
make

13. We then stop mysql service again, copy the .ibd file from backup directory to /var/lib/mysql/database_name/ and replace the existing dummy .ibd file:

/etc/init.d/mysql stop
cp -ap /home/backup/database_name/table_name.ibd /var/lib/mysql/database_name/

14. We run the ibdconnect tools of percona by referring to the query below:

[root@/]# cd /usr/local/percona-data-recovery-tool-for-innodb-0.5/
[root@localhost percona-data-recovery-tool-for-innodb-0.5]# pwd
/usr/local/percona-data-recovery-tool-for-innodb-0.5
[root@localhost percona-data-recovery-tool-for-innodb-0.5]# ls
check_data.c ibdconnect INSTALL print_data.c
constraints_parser ibdconnect.c lib split_dump.pl
constraints_parser.c include Makefile tables_dict.c
create_defs.pl incrementalupdate.c mysql-source
docs innochecksum page_parser
fetch_data.sh innochecksum.c page_parser.c
-o full location of your ibdata file
-f full location of your .ibd file
-d fullname of database name
-t fullname of table name
ibdconnect -o /var/lib/mysql/ibdata1 -f /var/lib/mysql/database_name/table_name.ibd -d database_name -t table_name

15. After the ibd-reconect job, we run a checksum on the ibdata1 using percona checksum tool:

innochecksum /var/lib/mysql/ibdata1
page 8 invalid (fails new style checksum)
page 8: new style: calculated = 0x239090D5; recorded = 0x56764549
[root@localhost include]# /usr/local/percona-data-recovery-tool-for-innodb-0.5/innochecksum /var/lib/mysql/ibdata1
page 8 invalid (fails new style checksum)
page 8: new style: calculated = 0x239090D5; recorded = 0x56764549
[root@localhost include]# /usr/local/percona-data-recovery-tool-for-innodb-0.5/innochecksum /var/lib/mysql/ibdata1
[root@localhost include]

We run it several times until no error message pops out further.

16. Finally, we start MySQL service: