Understanding MySQL Group Replication: Evolution, Transaction Lifecycle, and Conflict Detection

Ever Heard of MySQL MGR Technology?

MySQL is the most popular open‑source relational database; its Group Replication (MGR) introduced in 5.7.17 ensures data consistency, automatic failover, fault detection, and multi‑node writes. This article outlines MGR’s evolution, transaction lifecycle, and conflict‑detection mechanism.

Evolution of MGR

Traditional master‑slave replication synchronizes binlog from master to slave via dump, I/O, and SQL threads.

Asynchronous replication allows the master to commit without waiting for slaves, sacrificing consistency.

Semi‑synchronous replication requires at least one slave to acknowledge receipt before commit, improving safety but adding complexity.

Both asynchronous and semi‑synchronous modes have drawbacks: manual failover and high read load on slaves. MySQL 5.7 MGR addresses these issues.

MGR Architecture

MGR is a plugin that forms a distributed cluster where each server holds a full copy. It relies on ROW‑based binary logs and GTID. The architecture consists of an APIs layer, component layer, replication‑protocol layer, and a GCS API + Paxos engine.

Transaction Lifecycle in an MGR Cluster

In a three‑node cluster (DB1, DB2, DB3), each node runs an MGR layer composed of a Paxos module and a certification module. When a transaction T1 originates on DB1, it is broadcast as a transaction message. After a majority of nodes reach consensus via Paxos, each node’s certification module checks for conflicts. If the check passes, the local transaction commits; otherwise it rolls back. Remote transactions update relay logs before applying to binlog.

Transaction Message and Certification Info

A transaction message contains a write‑set (hash of database, table, primary‑key), gtid_executed, transaction_context_log_event, gtid_log_event, and log_event_group.

Certification info stores passed write‑sets and their GTID snapshots, acting as a map from primary‑key to GTID.

Conflict‑Detection Core Mechanism

Conflict passes when the transaction’s UUID_MGR ≥ the stored certification UUID_MGR. If not, the transaction is discarded.

For concurrent writes, the system enforces ordering: transactions updating the same row are checked sequentially, while independent rows are processed in parallel.

Key Features of MGR

Based on Paxos and native replication, providing high consistency.

High availability with automatic fault detection and failover.

Elastic scaling up to nine nodes, supporting single‑master and multi‑master modes.

Multi‑node writes with built‑in conflict detection.

Summary and Outlook

MySQL MGR guarantees data consistency, automatic fault detection, and multi‑node writes, making it a promising direction for future database systems. It is already being adopted in banking applications, and further promotion will continue to enhance open‑source database management.