> ## 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 title="VillageSQL is a drop-in replacement for MySQL with extensions." icon="database" href="/mysql-8.4/0.0.4/quickstart">
  All examples in this guide work on VillageSQL. Install Now →
</Card>

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:

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

```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                  | 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](/guides/binary-logging) — the binary log that replication streams from source to replica
* [MySQL Backup Strategies](/guides/backup-strategies) — complementary strategy to replication for durability