Migrating MySQL to RDS With Dump-and-Load Plus Native Binlog Replication

Introduction

You can migrate a large MySQL database to AWS RDS with near-zero data loss and a short cutover window using nothing but native MySQL tooling: a consistent logical dump to seed the target, native binlog replication to catch up and stay in sync, and a brief maintenance window to cut over. No DMS, no third-party pipeline, and the row bytes arrive exactly as they were written.

This is the how-to. For the argument about why native replication beats a logical pipeline for same-engine MySQL, see the companion piece, Why AWS DMS is rarely the best MySQL migration option.

Throughout, the source is a MySQL master on another cloud (with a read replica), and the target is AWS RDS MySQL 8.0. The database is called appdb here.

The shape of it

Three phases:

1. Seed. Take a consistent dump from the source that also records the exact binlog position it was taken at. Load it into RDS.
2. Catch up. Point RDS at the source master as a replica, starting from the recorded binlog position. It replays everything written since the dump, then stays in sync in real time.
3. Cut over. In a short maintenance window, stop writes on the source, let replication drain to zero lag, verify with checksums, promote RDS, and point the application at it.

The whole point of recording the binlog position inside the dump is that phases 1 and 2 join up seamlessly. The dump is a snapshot at position X, and replication resumes from exactly position X, so nothing is missed and nothing is applied twice.

Phase 1: the dump

Dump from the replica, not the master, so the primary takes no load. Capture the master's binlog coordinates as part of the dump.

mysqldump \
  --dump-slave=2 \
  --set-gtid-purged=OFF \
  --single-transaction \
  --databases appdb \
  | pv \
  | gzip \
  | aws s3 cp - "s3://<dump-bucket>/appdb-dump.sql.gz"

Every flag is doing load-bearing work:

• --dump-slave=2 briefly stops the replica, reads the master's binlog coordinates (not the replica's own), embeds a commented CHANGE MASTER TO MASTER_LOG_FILE=..., MASTER_LOG_POS=... line in the dump header, and restarts the replica. Those coordinates are the resume point for replication. The =2 makes the statement a comment so it does not execute on load.
• --single-transaction wraps the dump in a single consistent InnoDB snapshot, so you get a point-in-time-consistent dump with no table locks and no interruption to the replica's other work.
• --set-gtid-purged=OFF strips GTID directives. RDS cannot ingest SET @@GLOBAL.gtid_purged, so leaving them in makes the load fail.
• --databases appdb dumps only the application schema. Never dump the mysql system schema: the RDS admin user cannot write to it and the load dies on the first system-table statement.

Stream straight to S3 rather than writing a local file. On the real migration the source machines did not have the disk headroom for a dump that size, and streaming avoids the extra copy step. pv gives you a live throughput readout. Run it inside screen so an SSH drop does not kill a multi-hour dump.

Then extract the binlog position you will need for phase 2:

aws s3 cp s3://<dump-bucket>/appdb-dump.sql.gz - \
  | gunzip \
  | grep -m1 "CHANGE MASTER"
# -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000042', MASTER_LOG_POS=198741;

Note the file and position. That pair is the seam between the snapshot and the live stream.

Phase 1b: the load

Load into RDS from an EC2 instance, not your laptop, streaming back out of S3:

aws s3 cp s3://<dump-bucket>/appdb-dump.sql.gz - \
  | pv --size <compressed-size> \
  | gunzip \
  | mysql -h <rds-endpoint> -u <admin-user> -p appdb

Why EC2 and not local: a long load from a laptop dies when your cloud SSO session expires mid-stream. An EC2 instance with an instance-profile role has no session clock. Run it in screen for the same disconnect-survival reason.

Budget the time honestly. The load is the slow part, and it is bound by RDS write throughput, not network or compute. On the production-sized data (tens of GB compressed, a few hundred GB of uncompressed SQL), the dump ran in a couple of hours and the load took the best part of a day. If you need to speed the load window up, temporarily set innodb_flush_log_at_trx_commit = 2 and sync_binlog = 0 in the RDS parameter group during the load, then revert both before starting replication.

Phase 2: native replication into RDS

Here is the first RDS-specific gotcha. RDS does not let you run CHANGE REPLICATION SOURCE TO or CHANGE MASTER TO directly. It is a managed instance, so replication is configured through stored procedures.

First, on the source master, create a replication user (once):

CREATE USER 'replication'@'%' IDENTIFIED BY '<strong-password>';
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%';
FLUSH PRIVILEGES;

Then on RDS, point it at the master using the dump's coordinates:

CALL mysql.rds_set_external_master(
  '<master-host-or-ip>',
  3306,
  'replication',
  '<strong-password>',
  'mysql-bin.000042',   -- MASTER_LOG_FILE from the dump header
  198741,               -- MASTER_LOG_POS from the dump header
  0                     -- SSL off; revisit for production
);

CALL mysql.rds_start_replication();

Check health:

SHOW SLAVE STATUS\G
-- want: Slave_IO_Running: Yes
--       Slave_SQL_Running: Yes
--       Seconds_Behind_Master: 0
--       Last_IO_Error / Last_SQL_Error: empty

The management procedures you will actually use: mysql.rds_set_external_master, rds_start_replication, rds_stop_replication, and rds_reset_external_master.

The barriers that are not in the AWS docs

These are the parts that turn a two-hour job into a two-day one the first time.

The binlog file from the dump can be gone before you use it. If anything runs RESET MASTER on the source between the dump and starting replication (it can happen as part of a restore or rebuild), the file the dump header points at is deleted and replication fails with error 1236, "could not find first log file". The recovery, safe only if no writes have happened on the source since the dump, is to start from the master's current position instead:

-- on the master
SHOW MASTER STATUS;   -- use this file/position in rds_set_external_master

On a live production master with continuous binlogs this does not arise. It bites you in rehearsals with quiesced sources. Always SHOW MASTER LOGS on the master before configuring replication to confirm the file still exists.

Slave_IO_Running: Connecting forever. This is almost always network. RDS has to reach the source master on 3306, which means the source firewall must allow the AWS NAT gateway egress IPs. There is no useful error, just a permanent "Connecting" state, so check connectivity first.

Access denied on connect (error 1045). The password in rds_set_external_master does not match the replication user. Reset it on the master, then rds_stop_replication, rds_set_external_master again, and rds_start_replication.

Replication stops with duplicate-key errors (error 1062). The target data does not match the expected state at that binlog position, which means the dump and the recorded position are inconsistent. There is no clever fix: redo the dump and load from scratch. This is why the --dump-slave=2 coordinate capture matters so much; it is the thing that keeps the snapshot and the stream consistent.

Phase 3: cutover

This is a short maintenance window, not a big-bang. The sequence:

1. Put the application into maintenance mode.
2. Scale down everything that writes to the database, not just the obvious app servers. Background workers, schedulers, queue consumers, all of it. A single forgotten writer keeps the source moving and you never reach zero lag.
3. Watch Seconds_Behind_Master drain to 0 on RDS. This confirms replication has applied every write up to the moment the last writer stopped.
4. Stop replication cleanly: CALL mysql.rds_stop_replication();
5. Verify (next section).
6. Promote RDS to a standalone primary: CALL mysql.rds_reset_external_master();
7. Point the application's connection config at the RDS endpoint and bring it back up.

The real downtime is "time to drain lag" plus "time to verify" plus "time to flip config". With replication already caught up in real time, the drain is usually seconds.

Verification: prove it byte-for-byte

Do not validate on row-count estimates. MySQL 8.0 caches table statistics with a 24-hour default expiry (information_schema_stats_expiry), so SHOW TABLE STATUS and INFORMATION_SCHEMA.TABLES.TABLE_ROWS return stale numbers right after a bulk load, and you will scare yourself with phantom discrepancies. Use real counts, or better, checksums.

CHECKSUM TABLE on every table, source versus target, is the gold standard. It hashes the actual row content, so a match is positive proof the bytes are identical, including any binary data sitting in latin1 columns that a re-encoding migration tool would have silently altered.

For a large schema, split the tables into batches and run the batches in parallel so the whole verification finishes inside the maintenance window. On the real migration, every table checksummed in roughly an hour split into parallel batches, and they all matched, including the big binary tables.

-- run identically on source and target, compare the Checksum column
CHECKSUM TABLE appdb.table_a, appdb.table_b, appdb.table_c;

If a mismatch shows up, that table did not replicate cleanly and you investigate before promoting. If they all match, you have hard evidence the migration is exact, which is the whole reason to prefer native replication over a tool that transforms data in flight.

Takeaways

• Record the binlog position inside the dump (--dump-slave=2) so the snapshot and the live stream join seamlessly. That single flag is what makes the whole approach work.
• --single-transaction, --set-gtid-purged=OFF, and --databases <db> are not optional against RDS. Each one prevents a specific, load-breaking failure.
• RDS drives replication through stored procedures, not CHANGE MASTER TO.
• Run long dumps and loads from EC2 in screen, never from a laptop with an SSO session clock ticking.
• The load is RDS-write-bound. Budget on throughput, not data size, and tune innodb_flush_log_at_trx_commit and sync_binlog for the load window only.
• Cutover is: drain lag, stop replication, checksum, promote, reconfigure. Scale down every writer, not just the app servers.
• Validate with CHECKSUM TABLE, never with cached row estimates.