How to restore MySQL database from .frm and .ibd files?

MySQL databases often get corrupted due to issues like hardware failure, file system failure etc. Sometimes, you may not be having the backup of the databases to restore from. It is indeed a panic situation and the question is how to restore the database. The solution is simple. We can restore the entire database using from the .frm and .ibd files of a corrupt database. I will show you how to do it.

1. First of all we need an open source tool called dbsake which will help us to create the tables from the .frm files. The .frm files contain the table structure and it cannot be read directly. We can download the dbsake tool and give it the appropriate permissions using the steps given below.

# curl -s http://get.dbsake.net > dbsake
# chmod u+x dbsake
# ./dbsake –version
dbsake, version 2.1.0 9525896

2. We can recover the table structure using the following command

./dbsake frmdump /var/lib/mysql/testdb/staff.frm

In this command, testdb is the name of the database and staff.frm is the name of the table we want to restore. It will provide us the entire create table query with the structure like the following example.

CREATE TABLE `staff` (
`staff_id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
`first_name` varchar(45) NOT NULL,
`last_name` varchar(45) NOT NULL,
`address_id` smallint(5) unsigned NOT NULL,
`picture` blob,
`email` varchar(50) DEFAULT NULL,
`store_id` tinyint(3) unsigned NOT NULL,
`active` tinyint(1) NOT NULL DEFAULT ‘1’,
`username` varchar(16) NOT NULL,
`password` varchar(40) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`staff_id`),
KEY `idx_fk_store_id` (`store_id`),
KEY `idx_fk_address_id` (`address_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

3. We can then delete the corrupt table and recreate it using the query which we extracted from the .frm file.

4. If the entire database is corrupted, we can run the following query to recover all the tables from the .frm files. Please remember to backup the entire directory containing the tables of the databases which generally comprises of .frm, .ibd, .MYI, .MYD etc.

for tbl in `ls -1 /backup/mysql/testdb/*.frm`; do ./dbsake frmdump $tbl | mysql testdb; done;

In the above command, testdb is the name of the database and tbl is the variable which stores the names of all the tables. The directory /backup/mysql is the directory in which you have kept the backup of the database files.

This command will create all the tables using the table structure recovered from the .frm files. Each table will have it’s own tablespace which will be the .ibd file of that table.

5. Now, we have got all the tables with the structure. However, these tables are empty. We now want to restore the records of each table. This is done by importing the tablespace of each table. So, the next step would be to discard the newly created tablespace from the table and import the tablespace from the backup.

6. First we will discard the tablespace. Simply, login to MySQL and switch to the database which you are restoring. Run the following query to discard the tablespace

mysql> ALTER TABLE staff DISCARD TABLESPACE;

In the above query, staff is the name of the table we want to restore.

7. Now that the tablespace is discarded, copy the .ibd file to the mysql database directory of the database testdb from the backup. The name of the .ibd file for the table staff will be staff.ibd.

8. After the .ibd file is copied to the mysql database directory, change the owner and group to mysql. Run the following command

chown mysql:mysql staff.ibd

9. Now again, login to MySQL and switch to the database testdb.

10. Run the following query in MySQL

ALTER TABLE staff IMPORT TABLESPACE

The above query will restore the records of the table staff. You can now verify the records by executing the following query

mysql> select * from staff;

Do, this for all the tables and you will restore the entire database.

Sometimes, while importing the tablespace, you may encounter the following error

ERROR 1808 (HY000): Schema mismatch (Table has ROW_TYPE_DYNAMIC row format, .ibd file has ROW_TYPE_COMPACT row format.)
This generally happens if the .ibd file contains a different ROW_TYPE. In this case, first drop the table using the following query

mysql> drop table staff;

Now, modify the create table query we generated from step 2. Add ROW_FORMAT=compact after ENGINE=InnoDB and execute the query.

The query will look like below

CREATE TABLE `staff` (
`staff_id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
`first_name` varchar(45) NOT NULL,
`last_name` varchar(45) NOT NULL,
`address_id` smallint(5) unsigned NOT NULL,
`picture` blob,
`email` varchar(50) DEFAULT NULL,
`store_id` tinyint(3) unsigned NOT NULL,
`active` tinyint(1) NOT NULL DEFAULT ‘1’,
`username` varchar(16) NOT NULL,
`password` varchar(40) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`staff_id`),
KEY `idx_fk_store_id` (`store_id`),
KEY `idx_fk_address_id` (`address_id`)
) ENGINE=InnoDB ROW_FORMAT=compact DEFAULT CHARSET=utf8;

Now, repeat the steps 6 to 10.