Restore MySql Database From Just frm and idb File

Hello,

Few days back, I encountered a situation where we needed to restore MySql Database but we only had frm and idb file. Didn't have .sql file or log file from MySql server which is very much needed when you want to restore. So here in this blog I am going to explain how you can still restore MySql Database from only frm and idb file.

First you will need a tool called dbsake which you can install with following command.

curl -s http://get.dbsake.net > dbsake

Once installed, run following command to check if it's installed properly or not. 

chmod u+x dbsake

./dbsake --version

If it shows version and all, it's installed. Now first by using this tool we will get schema of table from frm file. Run following command. 

./dbsake frmdump /PATH_TO_FRM/file.frm

It will give you create table schema like this.

CREATE TABLE `table_name` (

  `column1` int(11) NOT NULL AUTO_INCREMENT,

  `column2` varchar(255) DEFAULT NULL,

  `column3` varchar(255) DEFAULT NULL,

  PRIMARY KEY (`column1`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Copy it and run it in your sql editor. If you are migrating it from MySql 5 to 6 you may want to add ROW_FORMAT

CREATE TABLE `table_name` (

  `column1` int(11) NOT NULL AUTO_INCREMENT,

  `column2` varchar(255) DEFAULT NULL,

  `column3` varchar(255) DEFAULT NULL,

  PRIMARY KEY (`column1`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=compact;

Now it will create table. It's time to load data into it. 

First we will delete the table space which is idb file created. Run following command.

ALTER TABLE table_name DISCARD TABLESPACE

Now copy the old idb file and replace the existing one in your MySql server.

Now attach table space again.

ALTER TABLE table_name IMPORT TABLESPACE

That's it and now if you browse the data in table, you can see all your old rows.