🚦 MySQL Group Replication: The JusDB Guide to High Availability

At JusDB, we work with enterprises that demand 99.99% uptime for their critical applications. Traditional MySQL replication is powerful but often limited by replication lag, manual failovers, and operational complexity. Since MySQL 5.7.17, Group Replication has been available as a built-in plugin, offering a native, fault-tolerant replication mechanism without the need for third-party tools. In this blog, we provide a comprehensive guide—based on real-world deployments—on how to configure, operate, and scale MySQL Group Replication effectively.

1 — Concepts

Group Replication is designed to create a self-healing cluster of MySQL servers. Each server maintains a full copy of the data and participates in a consensus protocol to validate transactions before committing them. This ensures strong consistency and automated failover.

• Available as a built-in plugin since MySQL 5.7.17.
• No third-party software required—native to MySQL.
• Implements Paxos consensus for membership negotiation, failure detection, and message delivery.
• Supports SINGLE-PRIMARY mode (simpler, one writer) and MULTI-PRIMARY mode (all writable, conflict resolution required).
• Requires at least 3 servers (recommended maximum: 9).
• Automatic split-brain protection ensures safe behavior under network partitions.

2 — Deployment Modes

2.1 Single-Primary Mode

The default mode where a single node acts as the primary (writer) and others serve as secondaries (read replicas). If the primary fails, the cluster automatically elects a new primary. This is the recommended mode for most workloads JusDB manages for our clients.

2.2 Multi-Primary Mode

All nodes accept writes, and conflicts are resolved using a “first committer wins” rule. Nodes joining the cluster first recover data before becoming fully online. While more flexible, this mode introduces complexity and limitations.

3 — Basic Requirements

• Only the InnoDB storage engine is supported.
• All tables must have primary keys.
• GTID mode must be enabled.
• Binary log format must be set to ROW.

4 — Benefits

Group Replication automates what previously required manual intervention, including:

• Automatic failover: When a node fails, the group elects a new primary seamlessly.
• Fault tolerance: Built-in quorum and consistency safeguards.
• Operational simplicity: Less reliance on external tooling.
• Scalability: Distribute read workloads across multiple replicas.

5 — Configuring Group Replication

5.1 OS-Level Preparation

• Enable passwordless SSH for the mysql user.
• Open firewall ports 3306 and 33061 between all members.
• Configure or disable SELinux appropriately.

5.2 Create Replication User

CREATE USER 'usr_replication'@'10.0.1.%' IDENTIFIED BY 'Pass!12345' REQUIRE SSL;
GRANT REPLICATION SLAVE ON *.* TO 'usr_replication'@'10.0.1.%';
FLUSH PRIVILEGES;

5.3 Update MySQL Configuration

Key parameters in my.cnf include:

• report_host for node identity.
• group_replication_local_address and group_seeds for member communication.
• transaction_write_set_extraction=XXHASH64 for conflict detection.
• gtid_mode=ON and enforce_gtid_consistency=ON.

Example for a 3-node cluster:

[mysqld]
server_id=1
bind_address=10.0.1.11
report_host=10.0.1.11
binlog_format=ROW
gtid_mode=ON
enforce_gtid_consistency=ON
group_replication_local_address="10.0.1.11:33061"
group_replication_group_name="ac419792-b575-4f86-b962-e7e0d433f491"
group_replication_group_seeds="10.0.1.11:33061,10.0.1.12:33061,10.0.1.13:33061"

5.4 Configure Replication Channel

CHANGE REPLICATION SOURCE TO 
SOURCE_USER='usr_replication',
SOURCE_PASSWORD='Pass!12345'
FOR CHANNEL 'group_replication_recovery';

5.5 Install Plugin

INSTALL PLUGIN group_replication SONAME 'group_replication.so';
SHOW PLUGINS;

5.6 Bootstrap the Primary Node

SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;

5.7 Add Remaining Nodes

START GROUP_REPLICATION;
SELECT * FROM performance_schema.replication_group_members;

If you encounter duplicate UUID errors, remove auto.cnf and restart the node.

6 — Handling Failures

• Replica crash: Restart MySQL and run START GROUP_REPLICATION.
• Primary crash: Cluster elects a new primary; restart the failed node and rejoin as replica.
• All nodes down: Requires manual bootstrap again.

7 — ProxySQL Integration

To deliver true application transparency, Group Replication should be combined with ProxySQL. Since version 1.4.0 (and MySQL 8 support from 2.6.0), ProxySQL has native Group Replication support.

7.1 Why Use ProxySQL with Group Replication?

• Seamless failover across primaries and replicas.
• Replica shunning when lag thresholds are exceeded.
• Load balancing between readers and writers.
• Support for multiple clusters.

7.2 Configuration Highlights

• Create a monitoring user on MySQL backends.
• Configure mysql_group_replication_hostgroups in ProxySQL to define writer/reader groups.
• Enable replica auto-discovery for dynamic scaling.

7.3 Monitoring via ProxySQL

SELECT * FROM monitor.mysql_server_group_replication_log ORDER BY time_start_us LIMIT 20;
SELECT * FROM runtime_mysql_servers;

ProxySQL automatically detects failovers and redirects client traffic transparently, ensuring zero downtime for applications.

8 — JusDB Best Practices

• Always deploy an odd number of nodes to maintain quorum.
• Encrypt replication links using SSL for security.
• Use row-based logging to avoid inconsistencies.
• Schedule backups from replicas to reduce load on the primary.
• Purge old binary logs regularly to save space.
• Continuously monitor replication lag, quorum status, and failover metrics.
• Perform failover drills to validate readiness.

9 — Conclusion

MySQL Group Replication is one of the most significant advancements in MySQL’s HA story. It eliminates manual failover, ensures strong consistency, and integrates seamlessly with ProxySQL for production-ready deployments. At JusDB, we design and implement Group Replication clusters that empower businesses to run mission-critical workloads with confidence, scalability, and resilience.

Looking to build a fault-tolerant MySQL environment? Talk to JusDB experts today.