Search Our Database

Orphan InnoDB database incident mostly happened when:

– user accidentally remove ibdata1 file. (mostly in /var/lib/mysql/ibdata1).

– ibdata file courrupted.

Note: To use this guide, we assumed:

– You have enabled “innodb_file_per_table” in my.cnf beforehand

– You have enabled “innodb_force_recovery=5” in my.cnf before you try to use this guide

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

(or you restore a copy of backup from else where)

Without “innodb_file_per_table” enabled, by default, InnoDB database is fully depend on ibdata1 file.

(Losing ibdata1 without “innodb_file_per_table” enabled basically means you have lost everything.)

When “innodb_file_per_table” is enabled in my.cnf, you will see 2 files format in the database folder:

.frm and .ibd

.ibd file (internal data dictionary)

.frm file (database structure information)

The relation between .ibd and ibdata1 is something like parent & child basis,

.ibd file will refer for data in ibdata1 through .frm file.

When ibdata1 accidentally removed, innodb database will refuse to start due to it’s inability to refer to the correct data block in ibdata.

When referring to the error log of mysql (usually locate in /var/lib/mysql/hostname.err), you can see something like:

121215 11:42:05 [ERROR] Cannot find or open table database_name/table_name from the internal data dictionary of InnoDB though the .frm file for the table exists. Maybe you have deleted and recreated InnoDB data files but have forgotten to delete the corresponding .frm files of InnoDB tables, or you have moved .frm files to another database?
or, the table contains indexes that this version of the engine doesn't support.

So our goal is to make .ibd file and ibdata1 in sync again.

1. To do this, you will need to recreate the table structure exactly same with the corrupted one, which is stored in .frm file. Complete steps as below:

By losing ibdata1, you need to restart mysql service to recreate ibdata1, and then copy your database folder to another directory as backup.

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

2. Login to mysql using terminal

[root@localhost /]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.0.92-50-log Percona SQL Server, Revision 85 (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

3. Create a dummy database with same name.

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

mysql>

4. Connect to the dummy database

mysql> use database_name

Database changed

5. Create a dummy table same name with 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. Describe the table to check it’s stucture.

mysql> desc table_name;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

7. Now, 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.

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

8. Start mysql service, connect to the database and flush tables.

/etc/init.d/mysql start
mysql -uroot -p
Enter password:

mysql> use database_name
Database changed

flush tables;

9. Describe table again and you will be able to see the losing structure of the table

desc table_name;

10. But the table cannot work without a proper CREATE command by identifying each structure, so to review the CREATE query of the corrupted table, use command “show create table table_name;”

show create table table_name;

11. The query can now be referring to easily. 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, download the tools, extract it, and make 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. Stop mysql service again, copy the .ibd file form 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. 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

After run you will see something like this:

15. After the ibd-reconect job, 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]

You will need to run it several times until no error message popping out further.

16. start mysql service now

/etc/init.d/mysql start