Group Replication & InnoDB Cluster
AdvancedGroup Replication provides active-active multi-primary or single-primary clustering with distributed conflict detection; InnoDB Cluster wraps it with MySQL Shell orchestration.
Overview
MySQL Group Replication (MGR) is a built-in plugin that implements a distributed consensus protocol (Paxos-based) to provide fault-tolerant, consistent replication across a group of servers. In single-primary mode, one node accepts writes and the rest are read-only standbys with automatic failover. In multi-primary mode, all nodes accept writes, but distributed conflict detection and rollback serialise conflicting transactions. InnoDB Cluster packages MGR with MySQL Router (for transparent client routing) and MySQL Shell (for cluster lifecycle management) into a production-ready HA solution. A minimum of three nodes is required to tolerate one failure.
Setting Up InnoDB Cluster with MySQL Shell
MySQL Shell's dba object provides idiomatic JavaScript/Python commands to bootstrap, add members, and check cluster status without writing raw SQL.
// MySQL Shell — JavaScript mode
// On the primary node
var cluster = dba.createCluster('prodCluster', {
multiPrimary: false, // single-primary (recommended)
gtidSetIsComplete: true
});
// Add two additional members
cluster.addInstance('root@mysql2:3306');
cluster.addInstance('root@mysql3:3306');
// Check health
cluster.status();
// Output shows role: PRIMARY / SECONDARY, memberState: ONLINE
// Force primary election after failure
cluster.setPrimaryInstance('root@mysql2:3306');MySQL Router — Transparent HA
MySQL Router acts as a transparent proxy in front of the cluster. It reads cluster metadata and routes writes to the current primary (port 6446) and reads to secondaries (port 6447), rerouting automatically after a failover.
# Bootstrap MySQL Router against the cluster metadata
mysqlrouter --bootstrap root@mysql1:3306 --directory /etc/mysqlrouter --user=mysqlrouter
# application.yml — connect via Router, not directly to MySQL
spring:
datasource:
url: jdbc:mysql://router-host:6446/orders?useSSL=true&failOverReadOnly=false
hikari:
read-only-datasource-url: jdbc:mysql://router-host:6447/orders
minimum-idle: 5
maximum-pool-size: 20Multi-Primary Conflict Detection
In multi-primary mode, concurrent writes to the same row on different nodes are detected via a certifier. The transaction that certifies second is rolled back (applier error 1205 or 1213). Design writes to be conflict-free or use single-primary mode.
-- Monitor Group Replication health
SELECT MEMBER_ID, MEMBER_HOST, MEMBER_ROLE, MEMBER_STATE
FROM performance_schema.replication_group_members;
-- Check transaction conflicts / rollbacks
SELECT * FROM performance_schema.replication_group_member_stats
WHERE MEMBER_ID = @@server_uuidG
-- Flow control — check if a node is throttled
SELECT COUNT_TRANSACTIONS_IN_QUEUE, COUNT_TRANSACTIONS_CHECKED
FROM performance_schema.replication_group_member_stats;Key Points to Remember
- 1MGR uses Paxos-based consensus — at least (N/2)+1 nodes must be online for writes to proceed
- 2Single-primary mode: one writer, auto-failover; multi-primary mode: all write, conflicts rolled back
- 3InnoDB Cluster = MGR + MySQL Router + MySQL Shell — full HA stack out of the box
- 4MySQL Router transparently routes port 6446 to the primary, port 6447 to read replicas
- 5Minimum three nodes required to maintain quorum and tolerate one failure without losing writes
- 6All tables must use InnoDB with explicit PRIMARY KEY — MyISAM and tables without PKs are rejected by MGR
Interview Questions
Sign in to ask AriaWhat is the minimum number of nodes required for MySQL Group Replication and why?
What is the difference between single-primary and multi-primary Group Replication modes?
How does MySQL Router improve availability for application connections?
What happens to a conflicting transaction in multi-primary mode?
What table engine and key requirements does Group Replication impose?
Ask Aria about Group Replication & InnoDB Cluster
Your personal AI tutor — ask anything about this concept
Revision Status
Personal Notes
Sign in to save personal notes for this topic.
Discussion
Sign in to join the discussion.