How to Set up MySQL Replication

Replication is the point where I feel like I graduated from “person who runs a database” to “person who runs database infrastructure.” The first time I set up a working primary-replica pair and watched data appear on the replica seconds after inserting it on the primary, it genuinely felt like magic — even though I now understand exactly what’s happening under the hood. In this guide, I’ll walk through binary log replication in MySQL from the fundamentals to a full production setup.

Why Replication Matters

I set up replication for a few recurring reasons in my own projects and client work:

• Read scaling – routing read-heavy traffic (reports, dashboards, search) to one or more replicas so the primary isn’t overloaded.
• High availability – promoting a replica to primary if the original server fails.
• Backups without impacting production – running mysqldump or logical backups against a replica instead of the live primary.
• Geographic distribution – placing replicas closer to users in different regions to reduce read latency.

How MySQL Replication Works Internally

MySQL replication is fundamentally based on the binary log (binlog) — a sequential record of every data-changing statement or row-level change made on the primary.

1. The primary writes every change to its binary log after the transaction commits.
2. Each replica runs an I/O thread that connects to the primary and streams new binlog events into its own relay log.
3. A separate SQL thread (or multiple parallel worker threads in modern MySQL) reads the relay log and applies those changes to the replica’s own data.

sequenceDiagram
    participant Primary
    participant BinLog as Primary Binary Log
    participant IOThread as Replica I/O Thread
    participant RelayLog as Replica Relay Log
    participant SQLThread as Replica SQL Thread
    participant ReplicaDB as Replica Database

    Primary->>BinLog: Write committed transaction
    IOThread->>BinLog: Request new events
    BinLog-->>IOThread: Stream binlog events
    IOThread->>RelayLog: Write to relay log
    SQLThread->>RelayLog: Read events
    SQLThread->>ReplicaDB: Apply changes

Replication Formats

SHOW VARIABLES LIKE 'binlog_format';

Step 1: Configuring the Primary Server

On the primary, I edit /etc/mysql/mysql.conf.d/mysqld.cnf:

[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
binlog_do_db = shop_db
expire_logs_days = 7

• server-id must be unique across every server in the replication topology — I always document these carefully to avoid conflicts.
• log_bin enables the binary log; without it, replication simply cannot function.
• expire_logs_days prevents binlogs from growing forever and consuming all available disk space.

I restart MySQL to apply these changes:

sudo systemctl restart mysql

Step 2: Creating a Replication User on the Primary

CREATE USER 'repl_user'@'10.0.0.%' IDENTIFIED WITH mysql_native_password BY 'ReplPass123!';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'10.0.0.%';
FLUSH PRIVILEGES;

I scope this user’s host to my internal network range only, since replication credentials should never be reachable from the public internet.

Step 3: Taking a Consistent Snapshot of the Primary

Before starting replication, the replica needs an initial copy of the data that’s consistent with a known binlog position. I use mysqldump with the right flags for smaller databases:

mysqldump -u root -p --all-databases --source-data=2 --single-transaction --routines --triggers > primary_snapshot.sql

• --single-transaction ensures a consistent InnoDB snapshot without locking the whole database.
• --source-data=2 embeds the exact binlog file and position as a commented CHANGE REPLICATION SOURCE TO statement inside the dump.

For much larger databases, I prefer Percona XtraBackup instead, since it performs a physical hot backup without the overhead of a full logical dump.

Step 4: Loading the Snapshot on the Replica

mysql -u root -p < primary_snapshot.sql

Step 5: Configuring the Replica Server

On the replica, I set a unique server-id:

[mysqld]
server-id = 2
relay_log = /var/log/mysql/mysql-relay-bin.log
read_only = ON

I always set read_only = ON on replicas to prevent accidental writes that would desync the topology — application code should only ever write to the primary.

Step 6: Pointing the Replica at the Primary

Using the binlog file and position I found inside the dump file’s header comment (or via SHOW MASTER STATUS on older versions, SHOW BINARY LOG STATUS in MySQL 8.4+):

CHANGE REPLICATION SOURCE TO
    SOURCE_HOST = '10.0.0.10',
    SOURCE_USER = 'repl_user',
    SOURCE_PASSWORD = 'ReplPass123!',
    SOURCE_LOG_FILE = 'mysql-bin.000003',
    SOURCE_LOG_POS = 154;

START REPLICA;

(Older MySQL/MariaDB versions use CHANGE MASTER TO and START SLAVE — the underlying mechanism is identical.)

Step 7: Verifying Replication Status

SHOW REPLICA STATUS\G

I always check two fields most closely:

Replica_IO_Running: Yes
Replica_SQL_Running: Yes
Seconds_Behind_Source: 0

If both threads show “Yes” and Seconds_Behind_Source is low, replication is healthy. If either thread shows “No,” I check Last_IO_Error or Last_SQL_Error for the specific failure reason.

Replication Topologies I’ve Worked With

graph TD
    subgraph Simple Primary-Replica
    P1[Primary] --> R1[Replica 1]
    P1 --> R2[Replica 2]
    end

graph TD
    subgraph Chained Replication
    P2[Primary] --> R3[Replica A]
    R3 --> R4[Replica B - relays from Replica A]
    end

Setting Up Semisynchronous Replication

For workloads where I care more about data safety than raw write throughput, I enable semisynchronous replication:

On the primary:

INSTALL PLUGIN rpl_semi_sync_source SONAME 'semisync_source.so';
SET GLOBAL rpl_semi_sync_source_enabled = 1;

On the replica:

INSTALL PLUGIN rpl_semi_sync_replica SONAME 'semisync_replica.so';
SET GLOBAL rpl_semi_sync_replica_enabled = 1;

This ensures the primary waits for acknowledgment from at least one replica before confirming a commit to the client, which meaningfully reduces the risk of losing recently committed transactions during a primary failure.

A Real-World Scenario: Read Scaling for a High-Traffic Blog Platform

For a client whose blog platform was seeing heavy read traffic from search engine crawlers and cached page misses, I set up:

1. One primary handling all writes (new posts, comments, edits).
2. Two read replicas behind a ProxySQL load balancer, splitting SELECT traffic across both.
3. Application-layer routing: all INSERT/UPDATE/DELETE statements went to the primary’s connection string; all SELECT statements went through ProxySQL, which automatically routed reads to whichever replica had the lowest replication lag.
4. Monitoring Seconds_Behind_Source continuously, with alerting if lag exceeded 5 seconds, since stale reads on a “your comment was posted” confirmation page would have been a poor user experience.

Security Considerations for Replication

• I scope the replication user’s host to the internal network only, never '%'.
• I enable SSL for replication traffic between primary and replicas, especially across regions or over any network I don’t fully control:

CHANGE REPLICATION SOURCE TO SOURCE_SSL = 1, SOURCE_SSL_CA = '/path/to/ca.pem';

• I set read_only = ON (or super_read_only = ON to also block privileged accounts) on every replica to prevent accidental writes.

Troubleshooting Common Replication Issues

Issue: Replica_SQL_Running shows “No”

I check:

SHOW REPLICA STATUS\G

Looking specifically at Last_SQL_Error. Common causes include a duplicate key error from a write that accidentally hit the replica, or a DDL statement that didn’t replicate cleanly. I resolve simple cases by skipping the problematic transaction (used cautiously) or, more safely, by re-syncing from a fresh snapshot.

Issue: High replication lag (Seconds_Behind_Source climbing)

I check whether the replica’s hardware is underpowered relative to the primary, whether large batch jobs are running on the replica competing for I/O, and whether I should enable multi-threaded replication:

SET GLOBAL replica_parallel_workers = 4;

Issue: Replica falls out of sync entirely

I re-provision from a fresh mysqldump or XtraBackup snapshot rather than trying to patch a badly diverged replica — trying to manually reconcile divergent data is rarely worth the time investment compared to a clean re-sync.

Performance Best Practices for Replication

• I use row-based replication (RBR) for consistency, especially with non-deterministic functions like UUID() or NOW().
• I enable multi-threaded replica applier threads for write-heavy primaries.
• I monitor replication lag continuously and alert on it, since stale replicas silently serving old data is a subtle but serious bug source.
• I keep replica hardware at least as capable as the primary, since a single-threaded historical bottleneck on the SQL thread can cause replicas to fall progressively further behind under sustained write load.

Frequently Asked Questions

Q: What’s the difference between synchronous, semisynchronous, and asynchronous replication? A: Asynchronous (MySQL’s default) doesn’t wait for replica acknowledgment at all. Semisynchronous waits for at least one replica to acknowledge receipt before confirming commit. True synchronous replication (like in Group Replication with certain configurations) requires stronger consensus before committing.

Q: Can a replica have its own replicas? A: Yes, this is called chained replication, and I use it to reduce load on the primary when many replicas need to be fed.

Q: How do I safely promote a replica to primary during a failover? A: I stop replication on the chosen replica, verify it has applied all available relay log events, set read_only = OFF, and repoint application traffic and any remaining replicas to the new primary.

Q: Does replication protect against data loss the same way backups do? A: No — replication protects against server failure, but a mistaken DROP TABLE on the primary replicates to every replica too. Backups and replication solve different problems and I always maintain both.

Interview Questions I’ve Encountered

1. Explain the roles of the I/O thread and SQL thread in MySQL replication.
2. What’s the difference between statement-based and row-based replication?
3. How would you set up semisynchronous replication, and why would you choose it?
4. How would you troubleshoot a replica that’s falling increasingly behind the primary?
5. Why is replication not a substitute for backups?
6. Describe how you would perform a failover from a primary to a replica with minimal downtime.

Summary and Key Takeaways

Setting up MySQL replication taught me more about how the database actually works internally than almost any other feature, since it forces me to understand binary logs, consistent snapshots, and thread-based change application in real depth. I now treat replication as a standard part of any production MySQL deployment I build, both for read scaling and for high availability.

Key takeaways:

• Replication relies on the binary log, streamed via an I/O thread and applied via an SQL thread.
• Always use row-based replication for consistency in modern deployments.
• Take a consistent snapshot with the correct binlog position before initializing a replica.
• Monitor Seconds_Behind_Source continuously and alert on replication lag.
• Replication complements, but never replaces, a proper backup strategy.

References

• MySQL 8.0 Reference Manual, Replication: https://dev.mysql.com/doc/refman/8.0/en/replication.html
• MySQL Replication Formats: https://dev.mysql.com/doc/refman/8.0/en/replication-formats.html
• MySQL Semisynchronous Replication: https://dev.mysql.com/doc/refman/8.0/en/replication-semisync.html