3.3 Basic Connection Routing

The Connection Routing plugin performs connection-based routing, meaning it forwards packets to the server without inspecting them. This is a simplistic approach that provides high throughput. For additional general information about connection routing, see Section 1.3, “Connection Routing”.

A simple connection-based routing setup is shown below. These and additional options are documented under Section 4.3.3, “Configuration File Options”.

[logger]
level = INFO

[routing:secondary]
bind_address = localhost
bind_port = 7001
destinations = foo.example.org:3306,bar.example.org:3306,baz.example.org:3306
routing_strategy = round-robin

[routing:primary]
bind_address = localhost
bind_port = 7002
destinations = foo.example.org:3306,bar.example.org:3306
routing_strategy = first-available

Here we use connection routing to round-robin MySQL connections to three MySQL servers on port 7001 as defined by round-robin routing_strategy. This example also configures the first-available strategy for two of the servers using port 7002. The first-available strategy uses the first available server from the destinations list. The number of MySQL instances assigned to each destinations is up to you as this is only an example. Router does not inspect the packets and does not restrict connections based on assigned strategy or mode, so it is up the application to determine where to send read and write requests, which is either port 7001 or 7002 in our example.

Note

Before MySQL Router 8.0, the now deprecated mode option was used instead of the routing_strategy option that was added in MySQL Router 8.0.

Assuming all three MySQL instances are running, next start MySQL Router by passing in the configuration file:

$> ./bin/mysqlrouter -config=/etc/mysqlrouter-config.conf

Now MySQL Router is listening to ports 7001 and 7002 and sends requests to the appropriate MySQL instances. For example:

$> ./bin/mysql --user=root --port 7001 --protocol=TCP

That will first connect to foo.example.org, and then bar.example.org next, then baz.example.org, and the fourth call goes back to foo.example.org. Instead, we configured port 7002 behavior differently:

$> ./bin/mysql --user=root --port 7002 --protocol=TCP

That first connects to foo.example.org, and additional requests will continue connecting to foo.example.org until there is a failure, at which point bar.example.org is now used. For additional information about this behavior, see mode.