29 Apr

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 of copying the .ibd and .frm files somewhere safe. That mostly works for MyISAM files right? Perhaps your system table space (ibdata1) became corrupted or was accidently 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 hundered 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.

Related posts from the blog:

  1. InnoDB tablespace fragmentation (find it and fix it!)
    If you frequently delete rows (or update rows with variable-length data types), you can end up with a...
  2. Life made easier: Facebook’s Online Schema Change for MySQL
    So often, you have a task where you need to perform an alteration of a huge table while...
  3. s3fox does not create valid export manifest files
    S3fox provides a handy utility for creating import manifest and signature files; however, it doesn’t seem to properly...
  4. Using SQL to create SQL (and other usefulness)
    Today, I had a task of modifying a slave to exclude (or if you want to say specifically...
  5. RPM “multiple packages” oddity, fixed
    I deal with all shipes and sizes, and distributions, of Linux. Each Linux distribution has quirks. Particularly with...

3 Responses to “Recovering a Schema From InnoDB .frm Files”

  1. Amelia 08. Mar, 2012 at 10:21 am #

    I am trying to use your script but it keeps throwing this error:

    bash: syntax error near unexpected token `(‘

    I figured out a couple other errors before this, but this one has me stumped.

    I am going to have to create the tables myself for now. If you could reply asap with any ideas I would be so thankful!!!!!!!

  2. missterr 07. Jul, 2012 at 12:33 pm #

    This is not work now. mysql 5.1.54

Trackbacks/Pingbacks

  1. Crash of an Innodb table | Random Bugs - 06. May, 2010

    [...] Recovering a Schema From InnoDB .frm Files *.frm files and reducing ibdata file Recovering an InnoDB table from only an .ibd file. Error 1005 with errno -1 on CREATE TEMPORARY TABLE MySQL InnoDB tables corrupt — how to fix? Related Posts:MySQL Cluster (OLD)MySQL Replication under DebianMigrating from/to MySQL to/from PostgresqlMySQL vs PostgreSQL BenchmarksMySQL root password recovery Share this post Hide Bookmarks [...]

Leave a Reply