This section describes how to use MySQL Router with InnoDB cluster
to achieve high availability. Regardless of whether you have
deployed a sandbox or production cluster, MySQL Router can configure
itself based on the InnoDB cluster's metadata using the
--bootstrap option. This
configures MySQL Router automatically to route connections to the
cluster's server instances. Client applications connect to
the ports MySQL Router provides, without any need to be aware of the
InnoDB cluster topology. In the event of a unexpected failure,
the InnoDB cluster adjusts itself automatically and MySQL Router
detects the change. This removes the need for your client
application to handle failover. For more information, see
Routing for MySQL InnoDB cluster.
Do not attempt to configure MySQL Router manually to redirect to the
ports of an InnoDB cluster. Always use the
--bootstrap option as this
ensures that MySQL Router takes its configuration from the
InnoDB cluster's metadata. See
Cluster Metadata and State.
The recommended deployment of MySQL Router is on the same host as the application. When using a sandbox deployment, everything is running on a single host, therefore you deploy MySQL Router to the same host. When using a production deployment, we recommend deploying one MySQL Router instance to each machine used to host one of your client applications. It is also possible to deploy MySQL Router to a common machine through which your application instances connect.
Assuming MySQL Router is already installed (see
Installing MySQL Router), use the
--bootstrap option to provide
the location of a server instance that belongs to the
InnoDB cluster. MySQL Router retrieves the InnoDB cluster's
metadata, consisting of a list of server instance addresses which
make up the cluster and their role in the cluster. You pass the
URI-like connection string of the server that MySQL Router should
retrieve the InnoDB cluster metadata from. For example:
shell> mysqlrouter --bootstrap ic@ic-1:3306 --user=mysqlrouter
You are prompted for the instance password and encryption key for
MySQL Router to use. This encryption key is used to encrypt the
instance password used by MySQL Router to connect to the cluster. The
ports you can use to connect to the InnoDB cluster are also
displayed. The MySQL Router bootstrap process creates a
mysqlrouter.conf file, with the settings
based on the cluster metadata retrieved from the address passed to
--bootstrap option, in the
ic@ic-1:3306. Based on the
InnoDB cluster metadata retrieved, MySQL Router automatically
metadata_cache section with
containing the addresses for all server instances in the cluster.
[metadata_cache:prodCluster] router_id=1 bootstrap_server_addresses=mysql://ic@ic-1:3306,mysql://ic@ic-2:3306,mysql://ic@ic-3:3306 user=mysql_router1_jy95yozko3k2 metadata_cluster=prodCluster ttl=300
When you change the topology of a cluster by adding another
server instance after you have bootstrapped MySQL Router, you need
based on the updated metadata. Either restart MySQL Router using the
--bootstrap option, or
manually edit the
section of the
mysqlrouter.conf file and
restart MySQL Router.
The generated MySQL Router configuration creates TCP ports which you use to connect to the cluster. Ports for communicating with the cluster using both Classic MySQL protocol and X Protocol are created. To use X Protocol the server instances must have X Plugin installed and configured. For a sandbox deployment, instances have X Plugin set up automatically. For a production deployment, if you want to use X Protocol you need to install and configure X Plugin on each instance, see Installing MySQL Shell. The default available TCP ports are:
6446- for Classic MySQL protocol read-write sessions, which MySQL Router redirects incoming connections to primary server instances.
6447- for Classic MySQL protocol read-only sessions, which MySQL Router redirects incoming connections to one of the secondary server instances.
64460- for X Protocol read-write sessions, which MySQL Router redirects incoming connections to primary server instances.
64470- for X Protocol read-only sessions, which MySQL Router redirects incoming connections to one of the secondary server instances.
Depending on your MySQL Router configuration the port numbers might be
different to the above. For example if you use the
--conf-base-port option, or
variable. The exact ports are listed when you start MySQL Router.
The way incoming connections are redirected depends on the type of
cluster being used. When using a single-primary cluster, by
default MySQL Router publishes a X Protocol and a classic
protocol port, which clients connect to for read-write sessions
and which are redirected to the cluster's single primary. With a
multi-primary cluster read-write sessions are redirected to one of
the primary instances in a round-robin fashion. For example, this
means that the first connection to port 6446 would be redirected
to the ic-1 instance, the second connection to port 6446 would be
redirected to the ic-2 instance, and so on. For incoming read-only
connections MySQL Router redirects connections to one of the secondary
instances, also in a round-robin fashion. To modify this behavior
Once bootstrapped and configured, start MySQL Router. If you used a
system wide install with the
--bootstrap option then issue:
shell> mysqlrouter &
If you installed MySQL Router to a directory using the
--directory option, use the
start.sh script found in the directory you
installed to. Alternatively set up a service to start MySQL Router
automatically when the system boots, see
Starting MySQL Router. You can now
connect a MySQL client, such as MySQL Shell to one of the
incoming MySQL Router ports as described above and see how the client
gets transparently connected to one of the InnoDB cluster
shell> mysqlsh --uri root@localhost:6442
To verify which instance you are actually connected to, simply
issue an SQL query against the
port status variable.
mysql-js> \sql Switching to SQL mode... Commands end with ; mysql-sql> select @@port; +--------+ | @@port | +--------+ | 3310 | +--------+
To test if 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. Exactly 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 Section 126.96.36.199, “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 Section 188.8.131.52, “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 assume there is a
single-primary sandbox cluster deployment with three server
instances and the instance listening at port 3310 is the current
primary. Simulate the instance leaving the cluster unexpectedly:
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
port variable to check which
instance your session has been redirected to. Notice that the
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.
mysql-js> \sql 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 shows that the InnoDB cluster provided us with automatic failover, that MySQL Router has automatically reconnected us to the new primary instance, and that we have high availability.
When MySQL Router is bootstrapped against a cluster, it records the server instance's addresses in its configuration file. If any additional instances are added to the cluster after bootstrapping the MySQL Router, they are not automatically detected and therefore are not used for connection routing.
To ensure that newly added instances are routed to correctly you
must bootstrap MySQL Router against the cluster to read the updated
metadata. This means that you must restart MySQL Router and include