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
How to do it...
The outline is: you create the table on the destination with the same table definition and execute the DISCARD TABLESPACE command on the destination. Execute FLUSH TABLES FOR EXPORT on the source, which ensures that changes to the named table have been flushed to disk, and so a binary table copy...
