How to Migrate a MySQL Database Between Servers

Overview

Migrating MySQL databases between servers is a common task when upgrading your Breeze or consolidating infrastructure. This guide covers multiple methods for safe and efficient transfers.

Method 1: mysqldump (Small to Medium Databases)

The simplest approach for databases under a few gigabytes:

Export

mysqldump -u root -p --single-transaction --routines --triggers --events mydb > mydb_backup.sql

Transfer

scp mydb_backup.sql root@new-breeze-ip:/tmp/

Import

mysql -u root -p -e "CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
mysql -u root -p mydb < /tmp/mydb_backup.sql

Method 2: Compressed Pipe (Large Databases)

For larger databases, stream directly between servers to save disk space:

mysqldump -u root -p --single-transaction mydb | gzip | \
  ssh root@new-breeze-ip "gunzip | mysql -u root -p mydb"

Method 3: mydumper/myloader (Very Large Databases)

For multi-gigabyte databases, use parallel dump and restore:

sudo apt install mydumper -y

# Parallel export (8 threads)
mydumper -u root -p password -B mydb -o /tmp/mydb_dump -t 8

# Transfer
rsync -avz /tmp/mydb_dump/ root@new-breeze-ip:/tmp/mydb_dump/

# Parallel import
myloader -u root -p password -B mydb -d /tmp/mydb_dump -t 8

Post-Migration Verification

# Compare table counts
mysql -u root -p -e "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema='mydb';"

# Verify row counts on critical tables
mysql -u root -p -e "SELECT table_name, table_rows FROM information_schema.tables WHERE table_schema='mydb' ORDER BY table_rows DESC LIMIT 10;"

Important Reminders

• Recreate users and grants on the new server
• Update application config files with new connection details
• Test queries and application functionality thoroughly