Tungsten Connector can be used in combination with an HAProxy installation to provide a high-availability connection to the underlying connectors that then provide an intelligent connection to the datasources within the cluster.
There are two primary ways to monitor MySQL health via HAProxy:
mysql-check - an haproxy-native test
The check consists of sending two MySQL packets, one Client Authentication packet, and one QUIT packet, to correctly close the MySQL session. HAProxy then parses the MySQL Handshake Initialisation packet and/or Error packet. It is a basic but useful test which does not produce errors or aborted connects on the server. This solution requires adding a user to MySQL:
INSERT INTO mysql.user (Host,User) values ('{ip_of_haproxy}','{username}');This method does NOT check for database presence nor database consistency. To do this, we must use an external check script (via xinetd) which is explained in the next section.
A check script - normally launched via xinetd, and allows for custom monitoring of the database health. This is the preferred method.
A practical example for deploying the HAProxy's native mysql-check
option::
INSERT INTO mysql.user (Host,User) values ('%','haproxy');
FLUSH PRIVILEGES;#--------------------------------------------------------------------- # backend #--------------------------------------------------------------------- listen connector bind *:3306 mode tcp option tcpka # enables keep-alive both on client and server side balance roundrobin option mysql-check user haproxy post-41 server conn1 db4:13306 check inter 5s rise 1 fall 1 weight 3 maxconn 5000 server conn2 db5:13306 check inter 5s rise 1 fall 1 weight 3 maxconn 5000
A suitable MySQL check script configuration can be added to a basic HAProxy installation using the following settings:
#--------------------------------------------------------------------- # backend #--------------------------------------------------------------------- listen connector bind *:3306 mode tcp option tcpka # enables keep-alive both on client and server side balance roundrobin default-server port 9200 server conn1 db4:13306 check inter 5s rise 1 fall 1 weight 3 maxconn 5000 server conn2 db5:13306 check inter 5s rise 1 fall 1 weight 3 maxconn 5000
The hostname and port numbers should be modified to match your cluster configuration.
This solution will work for
CONNECTION-based session IDs.
For correct operation through HAProxy, a check script needs to be installed on all hosts running the Tungsten Connector. This check script will respond to check calls by HAProxy to ensure the Connector is available on that node. This is done via the xinetd listener configuration to call the local check script each time the HAProxy daemon asks for status.
The connector check script will listen on port 9200 for connections from HAProxy and will return the status of the connector to HAProxy in the format of HTTP return codes.
To install the check script:
For the check to work, a mysql user must be created within the cluster which the check script can use. The user needs the permissions to be able to run the SQL in the check script:
mysql> GRANT USAGE ON *.* TO haproxy IDENTIFIED BY 'secret';If you are running smartscale the user will also need replication client privilege:
mysql> GRANT USAGE, REPLICATION CLIENT ON *.* TP haproxy IDENTIFIED BY 'secret';
Add the new user on each connector host by adding the following line
to user.map:
haproxy secret cluster_name
Create and configure a check script on each host running
Tungsten Connector. For example, create the file
/opt/continuent/share/connectorchk.sh:
#!/bin/sh
#
# 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 503 Service Unavailable\r" (else)
#
# The purpose of this script is make haproxy capable of monitoring mysql properly
#
MYSQL_HOST=`hostname`
MYSQL_PORT="3306" #Connector Port
MYSQL_USERNAME="haproxy"
MYSQL_PASSWORD="secret"
MYSQL_OPTS="-N -q -A test"
#If you create the following file, the proxy will return mysql down
#routing traffic to another host
FORCE_FAIL="/dev/shm/proxyoff"
OUT=""
return_ok()
{
echo -e "HTTP/1.1 200 OK\r\n"
echo -e "Content-Type: Content-Type: text/plain\r\n"
echo -e "\r\n"
echo -e "MySQL is running.\r\n"
echo -e "\r\n"
exit 0
}
return_fail()
{
echo -e "HTTP/1.1 503 Service Unavailable\r\n"
echo -e "Content-Type: Content-Type: text/plain\r\n"
echo -e "\r\n"
echo -e "MySQL is *down*.\r\n"
echo -e "$OUT\r\n\r\n"
exit 1
}
if [ -f "$FORCE_FAIL" ]; then
OUT="$FORCE_FAIL found"
return_fail;
fi
OUT=`mysql $MYSQL_OPTS --host=$MYSQL_HOST --port=$MYSQL_PORT --user=$MYSQL_USERNAME \
--password=$MYSQL_PASSWORD -e "select @@hostname;" 2>&1`
if [ $? -ne 0 ]; then
return_fail;
fi
return_ok;Set the permissions for the check script:
shell>chown tungsten.tungsten /opt/continuent/share/connectorchk.shshell>chmod 700 /opt/continuent/share/connectorchk.shshell>chmod +x /opt/continuent/share/connectorchk.sh
Install xinetd and add the xinetd service. On RedHat/CentOS:
shell> yum -y install xinetd telnetOn Debian/Ubuntu:
shell> apt-get install xinetd telnet
Add an entry for the connector check script to
/etc/services:
shell> echo "connectorchk 9200/tcp" >> /etc/services
Add a configuration to xinetd by creating the file
/etc/xinetd.d/connectorchk
with the following content:
# default: on
# description:connectorchk
service connectorchk
{
flags = REUSE
socket_type = stream
port = 9200
wait = no
user = tungsten
server = /opt/continuent/share/connectorchk.sh
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
}Now restart xinetd:
shell> service xinetd restartCheck the service is running:
shell> telnet localhost 9200You should get a response similar to this:
HTTP/1.1 200 OK Content-Type: Content-Type: text/plain MySQL is running.