MySQL Router 2.1  /  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 MySQL server instances which make up an InnoDB cluster. 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 InnoDB cluster, see InnoDB Cluster.

Introduction

For client applications to handle failover, they need to be aware of the InnoDB cluster's topology and know the role of each MySQL instance - whether it is primary or secondary. While it is possible for applications to implement that logic, MySQL Router provides this functionality for you. MySQL Router includes the InnoDB cluster metadata cache plugin, which enables MySQL Router to automatically configure itself based on the cluster's topology. This process is referred to as bootstrapping.

When boostrapped against an InnoDB cluster, MySQL Router acts as a proxy to the multiple MySQL instances which make up the cluster. MySQL Router maps application client requests to one of the instances in the cluster. Different ports are provided for different purposes, such as read-write or read-only sessions, using either MySQL protocol or X Protocol. If the cluster changes, for example due to a fail over, MySQL Router automatically handles changes to the roles of servers. Client applications continue to use the same MySQL Router port, while the destination server instance in the InnoDB cluster might have changed. All of this leads to a highly available MySQL database which is easy to configure.

Deploying Router with MySQL InnoDB cluster

The recommended deployment model for MySQL Router is bootstrapped against an InnoDB cluster, with Router running on the same host as the application.

Tip

Using a bootstrapped MySQL Router against an InnoDB cluster is the only recommended way of configuring Group Replication and MySQL Router.

The steps for deploying Router with an InnoDB cluster after the cluster is configured are:

  1. Install MySQL Router.

    For details, see the Installation section.

  2. 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.

  3. 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:3310 is the PRIMARY with a metadata server

  • Creates a self-contained installation with all generated directories and files at /opt/myrouter/

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

  • Files and directories are generated under /opt/myrouter/ including start.sh, stop.sh, log/, and a fully functional MySQL Router configuration file named mysqlrouter.conf.

See the --bootstrap and related configuration options for information to modify how the bootstrap process is configured. For example, passing in --conf-use-sockets enables Unix domain socket connections instead of the TCP/IP connections which are enabled by default.

Bootstrapping and InnoDB cluster Modes

InnoDB clusters can run in a single-primary mode where one server instance is writeable, or a multi-primary mode where multiple servers are writeable. When bootstrapping, the ports and sockets configured by MySQL Router are affected by the mode which the cluster is running in. You can check the mode which a cluster is running in by group_replication_single_primary_mode MySQL server configuration option.

Note

This document refers to default bootstrapping behavior. Other MySQL Router configuration options might affect this behavior, and generated configuration values can be modified 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.