How to Restore a Single .ibd File in MySQL

A table with a table-specific tablespace (stored in an .ibd file) can be restored individually without taking down the MySQL server.

How to Restore a Single .ibd File in MySQL

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

2. Issue this ALTER TABLE statement:

ALTER TABLE tbl_name DISCARD TABLESPACE;

Caution: This deletes the current .ibd file.

3. Put the backup .ibd file back in the proper database directory.

4. Issue this ALTER TABLE statement:

ALTER TABLE tbl_name IMPORT TABLESPACE;

5. Restore is now complete and the write lock can be released:

UNLOCK TABLES;

 


<<Click here to see all posts>>

 

Leave a Reply

Your email address will not be published. Required fields are marked *

Prove You Are Human Time limit is exhausted. Please reload CAPTCHA.