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).
Script
The script looks like this:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 |
#!/bin/bash
#
# This script checks if a mysql server is healthy running on localhost. It will
# return:
#
# "HTTP/1.x 200 OK\r" (if mysql is running smoothly)
#
# - OR -
#
# "HTTP/1.x 500 Internal Server Error\r" (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;
MYSQL_HOST="localhost"
MYSQL_PORT="3306"
MYSQL_USERNAME="mysqlchkusr"
MYSQL_PASSWORD="secret"
TMP_FILE="/tmp/mysqlchk.out"
ERR_FILE="/tmp/mysqlchk.err"
#
# 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)" != "" ]
then
# mysql is fine, return http 200
/bin/echo -e "HTTP/1.1 200 OK\r\n"
/bin/echo -e "Content-Type: Content-Type: text/plain\r\n"
/bin/echo -e "\r\n"
/bin/echo -e "MySQL is running.\r\n"
/bin/echo -e "\r\n"
else
# mysql is fine, return http 503
/bin/echo -e "HTTP/1.1 503 Service Unavailable\r\n"
/bin/echo -e "Content-Type: Content-Type: text/plain\r\n"
/bin/echo -e "\r\n"
/bin/echo -e "MySQL is *down*.\r\n"
/bin/echo -e "\r\n"
fi |
Steps on the MySQL server
First, you should create the script somewhere, and assign proper permissions:
|
1 2 |
chown nobody /opt//mysqlchk
chmod 744 /opt//mysqlchk |
Then, set permissions into the mysql server:
|
1 2 3 4 |
mysql> GRANT SELECT on mysql.* TO 'mysqlchkusr'@'localhost' \
-> IDENTIFIED BY 'secret' WITH GRANT OPTION;
mysql> flush privileges;
mysql> exit |
Test:
|
1 2 |
/opt/mysqlchk
HTTP/1.x 200 OK |
Now, configure xinetd by adding this line at the bottom of /etc/services:
|
1 |
mysqlchk 9200/tcp # mysqlchk |
Then add this file /etc/xinetd.d/mysqlchk:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
# 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):
|
1 2 |
/etc/init.d/xinetd stop
/etc/init.d/xinetd start |
Test:
|
1 2 3 4 5 6 7 8 9 10 11 |
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. |
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:
|
1 2 3 4 5 |
listen MySQL 10.135.2.67:3306
mode tcp
option httpchk
server 10.135.2.69:3306 10.135.2.69:3306 check port 9200 inter 12000 rise 3 fall 3
source 10.135.2.67 |
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