Practical Guide to MySQL Database Migration, Backup, and Multi‑Source Replication

This article is a summary of a recent customer database migration, describing the problems encountered and the lessons learned.

The migration is divided into three main phases: pre‑migration preparation, the migration itself, and data verification.

Background Description

The customer has three business databases (A, B, and C). Databases B and C need to be merged into a single instance.

Migration Pre‑Preparation

Pre‑preparation includes collecting environment information, synchronizing data, and confirming readiness. A checklist image illustrates the environment before and after migration, showing master‑slave relationships, MySQL versions, and instance IPs.

Data synchronization must ensure real‑time sync between the old and new instances before the migration window, so that only migration and verification occur during the cut‑over.

Data Synchronization Steps

1. Create a new instance, carefully matching the configuration of the old instance.

2. Backup data from the old instance and import it into the new one.

# Full backup
mysqldump -h127.0.0.1 -P3306 -uroot -p --default-character-set=utf8mb4 --single-transaction --master-data=2 --flush-logs --hex-blob --triggers --routines --events --all-databases > all_db.sql

Database A can be fully backed up, including triggers, stored procedures, and events.

# Single‑database backup
mysqldump -h127.0.0.1 -uroot -P3306 -p --default-character-set=utf8mb4 --master-data=2 --flush-logs --single-transaction --set-gtid-purged=off --hex-blob --databases databasename > one_database.sql

After backup, copy the dump files to the new instance and import them.

# Import data
mysql -h127.0.0.1 -P3306 -uroot < all_db.sql

MySQL 5.7 does not back up system stored procedures in mysql.proc ; an upgrade should be run after import.

# Run upgrade
mysql_upgrade --upgrade-system-tables --skip-verbose --force

3. Establish replication to keep data synchronized in real time.

Use a VIP address for the replication source when possible; otherwise ensure each replication level operates correctly. Create a dedicated replication user for the migration and recycle it after completion.

# Set up replication from old to new instance
CHANGE MASTER TO MASTER_HOST='10.186.60.201', MASTER_USER='repl', MASTER_PORT=3307, MASTER_PASSWORD='repl', MASTER_AUTO_POSITION=1;
start slave;

For databases B and C, multi‑source replication is used to merge them into the new instance.

# Multi‑source replication for B
CHANGE MASTER TO MASTER_HOST='10.186.60.209', MASTER_USER='repl', MASTER_PORT=3307, MASTER_PASSWORD='repl', MASTER_AUTO_POSITION=1 FOR CHANNEL 'channel1';
# Multi‑source replication for C
CHANGE MASTER TO MASTER_HOST='10.186.60.210', MASTER_USER='repl', MASTER_PORT=3307, MASTER_PASSWORD='repl', MASTER_AUTO_POSITION=1 FOR CHANNEL 'channel2';
start slave;

Pre‑Migration Confirmation

A checklist (image) lists items that must be verified before the cut‑over, such as confirming the old master (OM), new master (NM), old slave (OS), and new slave (NS) roles and performing multiple confirmations.

Migration Execution

1. Confirm that no traffic is being written to the databases. Check GTID changes to ensure write‑off status.

# Show master status
show master status\G

2. Unbind the old cluster's VIP and set the old instance to read‑only to prevent further writes.

# Unbind VIP
ip addr del 10.186.60.201/25 dev eth0
# Set old instance to read‑only
set global super_read_only=1;

3. Stop replication on the new instance.

# Stop slave
stop slave;

After these steps, the old and new clusters should be in identical states, allowing a final consistency check.

Data Verification

Once the clusters are confirmed to be consistent, the business team performs data verification to ensure the migration succeeded.