MySQL replication is one of the most relied-upon features in production MySQL environments. It powers read scaling, disaster recovery, analytics offloading, and geographic redundancy.
It is also one of the easiest features to break quietly. Replication can fail in ways that produce no obvious error, no alert, and no user complaint — until someone notices that the data on a replica is hours behind the source. Or until a failover is triggered and the replica turns out not to be a viable standby.
This guide covers how replication works, how to set it up, and how to monitor it so you always know what state it is in.
MySQL replication is asynchronous by default. Changes on the source are written to a binary log. The replica reads that binary log and applies the same changes to its own copy of the data.
Because this process is asynchronous, the replica can lag behind the source. Managing that lag — and knowing when it has grown too large — is one of the core responsibilities of running replication in production.
Add to my.cnf :
[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
Create a dedicated replication user:
CREATE USER ‘replication_user’@’replica_ip’
IDENTIFIED BY ‘strong_password’;
GRANT REPLICATION SLAVE ON *.*
TO ‘replication_user’@’replica_ip’;
FLUSH PRIVILEGES;
Take a consistent snapshot and note the binary log position:
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
— Note the File and Position values
UNLOCK TABLES;
Add to my.cnf :
[mysqld]
server-id = 2
relay_log = /var/log/mysql/mysql-relay.log
read_only = 1
Import the source snapshot, then connect to the source:
CHANGE MASTER TO
MASTER_HOST = ‘source_ip’,
MASTER_USER = ‘replication_user’,
MASTER_PASSWORD = ‘strong_password’,
MASTER_LOG_FILE = ‘mysql-bin.000001’,
MASTER_LOG_POS = 12345;
START SLAVE;
SHOW SLAVE STATUS\G
Look for: Slave_IO_Running: Yes , Slave_SQL_Running: Yes , and Seconds_Behind_Master: 0 (or a low number).
GTID (Global Transaction Identifier) replication assigns a unique ID to every transaction instead of tracking binary log file and position. This makes failover, replica promotion, and topology changes significantly easier.
Enable in my.cnf :
[mysqld]
gtid_mode = ON
enforce_gtid_consistency = ON
If you are starting a new replication environment in 2026, use GTID.
The dangerous characteristic of most replication failures is that they are quiet. The application continues working against the source. The replica falls behind or stops. Nobody knows until a human checks or an incident triggers a failover.
SHOW SLAVE STATUS gives you a snapshot. It tells you nothing about what happened in the past hour while you were not watching. By the time a human runs a manual check, a failure may have been running for hours.
MONyog monitors replication automatically — displaying lag, thread status, and errors across all monitored servers from a single dashboard. Configure alerts to notify you the moment any metric crosses a threshold.
Need to monitor MySQL replication across multiple servers? MONyog provides real time replication status, lag monitoring, and automatic alerts from a single dashboard. Start your free trial.
Replication lag is how far behind the replica is in applying source changes, measured in
seconds. For a DR or failover replica, keep this under 10 seconds at all times — ideally near
zero. For an analytics-only replica, several minutes may be acceptable. Define your threshold
based on use case and configure alerts to match.
In standard single-source replication, no — and read_only = 1 should be set to prevent it.
Writing directly to a replica can cause duplicate key errors that stop the SQL thread, leading
to silent data divergence. For multi-write setups, use MySQL Group Replication or InnoDB
Cluster, which are designed for this.
Position-based replication tracks a binary log file name and byte offset. GTID replication
assigns a unique identifier to every transaction. GTIDs make failover cleaner, prevent
accidentally replaying or skipping transactions, and simplify topology changes. New setups in
2026 should use GTID.
Seconds_Behind_Master can be misleading. It measures the timestamp of the last applied
event, but if the SQL thread is idle or has stopped without updating status, it may report 0
incorrectly. Continuous monitoring through MONyog is more reliable than periodic manual
checks.
No. MySQL and MariaDB binary log formats are incompatible. They cannot share a
replication chain. If migrating between the two, use a dump-and-restore approach.
No. Replication copies all changes — including accidental DELETE statements — from
source to replica in near real time. Replication protects against hardware failure.
Independent backups protect against logical data errors. You need both.
First, check Last_SQL_Error in SHOW SLAVE STATUS to understand the cause. If the error is
minor and safe to skip, you can advance past it with SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1
after stopping the slave — but use this carefully and only when you have confirmed the
skipped event is harmless. For significant divergence, a full resync from a fresh source
backup is the safest path.
In standard asynchronous replication, a source can support many replicas — commonly 5–
20 in production environments. Very large replica sets use chain replication (replica acting as
sub-source for additional replicas) to reduce load on the primary source. The practical limit
depends on available network bandwidth and the source’s write throughput.
