MySQL Load Balancing With Mylbhelper
When your app outgrows its single DB, the next logical step is attaching a slave to your current DB server to spread the read load. To do this efficiently, you will need a load balancer. If your app and growth is somewhat normal, you will at this point already have at least two front-end servers. Chances are that these are load balanced as well. So when it’s time to load balance your DB servers, you’ll already have the means to do this at hand.
In the highly likely - yet unfortunate - event that you have a load balancer without MySQL capabilities, you can always set up a generic TCP cluster (all traffic on port 3306 goes there and there). The down side of this is that there is no L7 checks - something you really want when load balancing your backend. The best you can do is L4 (is there anything accepting connections on this port?) Needless to say, there are a lot of problems that can impact your application which doesn’t make MySQL stop listen on its port. Table corruption, accidentally dropped tables, permission issues, max_connection hit, privilege problems etc.
A customer of ours was in this particular situation. They had a very decent hardware load balancer for their webservers with capacity to spare. So they ended up load balancing the mysql instances through the same device and using a piece of software I’ve written called mylbhelper.
In a nutshell, it runs as a daemon and periodically runs a custom query on the local DB server, if it fails in any way shape or form, it executes a custom script. The script which comes with mylbhelper blocks L4 access (ie. firewalls port 3306) so that the load balancer stops sending traffic to it. Of course you can write your own scripts. Once mylbhelper has successfully executed the predefined query (twice, to avoid flapping), another script runs. Obviously, the shipped script simply removes the firewall rule put in place.
And oh it’s written in C, so you’ll need libmysql in order to compile and run it. It runs on any posix compliant system and is released under the BSD license.