Geek Answers Handbook

How to recover FRM file for MySQL InnoDB table with ibdata and * .ibd files only?

This is a slightly different question than the related InnoDB repair issues that I saw on stackoverflow.

Suppose in my MySQL 5.1 database I restored the following: innodb_file_per_table = 1:

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

I 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 to recover a FRM file?

+4
source share
2 answers

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

old question, but I found this easier way to do this: 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 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 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.

I will add that you can download mysqlfrm here: https://dev.mysql.com/downloads/utilities/

I also found a faster way to get CREATE TABLE with 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

and then:

 mysql -uUSER -p recover_db < recover.sql

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

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

At this point, you can follow the instructions above from point 3.

+2
source

I myself found a solution.

A simple solution is to find the saved copy of CREATE TABLE SQL, run it on the development instance, and then copy the created FRM file to the restored instance.

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

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

  • In the restored database, run create table innodb_table_monitor (a int) ENGINE=InnoDB
  • See the MySQL server error file until the table monitor data is flushed (usually about a minute).
  • Run drop table innodb_table_monitor

  • Stop recovered database

  • Write SQL to match the output monitor of the table, for example:

     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

    may 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 output of a table monitor, look at the output for tables with a known schema.

  • Run the above SQL in a MySQL development instance

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

  • Restart the restored database

    Note. You can copy FRM files to a live database instance. The reason for stopping the server above is that if you destroy the database after creating the innodb_table_monitor table, it will leave the ibdata file in an inconsistent state and you will have to start with the backup.

  • Check if tables work with select * statements. If you are mistaken, you will see:

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

which means the database crashed.

If this happens, run create table innodb_table_monitor... on the dev instance and compare the output with the original result from the restored instance. You will probably see that you missed NOT NULL or something like this.

+3
source

More articles:


All Articles