The haproxy loadbalancer is amazing and can be used to reverse proxy a whole bunch of HTTP connections to a webserver farm behind it.
The HTTP proxy bit has some neat features like cookie handling so that a HTTP session “sticks” to one webserver in the pool and some smart healthcheck features. haproxy can also loadbalance TCP connections and this feature can be used with MySQL to loadbal connections. Being at TCP level though, one cannot really judge the health of a MySQL server in one of the backend pools. haproxy knows that port 3306 is open and thats about it. Couple that with the fact that haproxy is not really loadbalancing MySQL connections but “distributing them”, after all haproxy does not know if a connection is a simple one row lookup, an update to thousands of rows or a complex join returning thousands of rows.
So why not leverage HTTP to act as a middleman to MySQL and perform a healthcheck based on any parameters we choose? (eg. MySQL connection threads, replication status etc)
Well, don’t tell Perl but I have been continuing my secret love-affair with Python. Recently I have had some time to get hacking again. My effort is the first version of a MySQL health checker which implements a HTTP server on the MySQL server and returns a “pass” or “fail” assessment of it’s health. It’s pretty basic right now but should allow a sysadmin to perform HTTP healthchecks against it to look for the magic words: “pass” or “fail”.
Get it from my github: https://github.com/imran-uk/mysql-health-check