MySQL MGR Cluster: Paxos Consensus, Zero-Downtime HA & Brain-Split Proof Architecture

What Is MySQL Group Replication (MGR)?

Traditional master-slave replication has two fundamental flaws: asynchronous replication risks data loss, and failover depends on external tools like MHA, which cannot guarantee strong consistency. MySQL's official answer is MGR (MySQL Group Replication) — a native high-availability cluster built on the distributed Paxos consensus protocol . In MGR, all nodes are peers; data synchronizes with strong consistency, leader election and failover are automatic, and faulty nodes are ejected without manual intervention.

Unlike traditional one-way async replication, MGR uses bidirectional synchronous replication with full-cluster voting . It is the standard choice for finance, payment, and trading systems that demand zero data loss.

Why MGR? Fatal Defects of Traditional Master-Slave + MHA

Data inconsistency risk: Async replication loses data; replication lag causes divergence.

Unintelligent failover: External tools decide promotion, risking split-brain and election conflicts.

No data verification: When the master crashes, there is no reliable way to confirm a replica's completeness — failover becomes a gamble.

MGR solves these at the protocol layer:

Strong consistency: A transaction commits only after a majority of nodes acknowledge it.

Native HA: Built-in failure detection, leader election, and switchover — no MHA, no middleware.

Automatic fault tolerance: A single node failure is auto-ejected; the cluster continues uninterrupted.

Brain-split prevention: Paxos majority voting eliminates split-brain at the protocol level.

Bottom line: Ordinary workloads can use traditional replication; core transactional systems (payments, orders, funds) must use MGR.

Where MGR Applies & Two Cluster Modes

Production Scenarios Requiring MGR

Financial payments, settlement, ledgers — zero data loss mandatory.

Sub-second automatic failover with zero business awareness.

Strong consistency required; replication lag unacceptable.

Desire to eliminate third-party HA tooling in favor of native clustering.

Single-Primary Mode (Production Standard, 99% Adoption)

Exactly one node accepts writes; all others are read-only. On primary failure, the cluster votes a new primary automatically — no config changes, seamless cutover. Advantages: identical read/write logic to traditional replication, no write conflicts, stable and predictable.

Multi-Primary Mode (Rarely Used, Discouraged)

All nodes accept writes concurrently. Drawbacks: frequent write conflicts, transaction rollbacks, heavy performance penalty. Enterprise policy: always use single-primary; never deploy multi-primary.

Core Mechanism: Paxos Consensus in Plain Language

The key difference from master-slave: no more master-push/slave-pull; instead, the entire cluster votes on every transaction.

Single-transaction execution flow:

Client runs a write transaction on the primary.

Primary executes and generates the corresponding binlog events.

Primary broadcasts the transaction payload to all cluster members for voting.

Following Paxos, the transaction commits only when a strict majority ( > 50% ) vote yes .

All members replay the transaction in the same order, keeping global state identical.

If the vote fails, the transaction rolls back everywhere — inconsistency is impossible.

Critical insight: Commit authority resides in the cluster quorum, not a single master, eliminating single-point data loss.

Fault Tolerance & Automatic Failover

Cluster Quorum Basics (Golden Rule)

Production standard is a 3-node cluster . With three nodes, the cluster tolerates one node down and remains fully operational. If two nodes fail, the surviving node cannot reach a majority — the cluster enters read-only protection mode automatically.

End-to-End Failover Sequence

Primary crashes; surviving nodes detect heartbeat loss in real time.

Remaining nodes automatically start a leader election via Paxos voting.

New primary is elected and instantly enables read-write mode; others stay read-only.

When the failed node restarts, it rejoins, fetches missing transactions, and becomes a secondary.

Entire process is transparent to applications — no manual steps, no config edits.

Brain-Split Deep Dive: How MGR Eradicates It

Traditional Replication Brain-Split

Network partition isolates master from replicas. MHA may falsely declare the master dead and promote a replica, creating dual primaries accepting writes simultaneously — data diverges irrecoverably.

MGR's Majority-Vote Guarantee

A 3-node cluster splits into a 2-node partition and a 1-node partition.

Only the partition with ≥ 2 nodes (majority) can elect a primary and serve reads/writes.

The isolated single node lacks quorum — it freezes, becomes read-only, and rejects writes.

When the network heals, partitions merge automatically and state reconciles.

Conclusion: MGR eliminates split-brain at the consensus protocol layer.

Production Rules & Performance Characteristics

MGR is not a silver bullet; strict operational guardrails apply:

InnoDB only — MyISAM cannot participate in group replication transaction certification.

Every table must have a primary key — tables without PKs break cluster transaction sync.

Avoid large transactions and bulk writes — larger transactions prolong the cluster-wide voting round.

Co-locate nodes in the same data center with low-latency networking — cross-region latency inflates vote timeouts and kills throughput.

Single-primary read/write split — route reads across secondaries, funnel all writes to the primary.

Top 6 Production Pitfalls

Blindly enabling multi-primary mode — causes write conflicts, rollbacks, and throughput collapse; banned in production.

Deploying a 2-node cluster — loss of one node leaves a single survivor without quorum; cluster goes read-only instantly, zero fault tolerance.

Tables without primary keys — certification fails, replication stalls.

Cross-data-center high-latency deployment — vote timeouts spike, transaction commit failure rate surges.

Large transactions blocking the whole cluster — because every transaction requires full-cluster acknowledgment, a long-running transaction stalls group-wide progress.

Confusing MGR with traditional replication semantics — MGR is synchronous consensus, not async one-way copy; there is no replication lag.

Key Takeaways

MGR is MySQL's official native HA cluster, powered by Paxos — strong consistency, high fault tolerance.

Production uses single-primary mode exclusively ; multi-primary is prohibited.

Standard 3-node deployment tolerates one failure with automatic leader election and failover.

Majority voting at the protocol level eradicates brain-split and data divergence.

Mandatory: InnoDB, primary keys on all tables, no large transactions, same-DC low-latency networking.

Compared to traditional master-slave, MGR delivers zero lag, zero data-loss risk, and is the default architecture for core transactional workloads.