How to Migrate a MySQL Database to a New Host Without Downtime

To migrate a MySQL database to a new host without downtime, you copy the existing data to the new server, then use MySQL's binary log to replay every change made after the copy started, and finally point traffic at the new server during a pause short enough that users do not notice. The key insight is that the bulk copy is the slow part and the binary log catch-up is the part that shrinks the switchover window to seconds.

The procedure below assumes MySQL 8.0 or a compatible fork, and it assumes you can run commands as a user with RELOAD, REPLICATION SLAVE and REPLICATION CLIENT privileges on the source. It works for a single database or for all of them; adjust the filters accordingly.

Step one: take a consistent snapshot without locking writes

The old way was FLUSH TABLES WITH READ LOCK followed by a filesystem copy, which freezes writes for the duration of the copy. For a large database that is the downtime you are trying to avoid. Instead use mysqldump with --single-transaction, which wraps the dump in a consistent snapshot using InnoDB's MVCC. Writes continue while the dump runs.

mysqldump --single-transaction --master-data=2 \
  --source-data=2 --routines --triggers --events \
  --set-gtid-purged=OFF --databases appdb \
  | gzip > /tmp/appdb.sql.gz

The --source-data=2 flag (older versions spell it --master-data=2) writes the binary log coordinates into the dump as a comment, prefixed with CHANGE REPLICATION SOURCE TO. That comment is your starting point for replication. If the server uses GTID mode, the dump also records the GTID set, and you can use SOURCE_AUTO_POSITION=1 instead of explicit coordinates. Check which mode you are in first:

SHOW VARIABLES LIKE 'gtid_mode';
SHOW VARIABLES LIKE 'binlog_format';

If binlog_format is ROW (the default in modern versions) you are in good shape for replication. If it is STATEMENT, replication can diverge on non-deterministic queries, so switch it to ROW before you start, or accept that the replay may need verification afterward.

Step two: load the dump on the new host

On the target server, create the database and load the dump. Disable binary logging for the load itself with SET sql_log_bin=0 if the target will later be a replica, since you do not want the initial load replayed into its own binary log. Set a large max_allowed_packet first, or the load will fail on large rows.

mysql -e "SET GLOBAL max_allowed_packet=1073741824;"
zcat /tmp/appdb.sql.gz | mysql --init-command="SET sql_log_bin=0"

While the load runs, note the coordinates from the dump header. If you grep for them:

zcat /tmp/appdb.sql.gz | head -40 | grep -i "CHANGE REPLICATION"

you will see something like CHANGE REPLICATION SOURCE TO SOURCE_LOG_FILE='mysql-bin.000123', SOURCE_LOG_POS=4567;. That is the exact point at which the snapshot was taken, so it is where the target must begin reading the source's binary log.

Step three: stream the binary log to the target

Configure the target as a replica of the source using the coordinates from the dump. You are not replacing the source yet, just catching up. On the target:

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='old-host',
  SOURCE_USER='repl',
  SOURCE_PASSWORD='secret',
  SOURCE_LOG_FILE='mysql-bin.000123',
  SOURCE_LOG_POS=4567;
START REPLICA;
SHOW REPLICA STATUS\G

Watch Seconds_Behind_Source in the output of SHOW REPLICA STATUS. It will start high because the target is replaying everything that happened since the dump began, then fall as it catches up. When it reaches zero and stays there, the target is an exact copy of the source as of a moment ago. This is the state you want before you cut over.

If the target falls behind and never catches up, the source is writing faster than the target can replay. Look at io_thread and sql_thread states, check disk throughput on the target, and consider whether the target has enough I/O capacity for the write rate. Replication is single-threaded by default in older versions; modern versions support parallel applier threads via replica_parallel_workers.

Step four: cut over with a short write pause

When the target is caught up, you need to stop writes on the source, wait for the target to apply the last few transactions, and redirect clients. The pause should be seconds, not minutes. The mechanics depend on your client layer, but the database side is the same.

On the source, set it read-only so no new writes arrive:

SET GLOBAL super_read_only = ON;
SHOW MASTER STATUS;

Note the final coordinates. On the target, wait until SHOW REPLICA STATUS reports Exec_Master_Log_Pos equal to that position (or Executed_Gtid_Set matching the source's), then stop the replica and promote the target by setting it read-write:

STOP REPLICA;
SET GLOBAL super_read_only = OFF;
SET GLOBAL read_only = OFF;

Now point your application, load balancer or DNS at the new host. The window between super_read_only = ON on the source and the application connecting to the target is your visible downtime. With a warm target and a fast client failover, this is typically a few seconds. The write pause is the only unavoidable interruption; everything before it is done while the source serves traffic normally.

If you can tolerate a slightly longer pause, a safer variant is to keep the source read-only for a few seconds after promotion and verify a known test row exists on the target before releasing traffic. That costs a little more time but catches a misconfigured replica before users see errors.

What to check afterward

After cutover, confirm that the target has the correct server_id, that binary logging is enabled if the target will itself be replicated or backed up from, and that auto_increment_increment is 1 on the new primary. Run a checksum comparison of a few tables with CHECKSUM TABLE on both sides if you kept the source around. Keep the old source read-only for a while as a fallback; if something is wrong, you can point clients back to it, though any writes made on the target during that window will need to be reconciled.

Next, write the whole sequence down as a runbook with the exact commands and the expected output at each step, and rehearse it against a copy of production before you run it for real. The migration itself is mechanical; the failures come from small differences between the rehearsal and the real thing, so the runbook and the rehearsal are what make the procedure repeatable.