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. For more information, see Installing MySQL Router.
To bootstrap MySQL Router based on an InnoDB Cluster or
InnoDB ReplicaSet, you need the URI-like connection string to
an online instance. Run the mysqlrouter
command and provide the
instance is the
URI-like connection string to an online instance. MySQL Router
connects to the instance and uses the included metadata cache
plugin to retrieve the metadata, consisting of a list of server
instance addresses and their role. For example:
$> mysqlrouter --bootstrap icadmin@ic-1:3306 --account=mysqlrouter
You are prompted for the instance password and encryption key for MySQL Router to use. This key is used to encrypt the instance password used by MySQL Router to connect to the cluster. The ports you can use for client connections are also displayed. For additional bootstrap related options, see Bootstrapping Options.
At this point MySQL Router has not been started so that it would route connections. Bootstrapping is a separate process.
The MySQL Router bootstrap process creates a
mysqlrouter.conf file, with the settings
based on the metadata retrieved from the address passed to the
--bootstrap option, in the
icadmin@ic-1:3306. Based on the
metadata retrieved, MySQL Router automatically configures the
mysqlrouter.conf file, including a
If you are using MySQL Router 8.0.14 and later, the
automatically configures MySQL Router to track and store active
MySQL metadata server addresses at the path configured by
dynamic_state. This ensures
that when MySQL Router is restarted it knows which MySQL metadata
server addresses are current. For more information, see the
In earlier MySQL Router versions, metadata server information was
defined during MySQL Router's initial bootstrap operation and stored
in the configuration file, which contained the addresses for all
server instances in the cluster. For example:
[metadata_cache:prodCluster] router_id=1 bootstrap_server_addresses=mysql://icadmin@ic-1:3306,mysql://icadmin@ic-2:3306,mysql://icadmin@ic-3:3306 user=mysql_router1_jy95yozko3k2 metadata_cluster=prodCluster ttl=300
If using MySQL Router 8.0.13 or earlier, when you change the
topology of a cluster by adding another server instance after
you have bootstrapped MySQL Router, you need to update
based on the updated metadata. Either restart MySQL Router using
--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. By default, 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, which is the default for MySQL 8.0 and later. 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 underlying topology used. For example, when using a single-primary cluster, by default MySQL Router publishes a X Protocol and a classic MySQL 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, the first connection to port 6446 is redirected to the ic-1 instance. The second connection to port 6446 is 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 see the
Once bootstrapped and configured, start MySQL Router. If you used a
system wide install with the
--bootstrap option then
$> mysqlrouter &
If you installed MySQL Router to a directory using the
--directory option, use the
start.sh script found in the directory you
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 server instances.
$> mysqlsh --uri root@localhost:6442
To verify which instance you are connected to, issue an SQL
query against the
variable. For example:
mysql-js> \sql Switching to SQL mode... Commands end with ; mysql-sql> select @@port; +--------+ | @@port | +--------+ | 3310 | +--------+
Or, for example, using:
mysql-js> \sql Switching to SQL mode... Commands end with ; mysql-sql> SHOW VARIABLES WHERE Variable_name = 'port'; +--------+ | @@port | +--------+ | 3310 | +--------+