Jun 28, 2018

Restoring/recovering table in MySQL database from frm or ibd files

Step 1: Recreate the structure from the frm files

You don't need this step if you already have the structure of the table.

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:[email protected] –port=3388 “<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.



Step 2: Recreate the table in a new database

For example, in a new database 'transit'.

Step 3: Make sure the row_format is the same, 

show create table mytable;

alter table mytable ROW_FORMAT=COMPACT;

If you do alter the table by modifying the row_format etc, you may need to discard and import again if it fails.


Step 4: Remove the new idb file


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

ALTER TABLE mytable DISCARD TABLESPACE;


Step 5: Copy the old idb file

Step 6: Reactivate the table

ALTER TABLE mytable IMPORT TABLESPACE;