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)
*Note: There are some internal structures with this meta information, so you’ll need to dump/import that data after you get it loaded, so you avoid unpleasantries that will inevitably …
[Read more]