Have you ever stuck in a condition where you have to restore your mysql database from only an *.frm and *.ibd files? I have.

So what happen is somehow our mysql service is going down and can not be restarted. I try many things to no avail and getting tired of it.

And then I came across this page https://dev.mysql.com/doc/mysql-enterprise-backup/3.11/en/partial.restoring.single.html. I think probably I can just copy the *.frm and *.ibd file, wipe the current mysql data, reset the mysql, and restore those two files. Easy.

So I stupidly remove /var/lib/mysql/[schema_name], and also /var/lib/mysql/ib* file. Restart the mysql, and it start normally. Finally!

So what I have to do is just re-creating the schema and the tables, and then follow the earlier instruction, right? Wrong.

Try that and our mysql start crashing again.

So what probably wrong is when you recreate the table, it's not entirely the same with what it was. So to make sure it is exactly the same, you have to recreate it by extracting the table structure from the *.frm file.

So here's the complete step to restore your data from an *.frm and *.ibd files:

1. Download dbsake:

curl -s http://get.dbsake.net > dbsake
chmod u+x dbsake
./dbsake --version

2. Extract the table structure information from the .frm file using dbsake: ./dbsake frmdump /path/to/file.frm

3. Use the extracted table structure, to recreate the table. You can also recreate multiple tables using this one-liner: for tbl in `ls -1 /path/to/*.frm`; do ./dbsake frmdump $tbl | mysql -u [user] -p[password] [database_name]; done;

4. Get into the mysql console, and run: ALTER TABLE [tbl_name] DISCARD TABLESPACE; This command will remove the current /var/lib/mysql/[schema_name]/[tbl_name].ibd

5. Copy your backed up .ibd file to /var/lib/mysql/[schema_name]/. Make sure the owner is mysql:mysql and the permission is 660

6. Get back to the mysql console, and run: ALTER TABLE [tbl_name] IMPORT TABLESPACE;

Voilà!