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:
db/tablename.ibd innodb/ibdata1 innodb/ib_logfile0 innodb/ib_logfile1
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.
- Inside your restored database, run
create table innodb_table_monitor (a int) ENGINE=InnoDB
- Watch the MySQL server error file before the table monitor data continues to be left (usually 1 minute)
drop table innodb_table_monitor
Steer clear of the restored database
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 FIELDS: DB_ROW_ID DB_TRX_ID DB_ROLL_PTR id name
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.
Run the above mentioned SQL on the development demonstration of MySQL
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.
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.
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.