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 -e "CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';"
mysql -e "flush privileges;"
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
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:
Restart xinetd (you can watch for issues on /var/log/syslog):
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.
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
server 10x.xx.xx:3306 10x.xx.xx:3306 check port 9200 inter 12000 rise 3 fall 3
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