How to Migrate MySQL Databases to a New Server Without Downtime

Moving to a new server is stressful enough. Moving MySQL databases without taking your site offline adds another layer of risk that most tutorials brush past. If you get the cutover wrong — a brief window of writes to the old server while the new one isn't yet caught up — you lose data. If you take the site down for the migration, you lose customers.

This guide covers the two main approaches for zero-downtime MySQL migration: the mysqldump + replication method (works for most shared and VPS environments) and a quick checklist for the cutover moment when every second counts. Whether you're migrating between bare-metal servers, from a VPS to a dedicated machine, or moving to a cloud instance, these steps apply.

Pre-Migration Checklist

Before running a single command, confirm these items on both servers:

• MySQL/MariaDB versions are compatible: The destination version must be ≥ source version. Replication from MySQL 5.7 → 8.0 works; the reverse does not.
• Binary logging is enabled on the source: SHOW VARIABLES LIKE 'log_bin'; — must return ON. If not, add log_bin = /var/log/mysql/mysql-bin.log to /etc/mysql/my.cnf and restart MySQL.
• server-id values are unique: Source and destination must have different server-id values in my.cnf.
• Firewall allows port 3306: The destination server needs to reach the source on MySQL's port during the replication phase. Open it temporarily: ufw allow from DEST_IP to any port 3306
• Disk space available on destination: Run df -h /var/lib/mysql on both servers. Destination needs at least 2× the source database size (dump + import working space).

Method 1: mysqldump with --single-transaction (No Lock Approach)

This method works for InnoDB databases (the default storage engine for all modern MySQL/MariaDB installs). It takes a consistent snapshot without locking tables — your application stays live during the dump.

Step 1: Create a Replication User on the Source

mysql -u root -p

CREATE USER 'repl_user'@'DEST_SERVER_IP' IDENTIFIED BY 'StrongPassword123!';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'DEST_SERVER_IP';
FLUSH PRIVILEGES;

Replace DEST_SERVER_IP with the actual IP address of your new server.

Step 2: Take a Consistent Dump with Binlog Position

mysqldump   --single-transaction   --quick   --skip-lock-tables   --master-data=2   --all-databases   --triggers   --routines   --events   -u root -p   > /tmp/full_dump.sql

The --single-transaction flag opens a consistent read transaction before the dump starts, eliminating table locks for InnoDB. The --master-data=2 flag embeds the current binlog file name and position as a comment at the top of the dump — you'll use this to start replication from exactly the right point.

After the dump completes, note the binlog coordinates:

head -50 /tmp/full_dump.sql | grep "MASTER_LOG_FILE\|MASTER_LOG_POS"

Example output: -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000042', MASTER_LOG_POS=87634;

Step 3: Transfer the Dump to the Destination

rsync -avz --progress /tmp/full_dump.sql root@DEST_SERVER_IP:/tmp/full_dump.sql

For very large dumps (50 GB+), compress during transfer:

ssh root@DEST_SERVER_IP "mysqldump options... | gzip" > full_dump.sql.gz

Step 4: Import the Dump on the Destination

mysql -u root -p < /tmp/full_dump.sql

Monitor progress on large dumps:

pv /tmp/full_dump.sql | mysql -u root -p

Install pv first with apt install pv or yum install pv.

Method 2: Set Up MySQL Replication for Live Sync

Once the initial dump is imported, configure replication so the destination continuously receives new writes from the source. This keeps the two servers in sync until you're ready to cut over.

Configure the Source (my.cnf)

[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
expire_logs_days = 7

Restart MySQL: systemctl restart mysql

Configure the Destination (my.cnf)

[mysqld]
server-id = 2
relay_log = /var/log/mysql/relay-bin.log
read_only = ON

The read_only = ON prevents accidental writes to the destination during the migration window.

Start Replication on the Destination

mysql -u root -p

CHANGE MASTER TO
  MASTER_HOST='SOURCE_SERVER_IP',
  MASTER_USER='repl_user',
  MASTER_PASSWORD='StrongPassword123!',
  MASTER_LOG_FILE='mysql-bin.000042',
  MASTER_LOG_POS=87634;

START SLAVE;

Replace the MASTER_LOG_FILE and MASTER_LOG_POS values with the ones you captured from the dump header.

Monitor Replication Lag

SHOW SLAVE STATUS\G

Watch these fields:

• Slave_IO_Running: Yes — replication I/O thread is active
• Slave_SQL_Running: Yes — SQL thread is replaying events
• Seconds_Behind_Master: 0 — destination is fully caught up

If Seconds_Behind_Master is growing rather than shrinking, the destination MySQL server is under-resourced or the source write rate is too high. Consider tuning innodb_buffer_pool_size on the destination to match the source.

The Cutover: Switching Traffic with Minimal Downtime

Once Seconds_Behind_Master = 0 and stays at 0 for several minutes, you're ready for cutover. This is the only moment where your application will be briefly unavailable — the goal is to keep it under 30 seconds.

systemctl stop nginx   # or apache2

SHOW SLAVE STATUS\G | grep Seconds_Behind_Master

STOP SLAVE;
SET GLOBAL read_only = OFF;

systemctl start nginx

Post-Migration Validation

# On source server
xtrabackup --backup --target-dir=/tmp/xb_backup -u root -p
xtrabackup --prepare --target-dir=/tmp/xb_backup

# Transfer to destination
rsync -avz /tmp/xb_backup/ root@DEST_IP:/var/lib/mysql/

# On destination
chown -R mysql:mysql /var/lib/mysql/
systemctl start mysql