Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

InnoDB Cluster in a Nutshell (Part 2): MySQL Router

DZone's Guide to

InnoDB Cluster in a Nutshell (Part 2): MySQL Router

This article looks at MySQL router and explains what this simple tool is and what it does as well as gives a sample configuration.

· Database Zone ·
Free Resource

Discover Tarantool's unique features which include powerful stored procedures, SQL support, smart cache, and the speed of 1 million ACID transactions on a single CPU core!

MySQL InnoDB Cluster is an Oracle High Availability solution that can be easily installed over MySQL to provide high availability with multi-master capabilities and automatic failover. In the previous post, we presented the first component of InnoDB Cluster, group replication. Now, we will go through the second component, MySQL Router. We will address MySQL Shell in a final installment of this three-part series. By then, you should have a good overview of the features offered by MySQL InnoDB Cluster.

MySQL Router

This component is responsible for distributing the traffic between members of the cluster. It is a proxy-like solution to hide cluster topology from applications so applications don't need to know which member of a cluster is the primary node and which are secondaries.

The tool is capable of performing read/write splitting by exposing different interfaces. A common setup is to have one read-write interface and one read-only interface. This is default behavior that also exposes 2 similar interfaces to use x-protocol (i.e. used for CRUD operations and async calls).

The read and write split is done using a concept of roles: Primary for writes and Secondary for read-only. This is analogous to how members of a cluster are named. Additionally, each interface is exposed via a TCP port so applications only need to know the IP:port combination used for writes and the one used for reads. Then, MySQL Router will take care of connections to cluster members depending on the type of traffic to the server.

MySQL Router is a very simple tool, maybe too simple, as it is a layer four load balance and lacks some of the advanced features that some of its competitors have.

Here is a short list of the most important features of MySQL Router:

  • As mentioned, read and write split based on roles.
  • Load balancing both for reads and writes use different algorithms.
  • Configuration is stored in a configuration test file.
  • Automatically detects cluster topology by connecting and retrieving information, based on this information, the router configures itself with default rules.
  • Automatically detects failing nodes and redirects traffic accordingly.

Algorithms Used for Routing

An important thing to mention is the routing_strategy algorithms that are available, as they are assigned by default depending on the routing mode:

  • For PRIMARY mode (i.e. writer node — or nodes): uses the first-available algorithm that picks the first writer node from a list of writes and in case of failure moves to the next in the list. If the failing node comes back to life, it's automatically added to the list of servers and become PRIMARY again when cluster assign this status. When no writers are available, then write routing is stopped.
  • For read-only mode (i.e. read nodes): uses the round-robin algorithm between servers listed in the destinations variable. This mode splits read traffic between all servers in an even manner.

Additional routing_strategy algorithms:

  • next-available: similar to first-available, but in this case, a failing node is marked as crashed and can't get back into the rotation.
  • round-robin-with-fallback: same as round-robin, but it includes the ability in this case of using servers from the primary list (writers) to distribute the read traffic.

A Sample Configuration

For performance purposes, it's recommended to setup MySQL Router in the same place as the application, considering an instance per application server.

Here, you can see a sample configuration file auto-generated by --bootstrap functionality:

$ cat /etc/mysqlrouter/mysqlrouter.conf
# File automatically generated during MySQL Router bootstrap
[DEFAULT]
name=system
user=mysqlrouter
keyring_path=/var/lib/mysqlrouter/keyring
master_key_path=/etc/mysqlrouter/mysqlrouter.key
connect_timeout=30
read_timeout=30
[logger]
level = INFO
[metadata_cache:percona]
router_id=4
bootstrap_server_addresses=mysql://192.168.70.4:3306,mysql://192.168.70.3:3306,mysql://192.168.70.2:3306
user=mysql_router4_56igr8pxhz0m
metadata_cluster=percona
ttl=5
[routing:percona_default_rw]
bind_address=0.0.0.0
bind_port=6446
destinations=metadata-cache://percona/default?role=PRIMARY
routing_strategy=round-robin
protocol=classic
[routing:percona_default_ro]
bind_address=0.0.0.0
bind_port=6447
destinations=metadata-cache://percona/default?role=SECONDARY
routing_strategy=round-robin
protocol=classic
[routing:percona_default_x_rw]
bind_address=0.0.0.0
bind_port=64460
destinations=metadata-cache://percona/default?role=PRIMARY
routing_strategy=round-robin
protocol=x
[routing:percona_default_x_ro]
bind_address=0.0.0.0
bind_port=64470
destinations=metadata-cache://percona/default?role=SECONDARY
routing_strategy=round-robin
protocol=x

We are almost done now; only one post left. The final post is about our third component, MySQL Shell, so please keep reading.

Discover Tarantool's unique features such as powerful stored procedures, SQL support, smart cache, and the speed of 1 million ACID transactions on a single CPU.

Topics:
database

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}