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.

Answer 2

I figured out a solution on my own.

The simple solution is to find your saved copy of the CREATE TABLE SQL, run it on a development instance, then copy the generated FRM file to the restored instance.

However, in my case I did not have a copy of the CREATE TABLE command available.

You can get a MySQL server running with the ibdata, ib_logfiles, and *.ibd files. However without FRMs there will appear to be no tables in the databases.

1. In your restored database, run create table innodb_table_monitor (a int) ENGINE=InnoDB
2. Watch the MySQL server error file until the table monitor data has been dumped (usually about a minute)
3. Run drop table innodb_table_monitor

4. Stop the restored database

5. Write SQL to match the table monitor output, e.g.:

   TABLE: name db/mylosttable, id 0 7872, flags 1, columns 5, indexes 1, appr.rows 1828
   COLUMNS: id: DATA_MYSQL DATA_NOT_NULL len 12; name: type 12 DATA_NOT_NULL len 45;     
   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 17508, fields 0/5, uniq 1, type 1
   root page 3, appr.key vals 1828, leaf pages 9, size pages 10
   FIELDS:  DB_ROW_ID DB_TRX_ID DB_ROLL_PTR id name
   

   can be expressed as:

   drop table if exists mylosttable;
   create table mylosttable (
       id char(12) NOT NULL,
       name varchar(45) NOT NULL
   );
   

   If you are confused about the table monitor output, look at the output for tables with a known schema.

6. Run the above SQL on a development instance of MySQL

7. Copy the FRM files created in the development server to the restored database. You will find them in the MySQL data directory within the subdirectory for the corresponding database.

8. Restart the restored database

   Note you can copy the FRM files into a live database instance. The reason for stopping the server above is that if you crash the database after making the innodb_table_monitor table it will leave the ibdata file in an inconsistent state, and you'll have to start over from a backup.

9. Test that the tables work using select * statements. If you are wrong you will see:

   ERROR 2013 (HY000): Lost connection to MySQL server during query
   

which means the database has crashed.

If this occurs, do create table innodb_table_monitor... on the dev instance and compare the output to the original output from the restored instance. You will likely see you missed a NOT NULL or something small like that.