This can be a slightly different question compared to related InnoDB repair questions I've come across on stackoverflow.

Think that I've restored the next during my MySQL 5.1 database with innodb_file_per_table=1:


I've lost the db/tablename.frm file. I'm able to start the database server, but InnoDB gripes:

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 do i rebuild the FRM file?

I determined an answer by myself.

The easy solution is to locate your saved copy from the CREATE TABLE SQL, run it on the development instance, then copy the produced FRM file towards the restored instance.

However, during my situation I didn't possess a copy from the CREATE TABLE command available.

You can aquire a MySQL server running using the ibdata, ib_logfiles, and *.ibd files. However without FRMs there'll seem to be no tables within the databases.

  1. Inside your restored database, run create table innodb_table_monitor (a int) ENGINE=InnoDB
  2. Watch the MySQL server error file before the table monitor data continues to be left (usually 1 minute)
  3. Run drop table innodb_table_monitor
  4. Steer clear of the restored database

  5. Write SQL to complement 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

    could be expressed as:

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

    If you're unclear about the table monitor output, consider the output for tables having a known schema.

  6. Run the above mentioned SQL on the development demonstration of MySQL

  7. Copy the FRM files produced within the development server towards the restored database. You'll find them within the MySQL data directory inside the subdirectory for that corresponding database.

  8. Restart the restored database

    Note you are able to copy the FRM files right into a live database instance. The reason behind preventing the server above is when you crash the database after making the innodb_table_monitor table it'll leave the ibdata file within an sporadic condition, you'll also find to begin again from the backup.

  9. Test the tables work using select * claims. If you're wrong you will notice:

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

meaning the database has crashed.

If the happens, do create table innodb_table_monitor... around the dev instance and compare the output towards the original output in the restored instance. You will probably help you skipped a NOT NULL or something like that small like this.