Mastering MySQL Migration: Practical Scenarios and Step‑by‑Step Guide

1. Why Migrate

MySQL migration is a routine DBA task aimed at preserving data integrity and continuity when disk space is insufficient, business or machine bottlenecks arise, or projects require infrastructure changes such as cross‑datacenter moves.

2. Overview of MySQL Migration Plans

Migration revolves around backup and restore while keeping services running. For small databases (<10 GB) mysqldump is sufficient; for larger volumes (hundreds of GB to TB) tools like xtrabackup or direct data‑directory copy with rsync are preferred to avoid locks and reduce time.

3. Practical Migration Scenarios

3.1 Scenario 1 – Single‑Slave Migration

Move a large slave (102) to a new server (103) without using mysqldump. Steps include redirecting read traffic, stopping the slave, copying the data directory via rsync, granting binlog access, adjusting server_id, starting MySQL, and verifying synchronization with SHOW SLAVE STATUS and pt‑table‑checksum.

3.2 Scenario 2 – Migrate Specific Databases

Export selected databases from the original slave (102) using mysqldump, transfer them to a new master (103) and its slave (104) via rsync, import the data, and adjust permissions before switching traffic.

3.3 Scenario 3 – Dual‑Side Migration of Specific Databases

Perform two parallel migrations: first move 101/102 to 103/104, then move 101/102 to 105/106, following the same export‑transfer‑import workflow.

3.4 Scenario 4 – Full Master‑Slave Migration

Replace both master (101) and slave (102) with new servers (103/104). After copying data and configuring replication, switch the original master to become a slave of the new master, then cut the old sync and redirect traffic.

3.5 Scenario 5 – Dual‑Master Cross‑Datacenter Migration

Replace a dual‑master pair in one datacenter (1.101/1.102) with new instances (1.103/1.104) while keeping the other datacenter unchanged, then re‑establish cross‑datacenter dual‑master replication.

3.6 Scenario 6 – Multi‑Instance Cross‑Datacenter Migration

Use innobackupex to back up an instance, transfer the backup, restore it on a new server, adjust replication settings, and bring the new instance online, repeating for each required instance.

4. Key Considerations

Enable event_scheduler on the master if events are used.

Continuously monitor disk space, network stability, and server health.

Verify LOG FILE and LOG POS values when issuing CHANGE MASTER TO.

Avoid running scripts from $HOME; operate within the data directory.

Test all automation scripts thoroughly before production use.

Understand every command and its parameters.

Use mysqladmin to stop instances safely.

Set read_only=1 on slaves.

Ensure each server has a unique server_id.

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

Enable innodb_file_per_table=1 to avoid large ibdata files.

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

Perform operations on slaves or backup nodes, not on the primary master. xtrabackup does not lock InnoDB tables but does lock MyISAM tables.

5. Practical Tips

Use the relay master log file and exec master log position as reference for LOG FILE and LOG POS.

Combine rsync with expect or nohup for reliable copying.

Compress innobackupex backups with gzip on the fly.

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

Throttle innobackupex I/O with --throttle and speed up with --parallel (note: ineffective when using tar streams).

Prepare a checklist of commands and a flow diagram before migration.

Use rsync -avhW --no-compress --progress for fast local copies.

For cross‑partition transfers, dd or physical hard‑drive shipping can be effective.

6. Conclusion

The article walks through the motivation, planning, and execution of MySQL migrations across various scenarios, emphasizing the importance of maintaining continuous service, correctly handling replication, and carefully managing permissions and server configurations. Readers should follow the outlined steps and verify each operation to ensure a successful migration.