MySQL InnoDB Cluster (Group Replication) setup

Symptom

Goal: set up high availability with automatic failover for MySQL using InnoDB Cluster (Group Replication + MySQL Shell + MySQL Router).

1. Meet the prerequisites

InnoDB Cluster requires MySQL 8.0+, a unique server_id, GTID mode, and the InnoDB storage engine on every node. All instances must be reachable from each other over the network, and tables must have primary keys. Prepare a clusterAdmin user for administration.

MySQL

-- my.cnf on each instance:
-- server_id unique, gtid_mode=ON, enforce_gtid_consistency=ON
CREATE USER 'icadmin'@'%' IDENTIFIED BY 'secret';
GRANT ALL PRIVILEGES ON *.* TO 'icadmin'@'%' WITH GRANT OPTION;

2. Configure each instance with MySQL Shell

In mysqlsh, dba.configureInstance() makes each node suitable for Group Replication; it fixes missing parameters and asks for a restart if needed. Repeat on all nodes.

MySQL

-- mysqlsh:
dba.configureInstance('icadmin@node1:3306')
dba.configureInstance('icadmin@node2:3306')
dba.configureInstance('icadmin@node3:3306')

3. Create the cluster (seed node)

Connect to one node and start the cluster with dba.createCluster(); this node becomes the first primary. A cluster object is returned and used for subsequent additions.

MySQL

-- mysqlsh, while connected to node1:
var cluster = dba.createCluster('appCluster')

4. Add the other instances to the cluster

Add the secondary nodes with cluster.addInstance(). Choose clone (physical clone, fast for a new node) or incremental (via GTID) as recoveryMethod. At least 3 nodes provide an odd majority and automatic failover.

MySQL

-- mysqlsh:
cluster.addInstance('icadmin@node2:3306', {recoveryMethod: 'clone'})
cluster.addInstance('icadmin@node3:3306', {recoveryMethod: 'clone'})

5. Bootstrap MySQL Router

MySQL Router routes between the application and the cluster; it exposes read/write (6446) and read-only (6447) ports and redirects traffic automatically when the primary changes. Bootstrap generates the configuration from the cluster metadata.

MySQL

mysqlrouter --bootstrap icadmin@node1:3306 --user=mysqlrouter --directory /etc/mysqlrouter
# Then: mysqlrouter -c /etc/mysqlrouter/mysqlrouter.conf &

6. Check cluster status and verify failover

cluster.status() shows each node's role (R/W primary, R/O secondary) and state (ONLINE). clusterName and topology should be healthy. Test failover by shutting down the primary and observing the automatic election of a new primary.

MySQL

-- mysqlsh:
var cluster = dba.getCluster('appCluster')
cluster.status()
-- Members via SQL:
SELECT member_host, member_role, member_state FROM performance_schema.replication_group_members;