Recovering a Schema From InnoDB .frm Files

Sometimes you find yourself in a bad situation where your only hope of recovering your InnoDB data lies in a handful of .frm and .ibd data files that were heretofore part of a working MySQL installation. It could be the case that someone thought backing up InnoDB tables was simply a matter of copying the .ibd and .frm files somewhere safe. That mostly works for MyISAM files right? Perhaps your system tablespace (ibdata1) became corrupted or was accidentally deleted. Whatever the reason, you have a handful of .frm and .ibd files, and what you want is them imported into a functioning database.

The basics of how to do this are not too hard to understand, it’s the details that get tricky. The first part of this problem is how to extract the table definition from the .frm files. I’ll cover the second part of the problem in another post. You could write a program that reads and parses the .frm file and outputs the table definition, but that’s a lot of work — especially when you can trick MySQL into doing it for you. Here is how you would do this for the file foo.frm.

mysql> CREATE TABLE `test`.`foo` (id int) ENGINE=InnoDB;

We’ve created an InnoDB table called foo. MySQL has written a foo.frm and a foo.ibd file in $datadir/test. It has also made a record of this table in the data dictionary.

mysql> FLUSH TABLES;

This causes MySQL to close all open tables and flush the query cache. The idea is to force MySQL to forget about the table “foo` that we just created.

bash# cp foo.frm /var/lib/mysql/test;

We just overwrote the table definition for test.foo. We did it hot, while MySQL was still running.

mysql> SHOW CREATE TABLE `test`.`foo`;

That will output the table definition for the foo.frm file we just copied into the test directory. MySQL will probably be a bit confused, as the information in the .frm file is not likely to match what is in the system table space. You will probably see a message like this in your error log.

[ERROR] Table ./test/foo has no primary key in InnoDB data dictionary, but has one in MySQL! If you created the table with a MySQL version < 3.23.54 and did not define a primary key, but defined a unique key with all non-NULL columns, then MySQL internally treats that key as the primary key. You can fix this error by dump + DROP + CREATE + reimport of the table.

That’s OK though, we already got what we wanted, and now we can clean up.

mysql> DROP TABLE `test`.`foo`;

While this process works fine for a few tables, you can see how it’d be a real annoyance to have to go through this for a few hundred tables. I wrote a script that automates this process for you, I call it recover_schema.pl. Here is how you use it:

bash# recover_schema.pl --user=root --password=seekrit *.frm

This program needs to be run as root, and needs to have the credentials for a privileged MySQL user. It will accept a number of .frm files to convert to CREATE TABLE statements on then command line. It operates on the test database like the example above, and will create it if it does not exist. One CREATE TABLE statement will be output for every .frm file passed in on the command line.