What’s New in HAProxy 2.2

HAProxy is one of most popular open source load balancers. It is a TCP/HTTP load balancer that can handle incoming traffic and spread the request to multiple endpoints and servers. HAProxy uses reverse proxy to forward the request to the endpoints based on the load-balancing algorithm.

HAProxy can be used as a database load balancer for database servers too, it provides load balancing on TCP layers to forward the queries to the backend database server. In this blog, we will review some of new features of HAProxy 2.2 implementation for databases.

Health Check Improvement

There are some improvements related to tcp-check-connect which means we can add a comment before we try to connect to specific ports. Before that, we need to dig up what is the purpose of defined port, within this new parameter we can add comment to give description related to the port as shown below:

listen  haproxy_10.10.10.15_5433_rw_rw

        bind *:5433

        mode tcp

        timeout client  10800s

        timeout server  10800s

  tcp-check comment “check backend xinetd database service”

        tcp-check connect port 9201

        tcp-check expect string master is running

        balance leastconn

        option tcp-check

        default-server port 9201 inter 2s downinter 5s rise 3 fall 2 slowstart 60s maxconn 64 maxqueue 128 weight 100

        server 10.10.10.15 10.10.10.15:5432 check 

        server 10.10.10.16 10.10.10.16:5432 check 

listen  haproxy_10.10.10.15_5434_ro

        bind *:5434

        mode tcp

        timeout client  10800s

        timeout server  10800s

   tcp-check comment “check backend xinetd database service”

        tcp-check connect port 9201

        tcp-check expect string is running

        balance leastconn

        option tcp-check

        default-server port 9201 inter 2s downinter 5s rise 3 fall 2 slowstart 60s maxconn 64 maxqueue 128 weight 100

        server 10.10.10.15 10.10.10.15:5432 check 

        server 10.10.10.16 10.10.10.16:5432 check 

There is another parameter that can be useful which is tcp-check set-var and tcp-check unset-var. We can define the variables and set the values, and then we can compare the values with the return expression from the backend.

Dynamic Error Handling 

We can define an error page for specific return from backend database through xinetd service. For example we want to create 2 error pages for checking the database service; the first one is HTTP 200: service is up; and the second one is HTTP 503: Service is unavailable.

We create a file 503.http for error 503 and store store it in /etc/haproxy/ using below content:

HTTP/1.1 503 Service Unavailable

Content-Type: text/html

Content-Length: 40

Connection: close

Service of database is Unavailable.

And then for error code 200.http file, which is OK using below:

HTTP/1.1 200 OK

Content-Type: text/html

Content-Length: 40

Connection: close

Service of database is Running.

We need to configure MySQL check and configure through xinetd service. We can create a bash script file with the following content to check MySQL services. The script will be put in the database server, and we need to configure xinetd daemon.

#!/bin/bash

MYSQL_HOST="localhost"

MYSQL_PORT="3306"

MYSQL_USERNAME="mysqlchkuser"

MYSQL_PASSWORD="mysqlpassword"



TMP_FILE="/tmp/mysqlchklog.out"

ERR_FILE="/tmp/mysqlchklog.err"



/usr/bin/mysql --host=$MYSQL_HOST --port=$MYSQL_PORT --user=$MYSQL_USERNAME --password=$MYSQL_PASSWORD -e"show databases;" > $TMP_FILE 2> $ERR_FILE



return_ok(){

echo -e "HTTP/1.1 503 Service Unavailablern"

echo -e "Content-Type: Content-Type: text/plainrn"

echo -e "rn"

echo -e "MySQL is *down*.rn"

echo -e "rn"

}



return_fail(){

echo -e "HTTP/1.1 200 OKrn"

echo -e "Content-Type: Content-Type: text/plainrn"

echo -e "rn"

echo -e "MySQL is running.rn"

echo -e "rn"



}



if [ "$(/bin/cat $TMP_FILE)" != "" ]

  then

    return_ok

else

    return_fail

fi

Don’t forget to create mysqlchkuser in MySQL database with SELECT privileges and give executable permissions. The xinetd daemon for mysqlcheck would be like below:

# default: on

# description: mysqlchk

service mysqlchk

{

        flags           = REUSE

        socket_type     = stream

        port            = 9300

        wait            = no

        user            = nobody

        server          = /opt/mysqlchk

        log_on_failure  += USERID

        disable         = no

        only_from       = 0.0.0.0/0 

        per_source      = UNLIMITED }

And then we need to configure the haproxy to check the backend server.

​http-errors mysql-errors

    errorfile 503 /etc/haproxy/503.http    

    errorfile 200 /etc/haproxy/200.http



listen  MySQL 10.135.2.67:3306

        mode    tcp

option  httpchk

            errorfiles mysql-errors



            http-response return status 404 default-errorfiles if { status 503 }



            http-response return status 200 default-errorfiles if { status 200 }




        server  10.10.10.11:3306 10.10.10.11:3306 check port 9200 inter 12000 rise 3 fall 3

        source  10.10.10.17

We need to map the error return from the backend to the default error files, so it will return the custom error page instead of HTTP error code from the backend.