4

A recent server fiasco left me with a by-all-appearances intact ibdata file but only half of my .frm-containing directories. I've copied all of this to a fresh mysql install, and have successfully recovered the databases which I still had .frm files for. My question is - is it possible to regenerate the .frm files from the ibdata file somehow? Or somehow modify the .frm files from the development version of the same database such that they can be used for recovery?

Restoring from backup, sadly, is not a viable option.

Edited to clarify: I've tried to recover the databases using locally generated .frm files containing the same schema - no dice, even at innodb_force_recovery = 1.

bhaibel
  • 173
  • 1
  • 5
  • 1
    possible duplicate of [mysql crashed and lost table descriptions "show table status" all null](http://serverfault.com/questions/166734/mysql-crashed-and-lost-table-descriptions-show-table-status-all-null) – Warner Aug 10 '10 at 18:53

1 Answers1

3

I've never actually done this but your question relates to the tools I use so I was curious! It looks like this is actually possible, here is an example:

http://www.chriscalender.com/?p=28

Sometime you may need to recover a table when all you have is the .ibd file. In this case, if you try to load it into a new instance, your likely to encounter some errors about the table id not matching. And there is not really a way around this.

However, I’ve found two work-arounds for this:

Note: You will need the .ibd file and the CREATE TABLE statement for each table you want to recover using these methods.

  • Simulate the internal InnoDB table counter. That is, create work tables (with innodb_file_per_table enabled) until you have the internal pointer of table id equal to (1 – id_of_ibd_table_you_need_to_restore). (See Method #1)
  • Manually hex edit the .ibd file, changing the table id. (See Method #2)

The steps themselves are several pages long so I haven't pasted them here.

Another related post: http://www.mysqlperformanceblog.com/2011/05/13/connecting-orphaned-ibd-files/

I'll be curious to hear if that works, it looks like multiple people have used it successfully.

Community
  • 1
polynomial
  • 4,016
  • 14
  • 24
  • Note that you need ".ibd file and the CREATE TABLE statement" for this to work. The frm file includes all meta data MySQL needs. InnoDB has its own data dictionary but is missing some MySQL-specific information pieces. – johannes Sep 21 '11 at 15:08
  • About 6 months ago at my employer's company, a client tried to copy 30 .ibd files with no success. This question was the problem. The Chris Chandler link you are referring to was the solution he found. In order to help him, I wrote a stored procedure to add and drop a table 900 times at a shot (The tablespace_id was in the 900's) in a MySQL Sandbox. After running 912 times, he was able to drop in the .ibd file, run `flush tables;` and KABLAM !! He access to the table. He mysqldump'd it and saved his data. That experience proves this it works. +1 for the find and placing it in ServerFault. – RolandoMySQLDBA Jan 27 '12 at 16:33
  • One thing though: He had the .frm files. It was just the act of tablespace_id matching between the .ibd file and ibdata1 – RolandoMySQLDBA Jan 27 '12 at 16:35