MySQL Disaster Recovery – Recover InnoDB Table from IBD Backup

disaster recoveryinnodbMySQL

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?

Best Answer

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.

UPDATE 2012-05-15 15:59 EDT

Since it fails to start up, you may need uninstall MySQL 5.5.24 and go back to MySQL 5.5.23 or whichever version you were last running with.

UPDATE 2012-05-15 16:06 EDT

Please run tail -30 on the mysql error log and paste that in the question.f it is not configured, go to the data folder and do ls -l *.err to find it.

UPDATE 2012-05-15 17:54 EDT

Based on the display you pasted in, you evidently had MySQL 5.1 running before. It just so happens that MySQL 5.1 has a table called mysql.plugin. It may have been populated before. It probably had something concerning the InnoDB plugin. MySQL 5.1.38 introduced the InnoDB plugin which has new features that are now native to MySQL 5.5's InnoDB. In other words, MySQL 5.5 does not need any plugin to run the new InnoDB.

Please run this query:

SELECT COUNT(1) FROM mysql.plugin;

If you get 1, that's probably the MySQL 5.1 InnoDB Plugin interfering. Try moving it over like this:

CREATE TABLE mysql.plugin_new LIKE mysql.plugin;
ALTER TABLE mysql.plugin RENAME mysql.plugin_old;
ALTER TABLE mysql.plugin_new RENAME mysql.plugin;

Afterwards, please restart mysql and see if InnoDB comes back

MySQL backup InnoDB

As your link points out, ALTER TABLE can break a transaction

In fact, according to Page 418 Paragraph 3 of MySQL 5.0 Certification Study Guide

the following commands can break a transaction

ALTER TABLE
BEGIN
CREATE INDEX
DROP DATABASE
DROP INDEX
DROP TABLE
RENAME TABLE
TRUNCATE TABLE
LOCK TABLES
UNLOCK TABLES
SET AUTOCOMMIT = 1
START TRANSACTION

Thus, you cannot run any of these and have a stable mysqldump

SUGGESTION

You badly need to create a MySQL Replication setup

That way, the Master and stay up 24/7.

On the Slave, you can do the following:

STOP SLAVE;
mysqldump --single-transaction
START SLAVE;

and never disturb the Master.

CAVEAT

Since setting up Replication requires a stable mysqldump, you may need downtime for the one-time setup. My answer would be a lot different if your MySQL was in Linux.