MySQL Router and InnoDB Cluster Overview

2020-07-17 · mysql, high-availability, innodb-cluster

After I built Group Replication by hand, the obvious question was still open: how does the application find the primary after a failover? InnoDB Cluster is Oracle's answer, and it bundles three pieces: Group Replication for the data, MySQL Shell to create and manage the cluster, and MySQL Router, a small proxy that always knows the current primary.

Here's a three-node cluster on 8.0 (8.0.20 or thereabouts) built with Shell, with Router in front. If you've done Group Replication manually you'll recognise what Shell automates; if not, honestly, learn the Shell route first. You need mysql-shell on at least one machine, mysql-router on each application host (or a pair of dedicated hosts), TCP 3306 and 33061 open between database nodes, 6446 and 6447 open from the app hosts to Router, and an admin account that exists on all three nodes.

Let Shell check each instance

Shell verifies and applies the server settings Group Replication needs (gtid_mode, enforce_gtid_consistency, binlog_checksum, server_id, and so on). Run from any machine that can reach the nodes:

mysqlsh --js

mysql-js> dba.checkInstanceConfiguration('clusteradmin@db1:3306')
mysql-js> dba.configureInstance('clusteradmin@db1:3306', {clusterAdmin: 'clusteradmin', clusterAdminPassword: 'strong-password', restart: true})

Repeat configureInstance for db2 and db3. With restart: true the instance restarts to apply settings that cannot be changed at runtime; Shell writes them with SET PERSIST, so nothing needs to be edited in my.cnf.

Create the cluster

Connect to the node that will be the initial primary and create the cluster:

mysql-js> \connect clusteradmin@db1:3306
mysql-js> var cluster = dba.createCluster('prodCluster')
mysql-js> cluster.addInstance('clusteradmin@db2:3306')
mysql-js> cluster.addInstance('clusteradmin@db3:3306')
mysql-js> cluster.status()

On 8.0.17 and later addInstance offers clone as the recovery method, which copies the whole data set from an existing member using the clone plugin; choose it for a new node. Incremental recovery (replaying binlogs) is fine only if the joining node has all the history.

cluster.status() returns JSON; you want "status": "OK", three members "ONLINE", and one with "mode": "R/W".

Bootstrap Router

Router reads the cluster metadata that Shell stored in the mysql_innodb_cluster_metadata schema. Bootstrap it against any member:

sudo mysqlrouter --bootstrap clusteradmin@db1:3306 --user=mysqlrouter --directory=/var/lib/mysqlrouter --conf-use-sockets
sudo systemctl enable --now mysqlrouter

Bootstrap creates a mysqlrouter.conf with four listeners:

[routing:prodCluster_rw]
bind_address=0.0.0.0
bind_port=6446
destinations=metadata-cache://prodCluster/?role=PRIMARY
routing_strategy=first-available

[routing:prodCluster_ro]
bind_address=0.0.0.0
bind_port=6447
destinations=metadata-cache://prodCluster/?role=SECONDARY
routing_strategy=round-robin-with-fallback

Plus the X Protocol equivalents on 6448 and 6449. Applications send writes to port 6446 and reads that tolerate replication lag to 6447. Router keeps a cache of the topology and refreshes it from the metadata every ttl seconds (0.5 by default); on a primary change it drops the affected connections so the application reconnects to the new primary.

Point the application at Router

I install Router on the application server itself, so the connection string becomes 127.0.0.1:6446. That takes Router out of the network path as a separate failure point. Router is stateless, so one per app host is the intended design.

mysql -h 127.0.0.1 -P 6446 -u shopapp -p -e "SELECT @@hostname, @@super_read_only;"
mysql -h 127.0.0.1 -P 6447 -u shopapp -p -e "SELECT @@hostname, @@super_read_only;"

The first should return the primary with super_read_only = 0, the second one of the secondaries with 1.

Day-to-day commands

mysql-js> cluster.setPrimaryInstance('db2:3306')            // planned switchover
mysql-js> cluster.removeInstance('db3:3306')
mysql-js> cluster.rescan()                                   // after manual changes
mysql-js> cluster.rejoinInstance('db3:3306')
mysql-js> dba.rebootClusterFromCompleteOutage()             // after all nodes were down
mysql-js> cluster.listRouters()

rebootClusterFromCompleteOutage is the one to remember: after a full power loss the group does not bootstrap itself, and this command re-forms it from the node with the most recent GTID set.

Kill the primary

Do this before go-live, not after:

sudo systemctl stop mysqld     # on db1

mysql-js> cluster.status()     // db2 or db3 now "R/W", db1 "(MISSING)"

mysql -h 127.0.0.1 -P 6446 -u shopapp -p -e "SELECT @@hostname;"   # new primary, within seconds

Start db1 again; with group_replication_start_on_boot (which Shell sets) it rejoins as a secondary and cluster.status() returns to OK.

Problems I'd expect

Dba.createCluster: Instance 'db1:3306' cannot be added to an InnoDB cluster because it has asynchronous (master-slave) replication configured and running. – Stop and reset any classic replication channel (STOP SLAVE; RESET SLAVE ALL;) first.
Cluster.addInstance: The instance 'db2:3306' has an incompatible GTID set with the seed instance – The joining node has local transactions the group does not know about. If it holds no data of value, use RESET MASTER on it or choose clone recovery.
Router logs Metadata refresh failed and No metadata server available and connections to 6446 are refused – Router cannot reach any member with the mysqlrouter metadata account. Check firewall rules and that the account's host pattern matches the Router host.
cluster.status() shows "status": "NO_QUORUM" – A majority of members is unreachable, so the survivors refuse writes to avoid split-brain. If you are certain the others are dead, cluster.forceQuorumUsingPartitionOf('db1:3306') re-enables the partition you can see.
Applications get ERROR 2013 (HY000): Lost connection to MySQL server during query at failover – Expected once; Router deliberately drops connections to the old primary. Make sure the application's connection pool retries or validates connections.

It doesn't remove the need to understand Group Replication. Still, for a new 8.0 deployment that needs automatic failover, it's what I recommend by default.