TopolearnAutomatic failover without data loss is the standard production requirement for MySQL today.
Group Replication turns a set of MySQL servers (usually 3, 5 or 7) into a group that agrees on the order of transactions using a Paxos-based group communication system. When a member commits, the transaction's write set is broadcast; it commits only once a majority has agreed on its order, and conflicting concurrent transactions are detected by certification and rolled back.
- Single-primary mode (default): one member accepts writes; if it fails, the group elects a new primary automatically. Simplest and recommended.
- Multi-primary mode: all members accept writes, with conflict detection; harder to use correctly.
Because a majority must agree, a 3-member group survives one failure, a 5-member group two. A minority partition stops accepting writes, preventing split brain. Requirements: InnoDB tables, primary keys on every table, GTIDs, row-based binlogs.
InnoDB Cluster packages it: MySQL Shell (dba.createCluster(), cluster.addInstance(), cluster.status()) sets up and manages Group Replication, the clone plugin provisions new members, and MySQL Router sits next to applications and routes connections to the current primary (read-write port) or secondaries (read-only port), following failovers automatically. InnoDB ClusterSet adds asynchronous replica clusters in other regions for disaster recovery.
Group Replication's group_replication_consistency setting controls whether reads on secondaries can be stale (EVENTUAL, default) or must wait for preceding transactions (BEFORE, AFTER, BEFORE_AND_AFTER).
Example
// MySQL Shell (JavaScript mode): mysqlsh clusteradmin@db1
dba.configureInstance('clusteradmin@db1:3306'); // checks GTID, binlog, PKs...
var cluster = dba.createCluster('shop');
cluster.addInstance('clusteradmin@db2:3306', {recoveryMethod: 'clone'});
cluster.addInstance('clusteradmin@db3:3306', {recoveryMethod: 'clone'});
cluster.status();
// "status": "OK", "statusText": "Cluster is ONLINE and can tolerate up to ONE failure."
// on each app host: bootstrap MySQL Router against the cluster
// $ mysqlrouter --bootstrap clusteradmin@db1:3306 --directory /opt/router
// apps connect to the router: 6446 = read/write (primary), 6447 = read-onlyRemember
- Group Replication commits once a majority agrees on transaction order
- Single-primary mode with automatic election is the recommended setup
- InnoDB Cluster = Group Replication + MySQL Shell + MySQL Router
Watch out for
- Running a 2-member group: losing either member loses the majority.