Tungsten Clustering

Configuring HAProxy with a Check Script Via xinetd

✅ RECOMMENDED, but Obsolete

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 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

For correct operation with HAProxy, the external check script needs to be installed on all hosts running the Tungsten Connector.

This external script will respond to check calls by HAProxy to ensure the Connector is available and able to reach the database on that node.

This is typically done via the which listener configuration which executes the local check script each time the HAProxy daemon asks for status.

The check script will return the status of that Connector to HAProxy in the form of HTTP return codes.

Based on the default-server port 9200 entry in the configuration above, HAProxy will check via port 9200 on every defined backend host.

This solution will work for CONNECTION-based session IDs when using SmartScale.

note

The hostname and port numbers in the above example should be modified to match your cluster configuration.

To install the check script:

For the check to work, a mysql user with a password must be created within the cluster which the check script can use. The mysql user needs the permissions to be able to run the SQL contained within the check script:

WILDCARD IP ACCEPTABLE:
mysql> CREATE USER 'haproxy'@'%' IDENTIFIED WITH mysql_native_password BY 'secret';
mysql> GRANT USAGE ON *.* TO 'haproxy'@'%';

IP REQUIRED, BRIDGE MODE:
mysql> CREATE USER 'haproxy'@'{ip_of_application_host}' IDENTIFIED WITH mysql_native_password BY 'secret';
mysql> GRANT USAGE ON *.* TO 'haproxy'@'{ip_of_application_host}';

IP REQUIRED, PROXY MODE:
mysql> CREATE USER 'haproxy'@'{ip_of_connector_host}' IDENTIFIED WITH mysql_native_password BY 'secret';
mysql> GRANT USAGE ON *.* TO 'haproxy'@'{ip_of_connector_host}';

If you are running SmartScale the user will also need replication client privilege:

WILDCARD IP ACCEPTABLE:
mysql> GRANT USAGE, REPLICATION CLIENT ON *.* TO 'haproxy'@'%';

IP REQUIRED, BRIDGE MODE:
mysql> GRANT USAGE, REPLICATION CLIENT ON *.* TO 'haproxy'@'{ip_of_application_host}';

IP REQUIRED, PROXY MODE:
mysql> GRANT USAGE, REPLICATION CLIENT ON *.* TO 'haproxy'@'{ip_of_connector_host}';

Add the new user on each connector host by adding the following line to user.map:
```
haproxy secret cluster_name_here
```
In the above example, replace cluster_name_here with the name of the actual cluster service.

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: 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: 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.sh
shell> chmod 701 /opt/continuent/share/connectorchk.sh

Install which and add the xinetd service. On RedHat/CentOS:

shell> sudo yum -y install  xinetd telnet

On Debian/Ubuntu:

shell> sudo apt-get install xinetd telnet

Add an entry for the connector check script to /etc/services:

shell> echo "connectorchk         9200/tcp" | sudo tee -a /etc/services

Add a configuration to which by creating the file /etc/xinetd.d/connectorchk as the root user 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 which:
```
shell> sudo service xinetd restart
```
To verify that the connector check service is running via xinetd, connect to port 9200 using the telnet command:
```
shell> telnet localhost 9200
```
You should get a response similar to this:
```
HTTP/1.1 200 OK

Content-Type: text/plain

MySQL is running.
```