MySQL Router is part of InnoDB cluster, and is lightweight middleware that provides transparent routing between your application and back-end MySQL Servers. It can be used for a wide variety of use cases, such as providing high availability and scalability by effectively routing database traffic to appropriate back-end MySQL Servers. The pluggable architecture also enables developers to extend MySQL Router for custom use cases.
For additional details about how Router is part of InnoDB cluster, see InnoDB Cluster.
For client applications to handle failover, they need to be aware of the InnoDB cluster topology and know which MySQL instance is the PRIMARY. While it is possible for applications to implement that logic, MySQL Router can provide and handle this functionality for you.
MySQL uses Group Replication to replicate databases across multiple servers while performing automatic failover in the event of a server failure. When used with a MySQL InnoDB cluster, MySQL Router acts as a proxy to hide the multiple MySQL instances on your network and map the data requests to one of the instances in the cluster. As long as there are enough online replicas, and communication between the components is intact, applications will be able to contact one of them. Router also makes it possible for this to happen by simply repointing applications to connect to Router instead of directly to MySQL.
The recommended deployment model for MySQL Router is with InnoDB cluster, with Router sitting on the same host as the application.
The steps for deploying Router with an InnoDB cluster after the cluster is configured are:
Install MySQL Router.
For details, see the Installation section.
Bootstrap for an InnoDB cluster, and test.
Router can be automatically configured by calling it with
--bootstrap. During bootstrap, Router connects to the cluster, fetches its metadata, and configures itself for use. For details, see Chapter 3, Deploying MySQL Router.
Set up Router for automatic startup.
To make Router automatically start when the host reboots, you need to configure your system to start Router. This process is similar to how the MySQL server is configured to start automatically. For additional details, see Section 5.1, “Starting MySQL Router”.
For example, after creating a MySQL InnoDB cluster, you might configure Router using:
shell> mysqlrouter --bootstrap localhost:3310 --directory /opt/myrouter --user snoopy
This example bootstraps MySQL Router to an existing InnoDB cluster where:
localhost:3310is the PRIMARY with a metadata server
Creates a self-contained installation with all generated directories and files at
Only the host's system user named
snoopywill have access to
Files and directories are generated under
log/, and a fully functional MySQL Router configuration file named
related configuration options for information to modify how the
bootstrap process is configured. For example, passing in
Unix domain socket connections because only TCP/IP connections are
enabled by default.
When bootstrapping, the available ports and sockets are affected
MySQL server configuration option.
This document refers to default bootstrapping behavior. Other MySQL Router configuration options may affect this behavior, and generated configuration values can be modified after bootstrapping.
ON(the default): Both Read-Write (primary) and Read-Only (secondary) ports are configured.
OFF: Only Read-Write (primary) ports are configured.
all connections to ports 6446 and 64460 go to the single primary,
and all connections to ports 6447 and 64470 go to the secondaries
using the round-robin mode schedule.
shell> mysqlrouter --bootstrap localhost:3310 Classic MySQL protocol connections to cluster 'myCluster': - Read/Write Connections: localhost:6446 - Read/Only Connections: localhost:6447 X protocol connections to cluster 'myCluster': - Read/Write Connections: localhost:64460 - Read/Only Connections: localhost:64470
all connections to ports 6446 and 64460 go to the primaries using
the round-robin mode schedule.
shell> mysqlrouter --bootstrap localhost:3310 Classic MySQL protocol connections to cluster 'myCluster': - Read/Write Connections: localhost:6446 X protocol connections to cluster 'myCluster': - Read/Write Connections: localhost:64460