Guide to MySQL Transportable Tablespaces and Multi‑Source Replication Using XtraBackup

MySQL 5.7+ introduces multi‑source replication and, from 5.6, support for transportable tablespaces; combined with Percona XtraBackup's export option, they enable fast, reliable initialization of multiple source databases into a single target for analysis or further replication.

The core workflow consists of exporting the InnoDB tablespace on the source ( export tablespace ), creating an identical table on the target, discarding the target's existing tablespace ( DISCARD TABLESPACE ), copying the exported .ibd file, and finally importing it on the target ( IMPORT TABLESPACE ).

Prerequisites are a MySQL version ≥5.6, the InnoDB storage engine, and a post‑import ANALYZE TABLE to refresh statistics.

Implementation steps :

Generate test data and simulate load with sysbench (four tables, 1 M rows each) and create a small pressure using two concurrent threads.

Backup the source database using XtraBackup: innobackupex --databases=sbtest /data/mysql/backup/ .

Dump the source schema without data: mysqldump --no-data --set-gtid-purged=off sbtest > sbtest_schema.sql .

Generate batch DISCARD TABLESPACE statements with SELECT CONCAT('ALTER TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,' DISCARD TABLESPACE;') FROM information_schema.tables WHERE TABLE_SCHEMA='sbtest'; and similarly create IMPORT TABLESPACE statements.

Apply the XtraBackup log with export: innobackupex --apply-log --export /data/mysql/backup/... , producing .cfg and .exp files.

Create the target database and import the schema: mysql -e "create database sbtest;" and mysql sbtest < sbtest_schema.sql .

Discard the target tablespace files, copy the exported .ibd and .cfg files to the target data directory, adjust ownership, and run the generated IMPORT TABLESPACE script (warnings about discarded tablespaces are normal).

Finally, configure a multi‑source replication channel on the target:

CHANGE MASTER TO MASTER_HOST='10.186.60.16', MASTER_USER='repl', MASTER_PORT=3333, MASTER_PASSWORD='repl', MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=149327998 FOR CHANNEL '10-186-60-16';
CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE=('sbtest.%');
START SLAVE FOR CHANNEL '10-186-60-16';
SHOW SLAVE STATUS FOR CHANNEL '10-186-60-16'\G;

Reference links to Percona XtraBackup documentation, MySQL transportable tablespace description, and the original blog post are provided for further reading.