How To Use HAProxy with a Tungsten Connector Farm
erics, Posted June 5th, 2025 at 1:32:51pm
Using the Tungsten Connector with HAProxy
Tungsten Connector can be combined with an HAProxy installation to provide a high-availability entry point, which in turn routes intelligently to the underlying datasources inside the cluster.
There are three ways to monitor MySQL health in HAProxy—two are recommended and one is not (mysql-check, which floods Connector logs with failures):
check(native TCP check)
✅ RECOMMENDED
Addcheckto everyserverline so HAProxy opens a TCP handshake on a schedule. Success marks the node “up”; failure marks it “down”. Without it, HAProxy assumes the node is always reachable.- External check script (via
xinetd)
✅ RECOMMENDED
Runs custom SQL for deep health and consistency checks. mysql-check(native MySQL handshake)
❌ NOT RECOMMENDED
Sends a handshake and optional auth packet but cannot verify schema consistency and spams the logs.
See the example HAProxy configuration file below for a full haproxy.cfg file.
Additional detail is in the official HAProxy docs.
The two recommended methods are illustrated below:
Configuring HAProxy Using the Basic Native Check
✅ RECOMMENDED — no MySQL user required.
|
1 2 3 4 5 6 7 8 9 10 11 |
#--------------------------------------------------------------------- # backend #--------------------------------------------------------------------- listen connector bind *:3306 mode tcp option tcpka # keep-alive on client & server balance roundrobin server conn1 db1:3306 check inter 5s rise 1 fall 1 weight 3 maxconn 5000 server conn2 db2:3306 check inter 5s rise 1 fall 1 weight 3 maxconn 5000 server conn3 db3:3306 check inter 5s rise 1 fall 1 weight 3 maxconn 5000 |
Configuring HAProxy Using an External Check Script
✅ RECOMMENDED
|
1 2 3 4 5 6 7 8 9 10 11 12 |
#--------------------------------------------------------------------- # backend #--------------------------------------------------------------------- listen connector bind *:3306 mode tcp option tcpka balance roundrobin default-server port 9200 # HAProxy will poll /port 9200 server conn1 db1:3306 check inter 5s rise 1 fall 1 weight 3 maxconn 5000 server conn2 db2:3306 check inter 5s rise 1 fall 1 weight 3 maxconn 5000 server conn3 db3:3306 check inter 5s rise 1 fall 1 weight 3 maxconn 5000 |
Install the script on every Connector host; HAProxy polls each one on port 9200.
Step 1 — Create the MySQL health-check user
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
-- Wildcard host CREATE USER 'haproxy'@'%' IDENTIFIED WITH mysql_native_password BY 'secret'; GRANT USAGE ON *.* TO 'haproxy'@'%'; -- Bridge mode CREATE USER 'haproxy'@'{ip_of_application_host}' IDENTIFIED WITH mysql_native_password BY 'secret'; GRANT USAGE ON *.* TO 'haproxy'@'{ip_of_application_host}'; -- Proxy mode CREATE USER 'haproxy'@'{ip_of_connector_host}' IDENTIFIED WITH mysql_native_password BY 'secret'; GRANT USAGE ON *.* TO 'haproxy'@'{ip_of_connector_host}'; -- SmartScale needs REPLICATION CLIENT GRANT USAGE, REPLICATION CLIENT ON *.* TO 'haproxy'@'%'; |
Step 2 — Add credentials to /opt/continuent/tungsten/tungsten-connector/conf/user.map
|
1 |
haproxy secret cluster_name_here |
Step 3 — Deploy /opt/continuent/share/connectorchk.sh
|
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 |
#!/bin/sh # Return 200 OK when MySQL is healthy, 503 otherwise. MYSQL_HOST=$(hostname) MYSQL_PORT=3306 MYSQL_USERNAME=haproxy MYSQL_PASSWORD=secret MYSQL_OPTS="-N -q -A test" FORCE_FAIL=/dev/shm/proxyoff OUT="" return_ok() { printf 'HTTP/1.1 200 OK\r\nContent-Type: text/plain\r\n\r\nMySQL is running.\r\n' exit 0 } return_fail() { printf 'HTTP/1.1 503 Service Unavailable\r\nContent-Type: text/plain\r\n\r\nMySQL is *down*.\r\n%s\r\n' "$OUT" exit 1 } [ -f "$FORCE_FAIL" ] && { OUT="$FORCE_FAIL found"; return_fail; } mysql $MYSQL_OPTS -h"$MYSQL_HOST" -P"$MYSQL_PORT" -u"$MYSQL_USERNAME" -p"$MYSQL_PASSWORD" \ -e 'select @@hostname;' 2>&1 || return_fail return_ok |
|
1 2 3 |
chown tungsten:tungsten /opt/continuent/share/connectorchk.sh chmod 700 /opt/continuent/share/connectorchk.sh chmod +x /opt/continuent/share/connectorchk.sh |
Step 4 — Install xinetd
|
1 2 3 4 5 |
# RHEL / CentOS sudo yum -y install xinetd telnet # Debian / Ubuntu sudo apt-get install xinetd telnet |
Step 5 — Register the service
|
1 |
echo "connectorchk 9200/tcp" | sudo tee -a /etc/services |
Step 6 — Create /etc/xinetd.d/connectorchk as the root user
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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 # tighten as needed per_source = UNLIMITED } |
Step 7 — Restart and verify
|
1 2 |
sudo service xinetd restart telnet localhost 9200 |
|
1 2 3 4 5 6 7 |
HTTP/1.1 200 OK Content-Type: text/plain MySQL is running. |
Example haproxy.cfg
|
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 |
#--------------------------------------------------------------------- # 6-Node Composite-Cluster Configuration #--------------------------------------------------------------------- global log 127.0.0.1 local2 chroot /var/lib/haproxy pidfile /var/run/haproxy.pid maxconn 4000 user haproxy group haproxy daemon stats socket /var/lib/haproxy/stats # "show stat" | nc -U /var/lib/haproxy/stats defaults mode tcp option tcpka option dontlognull option redispatch retries 3 timeout queue 1m timeout connect 10s timeout client 1m timeout server 1m timeout check 10s frontend connector *:3306 default_backend cluster backend cluster balance roundrobin option tcplog # default-server port 9200 # uncomment if using external script server conn1 db1:3306 check inter 5s rise 1 fall 1 weight 6 maxconn 5000 server conn2 db2:3306 check inter 5s rise 1 fall 1 weight 6 maxconn 5000 server conn3 db3:3306 check inter 5s rise 1 fall 1 weight 6 maxconn 5000 |
Monitoring HAProxy Status
Ensure stats socket is enabled in haproxy.cfg:
|
1 2 3 |
global stats socket /var/lib/haproxy/stats ... |
(Restart HAProxy after editing haproxy.cfg.)
Check runtime stats with nc:
|
1 |
echo "show stat" | sudo nc -U /var/lib/haproxy/stats |
|
1 2 3 4 5 6 |
# pxname,svname,qcur,qmax,scur,smax,slim,stot,bin,bout,dreq,dresp,... connector,FRONTEND,,,0,1,3000,2,1261,4062,0,0,0,,,,,OPEN,... connector,conn1,0,0,0,1,5000,1,605,1915,...,UP,3,1,... connector,conn2,0,0,0,1,5000,1,656,2147,...,UP,3,1,... connector,conn3,0,0,0,0,5000,0,0,0,...,UP,3,1,... connector,BACKEND,0,0,0,1,300,2,1261,4062,...,UP,18,6,... |
You need nc installed, and the command must run with root privileges to access
the UNIX socket.
Categories: How-To's, Technology
Leave Your Comment
All fields marked with "*" are required.