Check the status of a mysql server in HAproxy

The following article describes how to check the status of a mysql server using a small shell script managed through the xinetd daemon to load balance MySQL

HAProxy is able to load balance MySQL The main issue is how to make sure that the backend MySQL server to forward the request to see if it is up and running
not just to establish a connection to port 3306, but a more complete check that performs a little operation against the MySQL server.

It is possible to make haproxy check the status of a mysql server using a small shell script managed through the xinetd daemon.

What this script basically does is performs a basic operation against the mysql database then returns http status 200 if the operation was successful
or http status 500 if it there was any error.

First download the script here (download script)

And assign proper permissions
chown nobody /opt//mysqlchk
chmod 744 /opt//mysqlchk | chmod +x /opt//mysqlchk 

Then, set permissions into the mysql server:
mysql> GRANT SELECT on mysql.* TO 'username'@'localhost'
-> IDENTIFIED BY 'secret' WITH GRANT OPTION;
mysql> flush privileges;
mysql> exit

or

mysql -e "CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';"
mysql -e "flush privileges;"

Test:
/opt/mysqlchk
HTTP/1.x 200 OK

Install xinetd by:
apt-get install xinetd -y

Now, configure xinetd by adding this line at the bottom of /etc/services
mysqlchk               9200/tcp                          # mysqlchk

Then add this file /etc/xinetd.d/mysqlchk

# default: on
# description: mysqlchk
service mysqlchk
{
flags = REUSE
socket_type = stream
port = 9200
wait = no
user = nobody
server = /opt/mysqlchk
log_on_failure += USERID
disable = no
only_from = 0.0.0.0/0 # recommended to put the IPs that need
# to connect exclusively (security purposes)
per_source = UNLIMITED # Recently added (May 20, 2010)
# Prevents the system from complaining
# about having too many connections open from
# the same IP. More info:
# http://www.linuxfocus.org/English/November2000/article175.shtml
}

Restart xinetd (you can watch for issues on /var/log/syslog):
/etc/init.d/xinetd restart

Test:
telnet localhost 9200
Trying 127.0.0.1...
Connected to localhost.localdomain.
Escape character is '^]'.
HTTP/1.1 200 OK

Content-Type: Content-Type: text/plain

MySQL is running.

Connection closed by foreign host.

Go back to HAProxy server
In order to make haproxy check the status of the mysql service through the xinetd-managed-script, we should add something similar to this
on the haproxy.cfg file:/etc/haproxy/haproxy.cfg

listen MySQL 10.135.2.67:3306
mode tcp
option httpchk
server 10x.xx.xx:3306 10x.xx.xx:3306 check port 9200 inter 12000 rise 3 fall 3
source 10x.xx.xx:

What is important?

option httpchk.- tells haproxy to check for full http response (i.e. http headers: 2xx OK or 5xx ERROR)
check port XXXX.- tells haproxy to check the status of the service by sending an http request on that port