MySQL Router 8.0  /  General Information  /  Routing for MySQL InnoDB cluster

1.1 Routing for MySQL InnoDB cluster

MySQL Router is part of InnoDB cluster and is lightweight middleware that provides transparent routing between your application and back-end MySQL Servers. It is used for a wide variety of use cases, such as providing high availability and scalability by 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.

Introduction

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 cluster instances. As long as there are enough online replicas and communication between the components is intact, applications will be able to contact one of them. MySQL Router also makes this possible by having applications connect to MySQL Router instead of directly to MySQL.

Deploying Router with MySQL InnoDB cluster

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 MySQL Router with an InnoDB cluster after configuring the cluster are:

  1. Install MySQL Router.

  2. Bootstrap InnoDB cluster, and test.

    Bootstrapping automatically configures MySQL Router for an existing InnoDB cluster by using --bootstrap and other command-line options. During bootstrap, Router connects to the cluster, fetches its metadata, and configures itself for use. Bootstrapping is optional.

    For additional information, see Chapter 3, Deploying MySQL Router.

  3. Set up MySQL Router for automatic startup.

    Configure your system to automatically start MySQL Router when the host is rebooted, a process 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 MySQL Router using:

shell> mysqlrouter --bootstrap localhost:3310 --directory /opt/myrouter --user snoopy

This example bootstraps MySQL Router to an existing InnoDB cluster where:

  • localhost:3310 is a member of an InnoDB cluster, and either the PRIMARY or bootstrap will redirect to a PRIMARY in the cluster.

  • Because the optional --directory bootstrap option was used, this example creates a self-contained installation with all generated directories and files at /opt/myrouter/. These files include start.sh, stop.sh, log/, and a fully functional MySQL Router configuration file named mysqlrouter.conf.

  • Only the host's system user named snoopy will have access to /opt/myrouter/*.

See --bootstrap and related options for ways to modify the bootstrap configuration process. For example, passing in --conf-use-sockets enables Unix domain socket connections because only TCP/IP connections are enabled by default.

Bootstrapping and group_replication_single_primary_mode

When bootstrapping, the available ports and sockets are affected by the group_replication_single_primary_mode MySQL Server configuration option.

Note

This document refers to default bootstrapping behavior. Other MySQL Router configuration options may affect this behavior, and generated configuration values can be manually changed after bootstrapping.

  • With group_replication_single_primary_mode=ON (the default): Both Read-Write (primary) and Read-Only (secondary) ports are configured.

  • With group_replication_single_primary_mode=OFF: Only Read-Write (primary) ports are configured.

For example:

With group_replication_single_primary_mode=ON, 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

With group_replication_single_primary_mode=OFF, 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

User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
Sign Up Login You must be logged in to post a comment.