Migrating a Live MySQL Database Without Downtime

  • 08 Jul, 2026
Migrating a Live MySQL Database Without Downtime

I move projects between hosting providers more often than most people, so this comes up a lot. The scenario: MySQL on a VPS, real traffic on it, moving to a different provider, sometimes managed, sometimes just another VPS. I want the switch to take seconds, not a maintenance window.

The simplest way to do this is to just stop the app, dump the database, restore it, and start the app back up:

# on the app server: stop whatever writes to the database
sudo systemctl stop your-app

# on the old database: dump everything
mysqldump --single-transaction --routines --triggers -u root -p your_database > dump.sql

# copy it over, then restore on the new database
mysql -h new-database-host -u admin -p your_database < dump.sql

# update the app's connection string to point at the new host, then
sudo systemctl start your-app

That’s genuinely fine for a lot of migrations, an internal tool, a low-traffic app, anything with an actual maintenance window you can schedule. The downtime is however long the dump and restore take, which scales with the size of the database, seconds for a small one, potentially hours for a large one.

If that downtime isn’t acceptable, the alternative is to make the new database a live replica of the old one before touching anything user-facing, let it catch up to real time while the old one keeps serving traffic, then cut over once they’re in sync. That’s the rest of this post.

A quick note on naming: MySQL 8.0.23 renamed the replication commands, CHANGE MASTER TO became CHANGE REPLICATION SOURCE TO, START SLAVE became START REPLICA, and so on. Same mechanics either way, this uses the current names. If you’re on an older version or MariaDB, swap in the old ones.

1. Turn on GTID replication on the source

Add this to the old server’s MySQL config and restart it:

[mysqld]
gtid_mode = ON
enforce_gtid_consistency = ON
log_bin = mysql-bin
binlog_format = ROW
server_id = 1

GTID mode gives every transaction a globally unique ID instead of tracking replication position by binlog file and byte offset. That matters later, it’s what lets the new server figure out exactly where to resume from automatically.

2. Create a replication user

CREATE USER 'replicator'@'%' IDENTIFIED WITH mysql_native_password BY 'a-strong-password';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replicator'@'%';
FLUSH PRIVILEGES;

This account only needs replication privileges, nothing else. Don’t reuse an existing app or admin user for this.

3. Open the firewall, temporarily

The new database needs to reach the old one on port 3306. The first time I hit this, I opened the port with ufw on the VPS itself, confirmed it was open, and the connection still got refused. Turns out most cloud providers put a second firewall in front of the VPS, entirely separate from anything configured inside the OS, and you have to open the port there too:

sudo ufw allow from <new-database-ip> to any port 3306

That’s the OS-level rule. On top of it, also open the same port at the platform level:

  • AWS EC2: an inbound rule for port 3306 on the instance’s Security Group.
  • Azure VM: an inbound rule on the VM’s Network Security Group (NSG).
  • Source is a managed database (RDS, Azure Database for MySQL): just add the new database’s IP to that resource’s own “allowed IP ranges” setting.

Scope it tightly, ideally to just the new database’s IP, and close it again once the migration is done.

4. Seed the new database with a consistent snapshot

Replication only streams changes going forward, so the new database needs a starting point first. With GTID mode on, mysqldump can capture that starting position automatically:

mysqldump --single-transaction --set-gtid-purged=ON -u root -p your_database > seed.sql

--single-transaction takes a consistent snapshot without locking the tables. --set-gtid-purged=ON embeds the exact GTID position the snapshot was taken at, right into the dump file. Restore it on the new instance:

mysql -h new-database-host -u admin -p your_database < seed.sql

5. Point the new database at the old one

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='old-vps-ip',
  SOURCE_USER='replicator',
  SOURCE_PASSWORD='a-strong-password',
  SOURCE_AUTO_POSITION=1,
  SOURCE_SSL=1;

START REPLICA;

SOURCE_AUTO_POSITION=1 is why the GTID setup mattered, the new server already knows exactly where the seed snapshot left off, and picks up replication from that precise point without you having to specify a binlog file or offset by hand.

6. Watch it catch up

SHOW REPLICA STATUS\G

Check three fields: Replica_IO_Running and Replica_SQL_Running should both say Yes, and Seconds_Behind_Source should be counting down toward 0. Don’t move on until it’s been sitting at 0 for a while, not just touched it once.

7. Cut over

Once you trust the sync, the actual cutover is short. Stop writes on the old database:

SET GLOBAL read_only = ON;

Give it a few seconds, check SHOW REPLICA STATUS on the new server one more time to confirm Seconds_Behind_Source: 0 with nothing left in flight, then stop replication and update your app’s connection string to point at the new database:

STOP REPLICA;
RESET REPLICA ALL;

Deploy the app with the new connection string, and you’re done. Total write downtime is however long that pause lasted, usually single-digit seconds.

8. Clean up

Drop the replication user from the old server, close the firewall rule you opened in step 3, and once you’re confident the new database has been stable for a while, decommission the old one.

The gotcha that isn’t about replication at all

Managed database providers often default to a stricter sql_mode than vanilla MySQL, and that has nothing to do with replication, it can still break your app after a perfectly successful cutover. DigitalOcean’s managed MySQL, for example, swaps STRICT_TRANS_TABLES for the stricter STRICT_ALL_TABLES and adds a few extra modes on top of MySQL’s own default. The one most likely to bite: your old server may have silently coerced bad data with a warning, the new one rejects it outright. Compare both before migrating:

SELECT @@GLOBAL.sql_mode;

If the new one’s stricter than what your app was built against, loosen it to match until you’ve fixed the underlying queries, don’t fight both problems mid-migration.

One thing I don’t have a confident answer for: what the seed step looks like for an app with sustained, heavy read and write traffic happening the entire time you’re dumping it. Some form of SQL export still has to produce that initial copy no matter which tool does it. Under light traffic, --single-transaction is basically free. Under a database getting hit constantly with writes, I genuinely don’t know how much that costs the database you’re migrating away from, I haven’t tested it myself.