Recovering mysql innodb table from table.ibd backup file

Question

Just practising DB disaster recovery. As per our back up policy, we do complete disk image back ups every half an hour (on our slave server).

Now for a scenario, where some how a table has been accidentally dropped, I thought recovery would be as simple as -

• copy .ibd and .frm file from backup to mysql data dir.
• alter table tbl_name import tablespace;

On issuing a select, DB throws an error - table does not exist. I understand this might have something to do with ib_logfile?

Any ideas how to go about restoring table in this situation?

Answer 1

InnoDB tables cannot be copied the same way that MyISAM tables can.

Just copying the .frm and .ibd files from one location to another is not enoght.

Copying the .frm and .ibd file of an InnoDB table is only good if and only if you can guarantee that the tablespace id of the .ibd file matches exactly with the tablespace id entry in the metdata of the ibdata1 file.

Have a look at Recovering an InnoDB table from only an .ibd file.

Also have a look at This Detailed explanation from @RolandoMySQLDBA.

Answer 2

Although there are at least three ways to recover from an .ibd file you should never do that in normal restore, only when backups are not available:

1. Update SPACE_ID in table.ibd
2. Update SPACE_ID in ibdata1
3. Fetch records from table.ibd

Instead you should backup InnoDB tables correctly with either tool:

• mysqldump
• xtrabackup
• mylvmbackup (if you have LVM)

UPDATE: Data recovery toolkit moved to GitHub