Home / server / Questions / 416633
Accepted
Jonathan
Asked: 2012-08-12 18:34:40 +0800 CST

HA Proxy for MySQL - Failover only

  • 772

I am interested in using HA proxy as a failover/load balancer in our MySQL environment. We have a master-master + 2 slaves setup.

I would like for the master/master setup, even though they are setup for it, to only write to one master. This is to avoid issues with split-brain.

I do plan on having a separate port on HA proxy for reads and for that to be load balanaced.

Is it possible use HA proxy as a fail over only and if so, how would you set it?

Is it something like balance with roundrobin, but give one of the servers a weight of 1 and the other a weight of 0? With the idea being if the first server goes offline, the second will be used no matter its weight?

mysql

2 Answers

  1. Best Answer

    Is it possible use HA proxy as a fail over only and if so, how would you set it?

    Yes, it is possible by adding the backup option to the end of server line, something like this:

    frontend FE_mysql_writer
    bind V.I.P.A:3306
    default_backend BE_mysql_writer

backend BE_mysql_writer
    mode tcp
    balance roundrobin
    option tcpka
    option httpchk
    server mysql1 ip1:3306 weight 1 check port 9199 inter 12000 rise 3 fall 3
    server mysql2 ip2:3306 weight 1 check port 9199 inter 12000 rise 3 fall 3 backup

    Port 9199 is used to monitor MySQL status:

    /etc/services 

    mysqlchk    9199/tcp            # mysqlchk

    /etc/xinetd.d/mysqlchk

    # /etc/xinetd.d/mysqlchk
# default: on
# description: mysqlchk
service mysqlchk
{
        flags           = REUSE
        socket_type     = stream
        port            = 9199
        wait            = no
        user            = nobody
        server          = /opt/mysqlchk
        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)
                                    # Prevents the system from complaining
                                    # about having too many connections open from
                                    # the same IP. More info:
                                    # http://www.linuxfocus.org/English/November2000/article175.shtml
}

    /opt/mysqlchk 

    #!/bin/bash
# /opt/mysqlchk 
# 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 500 Internal Server Error\r" (else)
#
# The purpose of this script is make haproxy capable of monitoring mysql properly
#
# Author: Unai Rodriguez
#
# It is recommended that a low-privileged-mysql user is created to be used by
# this script. Something like this:
#
# mysql> GRANT SELECT on mysql.* TO 'mysqlchkusr'@'localhost' \
#     -> IDENTIFIED BY '257retfg2uysg218' WITH GRANT OPTION;
# mysql> flush privileges;

MYSQL_HOST="localhost"
MYSQL_PORT="3306"
MYSQL_USERNAME="mysqlchkusr"
MYSQL_PASSWORD="pa$$w0rd"

TMP_FILE="/tmp/mysqlchk.out"
ERR_FILE="/tmp/mysqlchk.err"

#
# We perform a simple query that should return a few results :-p
#
/usr/bin/mysql --host=$MYSQL_HOST --port=$MYSQL_PORT --user=$MYSQL_USERNAME \
    --password=$MYSQL_PASSWORD -e"show databases;" > $TMP_FILE 2> $ERR_FILE

#
# Check the output. If it is not empty then everything is fine and we return
# something. Else, we just do not return anything.
#
if [ "$(/bin/cat $TMP_FILE)" != "" ]
then
    # mysql is fine, return http 200
    /bin/echo -e "HTTP/1.1 200 OK\r\n"
    /bin/echo -e "Content-Type: Content-Type: text/plain\r\n"
    /bin/echo -e "\r\n"
    /bin/echo -e "MySQL is running.\r\n"
    /bin/echo -e "\r\n"
else
    # mysql is fine, return http 503
    /bin/echo -e "HTTP/1.1 503 Service Unavailable\r\n"
    /bin/echo -e "Content-Type: Content-Type: text/plain\r\n"
    /bin/echo -e "\r\n"
    /bin/echo -e "MySQL is *down*.\r\n"
    /bin/echo -e "\r\n"
fi

    Source: http://sysbible.org/2008/12/04/having-haproxy-check-mysql-status-through-a-xinetd-script/

    But wait, when a master fails, how do you point the slaves to the new master? You had better use HAProxy to load balancing the read operations and let the write operation (includes the failover) to the Percona Pacemaker Resource Agents handle.

    • 7

  2. Note that some client implementations of mysql (such as the official JDBC connector) supports this without using load balancer software. If you happen to be so lucky (or unlucky) that you have such a connector and control over it's config, there are a few benefits, namely:

    • Client side failover. One less instance where something can go wrong.

    • Increased read performance by using the failover nodes for reads.

    • Lower latency (which is mostly nitpicking, but very important in some setups)

    See examples for JDBC.

    And there is mysqlproxy which solves it more or less the same way as ha-proxy.

    • 1