MySQL 8 Cookbook

Copying file-per-table tablespaces to another instance

Copying the tablespace file (the .ibd file) is the fastest way of moving data around, rather than exporting and importing through mysqldump or mydumper. The data is available immediately rather than having to be reinserted and the indexes rebuilt. There are many reasons why you might copy an InnoDB file-per-table tablespace to a different instance:

• To run reports without putting extra load on a production server
• To set up identical data for a table on a new slave server
• To restore a backed-up version of a table or partition after a problem or mistake
• To have busy tables on an SSD device, or large tables on a high-capacity HDD device