Select Page

Recovering a Schema From InnoDB .frm Files

Author: Mike Hamrick | | April 29, 2010

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.


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 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 Here is how you use it:

bash# --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.

How to Solve the Oracle Error ORA-12154: TNS:could not resolve the connect identifier specified

The “ORA-12154: TNS Oracle error message is very common for database administrators. Learn how to diagnose & resolve this common issue here today.

Vijay Muthu | February 4, 2021

Data Types: The Importance of Choosing the Correct Data Type

Most DBAs have struggled with the pros and cons of choosing one data type over another. This blog post discusses different situations.

Craig Mullins | October 11, 2017

How to Recover a Table from an Oracle 12c RMAN Backup

Our database experts explain how to recover and restore a table from an Oracle 12c RMAN Backup with this step-by-step blog. Read more.

Megan Elphingstone | February 2, 2017

Subscribe to Our Blog

Never miss a post! Stay up to date with the latest database, application and analytics tips and news. Delivered in a handy bi-weekly update straight to your inbox. You can unsubscribe at any time.

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.


Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.