Migrate MySQL Data Using Transportable Tablespaces

Migrating large MySQL databases using traditional logical backup tools like mysqldump can be highly time-consuming. MySQL’s transportable tablespaces feature allows you to copy physical InnoDB .ibd tablespace files directly between different server instances, drastically reducing data migration times. This article provides a step-by-step guide on how to perform this physical data migration safely and efficiently.

Prerequisites

Before beginning, ensure that both the source and destination MySQL servers meet these requirements: * They must run the same major version of MySQL (e.g., 8.0 to 8.0) to prevent compatibility issues. * The file-per-table setting (innodb_file_per_table) must be enabled on both servers (this is the default in modern MySQL versions). * The page sizes on both source and destination MySQL instances must match (default is 16KB).

Step 1: Create the Table Schema on the Destination

You must have an identical, empty table structure on the destination database before you can import the physical tablespace.

On the source database, get the exact CREATE TABLE statement:
```
SHOW CREATE TABLE my_database.my_table;
```
Log into the destination database and execute that CREATE TABLE statement to create the empty table structure.

Step 2: Discard the Tablespace on the Destination

To prepare the destination database to receive the new data file, you must discard its existing, empty tablespace.

Run the following command on the destination MySQL server:

ALTER TABLE my_database.my_table DISCARD TABLESPACE;

This command deletes the .ibd file associated with the table on the destination server, leaving only the .frm (in older versions) or data dictionary entry.

Step 3: Export the Tablespace on the Source

To copy the physical data file safely, you must ensure no write operations are happening and generate a metadata configuration file.

Run this command on the source MySQL server:

FLUSH TABLES my_database.my_table FOR EXPORT;

This command locks the table in read-only mode and creates a metadata file named my_table.cfg in the database directory. Keep this session open; closing it will release the lock and remove the .cfg file.

Step 4: Copy the Files to the Destination

While the source session is still open and the table is locked, copy the database files from the source server to the destination server.

You need to copy two files from the source database directory to the destination database directory: * my_table.ibd (the actual data) * my_table.cfg (the metadata file generated by the export command)

Use a secure copy tool like scp or rsync to transfer the files:

scp /var/lib/mysql/my_database/my_table.{ibd,cfg} user@destination-server:/var/lib/mysql/my_database/

Step 5: Adjust Permissions and Unlock the Source

Once the transfer is complete, you can unlock the source database table to resume normal operations.

On the source server, in the session where you executed the lock, run:

UNLOCK TABLES;

Next, on the destination server, ensure the MySQL user has ownership of the newly copied files:

chown -R mysql:mysql /var/lib/mysql/my_database/

Step 6: Import the Tablespace on the Destination

The final step is to register the copied tablespace file with the destination MySQL server.

Run the following command on the destination MySQL server:

ALTER TABLE my_database.my_table IMPORT TABLESPACE;

MySQL will validate the schema compatibility using the .cfg file, register the tablespace, and make the table active. Once the command completes successfully, you can query the table to verify the migration. The .cfg file is automatically deleted by MySQL after a successful import.