1.2 MySQL Primary-Replica Replication

1. What Is Primary-Replica Replication

• Synchronize data from the primary database to the replica database.

2. The Role of Primary-Replica Replication

Distributed System Replication.md

3. Use Cases for Primary-Replica Replication

• Read-heavy and write-light workloads where reads do not require very high data freshness.

4. How to Enable Primary-Replica Replication

• my.ini

  [mysqld]
  log-bin=mysql-bin # Enable binlog
  binlog-format=ROW # Select ROW mode
  server_id=1 # MySQL replication requires this to be defined; do not duplicate canal's slaveId

5. Principles of Primary-Replica Replication

When MySQL inserts, deletes, or updates data, in addition to updating the data, it also writes those changes to the binlog. MySQL bin-log.md

1. The primary starts a new log dump thread to read the bin-log and communicate with the replica’s I/O thread.
2. The replica’s I/O thread writes the bin-log sent by the primary into the relay-log.
3. The replica’s SQL thread reads the relay-log and updates the database.

6. Primary-Replica Replication Lag

6.1. What Is Primary-Replica Replication Lag

The primary has written data, but the replica has not synchronized it yet. If the client connects to the replica at this time, it cannot read that data.

6.2. Why Does Primary-Replica Replication Lag Occur

• Root cause: a consistency problem. Because primary-replica replication is asynchronous, after the primary writes data, if the replica has not synchronized it yet, reading from the replica will be inconsistent.
• Surface causes: assume the transaction execution time on the primary is T1, the time it is received by the replica is T2, and the time it finishes executing on the replica is T3.
  • T3-T1 is the primary-replica delay.
  • T2-T1 is the network transmission time. If the machines are across networks, this transmission time will be relatively long.
  • T3-T2 is the transaction execution time. If it is a large transaction or the replica machine has poor performance, this execution time will be relatively long.

6.3. How to Solve Primary-Replica Replication Lag

6.3.1. Semi-Synchronous Replication

• There are three primary-replica replication modes: asynchronous replication, fully synchronous replication, and semi-synchronous replication.
• MySQL uses asynchronous replication by default.
  • After the client submits to the primary, the primary receives and finishes executing before returning success to the client.
• It can be configured as fully synchronous replication.
  • After the client submits to the primary, the primary receives and finishes executing, then waits until all replicas receive and finish executing before returning success to the client.
• A plugin can be used to configure semi-synchronous replication.
  • After the client submits to the primary, the primary receives and finishes executing, then waits until any one replica receives it and writes it to the relay-log before returning success to the client.

6.3.2. Parallel Replication

6.3.2.1. What Is Parallel Replication

• If the replica SQL thread replays SQL using multiple threads, that is parallel replication.

6.3.2.2. Why Parallel Replication Is Needed

• Before the official 5.6 version, MySQL supported only single-threaded replication to avoid ordering problems. However, when the primary has high concurrency and high TPS, a single thread can cause serious primary-replica delay, so a parallel replication mechanism was introduced.

6.3.2.3. Principles of Parallel Replication

• Statements from the same transaction must be placed in the same thread.
• Two transactions that update the same row must be dispatched to the same thread.

6.3.2.4. Parallel Replication Strategies

6.3.2.4.1. MySQL 5.6 Schema-Based Parallel Replication

• The replica starts multiple threads to read logs from different databases in the relay log in parallel, and then writes to different databases.
• This is database-level parallelism.

6.3.2.4.2. MySQL 5.7 Group-Commit-Based Parallel Replication

• redo log group commit.

6.3.2.4.3. MySQL 8.0 Write-Set-Based Parallel Replication

• Conflict detection based on primary keys.

6.3.3. Stale Reads in Read-Write Splitting

For strong-consistency scenarios, solutions include:

• force reads to the primary;
• sleep;
• determine that there is no primary-replica lag;
• combine with semi-sync;
• wait for the primary position;
• wait for GTID.

7. References

• Deep Exploration of MySQL Primary-Replica Replication Principles - Zhihu
• MySQL Semi-Synchronous Replication - iVictor - CNBlogs
• Maimai: How to Solve MySQL Primary-Replica Synchronization Delay