Part 2 focuses on the most operationally sensitive part of classic replication: provisioning replicas from a consistent source snapshot.

Once your source and replicas are configured correctly, the next job is to capture a known-good data state and align the replicas to the matching binary log position.

Step 1: Confirm the Source Is Ready

Before taking a snapshot, verify that binary logging is enabled and that the source is healthy.

Useful checks:

SHOW VARIABLES LIKE 'log_bin';
SHOW VARIABLES LIKE 'binlog_format';
SHOW MASTER STATUS;

You should also verify that the replication user already exists and that network access from the replicas is possible.

Step 2: Lock the Source Long Enough to Capture Coordinates

To align the dump with a precise binary log position, place the source under a read lock, capture the coordinates, and keep the lock only as long as necessary.

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

The output from SHOW MASTER STATUS gives you the two values the replicas need:

  • File
  • Position

Make a note of both before moving on.

Step 3: Take a Logical Backup

While the lock is in place, create the bootstrap dump from the source.

Example:

mysqldump -uroot -p \
  --all-databases \
  --triggers \
  --routines \
  --events \
  --source-data \
  --set-gtid-purged=OFF \
  > replication_db_dump.sql

This combination works well for a full-environment bootstrap because it captures:

  • All databases
  • Stored routines
  • Events
  • Triggers
  • Source log metadata inside the dump file

When the dump is complete, release the read lock:

UNLOCK TABLES;

Step 4: Load the Snapshot on Each Replica

Copy the dump file to each replica and import it before enabling replication.

Example import flow:

mysql -uroot -p < replication_db_dump.sql

At this point, the replica has the same logical data set as the source had at the captured binlog coordinate.

Step 5: Point Each Replica at the Source

Now configure replication using the recorded binary log file and position.

Example:

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='10.0.0.10',
  SOURCE_USER='replication_user',
  SOURCE_PASSWORD='<strong-password>',
  SOURCE_LOG_FILE='mysql-bin.000001',
  SOURCE_LOG_POS=1482,
  GET_SOURCE_PUBLIC_KEY=1;

If you are working with older syntax or legacy automation, you may still see CHANGE MASTER TO. On current MySQL 8.0 builds, CHANGE REPLICATION SOURCE TO is the preferred form.

Step 6: Start Replication

Once the source coordinates are configured, start the replica threads.

START REPLICA;
SHOW REPLICA STATUS\G

These fields matter most during first validation:

  • Replica_IO_Running: Yes
  • Replica_SQL_Running: Yes
  • Seconds_Behind_Source: 0 or a small transient value
  • Replica_SQL_Running_State showing the replica has caught up and is waiting for new events

Step 7: Validate End-to-End Replication

A simple validation pattern is to create test objects on the source and confirm that they appear on each replica.

Example:

CREATE DATABASE db1;
CREATE DATABASE db2;
CREATE DATABASE db3;

Then on a replica:

SHOW DATABASES;

If you want a stronger test, create a table and insert a few rows, then verify both schema and data replication.

Common Failure Points

If the replica does not start cleanly, check these first:

  • Wrong source host or port
  • Incorrect replication credentials
  • Wrong SOURCE_LOG_FILE or SOURCE_LOG_POS
  • Duplicate server-id or server_uuid
  • Firewall or network path issues

Binlog-based replication is reliable, but it demands careful coordinate handling. That manual dependency is exactly why many teams prefer GTID once the baseline topology is working.

In Part 3, I move the same topology to GTID-based replication and show how auto-positioning simplifies source alignment.

<
Previous Post
Practical MySQL Replication and Scalability - Part 1: Replication Models and Binlog Prerequisites
>
Next Post
Practical MySQL Replication and Scalability - Part 3: Moving to GTID-Based Replication