Learning: MySQL InnoDB Cluster advanced (part 2) - production build, security, monitoring, failure recovery, backups, rolling upgrades and ClusterSet DR

#About this session

Warning

Several commands in Module 7 (forceQuorumUsingPartitionOf, forcePrimaryCluster) can cause split brain or data loss if used at the wrong time. Practise them in the lab only, and make them part of a written runbook before you ever need them in production.

#Module 1: design decisions

Decide these before you install anything:

Lab hostnames used below: db1, db2, db3 (one per zone), db4-db6 for the DR site, and app1 for the application.

#Module 2: prepare the servers

#Network

Open them between members, and 3306/33060 from Router hosts. Every member must resolve every other member's hostname the same way (DNS or /etc/hosts), and that name is what members advertise:

# /etc/my.cnf.d/server.cnf on db1 (adjust per host)
[mysqld]
server_id                  = 1
report_host                = db1.example.internal
bind_address               = 0.0.0.0
gtid_mode                  = ON
enforce_gtid_consistency   = ON
log_replica_updates        = ON
innodb_buffer_pool_size    = 12G
innodb_redo_log_capacity   = 4G
max_connections            = 1000
disabled_storage_engines   = "MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"

disabled_storage_engines stops anyone creating non-InnoDB tables later.

#Check and configure each instance

// mysqlsh --js, as an account with full privileges
dba.checkInstanceConfiguration('root@db1:3306')
dba.configureInstance('root@db1:3306', {clusterAdmin: "'clusteradmin'@'%'", restart: true})

configureInstance fixes required settings and creates a dedicated cluster admin account. Use that account (not root) for everything that follows. Repeat for db2 and db3.

Check the data for tables that will break Group Replication:

-- tables without a primary key
SELECT t.table_schema, t.table_name
FROM information_schema.tables t
LEFT JOIN information_schema.table_constraints c
  ON c.table_schema = t.table_schema AND c.table_name = t.table_name AND c.constraint_type = 'PRIMARY KEY'
WHERE t.table_type = 'BASE TABLE' AND c.constraint_name IS NULL
  AND t.table_schema NOT IN ('mysql','sys','information_schema','performance_schema');

-- non-InnoDB tables
SELECT table_schema, table_name, engine FROM information_schema.tables
WHERE engine <> 'InnoDB' AND table_schema NOT IN ('mysql','sys','information_schema','performance_schema');

Fix them first. An invisible auto-increment primary key (sql_generate_invisible_primary_key=ON) helps for new tables created by applications that don't define one.

#Module 3: create a hardened cluster

\connect clusteradmin@db1:3306
var cluster = dba.createCluster('prodCluster', {
  memberSslMode:  'VERIFY_IDENTITY',   // TLS with certificate and hostname checks (needs proper certs); use REQUIRED in a lab
  ipAllowlist:    '10.10.1.0/24,10.10.2.0/24,10.10.3.0/24',
  consistency:    'BEFORE_ON_PRIMARY_FAILOVER',
  expelTimeout:   5,
  autoRejoinTries: 3,
  exitStateAction: 'OFFLINE_MODE'
})
cluster.addInstance('clusteradmin@db2:3306', {recoveryMethod: 'clone'})
cluster.addInstance('clusteradmin@db3:3306', {recoveryMethod: 'clone'})
cluster.status({extended: 1})

What the options do:

Check option names and allowed values with \help dba.createCluster in your Shell version. Newer versions also support certificate-based member authentication (memberAuthType).

Change options later with cluster.setOption() (whole cluster) or cluster.setInstanceOption() (one member), for example to set election priority:

cluster.setInstanceOption('db2:3306', 'memberWeight', 80)   // preferred next primary
cluster.setInstanceOption('db3:3306', 'memberWeight', 20)

#Module 4: MySQL Router in production

Create a dedicated account for Routers (don't bootstrap with the admin account):

cluster.setupRouterAccount('router_app1')

On app1, bootstrap as a system service with that account:

sudo mysqlrouter --bootstrap clusteradmin@db1:3306 \
  --account router_app1 \
  --user mysqlrouter \
  --conf-bind-address 127.0.0.1 \
  --name app1-router
sudo systemctl enable --now mysqlrouter

The bootstrap account is used once to register the Router; --account is what Router uses afterwards to read cluster metadata.

Points that matter in production:

• Bind to localhost when Router runs on the application host, so only that host's app can use it.
• TLS: Router can terminate client TLS and use TLS to the members; check client_ssl_mode and server_ssl_mode in the generated mysqlrouter.conf.
• Connection pooling and read/write splitting (port 6450) exist in recent versions. Test your application with them before enabling.
• List and tidy Routers from Shell:

cluster.listRouters()
cluster.removeRouterMetadata('app-old::app-old-router')   // routers that no longer exist

Application side: connection retries with backoff, and short transactions. After a failover, existing connections are closed; the driver must reconnect.

#Module 5: monitoring

#Health

SELECT MEMBER_HOST, MEMBER_STATE, MEMBER_ROLE, MEMBER_VERSION
FROM performance_schema.replication_group_members;

Alert when:

• Any member isn't ONLINE for more than a minute or two.
• The number of ONLINE members drops below 3 (you've lost failure tolerance).
• No member is PRIMARY.

#Replication lag and flow control

SELECT MEMBER_ID,
       COUNT_TRANSACTIONS_IN_QUEUE          AS certify_queue,
       COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE AS apply_queue,
       COUNT_CONFLICTS_DETECTED             AS conflicts
FROM performance_schema.replication_group_member_stats;

A growing apply_queue on one secondary means it can't keep up (slow disk, big transactions). When queues pass the flow control thresholds (group_replication_flow_control_applier_threshold, ..._certifier_threshold), Group Replication slows the primary down to let the secondary catch up. Users see that as slower writes, not as an error. Monitor the queues so you know why.

#From Shell

cluster.status({extended: 1})      // includes transaction sets, versions, recovery info
cluster.describe()                 // topology
cluster.options()                  // effective options

Feed these into your monitoring through MySQL Enterprise Monitor, Percona Monitoring and Management, or Prometheus mysqld_exporter (which exposes the Group Replication tables).

#Large transactions

Group Replication certifies whole transactions. A 5-million-row DELETE becomes one huge message that can stall the group or exceed limits. Cap and batch:

SHOW VARIABLES LIKE 'group_replication_transaction_size_limit';

-- batch big deletes
DELETE FROM shop.audit_log WHERE created_at < '2026-01-01' LIMIT 10000;   -- repeat until 0 rows

#Module 6: planned maintenance

#Rolling restart or OS patching

1. Patch secondaries one at a time. Before each, check the cluster is OK; after each, wait for the member to be ONLINE.
2. Switch the primary to a patched member: cluster.setPrimaryInstance('db2:3306').
3. Patch the old primary.

#Rolling MySQL upgrade (for example 8.4.x to a newer 8.4.y)

• Upgrade MySQL Shell and MySQL Router first. They must be the same or a newer version than the servers.
• Then upgrade secondaries, switch over, upgrade the old primary.
• After all members run the new version, run dba.upgradeMetadata() if Shell reports the metadata schema needs it.
• During a mixed-version period, a member on a newer version can't be primary while older members exist in some cases; Group Replication picks the lowest-version member as primary. Keep the mixed period short.

#Module 7: failure recovery

Practise each of these in the lab.

#A member was expelled or restarted and didn't rejoin

cluster.status()                                  // member shows (MISSING) or ERROR
cluster.rejoinInstance('clusteradmin@db3:3306')

If rejoin fails because the member has errant transactions (GTIDs the rest of the cluster doesn't have, usually from someone writing to it directly while it was out), don't try to patch GTID sets by hand. Re-provision it:

cluster.removeInstance('db3:3306', {force: true})
cluster.addInstance('clusteradmin@db3:3306', {recoveryMethod: 'clone'})

Then find out who wrote to it, and why super_read_only didn't stop them.

#Lost quorum (majority down, minority still running)

Symptoms: status: NO_QUORUM, writes hang or fail. If the missing members really are down (and can't come back with diverging data), force the surviving partition to form a new group:

\connect clusteradmin@db1:3306
var cluster = dba.getCluster()
cluster.forceQuorumUsingPartitionOf('clusteradmin@db1:3306')

Caution

If the "missing" members are actually alive on the other side of a network split and still have quorum there, forcing quorum here creates two primaries. Confirm the other members are stopped or fenced (shut down, or firewalled) first.

Afterwards, rejoin or re-add the other members.

#Complete outage (all members stopped, for example a power cut)

Group Replication doesn't start a cluster automatically when every member was down; there's no running group to rejoin. Start MySQL on all members, then:

\connect clusteradmin@db1:3306
var cluster = dba.rebootClusterFromCompleteOutage('prodCluster')
cluster.status()

Shell checks which member has the most transactions (GTID set) and refuses if you picked a less up-to-date one. Let it choose; don't override that check unless you understand the data loss.

#Metadata out of step

If members were changed outside Shell (for example Group Replication started by hand):

cluster.rescan()

#Module 8: backups and point-in-time recovery

High availability isn't a backup: a DROP TABLE replicates to every member in milliseconds.

• Take backups from a secondary to keep load off the primary.
• Options: MySQL Enterprise Backup, Percona XtraBackup (physical; fastest restore), or MySQL Shell dump utilities (logical; portable):

// logical dump from a secondary, parallel and compressed
\connect clusteradmin@db3:3306
util.dumpInstance('/backup/mysql/2026-10-27', {threads: 8, compression: 'zstd'})

• Keep binary logs long enough for point-in-time recovery (binlog_expire_logs_seconds), and copy them off the servers.
• Restore drill: restore the latest backup to a standalone server, apply binary logs up to just before the bad statement (mysqlbinlog --stop-datetime or by GTID), and extract or swap in the data. Test this quarterly.

To rebuild a whole cluster from a backup: restore to one server, dba.createCluster() on it, and add the others with clone.

#Module 9: disaster recovery with InnoDB ClusterSet

A ClusterSet links the primary cluster to one or more replica clusters in other regions by asynchronous replication. Only one cluster accepts writes.

\connect clusteradmin@db1:3306
var cluster = dba.getCluster()
var cs = cluster.createClusterSet('prodSet')

// create the DR cluster on db4 (prepared like db1-3), then grow it
var dr = cs.createReplicaCluster('clusteradmin@db4:3306', 'drCluster', {recoveryMethod: 'clone'})
dr.addInstance('clusteradmin@db5:3306', {recoveryMethod: 'clone'})
dr.addInstance('clusteradmin@db6:3306', {recoveryMethod: 'clone'})
cs.status({extended: 1})

Router with a ClusterSet: re-bootstrap Routers (or bootstrap new ones) against the ClusterSet; by default they follow the primary cluster. You can pin a Router to a specific cluster:

cs.listRouters()
cs.setRoutingOption('app1-router', 'target_cluster', 'primary')

#Planned switchover (both regions healthy)

cs.setPrimaryCluster('drCluster')
cs.status()

No data loss: Shell waits for the replica cluster to catch up, then swaps roles. Routers following primary move automatically.

#Failover (primary region lost)

\connect clusteradmin@db4:3306
var cs = dba.getClusterSet()
cs.forcePrimaryCluster('drCluster')

Asynchronous replication means recent transactions may be lost. Check cs.status() lag regularly so you know how much. When the old region returns, its cluster is marked invalidated; bring it back as a replica:

cs.rejoinCluster('prodCluster')

If it has transactions the new primary doesn't have, it can't rejoin; you'll need to rebuild it (and decide what to do with those transactions).

#Read replicas

For extra read capacity without affecting quorum, add read replicas (asynchronous replicas managed by the cluster):

cluster.addReplicaInstance('clusteradmin@reports1:3306')

Router can send read-only traffic to them, depending on routing options.

#Exercises

1. Set memberWeight so that db3 is the preferred next primary. Kill the primary's mysqld and confirm db3 takes over.
2. Run a single INSERT ... SELECT that copies 2 million rows, while watching replication_group_member_stats. Note the queue sizes and the effect on other writes. Then do it in batches of 10,000.
3. Firewall db3 off from the other two members for longer than expelTimeout. Watch it get expelled and, after removing the firewall rule, rejoin automatically within autoRejoinTries.
4. Stop all three members. Bring the cluster back with rebootClusterFromCompleteOutage(). Which member did Shell choose, and why?
5. Write to a secondary directly after turning off super_read_only (lab only), then try rejoinInstance after a restart. Read the error, and recover the member with clone.
6. Create the ClusterSet, perform setPrimaryCluster to the DR cluster and back, timing how long your write loop sees errors.
7. Restore last night's dump to a standalone server and recover to a point 10 minutes before a deliberate DROP TABLE.

#Knowledge check

1. Why should all members of one cluster be in the same region, and how do you protect against losing the region?
2. What does consistency: BEFORE_ON_PRIMARY_FAILOVER protect against?
3. What is flow control, and how would users notice it?
4. When is forceQuorumUsingPartitionOf() safe, and what's the risk?
5. Why can't a member with errant transactions simply rejoin, and what's the clean fix?
6. What's the difference between setPrimaryCluster() and forcePrimaryCluster()?
7. In which order do you upgrade Shell, Router and the servers?

#Summary

• Design first: three or five members in one region across zones, single-primary, ClusterSet for another region.
• Prepare instances with configureInstance, a dedicated admin account, GTIDs, InnoDB-only tables with primary keys.
• Harden with TLS, allow-lists and sensible consistency, expelTimeout and exitStateAction settings.
• Run Router next to the application with its own account; make applications retry.
• Monitor member state and Group Replication queues; batch large transactions.
• Know the recovery commands - rejoinInstance, clone re-provisioning, forceQuorumUsingPartitionOf, rebootClusterFromCompleteOutage - and practise them.
• Back up from a secondary, keep binary logs, and drill point-in-time recovery.
• Upgrade tools first, then members in a rolling order.

#Further reading

• MySQL documentation: MySQL InnoDB Cluster, InnoDB ClusterSet, Group Replication (requirements, flow control, consistency), MySQL Router.
• On this site: the beginner session, the InnoDB Cluster setup quick reference, choosing MySQL high availability, cross-region async replica DR and MySQL 8.0 to 8.4 LTS upgrade.