File Repair

How to Restore Data from .FRM and .IBD Files

Summary: In certain situations (like MySQL server crash, hardware failure, or others) leading to database corruption and data loss, you can recover the data from .FRM and .IBD files. It also suggests a specialized MySQL repair tool to restore the database and all the data from corrupted data files.

Table of Contents

Steps to Restore Data from .FRM and .IBD Files”
  • Create a MySQL Database and Recreate the Table
  • Find Table Schema
  • Recreate the Database Table
  • Delete the New .IBD File
  • Copy the Old .IBD File
  • Import the Tablespace
  • A Better Alternative 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.

    phpMyAdmin panel

    • 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.

    mysql-database-path

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

    mysql-table

    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.

    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.

    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:

    mysqlfrm --server=root@127.0.0.1 --port 3307 ./filename.frm

    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:

    ALTER TABLE table_name DISCARD TABLESPACE;

     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:

    ALTER TABLE table_name IMPORT TABLESPACE;

     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.

    Free Download For Windows


    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.

     

    progress
    78% of people found this article helpful