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. 

Allow access to a host with a dynamic IP in MySQL

So I was working with the same client with the issue I described here and was setting up their MySQL.

Here's the thing though: When MySQL tries to resolve hostnames, its performance drops dramatically. What I would ideally like to do is keep the skip-name-resolve parameter in the MySQL configuration file, while avoiding allowing connections from everyone else to my client's database.

One way would of course be through iptables. One might say that since iptables allows only a specific set of addresses, then it would be OK to actually have a user like 'root'@'%'.

Honestly, it would probably be a secure scenario in most cases, but you never know. Someone might accidentally flush the iptables rules, allow access to all on port 3306, you get the picture. It's a client. And they're devs. And they'll have root access afterwards. The horror. The horror.

So what do we do? Simple. Let's change our script a bit.

Here we assume that the user we want to allow access to is root logging in from www.example.com with password "root_password". We also have a rule that allows root from 1.2.3.4 we want to keep. What we want is to check every so often if the IP address of www.example.com has changed, and if so, drop that user and create another one that will be able to connect from the new IP address.  

[root@server ~]# mysql -e 'select host,user from user' mysql
+----------------+----------+
| host           | user     |
+----------------+----------+
| 127.0.0.1      | usera    |
| 127.0.0.1      | root     |
| 127.0.0.1      | userb    |
| 1.2.3.4        | root     |
| 5.6.7.8        | root     |
+----------------+----------+

[root@server ~]# vi /root/nslookup.pl
#!/usr/bin/perl

use strict;
use warnings;
use DBI();

my $result_raw=`dig www.example.com`;
my @result=split(/\n/, $result_raw);
my $IP='0.0.0.0';
foreach my $line (@result)
{
   if ($line =~ /.*www.example.com.*/)
   {
      $IP = $line;
   }
}
chomp $IP;
$IP=~ s/(.*\s+)(\d.*)/$2/;
if ($IP =~ /(\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3})/ and $IP ne '0.0.0.0')
{
   system("/sbin/iptables -D INPUT 3");
   system("/sbin/iptables -I INPUT 3 -s $IP -j ACCEPT");
}
else
{
   exit 2;
}

my $users=`mysql -e 'select host,user from user' mysql`;
my $OLD_IP='0.0.0.0';
@result=split(/\n/, $users);
foreach my $line (@result)
{
     if ($line =~ /.*(\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}).*root.*/)
     {
        if (($line =~ /.*localhost.*/) || ($line =~ /.*127\.0\.0\.1.*/) || ($line =~ /.*1\.2\.3\.4.*/))
        {
            #print "skipping\n";
        }
        else
        {
            $OLD_IP = $line;
        }
     }
}
$OLD_IP =~ s/^((\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}))(.*)/$1/;
chomp $OLD_IP;
if ($OLD_IP =~ /(\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3})/ and $OLD_IP ne '0.0.0.0')
{
   if ($OLD_IP ne $IP)
   {
      my $dbh = DBI->connect("DBI:mysql:database=mysql;host=127.0.0.1", "root", "root_password", {'RaiseError' => 1});
      my $query="DROP USER 'root'\@'$OLD_IP'";
      $dbh->do($query);
      $query="CREATE USER 'root'\@'$IP' IDENTIFIED BY 'root_password'";
      $dbh->do($query);
      $query="GRANT ALL PRIVILEGES ON *.* TO 'root'\@'$IP'";
      $dbh->do($query);
      $dbh->disconnect();
   }
}
[root@server ~]# crontab -e
*/5 * * * * /usr/bin/perl /root/nslookup.pl > /dev/null 2>&1

With this script and using crontab we a) check every 5 minutes the IP of www.example.com; b) we replace the old iptables rule with a new one if needed; c) we scan through the users that are allowed to access MySQL, ignore everyone who is not root and addresses such as "localhost" (this shouldn't really exist if skip-name-resolve is on, but it's ok to have it in the script just in case), "127.0.0.1", and "1.2.3.4" (the rule that allows root from 1.2.3.4 we wanted to keep) and in case the IP has changed replace that user with a user from the new IP.

So if all goes to plan with our regular expressions and our pattern matching, the user 'root'@'5.6.7.8' will be dropped and a new one will be created:

[root@server ~]# mysql -e 'select host,user from user' mysql
+----------------+----------+
| host           | user     |
+----------------+----------+
| 127.0.0.1      | usera    |
| 127.0.0.1      | root     |
| 127.0.0.1      | userb    |
| 1.2.3.4        | root     |
| 9.10.11.12     | root     |
+----------------+----------+

Note that you'll need the perl-DBI and perl-DBD-MySQL packages in order to run this script.

Fun, right? Right?... Meh, I guess not.