5.6 Restoring a Single .ibd File

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:

  1. 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.

  2. 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;
    
  3. Issue this ALTER TABLE statement:

    ALTER TABLE tbl_name DISCARD TABLESPACE;
    

    Caution: This deletes the current .ibd file.

  4. Copy the backup .ibd file back to the appropriate database directory.

  5. Issue this ALTER TABLE statement:

    ALTER TABLE tbl_name IMPORT TABLESPACE;
    
  6. Release the write lock to complete the restore procedure:

    UNLOCK TABLES;
    

In this context, a clean.ibd file backup means:

You can make such a clean backup .ibd file with the following method:

  1. Stop all activity from the mysqld server and commit all transactions.

  2. 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:

  1. Use mysqlbackup with the --only-innodb option to back up the InnoDB installation.

  2. Run mysqlbackup ... apply-log to create a consistent version of the backup database.

  3. 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.

  4. Shut down the dummy mysqld server.

  5. Take a clean .ibd file from the backup.