Recover lost .frm files for InnoDB tables

Recently I found in a forum the following request for help:

My MySQL instance crashed because of free disk space fault. I saw in /var/lib/mysql all the files: ibdata1, ib_logfile* and all the folders containing frm files. Well, when i solved the problem and run successfully the instance, some databases disappeared. One of those is the most important, and i don't know how many tables had and their structures. Is there any way for recover the entire lost database (structure and data) only having the ibdata1 file?

First of all the observation sounds a bit strange because files do not just disappear. So I fear that its not just the .frm files which are lost. But let's think positive and assume just the .frm files have gone...

To recover the tables is a bit tricky because the .frm files contains the information about the table structure for MySQL.

If you have any old backup or only a structure dump it would be very helpful..

In InnoDB there is the table structure stored as well. You can get it out with the InnoDB Table Monitor as follows:

mysql> CREATE SCHEMA recovery;
mysql> use recovery;
mysql> CREATE TABLE innodb_table_monitor (id INT) ENGINE = InnoDB;

MySQL will write the output into its error log:

TABLE: name test/test, id 16, flags 1, columns 4, indexes 1, appr.rows 3
  COLUMNS: id: DATA_INT DATA_BINARY_TYPE len 4;
    DB_ROW_ID: DATA_SYS prtype 256 len 6;
    DB_TRX_ID: DATA_SYS prtype 257 len 6;
  DB_ROLL_PTR: DATA_SYS prtype 258 len 7;
  INDEX: name GEN_CLUST_INDEX, id 18, fields 0/4, uniq 1, type 1
   root page 312, appr.key vals 3, leaf pages 1, size pages 1
   FIELDS:  DB_ROW_ID DB_TRX_ID DB_ROLL_PTR id

With these information and some experience you can guestimate the original table structure:

Schema and table name: test.test

         id: DATA_INT DATA_BINARY_TYPE len 4;
  DB_ROW_ID: DATA_SYS prtype 256 len 6;
  DB_TRX_ID: DATA_SYS prtype 257 len 6;
DB_ROLL_PTR: DATA_SYS prtype 258 len 7;

The table has only 1 column called id which is an 4 byte int, the other columns are InnoDB internal stuff (19 byte!).

INDEX: name GEN_CLUST_INDEX, id 18, fields 0/4, uniq 1, type 1

The table has only one generated clustered index (no explicit index!).

So we can guess:

mysql> CREATE TABLE test.test (
  id INT UNSIGNED NOT NULL DEFAULT 0
) ENGINE = InnoDB CHARSET=utf8;

This table has to be created on a second system now. From there we see with the InnoDB table monitor:

TABLE: name test/test, id 0 1269, columns 4, indexes 1, appr.rows 0
  COLUMNS: id: DATA_INT DATA_UNSIGNED DATA_BINARY_TYPE DATA_NOT_NULL len 4;
    DB_ROW_ID: DATA_SYS prtype 256 len 6;
    DB_TRX_ID: DATA_SYS prtype 257 len 6;
  DB_ROLL_PTR: DATA_SYS prtype 258 len 7;
  INDEX: name GEN_CLUST_INDEX, id 0 909, fields 0/4, uniq 1, type 1
   root page 3, appr.key vals 0, leaf pages 1, size pages 1
   FIELDS:  DB_ROW_ID DB_TRX_ID DB_ROLL_PTR id

This is not 100% correct yet.

id seems to be SIGNED and not UNSIGNED and NULL seems to be allowed. So next try:

mysql> CREATE TABLE test.test (
  id INT SIGNED NULL
) ENGINE = InnoDB CHARSET=utf8;

TABLE: name test/test, id 0 1271, columns 4, indexes 1, appr.rows 0
  COLUMNS: id: DATA_INT DATA_BINARY_TYPE len 4;
    DB_ROW_ID: DATA_SYS prtype 256 len 6;
    DB_TRX_ID: DATA_SYS prtype 257 len 6;
  DB_ROLL_PTR: DATA_SYS prtype 258 len 7;
  INDEX: name GEN_CLUST_INDEX, id 0 911, fields 0/4, uniq 1, type 1
   root page 3, appr.key vals 0, leaf pages 1, size pages 1
   FIELDS:  DB_ROW_ID DB_TRX_ID DB_ROLL_PTR id

So this looks pretty much like it should. Do not be confused because of some other details. The original table was created on a MySQL 5.6.4 and the .frm recovery is done on a 5.1.55.

Now copy the .frm file to the original database and look if you can access your data. If it does you can do this table by table for all you zillions of tables...

When you are done. Take a backup and ideally do a proper install of your database!

Just a little detail: I created the original table like this:

mysql> CREATE TABLE test.test (id INT) ENGINE = InnoDB;

mysql> SHOW CREATE TABLE test.test\G
CREATE TABLE `test` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

MySQL seems to figure out itself what is the correct character set...