6.9.3. Using with HA Proxy

Tungsten Connector can be used in combination with an HA Proxy installation to provide a high-availability connection to the underlying connectors that then provide an intelligent connection to the datasources within the cluster.

A suitable configuration can be added to a basic HA Proxy 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 within HAProxy, a check script needs to be installed on all hosts running Tungsten Connector that will respond to A HAProxy connector check script needs to be installed on all of the hosts running connectors and a xinet listener setup.

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:

  1. 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 *.* to haproxy identified by 'secret';
  2. Add the new user on each connector host by adding the following line to user.map:

    haproxy secret cluster_name
  3. 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.sh
    shell> chmod 700 /opt/continuent/share/connectorchk.sh
    shell> chmod +x /opt/continuent/share/connectorchk.sh
  4. Install xinetd and add the xinetd service. On RedHat/CentOS:

    shell> yum -y install  xinetd telnet

    On Debian/Ubuntu:

    shell> apt-get install xinetd telnet
  5. Add an entry for the connector check script to /etc/services:

    shell> echo "connectorchk         9200/tcp" >> /etc/services
  6. 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
    }
  7. Now restart xinetd:

    shell> service xinetd restart
  8. Check the service is running:

    shell> telnet localhost 9200

    You should get a response similar to this:

    HTTP/1.1 200 OK
    
    Content-Type: Content-Type: text/plain
    
    
    MySQL is running.