Monday, June 19

How To Use HAProxy For MySQL Load Balancing

HAProxy is an open source software which can load balance HTTP and TCP servers. All your MySQL servers have to be configured to perform Master-Master replication as load balancing involves both reading and writing to all the backends.

HAproxy over MySQL


Prepare MySQL Servers
We need to prepare the MySQL servers by creating one additional user for HAProxy. This user will be used by HAProxy to check the status of a server and health of server.

mysqlchk

We need to create a script which detects the MySQL replication role on the database node as per below:
  • if master (SHOW SLAVE HOSTS > 1 AND read_only = OFF),
  • return 'MySQL master is running.'
  • if slave (Slave_IO_Running = Yes AND Slave_SQL_Running = Yes AND (Seconds_Behind_Master = 0 OR Seconds_Behind_Master < SLAVE_LAG_LIMIT))
  • Set limit of SLAVE_LAG_LIMIT which will be bear able for your environment.
  • return 'MySQL slave is running. (slave lag: 0)'
  • else
  • return 'MySQL is *down*'

How to use

  • Create a script in any path like I created at '/opt/mysqlcheck' and make it executable. 
#!/bin/bash
SLAVE_LAG_LIMIT=5
MYSQL_HOST="127.0.0.1"
MYSQL_PORT="3306"
MYSQL_USERNAME='USERNAME'
MYSQL_PASSWORD='PASSWORD'
MYSQL_BIN='/usr/bin/mysql'
MYSQL_OPTS="-q -A --connect-timeout=10"
TMP_FILE="/tmp/mysqlchk.$$.out"
ERR_FILE="/tmp/mysqlchk.$$.err"
FORCE_FAIL="/tmp/proxyoff"

preflight_check()
{
    for I in "$TMP_FILE" "$ERR_FILE"; do
        if [ -f "$I" ]; then
            if [ ! -w $I ]; then
                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 "Cannot write to $I\r\n"
                echo -e "\r\n"
                exit 1
            fi
        fi
    done
}

return_ok()
{
    echo -e "HTTP/1.1 200 OK\r\n"
    echo -e "Content-Type: text/html\r\n"
    echo -e "Content-Length: 43\r\n"
    echo -e "\r\n"
    if [ $role == "master" ]; then
        echo -e "<html><body>MySQL master is running.</body></html>\r\n"
    elif [ $role == "slave" ]; then
        echo -e "<html><body>MySQL slave is running. (Slave lag: $SLAVE_LAG)</body></html>\r\n"
    else
        echo -e "<html><body>MySQL is running.</body></html>\r\n"
    fi
    echo -e "\r\n"
  #  rm $ERR_FILE $TMP_FILE
    exit 0
}
return_fail()
{
    echo -e "HTTP/1.1 503 Service Unavailable\r\n"
    echo -e "Content-Type: text/html\r\n"
    echo -e "Content-Length: 42\r\n"
    echo -e "\r\n"
    echo -e "<html><body>MySQL is *down*.</body></html>\r\n"
    echo -e "\r\n"
    exit 1
}

preflight_check

if [ -f "$FORCE_FAIL" ]; then
        echo "$FORCE_FAIL found" > $ERR_FILE
        return_fail
fi

CMDLINE="$MYSQL_BIN $MYSQL_OPTS --host=$MYSQL_HOST --port=$MYSQL_PORT --user=$MYSQL_USERNAME --password=$MYSQL_PASSWORD -e"
SLAVE_IO=$(${CMDLINE} 'SHOW REPLICA STATUS' --vertical 2>/dev/null | grep Replica_IO_Running |  tail -1 | awk {'print $2'})
SLAVE_SQL=$(${CMDLINE} 'SHOW REPLICA STATUS' --vertical 2>/dev/null | grep Replica_SQL_Running | head -1 | awk {'print $2'})
echo $CMDLINE

if [[ "${SLAVE_IO}" == "Yes" ]] && [[ "${SLAVE_SQL}" == "Yes" ]]; then
    role='slave'
    SLAVE_LAG=$(${CMDLINE} 'SHOW REPLICA STATUS' --vertical 2>/dev/null | grep Seconds_Behind_Source | tail -1 | awk {'print $2'})
    if [[ $SLAVE_LAG = 0 ]]; then
        return_ok
    elif [ $SLAVE_LAG -lt $SLAVE_LAG_LIMIT ] ; then
        return_ok
    fi
else
    role='master'
    READ_ONLY=$($CMDLINE 'SHOW GLOBAL VARIABLES LIKE "read_only"' --vertical 2>/dev/null | tail -1 | awk {'print $2'})
echo "$READ_ONLY"
    [[ "${READ_ONLY}" == "OFF" ]] && return_ok
fi

return_fail
  • Create a service in /etc/xinetd.d/ and accordingly change /etc/service with port number and service name.
service mysqlchk
{
        flags           = REUSE
        socket_type     = stream
        port            = 9300
        wait            = no
        server          = /root/mysqlchk
        user            =  mysql
        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 # Recently added (May 20, 2010)
}
  • restart xinetd service 
  • Install HAProxy on the your selected node.
  • Update require information (SLAVE_LAG_LIMIT is in seconds):
  • SLAVE_LAG_LIMIT=5
  • MYSQL_HOST="localhost"
  • MYSQL_PORT="3306"
  • MYSQL_USERNAME='root'
  • MYSQL_PASSWORD='password'
  • You are good to go. Now ensure in HAProxy you have 2 listeners (3307 for write, 3308 for read) and use option tcp-check & tcp-check expect to distinguish master/slave similar to example below:

  • listen  haproxy_aa,aa,aa,aa_3307_write
  •         bind *:3307
  •         mode tcp
  •         timeout client  10800s
  •         timeout server  10800s
  •         balance leastconn
  •         option tcp-check
  •         tcp-check expect string MySQL\ master
  •         option allbackups
  •         default-server port 9200 inter 2s downinter 5s rise 3 fall 2 slowstart 60s maxconn 64 maxqueue 128 weight 100
  •         server xx.xx.xx.xx xx.xx.xx.xx:3306 check
  •         server yy.yy.yy.yy yy.yy.yy.yy:3306 check
  •         server zz.zz.zz.zz zz.zz.zz.zz:3306 check

  • listen  haproxy_aa,aa,aa,aa_3308_read
  •         bind *:3308
  •         mode tcp
  •         timeout client  10800s
  •         timeout server  10800s
  •         balance leastconn
  •         option tcp-check
  •         tcp-check expect string is\ running.
  •         option allbackups
  •         default-server port 9200 inter 2s downinter 5s rise 3 fall 2 slowstart 60s maxconn 64 maxqueue 128 weight 100
  •         server xx.xx.xx.xx xx.xx.xx.xx:3306 check
  •         server yy.yy.yy.yy yy.yy.yy.yy:3306 check
  •         server zz.zz.zz.zz zz.zz.zz.zz:3306 check
Posted by Tech blogger
Labels: , , , , , ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)