A table with a table-specific tablespace (stored in an
.ibd file) can be restored individually
without taking down the MySQL server. If you have a clean backup
of an .ibd file, you can restore it to the
MySQL installation from which it originated as follows:
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.
Put the backup .ibd file back in the
proper database directory.
Issue this ALTER TABLE statement:
ALTER TABLE tbl_name IMPORT TABLESPACE;
Restore is now complete and the write lock can be released:
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
ibbackup:
Use ibbackup to back up the
InnoDB installation.
Run ibbackup --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.