MySQL Enterprise Backup User's Guide (Version 3.11.1)
A table with a table-specific tablespace (stored in an
.ibd
file) can be restored individually without
taking down the MySQL server. This technique is applicable if you
delete or update the table data by mistake, without actually
losing the table itself through a DROP
TABLE
, TRUNCATE TABLE
, or
DROP DATABASE
statement.
If you have a clean backup of an .ibd
file, you
can restore it to the MySQL installation from which it originated
as follows:
For MySQL 5.5 and earlier, the table must already exist and not have been dropped or truncated since taking the backup. When an InnoDB table is truncated, or dropped and recreated, it gets a new table ID. Any ID mismatch between the table in the database and the backed-up table can prevent it from being restored. The requirement for matching table IDs is also the reason why you must restore to the same MySQL server from which the backup data came, not another server with a similar set of databases and tables. This restriction does not apply to MySQL 5.6 and later, as long as the restoration is made from one Generally Available (GA) version to another in the same series of MySQL servers.
Prevent write operations for the table to be restored. This prevents users from modifying the table while the restore is in progress.
LOCK TABLES tbl_name
WRITE;
Issue this ALTER TABLE
statement:
ALTER TABLE tbl_name
DISCARD TABLESPACE;
Caution: This deletes the current .ibd
file.
Copy the backup .ibd
file back to the
appropriate database directory.
Issue this ALTER TABLE
statement:
ALTER TABLE tbl_name
IMPORT TABLESPACE;
Release the write lock to complete the restore procedure:
UNLOCK TABLES;
In this context, a clean.ibd
file backup means:
There are no uncommitted modifications by transactions in the
.ibd
file.
There are no unmerged insert buffer entries in the
.ibd
file.
Purge has removed all delete-marked index records from the
.ibd
file.
mysqld has flushed all modified pages of
the .ibd
file from the buffer pool to the
file.
You can make such a clean backup .ibd
file with
the following method:
Stop all activity from the mysqld server and commit all transactions.
Wait until SHOW INNODB STATUS
shows that
there are no active transactions in the database, and the main
thread status of InnoDB
is Waiting
for server activity
. Then you can make a copy of the
.ibd
file.
Another method for making a clean copy of an
.ibd
file is to use
mysqlbackup:
Use mysqlbackup with the
--only-innodb
or
--only-innodb-with-frm
option to back up the
InnoDB
installation.
Run mysqlbackup ... apply-log
to create a
consistent version of the backup database.
Start a second (dummy) mysqld server on the
backup and let it clean up the .ibd
files
in the backup. Wait for the cleanup to end.
Shut down the dummy mysqld server.
Take a clean .ibd
file from the backup.