Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 40.3Mb
PDF (A4) - 40.4Mb
PDF (RPM) - 39.9Mb
HTML Download (TGZ) - 10.6Mb
HTML Download (Zip) - 10.6Mb
HTML Download (RPM) - 9.2Mb
Man Pages (TGZ) - 241.2Kb
Man Pages (Zip) - 346.4Kb
Info (Gzip) - 3.9Mb
Info (Zip) - 3.9Mb
Excerpts from this Manual

MySQL 8.0 Reference Manual  /  ...  /  Bootstrapping MySQL Router

21.4.1 Bootstrapping MySQL Router

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. Pass in the --bootstrap option when you start MySQL Router, and it retrieves the topology information from the metadata and configures routing connections to the server instances. Client applications then connect to the ports MySQL Router publishes, without any need to be aware of the underlying topology. 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. For more information, see Routing for MySQL InnoDB Cluster.

Note

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.

Configuring the MySQL Router User

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})

Deploying MySQL Router

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=instance option, where 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.

Tip

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
Tip

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 |
+--------+

Using ReplicaSets with MySQL Router

You can use MySQL Router 8.0.19 and later to bootstrap against an InnoDB ReplicaSet, see Section 21.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 ReplicaSet.listRouters() and ReplicaSet.removeRouterMetadata().