Restoring table data in MySQL database using .frm and .ibd files only

The Challenge: You have to recover table data and you only have .frm and .ibd file from your mySQL data directory. Copying the directory directly will not work.

Knowledge: Table structures are store in .frm files, so you can recover the table structure from this file.

Process for recovering one table using .frm files

There are two different ways of recovering corrupted table

• (Method 1) Spawning a new MySQL instance and run structure recovery (Usage of the following switches is neede –server or –basedir along with –port)
• (Method 2) Recovery of a table without requirement of a MySQL instance (Usage of –diagnostic which reads the .frm files byte-by-byte and tries to recover all the information possible)
• (Method 3) Use a 3rd party service, https://recovery.twindb.com/

Using method 1: Spawning a new MySQL instance and run structure recovery

1st step: Recreate the structure from the frm files

To recreate the table structure, you can use the tool “mysqlfrm” provided with MySQL Utilities This tool extracts the structure and create a “Create table” script.

mysqlfrm –server=root:mypassword@localhost –port=3311 “<source/path>/mytable.frm” > “<destination/path>/recovered_mytable.sql”

The port instruction is any available port, it’s not the port of the mysql server. The end of the script is to redirect the output in a file.

2nd step: Recreate the table in a new database

In a new database, create the new table with the script generated at the step 1. This script will create 2 files in the database data folder :

• mytable.frm
• mytable.idb

3rd step: Remove the new idb file

To remove the new idb file, execute the sql command :

ALTER TABLE mytable DISCARD TABLESPACE;

This command removes the link between the table and the tablespace, and removes the idb file.

4th step: Copy the old idb file

The idb file recovered from the old server must be copied in place of the idb file deleted at the step 3.

5th step: Reactivate the table (this will link the frm structure to the new idb file data)

The link broken at the step 3 is restored with the command :

ALTER TABLE mytable IMPORT TABLESPACE;

You can ignore any MySQL warnings. Data will be restored.