Restoring Table Structure from frm and ibd Files
InnoDB uses the ib_log file to recover the data. It also needs the ibdata1 file containing data dictionary and also some data pending in the table. The data dictionary is a duplicate system that is used for recording table structure and matching table id with the physical .ibd file. ibd files are always moved along with the InnoDB data dictionary. The table can be recovered using the .frm files and cannot be created as an InnoDB table.
A server may be crashed and the data is recovered from .frm and .ibd files. Usually, all table structures will be stored in .frm files. The solution to the problem is to recover the table structures to find the data loss or to recreate the table. The corrupted table can be recovered in two ways.
Spawning a new MYSQL instance and run a recovery structure: –server or –basedir switches are used along with –port. The Spawn will shut down the instance after completing the recovery.
Recovering the Table without the Requirement of SQL instance: –diagnostic is used to read the .frm files to recover the data without any requirement of SQL instance. This method is used to recover all data by reading .frm files byte by byte.
Spawning a new MYSQL instance and run a recovery structure:
Step 1 – Recreating the table structure with the frm files
"mysqlfrm" tool is used with the MYSQL Utilities for recreating the table structure. Using this tool "Create table" script is created and the structure is extracted.
The above port instruction is not a port of the MYSQL server and it can be of any port. The end of the script redirects the file output.
Step 2 - Recreating the table in a new database
A new table is created in the new database with a script created in step 1. Two files, mytable.frm and mytable.ibd are created in the database data folder.
Step 3 - Removing the new ibd file
The following SQL command is executed for removing the new ibd file,
Using the above command, the link between the table and table space is removed. Ibd file is also removed.
Step 4 - Copying the old ibd File
In the place of deleted ibd file, recovered ibd file should be copied.
Step 5 – Reactivating the Table
The broken link is restored with the following command
You may receive an error message. Don't worry about that, it will not affect the restoring process.
Recovering the Table without the Requirement of SQL instance:
After the execution of the above command, all recovered data will be in the "recovered_mytable.sql" file. Next, Ibd file is recreated using the steps 2, 3, 4 and 5. MYSQL table can be restored only from .ibd file.
If you are having ibd file, SQL scripts are needed for recreating the table. Now the new table has to be created with the SQL script using the "Create table" command. mytable.frm and mytable.ibd files are created in the database folder.
InnoDB uses the ib_log file to recover the data. It also needs the ibdata1 file containing data dictionary and also some data pending in the table. The data dictionary is a duplicate system that is used for recording table structure and matching table id with the physical .ibd file. ibd files are always moved along with the InnoDB data dictionary. The table can be recovered using the .frm files and cannot be created as an InnoDB table.
A server may be crashed and the data is recovered from .frm and .ibd files. Usually, all table structures will be stored in .frm files. The solution to the problem is to recover the table structures to find the data loss or to recreate the table. The corrupted table can be recovered in two ways.
Spawning a new MYSQL instance and run a recovery structure: –server or –basedir switches are used along with –port. The Spawn will shut down the instance after completing the recovery.
Recovering the Table without the Requirement of SQL instance: –diagnostic is used to read the .frm files to recover the data without any requirement of SQL instance. This method is used to recover all data by reading .frm files byte by byte.
Spawning a new MYSQL instance and run a recovery structure:
Step 1 – Recreating the table structure with the frm files
"mysqlfrm" tool is used with the MYSQL Utilities for recreating the table structure. Using this tool "Create table" script is created and the structure is extracted.
Code:
mysqlfrm –server=root:mypassword@localhost –port=3311 "<source/path>/mytable.frm" > "<destination/path>/recovered_mytable.sql"Step 2 - Recreating the table in a new database
A new table is created in the new database with a script created in step 1. Two files, mytable.frm and mytable.ibd are created in the database data folder.
Step 3 - Removing the new ibd file
The following SQL command is executed for removing the new ibd file,
Code:
ALTER TABLE mytable DISCARD TABLESPACE;Step 4 - Copying the old ibd File
In the place of deleted ibd file, recovered ibd file should be copied.
Step 5 – Reactivating the Table
The broken link is restored with the following command
Code:
ALTER TABLE mytable IMPORT TABLESPACE;Recovering the Table without the Requirement of SQL instance:
Code:
mysqlfrm –diagnostic "<source/path>/mytable.frm" > "<destination/path/recovered_mytable.sql>"If you are having ibd file, SQL scripts are needed for recreating the table. Now the new table has to be created with the SQL script using the "Create table" command. mytable.frm and mytable.ibd files are created in the database folder.
