Using MySQL Transportable Tablespace to Quickly Copy Large InnoDB Tables Between Environments

Background : A developer needed to move a large table (≈25 GB compressed, billions of rows) from a pre‑production MySQL 5.7.25 instance to production overnight, with a very short maintenance window.

Solution Exploration : Traditional approaches such as mysqldump or SELECT ... INTO OUTFILE were deemed too slow or risky because they would generate huge binlog entries and cause replication lag. The chosen method was MySQL’s transportable tablespace, a feature introduced in MySQL 5.6 that allows fast copying of InnoDB tables between instances.

Implementation Steps :

On the target instance, create the table structure and run ALTER TABLE t DISCARD TABLESPACE so only the .frm file remains.

On the source instance, open two sessions:

Session 1 executes FLUSH TABLES t FOR EXPORT, which locks the table, writes dirty pages to disk, and creates a t.cfg file containing dictionary information.

Session 2 copies the generated t.ibd and t.cfg files to the target’s data directory (both master and slave if the target is a master‑slave setup) and sets ownership to mysql:mysql.

Session 1 then runs UNLOCK TABLES to release the lock.

Finally, on the target, execute ALTER TABLE t IMPORT TABLESPACE (only on the master if the target is replicated).

Performance Test : Importing the table with ALTER TABLE ... IMPORT TABLESPACE completed in about 6 minutes, with acceptable I/O consumption and replication delay. The total operation, including data transfer, took roughly 10 minutes, a dramatic reduction from the previously required several hours.

Limitations :

Source and target MySQL versions must match.

The method works only for InnoDB tables.

During FLUSH TABLES … FOR EXPORT, the source table is locked and unavailable for writes.