MySQL Shell 8.0 (part of MySQL 8.0)
You bootstrap MySQL Router against an InnoDB ReplicaSet or
InnoDB Cluster to automatically configure routing. The
bootstrap process is a specific way of running MySQL Router, which
does not start the usual routing and instead configures the
mysqlrouter.conf
file based on the
metadata. To boostrap MySQL Router at the command-line, pass in the
--bootstrap
option when you
start the mysqlrouter command, and it
retrieves the topology information from the metadata and
configures routing connections to the server instances.
Alternatively, on Windows use the MySQL Installer to bootstrap MySQL Router,
see
MySQL Router Configuration with MySQL Installer.
Once MySQL Router has been bootstrapped, client applications then
connect to the ports it publishes. MySQL Router automatically
redirects client connections to the instances based on the
incoming port, for example 6646 is used by default for
read-write connections using classic MySQL protocol. In the event of
a topology change, for example due to an unexpected failure of
an instance, MySQL Router detects the change and adjusts the routing
to the remaining instances automatically. This removes the need
for client applications to handle failover, or to be aware of
the underlying topology. For more information, see
Routing for MySQL InnoDB Cluster.
Do not attempt to configure MySQL Router manually to redirect to
the server instances. Always use the
--bootstrap
option as this
ensures that MySQL Router takes its configuration from the
metadata. See Cluster Metadata and State.
When MySQL Router connects to an InnoDB Cluster or
InnoDB ReplicaSet, it requires a user account which has the
correct privileges. From MySQL Router version 8.0.19 this internal
user can be specified using the
--account
option. In
previous versions, MySQL Router created internal accounts at each
bootstrap of the cluster, which could result in a number of
accounts building up over time. From MySQL Shell version
8.0.20, you can use AdminAPI to set up the user account
required for MySQL Router. Use the
setupRouterAccount(user, [options])
operation to create a MySQL user account or upgrade an
existing account so that it can be used by MySQL Router to operate
on an InnoDB Cluster or InnoDB ReplicaSet. This is the
recommended method of configuring MySQL Router with
InnoDB Cluster and InnoDB ReplicaSet.
To add a new MySQL Router account named
myRouter1
to the InnoDB Cluster
referenced by the variable
testCluster
, issue:
mysqlsh> testCluster.setupRouterAccount(myRouter1
)
In this case, no domain is specified and so the account is
created with the wildcard (%
) character,
which ensures that the created user can connect from any
domain. To limit the account to only be able to connect from
the example.com
domain, issue:
mysqlsh> testCluster.setupRouterAccount(myRouter1
@example.com
)
The operation prompts for a password, and then sets up the MySQL Router user with the correct privileges. If the InnoDB Cluster or InnoDB ReplicaSet has multiple instances, the created MySQL Router user is propagated to all of the instances.
When you already have a MySQL Router user configured, for example
if you were using a version prior to 8.0.20, you can use the
setupRouterAccount()
operation to
reconfigure the existing user. In this case, pass in the
update
option set to true. For example, to
reconfigure the myOldRouter
user,
issue:
mysqlsh> testCluster.setupRouterAccount(myOldRouter, {'update':1})
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
--bootstrap=
option, where instance
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:
shell> mysqlrouter --bootstrap icadmin@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 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
above example icadmin@ic-1:3306
. Based on
the metadata retrieved, MySQL Router automatically configures the
mysqlrouter.conf
file, including a
metadata_cache
section. If you are using
MySQL Router 8.0.14 and later, the
--bootstrap
option
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 dynamic_state
documentation.
In earlier MySQL Router versions, metadata server information was
defined during MySQL Router's initial bootstrap operation and
stored statically as
bootstrap_server_addresses
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
bootstrap_server_addresses
based on the updated metadata. Either restart MySQL Router using
the --bootstrap
option,
or manually edit the
bootstrap_server_addresses
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 the
group_replication_single_primary_mode
variable. The exact ports are listed when you start MySQL Router.
The way incoming connections are redirected depends on the
underlying topology being 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, 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 see the
routing_strategy
option.
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 server
instances.
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 | +--------+
You can use MySQL Router 8.0.19 and later to bootstrap against an
InnoDB ReplicaSet, see
Section 6.4, “MySQL Router”. The only
difference in the generated MySQL Router configuration file is the
addition of the
cluster_type
option. When
MySQL Router is bootstrapped against a ReplicaSet, the generated
configuration file includes:
cluster_type=rs
When you use MySQL Router with InnoDB ReplicaSet, be aware that:
The read-write port of MySQL Router directs client connections to the primary instance of the ReplicaSet
The read-only port of MySQL Router direct client connections to a secondary instance of the ReplicaSet, although it could also direct them to the primary
MySQL Router obtains information about the ReplicaSet's topology from the primary instance
MySQL Router automatically recovers when the primary instance becomes unavailable and a different instance is promoted
You work with the MySQL Router instances which have been
bootstrapped against a ReplicaSet in exactly the same way as
with InnoDB Cluster. See
Working with a Cluster's Routers for information on
and
ReplicaSet
.listRouters()
.
ReplicaSet
.removeRouterMetadata()