Recovering InnoDB Table Structures

Sometimes, when moving a database, you can’t create an SQL dump and are left with only “*.frm” files. You upload them to the “/var/db/mysql” directory, hoping the innodb tables will work, only to despair because they don’t…

For example, mysqldump may throw an error:

Got error: 1146: Table `table_name` doesn’t exist when using LOCK TABLES

This clever trick will help

Create a database called “my_test”:

mysql> create database my_test;
mysql> use my_test;

Then create an empty table in it whose name matches the file “table_name.frm”:

mysql> create table `table_name` (`id` int primary key) ENGINE=Innodb;

Next, replace the file you created in the test database “my_test” with the original table file:

cp /var/db/mysql/database_name/table_name.frm /var/db/mysql/my_test/table_name.frm

Now let’s look at our table’s structure:

mysql> use my_test;
mysql> show create table table_name;

You’ll see the structure, and then you can make a dump or carry on with whatever you were doing!

You can also move all your damaged tables and rename the database back to its original name (deleting the old one first, of course).

P.S. Read this useful article: Recovering InnoDB Data in MySQL