6.10.3. Using the Connector 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.

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}');

    Warning

    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.

6.10.3.1. Configuring HA Proxy using the native MySQL Check

A practical example for deployiong 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

6.10.3.2. Configuring HA Proxy with a Check Script

A suitable MySQL check script 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.