MySQL Replication Basics

How MySQL replication works: source and replica setup, binary log-based replication, GTID replication, replication lag, monitoring with SHOW REPLICA STATUS, and common replication problems.

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples in this guide work on VillageSQL. Install Now →

MySQL replication copies changes from one server (the source) to one or more servers (replicas). The source writes every change to the binary log; each replica reads that log and replays the changes. Replication is the foundation for read scaling, high availability, and geographic distribution.

How Replication Works

1. The source commits a transaction and writes it to the binary log.
2. The replica’s I/O thread connects to the source, reads new binlog events, and writes them to a local relay log.
3. The replica’s SQL thread reads from the relay log and executes each event.

This is asynchronous by default — the source doesn’t wait for replicas to confirm receipt. The replica can lag behind the source.

Setting Up a Replica

On the source — enable binary logging and set a unique server ID in my.cnf:

[mysqld]
server_id = 1
log_bin = /var/log/mysql/mysql-bin
binlog_format = ROW

Create a replication user:

CREATE USER 'repl'@'%' IDENTIFIED BY 'repl_password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

Take a consistent snapshot using mysqldump with --source-data:

mysqldump --single-transaction --source-data=1 --all-databases \
  -u root -p > source_snapshot.sql

On the replica — set a different server ID:

[mysqld]
server_id = 2

Restore the snapshot and start replication:

mysql -u root -p < source_snapshot.sql

CHANGE REPLICATION SOURCE TO
    SOURCE_HOST = '192.168.1.10',
    SOURCE_USER = 'repl',
    SOURCE_PASSWORD = 'repl_password',
    SOURCE_AUTO_POSITION = 1;

START REPLICA;

SOURCE_AUTO_POSITION = 1 enables GTID-based replication. If you’re not using GTIDs, specify SOURCE_LOG_FILE and SOURCE_LOG_POS instead (values from the snapshot file header).

GTID Replication

Global Transaction Identifiers (GTIDs) assign a unique ID to every committed transaction. They make failover and replica setup simpler — instead of tracking binlog filenames and positions, MySQL tracks which transactions each server has applied.

Enable GTIDs on both source and replica:

[mysqld]
gtid_mode = ON
enforce_gtid_consistency = ON

With GTIDs enabled, SOURCE_AUTO_POSITION = 1 is all you need — MySQL figures out which transactions the replica is missing and replays them automatically.

Monitoring Replication

SHOW REPLICA STATUS\G

Key fields to watch:

Field	What it means
Replica_IO_Running	Yes = I/O thread connected and reading from source
Replica_SQL_Running	Yes = SQL thread applying events
Seconds_Behind_Source	Replication lag in seconds (0 = caught up)
Last_SQL_Error	Last error that stopped the SQL thread
Executed_Gtid_Set	GTIDs this replica has applied (GTID mode)

Both Replica_IO_Running and Replica_SQL_Running must be Yes for replication to be working.

Replication Lag

Replication lag happens when the replica’s SQL thread can’t keep up with the source’s write rate. Common causes:

• Single-threaded SQL thread: by default, replicas apply events serially. Enable parallel replication with replica_parallel_workers:

SET GLOBAL replica_parallel_workers = 4;
SET GLOBAL replica_parallel_type = 'LOGICAL_CLOCK';

• Long-running queries on the replica: queries that lock rows block the SQL thread.
• Network latency: slow I/O thread causes the SQL thread to run out of work.
• Disk I/O on replica: syncing relay log or data files is the bottleneck.

Replication Architectures

The default setup — one source, one or more async replicas — isn’t the only option. MySQL offers several replication architectures with different consistency and availability trade-offs.

Architecture	Consistency guarantee	Failover	Write overhead	Complexity
Async replication (default)	None — replica may lag arbitrarily	Manual; some transactions may be lost	Minimal — source doesn’t wait	Low
Semi-sync replication	At least one replica has received the event before the source commits	Manual; at most one transaction lost	~1 RTT added to each write	Low–Medium
Group Replication	All members agree before commit (multi-primary or single-primary)	Automatic within the group	Higher — consensus round per transaction	High
InnoDB Cluster	Same as Group Replication (built on it)	Automatic with MySQL Router for client failover	Same as Group Replication	High

When to use each:

• Async replication — the right default for read scaling and disaster-recovery replicas where some lag is acceptable.
• Semi-sync — when you need durability insurance against source failure but can’t tolerate the operational complexity of Group Replication. Adds ~1 network round trip per write commit.
• Group Replication / InnoDB Cluster — when you need automatic failover and can afford the write latency increase. InnoDB Cluster wraps Group Replication with MySQL Router and MySQL Shell for easier management.

Note: Group Replication requires all tables to use InnoDB and have a primary key. Enable it with the group_replication plugin; configuration is beyond the scope of this guide.

Read Scaling with Replicas

Direct read-heavy queries to replicas to reduce load on the source:

-- Application connects to replica for reads
SELECT * FROM orders WHERE customer_id = 42;

-- Connect to source for writes
INSERT INTO orders (customer_id, total) VALUES (42, 99.99);

The application must tolerate replication lag. A replica may not have the row inserted a few milliseconds ago on the source. For reads that must reflect the latest write, connect to the source.

Replication vs. Alternatives

Replication solves specific problems well and is the wrong tool for others. Before reaching for it, check whether it actually fits.

Scenario	Replication	Sharding	Read replicas only	Distributed DB (e.g., Vitess, TiDB)
Read-heavy workload, writes fit on one server	Good fit	Overkill	Simpler — just add replicas	Overkill
Write volume exceeds one server	Won’t help — all replicas replay every write	Good fit	Won’t help	Good fit
HA / automatic failover	Needs Group Replication or InnoDB Cluster	Doesn’t help directly	Doesn’t help directly	Built in
Geo-distribution / low-latency reads in multiple regions	Works well with delayed or regional replicas	Possible but complex	Works well	Built in for some
Point-in-time recovery	Good — binlog enables PITR	Complicates PITR	Good	Varies
Schema is highly partitionable by tenant/key	Works but wastes replica write bandwidth	Better fit	Works but same waste	Good fit
Tight read-your-writes consistency required	Use source for those reads	N/A	Use source for those reads	Depends on product

The short version: replication is the right call when one server handles all your writes and you need read scale, HA, or a warm standby. If your write throughput is the bottleneck, replication copies the problem to every replica — sharding or a distributed database addresses it at the source.

Stopping and Starting Replication

STOP REPLICA;           -- stop both threads
STOP REPLICA IO_THREAD; -- stop only the I/O thread
STOP REPLICA SQL_THREAD; -- stop only the SQL thread

START REPLICA;

To skip a single failing event (use carefully — skipping events causes the replica to diverge):

-- GTID mode: inject an empty transaction for the failed GTID
SET GTID_NEXT = 'source_uuid:N';
BEGIN; COMMIT;
SET GTID_NEXT = 'AUTOMATIC';
START REPLICA;

Frequently Asked Questions

Does replication work across MySQL major versions?

MySQL supports replicating from an older source to a newer replica, but not the reverse. Always upgrade replicas before upgrading the source.

What’s the difference between semi-synchronous and asynchronous replication?

In asynchronous replication (the default), the source commits without waiting for any replica to acknowledge receipt. In semi-synchronous replication, the source waits for at least one replica to confirm it received the event before returning to the client. Semi-sync reduces the risk of data loss on source failure, at the cost of slightly higher write latency.

Troubleshooting

Problem	Solution
Replica_IO_Running: No	Check Last_IO_Error — typically a connection or auth failure; verify SOURCE_HOST, user, password, and REPLICATION SLAVE grant
Replica_SQL_Running: No	Check Last_SQL_Error — usually a data conflict; decide whether to skip the event or fix the root cause
Replication lag growing	Enable replica_parallel_workers; check for long-running queries on the replica blocking the SQL thread
ERROR 1062: Duplicate entry on replica	Replica has a row the source doesn’t — indicates manual writes to the replica; use replica_skip_errors cautiously or resync
GTID gap after failover	New source is missing GTIDs the old source had applied — use SET GTID_PURGED carefully during failover

See also

• MySQL Binary Logging — the binary log that replication streams from source to replica
• MySQL Backup Strategies — complementary strategy to replication for durability