Search Our Database

How to recover an orphan InnoDB database from .ibd file

Last updated on |
by

Introduction

Orphaned InnoDB databases occur when the system is unable to access critical database files, often resulting from the accidental removal or corruption of the ibdata1 file. This file, located in /var/lib/mysql/ibdata1, stores essential metadata and indexing information for InnoDB tables. If the file is corrupted or deleted, MySQL will fail to locate or open the affected tables, rendering the database unusable. Recovering from this type of failure requires a methodical approach to restore the lost connection between the database’s structure and its internal data.

innodb-1

This guide will walk you through the steps to recover your InnoDB database when the innodb_file_per_table setting is enabled. This setting separates the storage of data into individual table-specific files, which allows recovery of tables even when the global data file (ibdata1) is lost or damaged. With proper preparation, such as enabling the innodb_force_recovery option, and by following the steps below, you can recover your database and bring the corrupted tables back online.

innodb-2

This article assumes you have already configured the necessary recovery options in your my.cnf file and that the database folder remains intact or has been restored from a backup. If the `ibdata1` file has been lost, the steps provided will help you reestablish the link between your table structure and data files to restore functionality.

Important Note: Without the innodb_file_per_table option enabled, the InnoDB database relies fully on the ibdata1 file. Losing this file without innodb_file_per_table enabled results in complete data loss.

 

Prerequisites

  • Ensure innodb_file_per_table and innodb_force_recovery=5 are enabled in the my.cnf file.
  • The database folder /var/lib/mysql/database_name/ and its contents remain intact or have been restored from a backup.

 

Step-by-Step Guide

Step 1: Restart MySQL and Backup Your Database

Restart the MySQL service to recreate the ibdata1 file and create a backup of your database folder.

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

innodb-3

 

Step 2: Log into MySQL

Access MySQL via the terminal using the root user.

mysql -uroot -p 
Enter password: 

innodb-4

Step 3: Create a Dummy Database

Create a dummy database with the same name as the original one.

CREATE DATABASE database_name;

 

Step 4: Connect to the Dummy Database

Switch to the newly created dummy database.

USE database_name;

innodb-5

 

Step 5: Create a Dummy Table

Create a dummy table with the same name as the corrupted table, using a random structure, and enable the InnoDB engine.

CREATE TABLE table_name (id INT) ENGINE=InnoDB;

innodb-6

Step 6: Verify the Table Structure

Describe the table to check its structure.

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

innodb-7

 

Step 7: Replace the .frm File

Stop the MySQL service and copy the .frm file from the backup to the database folder.

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

innodb-8

 

Step 8: Start MySQL and Flush Tables

Start the MySQL service, connect to the database, and flush the tables.

/etc/init.d/mysql start
mysql -uroot -p
USE database_name;
FLUSH TABLES;

 

Step 9: Verify the Table Structure

Use the DESC command to verify that the correct table structure has been restored.

DESC table_name;

innodb-9

 

Step 10: Retrieve the Table’s Create Statement

Retrieve the full CREATE query for the table.

SHOW CREATE TABLE table_name;

innodb-10

 

Step 11: Drop the Dummy Table and Recreate It

Drop the dummy table and recreate it using the recovered structure.

DROP TABLE table_name;

innodb-11

 

Step 12: Install the Percona InnoDB Recovery Tool

Download, extract, and install the Percona InnoDB recovery tool.

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
./configure
make

 

Step 13: Replace the .ibd File

Stop the MySQL service again and replace the .ibd file with the backup.

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

innodb-12

 

Step 14: Use the Percona ibdconnect Tool

Run the ibdconnect tool to sync the .ibd and ibdata1 files.

ibdconnect -o /var/lib/mysql/ibdata1 -f /var/lib/mysql/database_name/table_name.ibd -d database_name -t table_name

innodb-13

 

Step 15: Run innochecksum on ibdata1

Perform a checksum on ibdata1 using the Percona checksum tool. Run the command multiple times until there are no error messages.

innochecksum /var/lib/mysql/ibdata1

 

Step 16: Start MySQL Service

Finally, start the MySQL service.

/etc/init.d/mysql start

 

Conclusion

By following this guide, you can recover orphaned InnoDB tables in MySQL after issues with the ibdata1 file. For additional assistance or if you encounter any issues, please contact our support team at support@ipserverone.com.