Having HAProxy check mysql status through a xinetd script

HAProxy is able to load balance MySQL wonderfully. The main issue is how to make sure that the backend MySQL server to forward the request to is up and running (I mean not just to establish a connection to port 3306, I mean something more “complete”, 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 (i.e. mysql was not available).


The script looks like this:

# This script checks if a mysql server is healthy running on localhost. It will
# return:
# "HTTP/1.x 200 OKr" (if mysql is running smoothly)
# - OR -
# "HTTP/1.x 500 Internal Server Errorr" (else)
# The purpose of this script is make haproxy capable of monitoring mysql properly
# Author: Unai Rodriguez
# It is recommended that a low-privileged-mysql user is created to be used by
# this script. Something like this:
# mysql> GRANT SELECT on mysql.* TO 'mysqlchkusr'@'localhost' 
#     -> IDENTIFIED BY '257retfg2uysg218' WITH GRANT OPTION;
# mysql> flush privileges;



# We perform a simple query that should return a few results :-p
/usr/bin/mysql --host=$MYSQL_HOST --port=$MYSQL_PORT --user=$MYSQL_USERNAME 
	--password=$MYSQL_PASSWORD -e"show databases;" > $TMP_FILE 2> $ERR_FILE

# Check the output. If it is not empty then everything is fine and we return
# something. Else, we just do not return anything.
if [ "$(/bin/cat $TMP_FILE)" != "" ]
	# mysql is fine, return http 200
	/bin/echo -e "HTTP/1.1 200 OKrn"
	/bin/echo -e "Content-Type: Content-Type: text/plainrn"
	/bin/echo -e "rn"
	/bin/echo -e "MySQL is running.rn"
	/bin/echo -e "rn"
	# mysql is fine, return http 503
	/bin/echo -e "HTTP/1.1 503 Service Unavailablern"
	/bin/echo -e "Content-Type: Content-Type: text/plainrn"
	/bin/echo -e "rn"
	/bin/echo -e "MySQL is *down*.rn"
	/bin/echo -e "rn"

Steps on the MySQL server

First, you should create the script somewhere, and assign proper permissions:

chown nobody /opt//mysqlchk
chmod   744  /opt//mysqlchk

Then, set permissions into the mysql server:

mysql> GRANT SELECT on mysql.* TO 'mysqlchkusr'@'localhost' 
mysql> flush privileges;
mysql> exit


HTTP/1.x 200 OK

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       = # 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 stop
/etc/init.d/xinetd start


telnet localhost 9200
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.

Steps on the HAProxy server
Now, 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:

listen  MySQL
        mode    tcp
	option  httpchk
        server check port 9200 inter 12000 rise 3 fall 3

What is important?

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

HAProxy hot-reconfiguration

As of version 1.2.8, a new soft-reconfiguration mechanism has been introduced.
It is now possible to “pause” all the proxies by sending a SIGTTOU signal to
the processes. This will disable the listening socket without breaking existing
connections. After that, sending a SIGTTIN signal to those processes enables
the listening sockets again. This is very useful to try to load a new
configuration or even a new version of haproxy without breaking existing
connections. If the load succeeds, then simply send a SIGUSR1 which will make
the previous proxies exit immediately once their sessions are closed ; and if
the load fails, then simply send a SIGTTIN to restore the service immediately.
Please note that the ‘grace’ parameter is ignored for SIGTTOU, as well as for
SIGUSR1 when the process was in the pause mode. Please also note that it would
be useful to save the pidfile before starting a new instance.

The ‘-st’ and ‘-sf’ command line options are used to inform previously running
processes that a configuration is being reloaded. They will receive the SIGTTOU
signal to ask them to temporarily stop listening to the ports so that the new
process can grab them. If anything wrong happens, the new process will send
them a SIGTTIN to tell them to re-listen to the ports and continue their normal
work. Otherwise, it will either ask them to finish (-sf) their work then softly
exit, or immediately terminate (-st), breaking existing sessions. Citation.


The command to be issued to restart HAProxy gracefully would be:

haproxy -f configfile -sf

Example (added the PID location):

haproxy -f /etc/haproxy/haproxy.cfg -p /var/run/haproxy.pid -sf $(cat /var/run/haproxy.pid)


HAProxy installation under Debian Etch (compiling from sources)

HAProxy is an excellent load balancer which performs extremely well. This page explains how to install HAProxy since this is one of the recommended versions on the ”HAProxy Mailing List”:

Server response time discrepancy

Also, it has been recommended on that thread to use one of the following kernels:

  • 2.6.22
  • 2.6.25
  • 2.6.18

Installation Steps

First, install some required tools/packages:

apt-get update
apt-get install build-essential make libpcre3 libpcre3-dev

If you want to stick to one of the recommended kernels, at the time this how-to was written, Debian Etch standard apt-get repositories include the kernel 2.6.18, which could be installed (optional):

apt-get install linux-kernel-headers

Then you should reboot after, to start using this new kernel.

If you want to find out which kernel you are using, you may want to run this:

uname -rs

The output should be something like this:

Linux 2.6.18-6-686

Now, you should configure syslog daemon to listen following this document:

Configuring syslog to receive messages from the network (aka listen)

Check the HAProxy’s README file, and make sure:

To build haproxy, you will need :

  • GNU make. Neither Solaris nor OpenBSD’s make work with this makefile. However, specific Makefiles for BSD and OSX are provided.
  • GCC between 2.91 and 4.3. Others may work, but not tested.
  • GNU ld

Proceed with the compilation as follows (note that I have used TARGET, CPU and USE_PCRE. These options need to be double checked on the readme file, it is very clear):

cd /opt/
wget http://haproxy.1wt.eu/download/1.3/src/haproxy-
tar zxvf haproxy-

# Double check your options on the readme file first!!!!
# http://sysbible.org/att/HAProxy-1.3.15_README.txt
cd /opt/haproxy-
make TARGET=linux26 CPU=i686 USE_PCRE=1
make install

ln -s /usr/local/sbin/haproxy /usr/sbin/haproxy

Now you should be ready to go:


HA-Proxy version 2008/06/21
Copyright 2000-2008 Willy Tarreau

Usage : haproxy -f  [ -vdVD ] [ -n  ] [ -N  ]
        [ -p
 ] [ -m  ]
        -v displays version ; -vv shows known build options.
        -d enters debug mode ; -db only disables background mode.
        -V enters verbose mode (disables quiet mode)
        -D goes daemon ; implies -q
        -q quiet mode : don't display messages
        -c check mode : only check config file and exit
        -n sets the maximum total # of connections (2000)
        -m limits the usable amount of memory (in MB)
        -N sets the default, per-proxy maximum # of connections (2000)
        -p writes pids of all children to this file
        -de disables epoll() usage even when available
        -ds disables speculative epoll() usage even when available
        -dp disables poll() usage even when available
        -sf/-st [pid ]* finishes/terminates old pids. Must be last arguments.
haproxy -vv

HA-Proxy version 2008/06/21
Copyright 2000-2008 Willy Tarreau

Build options :
  TARGET  = linux26
  CPU     = i686
  CC      = gcc
  CFLAGS  = -O2 -march=i686 -g