Why Traditional Replication Models Are No Longer Sufficient
Operating a classic MySQL Master-Slave topology always comes with an ever-present fear: the Master crashing unexpectedly in the middle of the night. When that happens, manual intervention introduces several complex dilemmas:
- Which Replica is holding the latest binary log position?
- Will promoting a replica to Master cause in-flight transactions to be lost?
- How do you resolve conflicts if a split-brain scenario occurs and two nodes accept writes simultaneously?
In practice, data loss risks scale directly with system size. In a payment ecosystem handling 2,000 write OPS, just a few seconds of replication lag can offset RPO (Recovery Point Objective) by thousands of records. Manual failover routines often consume 10 to 30 minutes of downtime—far exceeding standard SLA commitments.
Modern architectures demand an automated consensus mechanism that evicts failed nodes and elects a new leader in real time. Oracle specifically engineered MySQL Group Replication (MGR) to tackle this challenge.
How Paxos and Group Replication Work
Unlike passive, one-way asynchronous replication, MGR incorporates the XCom (Group Communication System) layer, which operates on an implementation of the Paxos consensus algorithm.
1. Transaction Processing Flow with Paxos
When a transaction is ready to commit on a node, disk writes do not occur immediately. Instead, write-set data (containing primary key hashes and snapshot versions) is broadcast to all cluster members over the XCom port (default 33061).
The cluster conducts a majority vote (Quorum):
- As long as more than 50% of the nodes acknowledge and agree on the execution sequence (global order), the transaction is considered valid.
- The Certification phase verifies version conflicts. If no prior transaction has modified the same row, the commit completes on the originating node, and the remaining nodes apply the changes.
- The cluster requires at least
2F + 1nodes to tolerateFconcurrent node failures. In a 3-node topology (N=3), the cluster remains fully operational if 1 node fails.
2. Single-Primary vs Multi-Primary Mode
MGR supports two deployment modes:
- Single-Primary Mode (Recommended): The cluster designates a single node to handle both Read/Write operations, while the remaining nodes act as Read-Only Secondaries. If the Primary fails, Paxos automatically elects the Secondary with the highest weight within 2–5 seconds, requiring virtually zero intervention from application logic.
- Multi-Primary Mode: All nodes accept concurrent writes. When two nodes update the same row simultaneously, the Certification mechanism rejects and rolls back the later transaction. This mode is suitable only for specific use cases and requires robust retry logic in the application.
Deploying a 3-Node MySQL Group Replication Cluster
This deployment model consists of 3 nodes running Ubuntu 22.04 LTS and MySQL 8.0 in a Single-Primary architecture:
- Node 1:
192.168.10.11(hostname:mgr-node1) - Node 2:
192.168.10.12(hostname:mgr-node2) - Node 3:
192.168.10.13(hostname:mgr-node3)
Step 1: Configure Prerequisites
MGR has mandatory requirements: use the InnoDB storage engine, enable GTID (Global Transaction Identifier), configure the binary log format to ROW, and ensure all tables have a Primary Key.
Open /etc/mysql/mysql.conf.d/mysqld.cnf on Node 1 and add:
[mysqld]
server_id = 1
gtid_mode = ON
enforce_gtid_consistency = ON
binlog_format = ROW
log_replica_updates = ON
binlog_checksum = NONE
# Initialize Group Replication plugin
plugin_load_add = 'group_replication.so'
group_replication_group_name = "8a9f24e0-7bf2-11ed-a1eb-0242ac120002"
group_replication_start_on_boot = OFF
group_replication_local_address = "192.168.10.11:33061"
group_replication_group_seeds = "192.168.10.11:33061,192.168.10.12:33061,192.168.10.13:33061"
group_replication_bootstrap_group = OFF
group_replication_single_primary_mode = ON
group_replication_enforce_update_everywhere_checks = OFFOn Node 2 and Node 3, apply the same configuration, updating server_id to 2 and 3, and specifying each machine’s corresponding IP address in the group_replication_local_address parameter.
Step 2: Create a Dedicated Replication User
Log in to MySQL on all 3 nodes to grant the required permissions for the data recovery channel:
SET SQL_LOG_BIN=0;
CREATE USER 'repl'@'%' IDENTIFIED BY 'MatKhauBaoMatCucCao123@';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
GRANT BACKUP_ADMIN ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
CHANGE REPLICATION SOURCE TO SOURCE_USER='repl', SOURCE_PASSWORD='MatKhauBaoMatCucCao123@' FOR CHANNEL 'group_replication_recovery';Step 3: Initialize the Group (Bootstrap from Node 1)
Only one node is permitted to enable the bootstrap flag to seed the cluster. Execute on Node 1:
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;Immediately reset the bootstrap_group flag to OFF. This prevents the node from attempting to bootstrap a new group upon service restart.
Step 4: Join Node 2 and Node 3 to the Cluster
On Node 2 and Node 3, start group replication directly without using the bootstrap flag:
START GROUP_REPLICATION;The system will automatically synchronize schemas and existing data from Node 1 to the new nodes via the recovery channel.
Step 5: Verify Cluster Health and Status
Query the Performance Schema table on any node:
SELECT member_id, member_host, member_port, member_state, member_role
FROM performance_schema.replication_group_members;Once all nodes report an ONLINE status, the cluster is ready for traffic:
+--------------------------------------+---------------+-------------+--------------+-------------+
| MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE |
+--------------------------------------+---------------+-------------+--------------+-------------+
| 306c58cf-5e8c-11ed-9f86-000c29a8a111 | 192.168.10.11 | 3306 | ONLINE | PRIMARY |
| 422d8612-5e8c-11ed-8f52-000c29a8a222 | 192.168.10.12 | 3306 | ONLINE | SECONDARY |
| 505d9123-5e8c-11ed-7a11-000c29a8a333 | 192.168.10.13 | 3306 | ONLINE | SECONDARY |
+--------------------------------------+---------------+-------------+--------------+-------------+Step 6: Simulate a Failure and Test Failover
Stop the MySQL process on Node 1 to test the system’s reaction:
sudo systemctl stop mysqlCheck the cluster status again from Node 2. Within 3 to 5 seconds, Node 1 transitions to UNREACHABLE and is automatically removed from the active topology.
Node 2 or Node 3 will be promoted to the new PRIMARY. All committed transactions remain intact (RPO = 0).
Summary and Production Best Practices
MySQL Group Replication delivers a standardized distributed database architecture that prevents split-brain conditions without requiring third-party tools in the consensus layer. When deploying to production, keep the following in mind:
- Always maintain an odd number of nodes (minimum 3 nodes) to preserve Quorum guarantees.
- Deploy ProxySQL or MySQL Router as an intermediary layer to automatically route read/write traffic to the newly elected Primary node post-failover.
- Keep inter-node network latency under 10ms to minimize overhead during the Paxos consensus phase.
