Understanding MySQL Replication: From Asynchronous to Group and Cluster Solutions

This article reviews MySQL replication concepts and several replication solutions, while clarifying common misconceptions.

What is MySQL Replication?

Replication protects data by creating a backup in a separate environment, typically a master‑slave setup where the backup resides on a different server.

MySQL Replication Solutions

MySQL offers several replication options:

1. Asynchronous replication

In asynchronous replication, the client transaction completes on the master without waiting for the slave; the master writes changes to the binary log, a dump thread sends them to the slave, which stores them in a relay log and then executes them.

2. Semi‑synchronous replication

The master waits for at least one slave to acknowledge that it has written the transaction to its relay log before committing, improving data safety at the cost of some performance.

3. Group replication

Introduced in MySQL 5.7, group replication uses a plugin that requires a majority of nodes to agree before a transaction is committed, providing near‑synchronous consistency across the cluster.

4. Percona XtraDB Cluster / Galera Cluster

This solution focuses on strong consistency using a multi‑master approach; each node broadcasts transaction commits via the Galera library, ensuring all nodes apply the same changes or roll back.

Row‑Based vs. Statement‑Based Replication

Statement‑based replication records the original SQL statements in the binary log, which aids auditing and reduces log size but can cause inconsistencies with non‑deterministic queries. Row‑based replication records the actual row changes, offering higher safety and consistency at the expense of increased network traffic and larger logs.

Statement‑based advantages: easier audit, less data transfer, smaller logs.

Statement‑based drawbacks: nondeterministic queries, performance penalties for certain operations, potential consistency issues.

Row‑based advantages: every change is replicated, better performance for small‑row updates, improved consistency.

Row‑based drawbacks: higher network usage, slower for large‑row operations, harder to audit SQL.

Common Misconceptions About Replication

Misconception 1: Replication is a cluster

Standard asynchronous or semi‑synchronous replication does not provide the same guarantees as a true cluster; only solutions like Percona XtraDB Cluster ensure that any request to any node yields identical results.

Misconception 2: Replication as a manual failover solution

While replication can aid availability, it does not guarantee transaction correctness on slaves without additional configuration, and performance trade‑offs must be considered.

Misconception 3: Replication eliminates the need for backups

Replication provides a live copy for read scaling, but backups (typically offline) are essential for disaster recovery.

Misconception 4: Replication automatically load‑balances writes

Writes must still be directed to the master; reads can be offloaded to slaves, often requiring external routing tools.