Home / Databases / MySQL

DatabasesHow-toAdvanced

MySQL InnoDB Cluster: high availability with Group Replication and MySQL Router

Build a three-node MySQL InnoDB Cluster with MySQL Shell, put MySQL Router in front of the applications, understand single-primary failover and quorum, and run status checks, switchovers and recovery.

Deepak Bhardwaj2 min read

Applies toMySQL 8.0 / 8.4 LTS, MySQL Shell 8.4, MySQL Router 8.4

Architecture

  • 3 MySQL servers with Group Replication (Paxos-based consensus). Single-primary mode: one read-write node, two read-only nodes.
  • MySQL Router on each application host (or as a pair). It sends writes to the primary and reads to the secondaries, and follows failover automatically.
  • MySQL Shell (mysqlsh) to manage it all.

Three nodes tolerate one failure. Five tolerate two.

1. Prepare each instance

my.cnf basics: unique server_id, gtid_mode=ON, enforce_gtid_consistency=ON, InnoDB-only tables with primary keys on every table.

// mysqlsh as root on each node
dba.configureInstance('root@mysql1:3306', {clusterAdmin: 'clusteradmin', clusterAdminPassword: '...'})

2. Create the cluster

\connect clusteradmin@mysql1:3306
var c = dba.createCluster('salesCluster', {memberSslMode: 'REQUIRED'})
c.addInstance('clusteradmin@mysql2:3306', {recoveryMethod: 'clone'})
c.addInstance('clusteradmin@mysql3:3306', {recoveryMethod: 'clone'})
c.status()

clone copies a full snapshot to new members. It's the fastest way to provision them.

3. MySQL Router

mysqlrouter --bootstrap clusteradmin@mysql1:3306 --directory /opt/router --user=mysqlrouter --conf-use-sockets
/opt/router/start.sh

Default ports: 6446 read-write, 6447 read-only (also 6450 for read-write splitting in 8.2+). Apps connect to localhost:6446 on their own host, or to a router VIP.

4. Failover behaviour

  • If the primary fails, the remaining members elect a new primary in seconds, as long as a majority is still connected.
  • Router notices the new primary through cluster metadata and redirects new connections. Existing connections to the old primary are dropped, so apps must retry.
  • If a majority is lost (2 of 3 down), the group stops accepting writes, to protect consistency.

Consistency choice:

c.setOption('consistency', 'BEFORE_ON_PRIMARY_FAILOVER')   // new primary applies its backlog before serving

5. Operations

c.status({extended: 1})
c.setPrimaryInstance('mysql2:3306')        // planned switchover
c.rejoinInstance('mysql3:3306')            // after a node restart, if it doesn't rejoin automatically
c.removeInstance('mysql3:3306')
dba.rebootClusterFromCompleteOutage()      // after all nodes went down
c.forceQuorumUsingPartitionOf('mysql1:3306')  // last resort when a majority is permanently lost

6. Limitations to know

  • Every table needs a primary key, and only InnoDB is supported.
  • Large transactions are slow to certify: cap them (group_replication_transaction_size_limit) and batch big deletes.
  • Network latency between members adds to every commit, so keep them within one region (spread across AZs).

7. DR

Add an InnoDB ClusterSet: a replica cluster in another region, linked by asynchronous replication, with clusterSet.setPrimaryCluster() for a controlled switchover or forcePrimaryCluster() for failover.

Warning

forceQuorumUsingPartitionOf can cause split brain if the "lost" members are actually alive somewhere else. Make sure they are really down, or fenced off, before you use it.

Related fixes