MySQL Load Balancing Part 1

MySQL is one of the most popular databases out there. Unfortunately, when you need to have a service that is highly available, most people just use google and are happy to do a copy/paste haproxy configuration. This is bad though. Really bad.

See, haproxy has its flaws.

First of all, it does not support SSL/HTTPS. At the time of writing, the development version offers some experimental support, which means no one would use that in a production environment.

Second flaw, and more related to what we want to do: if you want to use it to check a MySQL cluster, you'll need to create a user 'haproxy' able to log in from your load balancer IP without a password. Ouch. Which brings us to flaw number three:

To balance this, haproxy needs only usage privileges (which really means no privileges at all), so it can only check if it can connect to your MySQL server and nothing else. That means that there are a number of cases where your cluster will be down, but that will go unnoticed.

Fourth flaw: option mysql-check user haproxy (the default method of checking MySQL node availability in haproxy) is buggy.

Fifth flaw: Its stats screen works but it's good if you want to see if a server is up, down, or in a transitional state but that's about it.

Sixth flaw: It doesn't have any built-in way to perform clustering with another haproxy, so you have to use corosync/pacemaker to achieve this, something that at the time of writing -and in my own experience- is buggy.

So what can we do? Well, we can use my favorite load balancer, which just so happens to be free and open-source; Zen Load Balancer!

What is great about the Zen Load Balancer is that not only can it do straightforward TCP checks to the service we need, but if there is a Nagios plugin for it, we can use that to check its health instead!

So, let's go ahead and install our MySQL libs first, our Nagios plugin needs them:

root@zen-lb:~# apt-get install libmysqlclient18

Now, install the check_mysql Nagios plugin:

root@zen-lb:~# apt get update
root@zen-lb:~# apt-get install nagios-plugins-standard
root@zen-lb~# cp /usr/lib/nagios/plugins/check_mysql /usr/local/zenloadbalancer/app/libexec/.
root@zen-lb~# chmod 755 /usr/local/zenloadbalancer/app/libexec/check_mysql 

Cleanup:

root@zen-lb1~# apt-get remove nagios nagios-plugins-standard
root@zen-lb1~# apt-get autoremove

Now, provided that you have created a user called "zen" with password "zenpassword" in your MySQL  that has access to a schema called "mydatabase", these are the steps you need to take to create your MySQL server farm on Zen Load Balancer:
a) Go to Manage->Farms and choose "Add new Farm".
b) Choose a name for your farm and Profile: TCP
c) Select the network interface/IP you'd like your farm to listen on and its port (usually 3306)
d) Go ahead and select "Edit"

Go ahead and edit the farm's parameters to suit the needs of your network. The settings and what they do are really straight forward.
As always you need to remember: Set the response timeout too soon and you'll get false positives, resulting to servers getting shut off and connections between a client and a working server cut off, which results to unhappy clients; Set it too high and your load balancer will be late shutting off traffic to dead servers, resulting your service seeming unavailable to some clients, which results to unhappy clients.
Now here's where we tell Zen to check our MySQL database using Nagios:

- Check "Use FarmGuardian to check Backend Servers".
- Populate the "Check every secs" box to how often you want Zen to query your MySQL.
- Fill in the "Command to check" box with:

check_mysql -H HOST -P 3306 --user=zen --password=zenpassword -d mydatabase

Of course, you need to change the user, password and database to whatever you have already set up."HOST" is a variable which means that this is the IP of your real servers Zen needs to check once you have defined them. Respectively, we could have used "-P PORT" instead of "-P 3306".
- Check "Enable farmguardian logs" if you want to have more control and be able to debug (logs will be at /usr/local/zenloadbalancer/logs/).

After that, go to the "Edit real IP servers configuration" section and add your real servers to your server farm.