How to Restore Data from .FRM and .IBD Files

When InnoDB storage engine is used, an .FRM file is used by MySQL Server to store table schema and .IBD file is used to store data and indexes of a MySQL database. In the event of accidental deletion of data or MySQL database corruption, you can restore the data from the .FRM and .IBD files.

If you have a backup of schema, you can use the utility available online to extract schema from .FRM file.

Steps to Restore Data from .FRM and .IBD Files

Step 1 – Create a MySQL Database and Recreate the Table

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.

• Navigate to the path where your MySQL databases are located and you can see the newly added database. However, the database won’t have any .FRM and .IBD files. In our case, the databases can be found in C:\xampp\mysql\data but the path might differ based on the web tool you’re using to access MySQL databases.

• Open the ‘employee’ database and you can see there is no table in it.

Step 2 – Find Table Schema

Note: If the table schema is available, then skip to the next step.

If you don’t have table schema, download MySQL Utilities. Released by Oracle, MySQL Utilities comprises a set of scripts to perform some common DBA tasks easily. Mysqlfrm is one of the tools in the utilities, you can use, to recover a table structure.

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

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

Note: While this method is useful to retrieve data from a damaged .FRM file, it may fail to recover all the data. To recover collation or character set, you’ll need to run the following command to recover information from the .FRM file:

Step 3 – Recreate the Database Table

In the newly created database (i.e. employee in our case), create a new table using the script generated in step 2. Executing the script will create a new .FRM and .IBD file in the database data folder.

Step 4 – Delete the New .IBD File

Run the following command to delete the new .IBD file:

 This command breaks the link between MySQL and the tablespace to remove the .IBD file.

Step 5 – Copy the Old .IBD file

Now, copy the original .IBD file in the new database.

Step 6 – Import the Tablespace

Restore the link between the MySQL table and the tablespace by executing the below command:

 These steps will help you restore data from .FRM and .IBD files. But, it has certain downsides:

• You will need to manually perform all the steps for each table to restore the MySQL database; this can take a lot of time.
• Using mysqlfrm to extract CREATE TABLE definition might result in schema mismatch.
• Mysqlfrm cannot retrieve AI number sequences and FK constraints.

A Better Alternative to Restore Data from .FRM and .IBD Files

Using a specialized MySQL repair tool like Stellar Repair for MySQL provides an easier alternative to restore your database and all its data with minimal downtime. Also, the software can recover data .FRM and .IBD files without modifying the table schema. You can verify the data before saving it by downloading the demo version of the software.

FAQ

Q. When trying to remove .IBD file I get an error “This action can't be completed because the file is open in ...” Please help me fix it.

A. You will get such error if the .IBD file you are trying to remove is already in use. To fix the error, close your MySQL service, delete the file, and then restart the MySQL service.

Q. I used the mysqlfrm utility to recover CREATE TABLE statement for a table. I was able to recreate the table structure by executing the CREATE statement, deleted the .IBD file, and copied the old .IBD file. However, when trying to import the tablespace, the table schema mismatches. What can I do to resolve this issue?

A. Add “ROW_FORMAT=compact” at the end of the CREATE TABLE query to resolve the issue.