Easy Load-balancing and High-availability using MySQL Router

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

High-availability group with a primary and two secondaries
High-availability Group

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.

Routing connections to the primary of a high-availabilty group
Routing connections to the primary

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:

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:

 

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.

About mats.kindahl@oracle.com

Mats is team lead and head architect for the MySQL High-Availability Team, which maintain and release MySQL Fabric. Mats has been working for MySQL since 2004 and has implemented a number of server features, mainly in replication. Prior to joining MySQL, Mats worked with implementing C and C++ compilers and as a researcher developing algorithms for verification of distributed systems.

4 thoughts on “Easy Load-balancing and High-availability using MySQL Router

  1. If on our read servers we also do a small write just to record that a “transaction’ was made which we roll-up onto a backend server nightly can we still use the read-only mode style round robin

    1. The current incarnation of the router does not really control what kind of queries you send over the connection, the mode is just a description of the intentions. A better description of the modes are probably just round-robin (for read-only) and fail-over (for read-write).

      Something that we would like to do is to separate these concept and ensure that, for example, read-only queries cannot write by accident, but then it is necessary to separate the mode (what the connection does) from the distribution policy (how the connections are distributed).

  2. What if your router server is down? Should you have multiple router servers, and if so how would you then connect?

Leave a Reply

Your email address will not be published. Required fields are marked *

Please enter. * Time limit is exhausted. Please reload CAPTCHA.