How to Restore Data from .FRM and .IBD Files

When you use InnoDB storage engine in MySQL Server, it stores table schema in .FRM file and the data and indexes in the .IBD file. In case of accidental deletion of tables or corruption in tables, you can use these files to recover the tables. These come in handy when you need to recover specific tables instead of complete backup. In this article, we will discuss how to restore data from .FRM and .IDB files in MySQL Server.

Process to Restore Data from .FRM and .IBD Files

Here’s the stepwise process to restore data from .FRM and .IDB file in MySQL Server:

Step 1: Create New MySQL Database

First, you need to create a MySQL database. Follow these steps:

Note: Here, we will be using phpMyAdmin to create the MySQL database.

• Log in into phpMyAdmin.
• Enter a database name (for instance employee) and click Create. This will create a database named employee.

• You can check the newly added database by following the path where your MySQL databases are located. In our case, the databases is located at C:\xampp\mysql\data.

Step 2: Find Table Schema to Create Table in Database

When you open the new database, you will see there are no tables in it. If the schema is known, then create a table. If you have a backup file, then you can easily get the schema and name of your database. You can use the same name to create the table. But if you have no backup file, then you can extract the schema of the table using the .FRM file.

There are various utilities available online to extract schema from an .FRM file. You can download MySQL Utilities, comprising a set of scripts to perform some common DBA tasks. Mysqlfrm is one of the tools in the utilities that you can use to recover a table structure.

After downloading the tool, run the following command to find the database schema.

mysql.FRM --diagnostic path filename.frm path   

This command reads all the information from the .frm file byte by byte without the need of a MySQL instance.

Step 3: Create the Database Table

Once you know the schema of the table, you can use the schema to create a table in the newly created database (i.e., employee in our case). Here are the steps:

• In phpMyAdmin, select the new database (you recently created) from the left-side menu.
• Go to the Structure tab and click Create Table. Enter the name of table, columns, etc., as per the structure of table.
• Once you create the table in the database, it will create new .FRM and .IBD files in the database data folder.

Note: Some tablespace id of the .ibd file mismatches with the tablespace ID entry in the metadata of the ibdata1 file. This can corrupt or create issues in the .ibd file. This command breaks the link between the MySQL table and the tablespace, thus helps prevent corruption or other issues.

Step 6 – Import the Tablespace

Now, restore the link between the MySQL table and the tablespace by executing the below command:

ALTER TABLE table_name IMPORT TABLESPACE;

Limitation of using .FRM and .IBD files to restore data:

• It is ideal to restore data of a single table in the MySQL database. Restoring multiple tables is a time-consuming process as you have to perform all the steps for each table manually.
• Using the utility (mysqlfrm) to extract the CREATE TABLE definition can result in a schema mismatch.
• Mysqlfrm cannot retrieve AI number sequences and FK constraints.