MySQL 8.0 Enterprise Router

Tuesday, May 7, 2019 by Lucas Vogel

MySQL Router is an enterprise middleware service for InnoDB clusters used to route client requests to database servers based on a number of configuration scenarios. Let's take a look at some of the configuration options, as well as keep track of what's new in version 8.0.

Category: Enterprise Features


Router is a standalone service that sits between applications and your MySQL database cluster and routes requests based on a combination of cluster metadata (for database configuration) and a configuration file (for routing strategies and other details). The cluster metadata is self-configured by the router upon startup by passing in the --bootstrap parameter with the appropriate cluster metadata connection string. This connection stays open, reading the performance_schema tables which are updated in real time by the replication manager whenever anything happens to server state (add/remove servers, etc.). If a connection is made to a server that goes down, the connection is automatically closed and the application must reconnect and retry the operation.

How It Works

Typically MySQL Router is deployed to an application server, where it is configured to connect to an instance for metadata information about the cluster configuration using the --bootstrap parameter listed above. The router then reads the configuration file, which can contain one or more routing configurations based on the strategy needed by the application and/or database configuration.

If we look at the sample configuration file in the documentation, we see four connections - two read-write and two read-only, for relational and Document Store data. Depending on the scenario, your application would connect to localhost:xxxx based on the specified port # in the configuration, and MySQL Router would automatically manage the connection routing for you.

Router Configuration

There are a number of configuration options available, with options for default values, routing, metadata cache, logging, and HTTP server settings - each having their own section. One of the routing options is the routing_strategy setting. There are four options to choose from:

  • first-available routes new connections to the first available server; if a failure occurs, it will try the next server until all server options are exhausted.
  • next-available routes to the first available server, but if the server isn't available, that server is discarded from the list, never to be attempted again. (The list is refreshed after a router restart)
  • round-robin will route each connection to the next available server in its list, repeating indefinitely
  • round-robin-with-fallback uses a list of secondary servers; if that list is exhausted, it will attempt to connect to the list of primary servers.

There are a lot of nuances to the settings, so a thorough read of the documentation is strongly encouraged.

What's New in MySQL Router 8.0

8.0.16

Log Rotation

You can now send a SIGHUP signal to the router in order to get it to release its log file. This is in support of the logrotate utility, which is designed to help manage log files.

HTTP Server

HTTP server functionality now added as a feature of MySQL Router. Not much information is given about this feature - the release notes state that it's based on the libevent event notification http library; the MySQL Server blog says it's for monitoring and management, and that if you configure it you can send static files with it (or does that mean static file hosting?). Either way, it should be an interesting development to see what this new feature brings in future releases.

8.0.3 to 8.0.14

Several new features have been added in addition to the detailed list of bug fixes found in the release notes.

routing_strategy

routing_strategy was a new feature added to replace the mode option, and to offer more routing options as a result.

SSL

SSL-specific command line options were added to allow applications to authenticate with the router using certificates.

bootstrap

The bootstrap functionality allows MySQL Router to connect to any server in the cluster, and not just the primary. It also allows for a --config option.

Concurrency

The maximum number of connections is now at 5000, a 10x improvement to the previous limit of 500.

Plugin information

A mysqlrouter_plugin_info utility was added for obtaining information about plugins.

mysql_server_mock

A mysql_server_mock utility was added to make replication group testing more predictable. There is no information about this utility other than to run it in order to see the options.

8.0.17

This version of MySQL Router offers OpenAPI v2 compatible REST endpoints for monitoring, service health, metadata-cache, routing, and X Protocol support for mysql_server_mock.