HA Proxy for MySQL - Failover only

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.

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.

HA Proxy for MySQL - Failover only

Related

Recent Posts