Reducing MySQL Migration Downtime with mysqldump and Binary Logs

One of the things I enjoy about database consulting is discovering another way to solve a familiar problem.

During a recent MySQL migration, I learned that you can restore a base backup created with mysqldump, then apply binary logs to bring the new server forward. I knew about dumps and binary logs individually, but I hadn’t used them together this way.

It turned out to be a useful technique for minimizing the downtime required to switch to a new server.

Our dump and restore took approximately 11 hours. By starting the dump 24 hours before the planned cutover, we gave ourselves time to complete the initial load and catch up on changes while the original server remained in use.

That head start mattered: by the time the restore finished, seven binary logs were already waiting to be applied.

Moving the bulk of the work ahead of cutover

With a straightforward offline migration, the application stays down while you dump the database, transfer it, restore it, and validate the new server.

For this migration, that would have put an 11-hour dump and restore directly into the outage window.

Combining a base restore with binary log replay changes the timing. The dump provides the starting point, and the binary logs supply the changes recorded afterward. MySQL documents this combination for backup and recovery; the same mechanism can help prepare a migration target.

The source can continue serving the application while the initial data is loaded. Once that load finishes, the target can begin catching up.

The critical detail is knowing exactly where that catch-up process should start.

Capturing the binary log file and position

The key mysqldump option is:

--source-data=2

For older clients, the equivalent option is:

--master-data=2

--source-data was introduced in MySQL 8.0.26. The value 2 writes the binary log coordinates into the dump as a commented SQL statement, so restoring the dump does not execute replication configuration.

For an InnoDB workload, combine it with --single-transaction to align the coordinates with a consistent snapshot. This combination briefly takes a global read lock at startup. Nontransactional tables need separate consistency planning, and schema changes should be avoided during the dump.

An illustrative command looks like this:

mysqldump \
  --host=source-server \
  --user=backup_user \
  --password \
  --single-transaction \
  --source-data=2 \
  --routines \
  --events \
  --triggers \
  --databases appdb \
  > base_dump.sql

These are example names and options, not the exact command from our migration. Database scope, permissions, version compatibility, and GTID handling need to match the environment.

Inside the dump, the recorded coordinates look something like this:

-- CHANGE REPLICATION SOURCE TO
-- SOURCE_LOG_FILE='mysql-bin.000120',
-- SOURCE_LOG_POS=456789;

Older output uses CHANGE MASTER TO, MASTER_LOG_FILE, and MASTER_LOG_POS.

Those coordinates identify where replay should begin. Use the position recorded by the dump—not a position collected after the dump finishes. Changes made while the dump was running are part of what the new server needs to catch up on.

Restoring the base and applying the backlog

The base restore is familiar:

mysql \
  --host=target-server \
  --user=migration_user \
  --password \
  < base_dump.sql

After restoring, use mysqlbinlog to read the required binary logs and pass the resulting statements to the target server.

For example, if the dump recorded mysql-bin.000120 at position 456789, and seven complete files were ready:

mysqlbinlog \
  --start-position=456789 \
  mysql-bin.000120 \
  mysql-bin.000121 \
  mysql-bin.000122 \
  mysql-bin.000123 \
  mysql-bin.000124 \
  mysql-bin.000125 \
  mysql-bin.000126 \
  | mysql \
      --host=target-server \
      --user=migration_user \
      --password \
      --binary-mode

--start-position applies only to the first file listed. The subsequent files are processed from their beginnings. MySQL recommends processing multiple logs through a single target connection.

This example assumes the logs contain changes for the restored database scope. Binlogs can contain activity from multiple databases, so a partial migration needs a carefully tested replay strategy.

Every required file must be available and applied in order. Keep a record of the last successfully applied boundary, check for errors, and avoid replaying transactions already applied. For ongoing catch-up, use complete, safely collected logs or a supported streaming method rather than copying an actively changing file.

Why starting 24 hours early mattered

The timeline was a major part of making this approach useful:

| Point in the migration | What was happening |

|---|---|

| Approximately 24 hours before cutover | Started the base dump |

| Approximately 11 hours later | Dump and restore completed; seven binlogs were waiting |

| Remaining time before cutover | Applied the backlog and continued catching up |

| Cutover window | Stopped source writes, applied the final changes, validated, and switched connections |

Starting a day early gave us roughly 13 hours after the initial load to work through the backlog and prepare for the switch.

Seven binlogs do not translate into a fixed replay duration. File sizes, transaction volume, and target performance all matter. The practical question is whether the target can apply changes faster than the source produces them.

The 24-hour lead time was part of our migration plan, not a universal rule. A rehearsal helps determine how much runway a particular workload needs.

Keeping the final outage small

At cutover, the goal is to have only a small amount of work left:

1. Stop application writes and other writers on the source, allowing in-flight transactions to finish.

2. Record the final source binary log file and position.

3. Apply all remaining changes through that boundary.

4. Validate the target and redirect application connections.

5. Enable application writes and scheduled work on the new server.

The target should remain isolated from independent application writes during catch-up. Scheduled events and jobs also need to be controlled so they do not modify data before cutover.

Binary log retention deserves attention before the migration starts. The required logs must remain available for the entire dump, restore, and catch-up period, with additional room for delays.

What made this technique valuable for us was the ability to complete the long-running data movement while the original system remained available. The 11-hour dump and restore still had to happen, but we could do that work ahead of the switchover.

For anyone planning a MySQL migration, this is a useful approach to understand—and one worth testing before deciding how much downtime the move requires.