> ## Documentation Index
> Fetch the complete documentation index at: https://villagesql.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# 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.

> [card] [VillageSQL is a drop-in replacement for MySQL with extensions.](https://villagesql.com/docs/en/8.4/stable/install-server.md)
>
> All examples on this page 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`:

```ini theme={null}
[mysqld]
server_id = 1
log_bin = /var/log/mysql/mysql-bin
binlog_format = ROW
```

Create a replication user:

```sql theme={null}
CREATE USER 'repl'@'%' IDENTIFIED BY 'repl_password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
```

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

```bash theme={null}
mysqldump --single-transaction --source-data=1 --all-databases \
  -u root -p > source_snapshot.sql
```

**On the replica** — set a different server ID:

```ini theme={null}
[mysqld]
server_id = 2
```

Restore the snapshot and start replication:

```bash theme={null}
mysql -u root -p < source_snapshot.sql
```

```sql theme={null}
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:

```ini theme={null}
[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

```sql theme={null}
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:

* **Applier parallelism**: MySQL 8.4 already applies events in parallel by default — `replica_parallel_workers` is 4 and `replica_parallel_type` is `LOGICAL_CLOCK`. Raise the worker count if the replica still falls behind:

```sql theme={null}
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:

```sql theme={null}
-- 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

```sql theme={null}
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):

```sql theme={null}
-- 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 | Raise `replica_parallel_workers` above its default of 4; check for long-running queries on the replica blocking the applier |
| `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](https://villagesql.com/docs/en/guides/binary-logging.md) — the binary log that replication streams from source to replica
* [MySQL Backup Strategies](https://villagesql.com/docs/en/guides/backup-strategies.md) — complementary strategy to replication for durability
* [Read Replicas in MySQL](https://villagesql.com/docs/en/guides/read-replicas.md) — using the replicas this guide sets up


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.