MySQL Group Replication for High Availability

1. What makes Group Replication different from classic replication

MySQL Group Replication differs fundamentally from classic asynchronous or semi-synchronous replication, because group members do not sit in a rigid source-replica hierarchy, but jointly decide via a consensus protocol which transactions get applied. Every member knows the state of the whole group, automatically detects when a node fails, and removes it from active membership, without any external tool such as an orchestrator having to make that decision.

This self-management is the central advantage of Group Replication over setups that combine classic replication with external failover tools like Orchestrator or MHA. Instead of an external system monitoring the database state from outside and intervening on failure, the group itself carries responsibility for consistency and availability. This reduces operational complexity, but comes with its own requirements around network latency and group size that must be understood before deployment.

-- Manual bootstrap of the first Group Replication member (without MySQL Shell)
-- Only set bootstrap_group on the very first node, and only for the startup
SET GLOBAL group_replication_bootstrap_group = ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group = OFF;

-- On every additional node: join the existing group, no bootstrap flag
CHANGE REPLICATION SOURCE TO
  SOURCE_USER = 'repl',
  SOURCE_PASSWORD = 'strong-password-here'
  FOR CHANNEL 'group_replication_recovery';
START GROUP_REPLICATION;

2. The group communication system and consensus

At the core of Group Replication is the Group Communication System, or GCS, built on the Paxos-based protocol XCom. Before final commit, every transaction goes through a group-wide certification: the transaction is distributed to all reachable members, each member checks whether it conflicts with already certified transactions, and only once a majority of the group agrees is the transaction considered certified and applied.

This majority principle, also called a quorum, is why Group Replication works reliably without needing a single coordinator: as long as more than half of the configured members are reachable, the group can keep operating. If the majority fails, for example during a network split that divides the group into two equal halves, the smaller partition automatically refuses further write operations to prevent a split-brain scenario with conflicting data states. This is why groups are typically run with an odd number of members, usually three or five nodes.

# my.cnf configuration for a Group Replication member
[mysqld]
server-id = 1
gtid_mode = ON
enforce_gtid_consistency = ON
binlog_checksum = NONE
log_bin = binlog
log_slave_updates = ON
binlog_format = ROW

# Group Replication plugin settings
plugin_load_add = 'group_replication.so'
group_replication_group_name = "aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee"
group_replication_start_on_boot = OFF
group_replication_local_address = "10.0.0.1:33061"
group_replication_group_seeds = "10.0.0.1:33061,10.0.0.2:33061,10.0.0.3:33061"
group_replication_bootstrap_group = OFF
group_replication_single_primary_mode = ON
group_replication_enforce_update_everywhere_checks = OFF

3. Single-primary mode in detail

In single-primary mode, the recommended default for most Group Replication use cases, only one group member accepts write operations while all others are automatically read only. This role is assigned by the group itself, based on an internal election after joining or after the current primary fails. Applications do not need to know which node is currently primary, because SELECT @@global.read_only or the performance_schema.replication_group_members table make the current role queryable at any time.

The advantage of single-primary mode lies in the simpler consistency guarantee: since all writes flow through a single node, write conflicts between multiple concurrently writing members are entirely eliminated. Applications migrating from classic source-replica replication find a familiar behavior pattern in single-primary mode, extended with automatic failover that a classic setup would have required additional external tools for.

4. Multi-primary mode and conflict handling

In multi-primary mode, every group member accepts write operations simultaneously, which at first glance sounds like higher scalability, but in practice requires considerable care in the application architecture. If application A writes a row on member 1 while application B changes the same row on member 2 at the same time, Group Replication certification detects the conflict during the certification attempt, and one of the two transactions is rejected with an error that the application must handle.

This optimistic conflict handling means application code must be prepared for certification errors, typically through retry logic at the transaction level. Multi-primary mode works well for workloads with geographically distributed writers on distinct, clearly separated data ranges, for example when each region primarily writes into its own tables. For general OLTP applications with high write contention on the same rows, multi-primary mode frequently adds more complexity than it gains in scalability.

5. Automatic failover within the group

If the current primary member fails in single-primary mode, the Group Communication System detects the failure through failure detection mechanisms with configurable timeouts, removes the node from active group membership, and automatically elects a new primary from the remaining members. This entire process typically completes within a few seconds without an administrator having to intervene manually, which makes Group Replication considerably simpler than classic replication with manual or externally orchestrated failover.

What matters for applications is that this failover does not automatically mean the application keeps running seamlessly: connections to the failed primary are dropped and must be reestablished. This is where MySQL Router comes in, monitoring group membership and transparently routing connections to whichever node is currently primary, so the application itself needs no knowledge of the internal topology.

6. Building an InnoDB Cluster with MySQL Shell

InnoDB Cluster is the management layer Oracle provides on top of Group Replication, considerably simplifying cluster setup, monitoring and maintenance. Instead of manually setting every configuration variable on every node, MySQL Shell's AdminAPI handles configuration and validation of group members.

// MySQL Shell (JavaScript mode): bootstrap a 3-node InnoDB Cluster
shell> mysqlsh --uri root@10.0.0.1:3306

// Check that this instance meets Group Replication requirements
dba.checkInstanceConfiguration('root@10.0.0.1:3306');

// Create the cluster on the first (seed) node
var cluster = dba.createCluster('productionCluster');

// Add the remaining members, MySQL Shell handles GTID recovery automatically
cluster.addInstance('root@10.0.0.2:3306');
cluster.addInstance('root@10.0.0.3:3306');

// Inspect the current topology and roles
cluster.status();

// Example output excerpt:
// "primary": "10.0.0.1:3306",
// "status": "OK",
// "statusText": "Cluster is ONLINE and can tolerate up to ONE failure."

During addInstance, the AdminAPI automatically handles what is called distributed recovery: the new node reconciles its GTID position with the group and synchronizes missing transactions via classic asynchronous replication before formally joining the Group Replication group. This eliminates the manual backup creation and restore that classic replication requires.

7. MySQL Router for transparent routing

MySQL Router sits between the application and the cluster, routing connections based on the current group role. Applications connect to a fixed router port, usually 6446 for read-write and 6447 for read-only connections, without knowing the IP addresses of individual cluster nodes. If the primary fails and the group elects a new one, the Router detects the change automatically through InnoDB Cluster metadata and routes new connections to the current primary.

This abstraction layer is the key building block that makes Group Replication genuinely transparent for applications. Without the Router, the application would have to query group membership itself and adjust its connection string on every failover, which in practice leads to delays and error handling code that the Router fully encapsulates.

# Bootstrap MySQL Router against the InnoDB Cluster metadata
mysqlrouter --bootstrap root@10.0.0.1:3306 \
  --directory /opt/mysqlrouter \
  --conf-use-gr-notifications

# Resulting mysqlrouter.conf (excerpt): fixed ports for the application
[routing:productionCluster_rw]
bind_address = 0.0.0.0
bind_port = 6446
destinations = metadata-cache://productionCluster/?role=PRIMARY
protocol = classic

[routing:productionCluster_ro]
bind_address = 0.0.0.0
bind_port = 6447
destinations = metadata-cache://productionCluster/?role=SECONDARY
protocol = classic

# Start the router as a service
mysqlrouter --config /opt/mysqlrouter/mysqlrouter.conf

8. Monitoring group membership

The central place for monitoring Group Replication is the performance_schema.replication_group_members table, showing status, role and version for every member. replication_group_member_stats additionally provides details on conflicts, certification rates and transaction queue size, giving an early warning of an overloaded member before it gets expelled from the group.

-- Check the health and role of every group member
SELECT member_id, member_host, member_port, member_state, member_role
FROM performance_schema.replication_group_members;

-- Example output:
-- member_id | member_host | member_port | member_state | member_role
-- uuid-1    | 10.0.0.1    | 3306        | ONLINE       | PRIMARY
-- uuid-2    | 10.0.0.2    | 3306        | ONLINE       | SECONDARY
-- uuid-3    | 10.0.0.3    | 3306        | ONLINE       | SECONDARY

-- Inspect certification conflicts and queue size per member
SELECT member_id, count_transactions_in_queue, count_conflicts_detected
FROM performance_schema.replication_group_member_stats;

A state that deserves special attention is member_state = UNREACHABLE, signaling that a member cannot be reached by the rest of the group but has not yet been formally expelled. If this state persists beyond the configured group_replication_unreachable_majority_timeout, the group must decide whether to keep operating with reduced membership or block write operations to preserve consistency.

9. Single-primary vs. multi-primary compared

The choice between single-primary and multi-primary mode in Group Replication largely determines application-layer complexity and achievable write scaling. The table below compares the key differences.

In practice, the vast majority of production Group Replication clusters run in single-primary mode, because predictable application behavior usually outweighs the theoretical scaling gain of multi-primary mode. Multi-primary pays off mainly in scenarios where write load is already architecturally separated by data range, for example in multi-tenant systems with strictly separated tenant schemas.

10. Summary

MySQL Group Replication shifts responsibility for consistency and failover from external tools directly into the database itself, driven by a Paxos-based consensus protocol and group-wide transaction certification. Single-primary mode remains the right choice for most use cases, because it combines the familiar behavior of classic replication with automatic failover, without accepting write conflicts. Multi-primary mode opens up additional scaling but demands conflict handling at the application layer.

InnoDB Cluster and MySQL Router make Group Replication considerably more accessible for production use, because they automate setup, recovery and connection routing that would otherwise be manual and error prone. Anyone who continuously monitors group membership and certification statistics catches problems before they lead to a member being expelled or the write quorum being lost.

11. FAQ: MySQL Group Replication