Mastering MySQL Migration: Practical Scenarios and Step-by-Step Guides

1. Why Migrate

MySQL migration is a routine DBA task. Migration means moving an existing object while preserving its integrity and continuity, similar to children moving sand to build a castle.

Production environments require migration for reasons such as insufficient disk space, business bottlenecks, machine resource limits, or project restructuring (e.g., adding nodes across data centers or consolidating servers). The goal is to keep services running smoothly.

2. MySQL Migration Overview

Migration revolves around data backup and recovery while ensuring continuous business operation. The challenge is to perform backup and restore quickly and safely.

Backup options include full or incremental backups using mysqldump, xtrabackup, or mydumper. For databases under 10 GB, mysqldump is acceptable; for larger volumes (hundreds of GB or TB), xtrabackup or direct data‑directory copy (via rsync) is preferred to avoid locks and long downtime.

Restoration for small databases can be done by direct import, while large databases follow the procedures described in Section 4.

3. MySQL Migration in Practice

After understanding the reasons and methods, we examine concrete production scenarios. The following assumptions apply:

Server IPs are anonymized.

Same‑site servers use the D‑segment of the IP; cross‑site servers use C‑ and D‑segments.

Only high‑level methods are shown; exact commands depend on the reader’s knowledge.

Refer to Section 5 for detailed precautions.

3.1 Scenario 1 – Single‑Master/Slave Migration of a Slave

Project A has a master (101) and a slave (102). The slave must be moved to a new server (103) because its data size is too large for mysqldump. The architecture is shown in the image below.

Redirect read traffic from 102 to the master.

Verify 102’s MySQL status (PROCESS LIST) and stop its service.

Create a new MySQL instance on 103, stop MySQL, and move the data directory for backup.

Copy the entire MySQL data directory from 102 to 103 using rsync.

Grant replication privileges (REPLICATION SLAVE, REPLICATION CLIENT) on 101 to 103.

After copying, set a unique server_id in 103’s configuration.

Start MySQL on 103, ensuring correct data paths and permissions.

Run SHOW SLAVE STATUS on 103; Seconds_Behind_Master should decrease.

When Seconds_Behind_Master reaches 0, run pt-table-checksum to verify data consistency.

Validate account permissions with the development team.

Switch part of the read traffic from 101 to 103 and monitor.

If business runs without issues, the migration is successful.

3.2 Scenario 2 – Single‑Master/Slave Migration of Specific Databases

Both master (101) and slave (102) are moved to new servers (103 & 104), but only selected databases are transferred.

Create fresh instances on 103 and 104 and establish replication.

Export required databases from 102 using mysqldump during low‑traffic periods.

Collect necessary accounts and privileges.

Transfer the dump to 103 via rsync, compressing if needed.

Import the dump on 103; data automatically syncs to 104.

Authorize accounts on 103 and notify developers for verification.

Migrate business traffic from 101/102 to 103/104 and observe.

If no issues arise, the migration succeeded.

3.3 Scenario 3 – Dual‑Side Migration of Specific Databases

Both master and slave are moved to four new servers (103‑106). The process mirrors Scenario 2 but is performed twice.

Set up replication between 103 and 104, then migrate the required databases from 102 to 103.

Repeat the same steps for 105 and 106.

After both migrations, shift business traffic accordingly and verify.

3.4 Scenario 4 – Full Master‑Slave Migration

All databases are moved from 101/102 to new servers 103 (master) and 104 (slave). The diagram is shown below.

Redirect read traffic from 102 to the master.

Stop 102 after confirming its status.

Create a new instance on 104, stop MySQL, and backup its data directory.

Copy 102’s data directory to 104 via rsync.

Grant replication rights on 101 to 104.

Assign a unique server_id to 104.

Start MySQL on 104 and verify SHOW SLAVE STATUS.

When synchronization completes, run pt-table-checksum for consistency.

Validate account permissions.

Switch read traffic from 102 to 104.

Make 103 a slave of 101, then promote 104 to be a slave of 103 using a series of STOP/START SLAVE commands and CHANGE MASTER TO with the correct log file and position.

After confirming both sides are synchronized, cut the old master‑slave link and switch write traffic to the new master, ensuring FLUSH TABLES WITH READ LOCK is used if necessary.

Monitor the business; if stable, the migration succeeded.

3.5 Scenario 5 – Dual‑Master Cross‑Data‑Center Migration

Project uses a dual‑master setup across two data centers. The A‑site master/slave (1.101/1.102) are moved to new servers (1.103/1.104) while B‑site remains unchanged. After migration, 1.103 and 2.101 become dual masters.

Create instances on 1.103/1.104 and set up replication.

Stop 1.102 after confirming its status.

Create a new instance on 1.103, stop MySQL, and backup its data directory.

Copy 1.102’s data directory to 1.103 via rsync.

Grant replication privileges on 1.101 to 1.103.

Assign a unique server_id to 1.103.

Start MySQL on 1.103 and verify synchronization.

Make 1.104 a slave of 1.103 using the same procedure as Scenario 4.

Finally, configure 1.103 to become a slave of 2.101, completing the cross‑site dual‑master setup.

Switch read/write traffic to the new masters and monitor.

3.6 Scenario 6 – Multi‑Instance Cross‑Data‑Center Migration

Multiple instances on a single machine are replaced to fix data issues. The process uses innobackupex for backup, rsync for transfer, and careful configuration of replication filters.

Backup instance 7936 on 1.113 with innobackupex (including --slave-info).

Copy the compressed backup to 2.117.

Create data directories and configuration files on 2.117.

Restore logs and data on 2.117 using innobackupex.

Adjust configuration parameters such as replicate-ignore-db, innodb_file_per_table=1, read_only=1, and server_id.

Set proper directory permissions.

Grant replication rights on 1.112 to 2.117.

Execute CHANGE MASTER TO using log info from xtrabackup_slave_info and start the slave.

Repeat similar steps for instance 7939, adding replicate-wild-do-table.

Validate data consistency and account permissions with developers.

Migrate business traffic to the new instances and monitor.

4. Precautions

Enable event_scheduler on the master if events are involved.

Continuously monitor server health (disk space, network stability) and business performance.

Ensure correct LOG FILE and LOG POS values in CHANGE MASTER TO.

Run scripts in the data directory, not in $HOME.

Automate migration with scripts, but test them thoroughly.

Understand every command and its parameters before execution.

When stopping MySQL in multi‑instance environments, use mysqladmin to avoid shutting down unrelated instances.

Set read_only=1 on slaves to prevent accidental writes.

Assign unique server_id values to each server.

Configure replicate-ignore-db and replicate-wild-do-table correctly.

Enable innodb_file_per_table=1 on new instances to avoid large ibdata1 files.

When compressing with gzip, remember it deletes the original file after compression.

Perform all operations on slaves or backup nodes; avoid running heavy tasks on the master. xtrabackup does not lock InnoDB tables but locks MyISAM tables; handle MyISAM tables accordingly.

5. Tips

Use the relay master’s log file and exec_master_log_pos as the reference for LOG FILE and LOG POS.

Combine rsync with expect and nohup for reliable copying.

Compress innobackupex backups with gzip on the fly.

Add --slave-info to innobackupex for easier slave setup.

Use --throttle to limit I/O impact and --parallel=n to speed up backup (note that --parallel is ineffective with tar streams).

Prepare a checklist and script commands before migration.

For fast local copies, use rsync -avhW --no-compress --progress.

Use dd for rapid cross‑partition data copying, or ship hard drives for large transfers.

6. Summary

The article starts with the reasons for migration, presents overall strategies, walks through practical scenarios, and concludes with precautions and tips. Key points are: (1) migration aims to keep services stable; (2) the core challenge is maintaining master‑slave synchronization across servers; (3) permission handling, read/write ordering, and cross‑data‑center impacts must be carefully managed.

Readers can follow the provided guidance, but must verify each step to ensure a safe migration.