Are you looking for steps to recover the orphan InnoDB database from the ibd file? We can help you do it.
Here at Bobcares, we have seen several such InnoDB-related queries as part of our Server Management Services for web hosts and online service providers.
Today we will take a look at how to recover the orphan InnoDB database from the .ibd file
When Orphan InnoDB database incident occur
Now let’s see when does the Orphan InnoDB database incident happens:
- If a user accidentally removes the ibdata1 file. (mostly in /var/lib/mysql/ibdata1).
- If ibdata file is corrupted.
- Enabling “innodb_file_per_table” in my.cnf beforehand
- 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.
[[email protected] /]# 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
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:
[[email protected]/]# cd /usr/local/percona-data-recovery-tool-for-innodb-0.5/ [[email protected] percona-data-recovery-tool-for-innodb-0.5]# pwd /usr/local/percona-data-recovery-tool-for-innodb-0.5 [[email protected] 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 [[email protected] 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 [[email protected] include]# /usr/local/percona-data-recovery-tool-for-innodb-0.5/innochecksum /var/lib/mysql/ibdata1 [[email protected] include]
We run it several times until no error message pops out further.
16. Finally, we start MySQL service.
[Need any further assistance with InnoDB-related queries? – We’re available 24*7]
Today, we saw how our Support Engineers recover the orphan InnoDB database from the ibd file.