To test if InnoDB Cluster high availability works, simulate an unexpected halt by killing an instance. The cluster detects the fact that the instance left the cluster and reconfigures itself. How the cluster reconfigures itself depends on whether you are using a single-primary or multi-primary cluster, and the role the instance serves within the cluster.
In single-primary mode:
If the current primary leaves the cluster, one of the secondary instances is elected as the new primary, with instances prioritized by the lowest
server_uuid. MySQL Router redirects read-write connections to the newly elected primary.
If a current secondary leaves the cluster, MySQL Router stops redirecting read-only connections to the instance.
For more information see Single-Primary Mode.
In multi-primary mode:
If a current "R/W" instance leaves the cluster, MySQL Router redirects read-write connections to other primaries. If the instance which left was the last primary in the cluster then the cluster is completely gone and you cannot connect to any MySQL Router port.
For more information see Multi-Primary Mode.
There are various ways to simulate an instance leaving a
cluster, for example you can forcibly stop the MySQL server on
an instance, or use the AdminAPI
dba.killSandboxInstance() if testing a
sandbox deployment. In this example, there is a single-primary
sandbox cluster deployment with three server instances and the
instance listening at port 3310 is the current primary. The
instance leaves the cluster unexpectedly, simulated by
killing an the instance:
Or, by issuing the Python command:
The cluster detects the change and elects a new primary automatically.
Assuming your session is connected to port 6446, the default
read-write classic MySQL protocol port, MySQL Router should detect the
change to the cluster's topology and redirect your session to
the newly elected primary. To verify this, switch to SQL mode in
MySQL Shell using the
\sql command and
select the instance's
variable to check which instance your session has been
SELECT statement fails
as the connection to the original primary was lost, this means
the current session has been closed. MySQL Shell automatically
reconnects for you and when you issue the command again the new
port is confirmed.
Switching to SQL mode... Commands end with ;
mysql-sql> SELECT @@port;
ERROR: 2013 (HY000): Lost connection to MySQL server during query
The global session got disconnected.
Attempting to reconnect to 'root@localhost:6446'...
The global session was successfully reconnected.
mysql-sql> SELECT @@port;
| @@port |
| 3330 |
1 row in set (0.00 sec)
In this example, the instance at port 3330 has been elected as the new primary. This election shows that the InnoDB Cluster has provided automatic failover, and that MySQL Router has automatically reconnected us to the new primary instance, and that we have high availability.