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