The newest member of the MySQL family, the MySQL Router, was released on labs.mysql.com just a few days ago. In the two previous posts, you could see what kind of features that are currently available as well as how to compile and install the MySQL Router.
Connection Routing for High-Availability
When using MySQL for high-availability, the traditional way is to create a high-availability group with one primary server and one or more secondary servers. Normally, the primary handle all the traffic, while the secondaries are on standby, ready to take over if the primary should fail. This is a traditional setup, but there is one problem with this from an application perspective: if the application cannot reach the server, it needs to handle the fail-over and find the correct secondary to connect to. This requires special code to be added to the application, which is not always possible nor desirable, so instead of having to handle this in the application, the MySQL Router comes to the rescue.
The MySQL Router is built over a harness that handles the life-cycle of plugins ad also contain some basic features such as logging support and a platform-independent library to do operating system-specific things such as handling files and dynamic libraries.
The MySQL Router comes with a few plugins, but to route traffic, you should use the connection routing plugin (surprise!). The idea is simple: always establish connections to the primary of a high-availability group.
Connection routing is a high-performance approach to routing traffic but, as with all choices, there is a trade-off between flexibility and performance. With connection-based routing you cannot route packets differently based on the contents of the packets. Since sending connections to the primary of a high-availability group does not require any knowledge of the contents of the packets, connection routing is a perfect solution here.
To use the MySQL Router for connection routing, you need to have servers to route traffic to. Suppose that you have three servers: srv1.example.com, srv2.example.com, and srv3.example.com and you want to set up the router to send connections to srv1.example.com as long as it is up, and if it is down, you want to send traffic to srv2.example.com, etc. The configuration file for this is simply:
|
1
2
3
4
5
6
7
8
9
10
11
12
|
[DEFAULT] logging_folder = /var/log/mysqlrouter plugin_folder = /usr/lib/mysqlrouter config_folder = /etc/mysql runtime_folder = /var/run [logger] [routing:failover] bind_address = 0.0.0.0:7001 mode = read-write destinations = srv1.example.com,srv2.example.com,srv3.example.com |
The format of the configuration file follows the traditional INI file format with sections and options, but it has a few tweaks. The first two sections simply set the defaults for some common paths and load the logger plugin. The interesting section is the third section.
In this section, we configure the routing plugin and assign a section key failover. In this case, the section key is not strictly needed, but it is a good habit to assign a key to the configuration section since this makes it easier to debug as well as allowing you to have multiple configuration sections for a plugin.
By assigning a value to the bind_address you provide a port and IP address that the router should listen to. If you provide 0.0.0.0 as IP address, it means that you bind to all IP addresses the machine has.
The destinations option provides a list of destination addresses that should be used when establishing connections and the mode option defines how the list shall be used. With a value read-write, the router will establish a connections with the first server on the list that responds, which essentially give us the traditional primary-secondary handling.
Connection Routing for Load-Balancing
The above works fine when you have read-write traffic, but sometimes you might want to use a different policy for how connections are established.
Suppose that you have an application that sends solely read-only queries and that connect to the router to read the data. With the configuration in the previous section, all queries will be sent to the primary of the group, which means that the primary will handle all the read traffic as well as all the write traffic. If you have configured your system correctly, you have spare capacity on your secondaries so it would be a waste of resource to not use that extra capacity. Right? (N.B., sometimes you actually want to have the spare capacity on the secondaries, so do not take this as a carte blanche to always send traffic to secondaries.)
The router plugin also supports another mode, called read-only, which distributes the connections over the available servers in a round-robin fashion rather than sending all connections to the first server that replies. To configure this, you can add a second section to the configuration file above:
|
1
2
3
4
|
[routing:balancing] bind_address = 0.0.0.0:7002 mode = read-only destinations = srv1.example.com,srv2.example.com,srv3.example.com |
The configuration section is quite similar, but we are using a different port this time (so that clients have somewhere to connect) and we have also changed the mode to read-only, which will send the first connection to the first server, second connection to second server, etc.
Summary
As you can see, configuring the MySQL Router is quite a straightforward process and with the routing plugin you can handle both a traditional primary-secondary setup along with doing connection load balancing over the available servers.