How do I recreate an FRM file for an MySQL InnoDB table with only the ibdata and *.ibd files?

Question

This is a slightly different question than the related InnoDB repair questions I have seen on stackoverflow.

Assume that I have restored the following in my MySQL 5.1 database with innodb_file_per_table=1:

db/tablename.ibd
innodb/ibdata1
innodb/ib_logfile0
innodb/ib_logfile1

I have lost the db/tablename.frm file. I can start the database server, but InnoDB complains:

110723 13:26:33  InnoDB: Error: table 'db/tablename'
InnoDB: in InnoDB data dictionary has tablespace id 5943,
InnoDB: but tablespace with that id or name does not exist. Have
InnoDB: you deleted or moved .ibd files?

How can I reconstruct the FRM file?

Answer 1

EDIT: I created a simple script that does all the steps described below: https://ourstickys.com/recover.sh

---

old question, but I found this easier way to do it: https://dba.stackexchange.com/questions/16875/restore-table-from-frm-and-ibd-file

I have recovered my MySQL 5.5 *.ibd and *.frm files with using MySQL Utilites and MariaDB 10.

1) Generating Create SQLs.
You can get your create sql's from frm file. You must use : https://dev.mysql.com/doc/mysql-utilities/1.5/en/mysqlfrm.html

shell> mysqlfrm --server=root:pass@localhost:3306 c:\MY\t1.frm --port=3310

Other way you may have your create sql's.

2) Create Your Tables
Create your tables on the database.

3) alter table xxx discard tablespace
Discard your tables which do you want to replace your *.ibd files.

4) Copy your *.ibd files (MySQL Or MariaDB) to MariaDB's data path
First i try to use MySQL 5.5 and 5.6 to restrore, but database crashes and immediately stops about tablespace id broken error. (ERROR 1030 (HY000): Got error -1 from storage engine) 
After i have used MariaDB 10.1.8, and i have succesfully recovered my data.

5) alter table xxx import tablespace
When you run this statement, MariaDB warns about file but its not important than to recover your data :) Database still continues and you can see your data.

I hope this information will helpful for you.

Let me add that you can download the mysqlfrm here: https://dev.mysql.com/downloads/utilities/

---

I also found a faster way to get the CREATE TABLE by using dbsake:

curl -s http://get.dbsake.net > dbsake
chmod u+x dbsake

then:

#only one table
./dbsake frmdump /path/to/table.frm > recover.sql

#multiple tables
./dbsake frmdump /path/to/*.frm > recover.sql

followed by:

mysql -uUSER -p recover_db < recover.sql

you can also execute it in a one liner if you want:

./dbsake frmdump /path/to/*.frm | mysql -uUSER -p recover_db

at this point you can follow the above instructions from point 3 on.