1

I was working with a mysql innodb datbase. When I was working with mysqlworkbench accidentally all the .frm files got deleted from mysql data folder but I still have ibdata file in the data folder. I was not using innodb_file_per_table means all of my innodb tables data were in the single ibdata file.

Unfortunately I don't have a backup to restore from. Could any one suggest me how to recover the data from the ibdata file.

slm
  • 7,355
  • 16
  • 54
  • 72
santhosh
  • 11
  • 3
  • http://stackoverflow.com/questions/6804397/how-do-i-recreate-an-frm-file-for-an-mysql-innodb-table-with-only-the-ibdata-and – user9517 Sep 05 '13 at 07:34

1 Answers1

1

Your question seems identical to this one on Stack Overflow.
I have reproduced part of the accepted answer here.

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

If you don't have access to the CREATE TABLE statements you can try the following:

  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 from your development system 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.

Community
  • 1
voretaq7
  • 79,345
  • 17
  • 128
  • 213
  • Thanks for the update. In my case innodb_file_per_table is not enabled. Can you point out what will be the changes in this case ? – santhosh Sep 06 '13 at 08:22
  • @santhosh Nope. But you can update your question to include that important information and someone might be able to (or try the above solution and see what happens in your environment...) – voretaq7 Sep 06 '13 at 15:25
  • I had already included this information . See "I was not using innodb_file_per_table means all of my innodb tables data were in the single ibdata file." Do I still needs to edit my question ? – santhosh Sep 09 '13 at 04:32