Best practices for migrating a large MySQL database with millions of rows

For a large MySQL database migration with minimal downtime, Percona XtraBackup is the superior choice over mysqldump. While mysqldump with `--single-transaction` provides a logical backup suitable for smaller databases, its single-threaded nature means importing 5 million rows will be prohibitively slow, extending your downtime window significantly. XtraBackup performs physical backups by copying data files directly, which is far faster and allows for incremental backups to reduce transfer time. The core concept here is leveraging a physical backup tool that operates at the file system level for speed, combined with binary log positioning for near-zero downtime.

Key steps involve taking a full backup on the source server, transferring it to the destination, preparing it for consistency, and then configuring replication to catch up any changes that occurred during the transfer. Finally, you promote the new server and repoint your application.

# On source server (take backup)

xtrabackup --backup --target-dir=/path/to/backup

xtrabackup --prepare --target-dir=/path/to/backup



# Transfer backup to new server

rsync -avrP /path/to/backup/ new-server:/path/to/backup/



# On new server (prepare and restore)

xtrabackup --copy-back --target-dir=/path/to/backup

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



# Start MySQL on new server and setup replication