Using HAProxy for MySQL failover and redundancy

(Update 1 – Aug 29, 2009:) It appears this configuration wasn’t working with HAProxy version 1.3.20 due to the “option nolinger” feature. I have removed it from the configuration and can confirm it works well with HAProxy v.1.3.15 to v.1.3.20. Because of this, you’ll also notice a significant increase in TIME_WAIT sessions, as well as ip_conntrack_count increasing from ~150 to ~925.

This post summarizes my reflections on failover, redundancy, and ultimately scaling MySQL databases using load-balancing software known as HAProxy.

haproxy-01

At my current employer, we have been using HAProxy to build very simple server clusters to help clients scale their databases. It works for most people assuming their application:

  • Has an acceptable ratio of reads/writes (i.e: 100:1)
  • Can separate reads and writes at the application level

If your read/write ratio is lower, that’s when you need to look into different scaling solutions such as sharding.

I’ve designed a slightly more complex HAProxy configuration file which load-balances requests to MySQL databases. It detects failures such as broken replication and offline servers, and adjusts the availability of servers accordingly.

Each database server is running an xinetd daemon. Port 9201 is used to monitor replication and port 9200 is used to monitor mysql status. These ports are monitored by HAProxy as you will see in the configuration file below.

HAProxy backend to monitor replication

128
                    129
                    130
                    131
                    132
                    133
backend db01_replication
                            mode tcp
                            balance roundrobin
                            option tcpka
                            option httpchk
                            server db01 172.16.0.60:3306 check port 9201 inter 1s rise 1 fall 1

HAProxy backend to monitor mysql status

168
                    169
                    170
                    171
                    172
                    173
backend db01_status
                            mode tcp
                            balance roundrobin
                            option tcpka
                            option httpchk
                            server db01 172.16.0.60:3306 check port 9200 inter 1s rise 2 fall 2

I modified the mysqlchk_status.sh script found at SysBible with my own.

The mysqlchk_replication.sh script is similar to the one above, except it checks a few other variables such as Slave_IO_Running, Slave_SQL_Running and Seconds Behind Master. Success will always return a ‘200 OK’ and failures will always return a ‘503 Service Unavailable’.

servers-01My test setup

  • 2 HAProxy load-balancers in Active-Passive mode (VRRP using Keepalived)
  • 2 MySQL database servers with Master-Master replication in Active-Passive mode
  • 3 MySQL database servers with slave replication (read-only)

Failure scenarios

Based on a small set of failure scenarios, we’re able to determine how the load balancers should direct traffic. We obviously don’t want read requests from a database server who’s not replicating its master. We also don’t want to send writes to a server who’s offline. The examples below describe how HAProxy will react in those scenarios.

1. Replication breaks, lags, or stops working on DB02

haproxy-02

servers-02

  • DB01 becomes the master database server.
  • HAProxy stops sending requests to DB02 and DB05 (its slave).
  • Despite this, DB01 and DB05 are still able to receive replicated data from DB02.

2. Replication breaks, lags, or stops working on DB01

haproxy-03

servers-03

  • DB02 becomes the master database server.
  • HAProxy stops sending requests to DB01, DB03 and DB04 (its slaves).
  • Despite this, DB02, DB03 and DB04 are still able to receive replicated data from DB01.

3. Replication breaks, lags, or stops working on DB01 & DB02

haproxy-04

servers-04

  • There is no writable master database server. Service is severely degraded and action should be taken to bring one master server back into replication.
  • This is a split-brain problem. Both servers are online, but they aren’t replicating each other.
  • HAProxy only sends read requests to DB01 and DB02
  • HAProxy stops sending requests to DB03, DB04 and DB05 (the slaves).
  • Despite this, DB03 and DB04 are still able to receive replicated data from DB01.
  • Despite this, DB05 is still able to receive replicated data from DB02.

4. DB02 is offline, due to a server crash or something similar

haproxy-05

servers-05

  • DB01 becomes the master database server.
  • HAProxy stops sending requests to DB02 and DB05 (its slave).
  • DB05 can’t receive replicated data from DB02.
  • DB01 goes into backup mode which can have different settings to support more concurrency, send alerts, etc.

5. DB01 is offline, due to a server crash or something similar

haproxy-06

servers-06

  • DB02 becomes the master database server.
  • HAProxy stops sending requests to DB01, DB03 and DB04 (its slaves).
  • DB03 and DB04 can’t receive replicated data from DB01.
  • DB02 goes into backup mode which can have different settings to support more concurrency, send alerts, etc.

6. DB01 and DB02 are offline, due to a server crash or something similar

haproxy-07

servers-07

  • There is no master database server.
  • HAProxy stops sending requests to all DB servers.
  • Call your sysadmin because your website is probably down.

Download

WARNING / DISCLAIMER
This configuration has not been tested in a production environment and should be used at your own risk.

Here are the scripts and config files, or scroll down to view the code:

The xinetd config file

The mysqlchk_status script

The HAProxy config file

WARNING / DISCLAIMER
This configuration has not been tested in a production environment and should be used at your own risk.

Comments (14)

  • Simon

    August 11, 2009

    9:33 pm

    Very interesting. Using MySQL Proxy you could transparently redirects writes to master and reads to slaves. Also it could potentially do some of the availability testing logic for you.

    http://forge.mysql.com/wiki/MySQL_Proxy

  • Alex

    August 29, 2009

    12:35 am

    I’ve looked into this solution with MySQL Proxy but it doesn’t seem to be built for splitting reads/writes.

    There are more details about splitting reads/writes with MySQL Proxy here: http://forge.mysql.com/wiki/MySQL_Proxy_RW_Splitting

    Although I personally think it’s something which should be done at the application level.

  • Istvan Podor

    September 6, 2009

    4:42 am

    Simon:

    MysqlProxy is in ALPHA state and let me quote mysql.com : “MySQL Proxy is currently an Alpha release and should not be used within production environments. ”

    So good for testing or proof-of-concept, but I would never risk my production systems. But Mysqlproxy will be a huge thing when it will be released finaly :)

    Alex:

    This is a great article thanks!
    As I can see you are using multi-master set up. I think in case of a master failure its a kind of expensive to take offline all its slaves too and maybe this is where MMM (or something less friendly or advanced) could be better.
    But of course, everything is depend on the application you are using :)

    Thanks again.

  • Alex

    September 6, 2009

    12:14 pm

    Istvan: That is correct regarding MySQL Proxy. That’s exactly why I haven’t bothered installing it after reading the docs and realizing its early infancy state. I’m also very anxious to see its official stable/production release down the road.

    The goal with this configuration is to maintain available sites, as opposed to being “entirely down”. Even if the websites are accessed in a “degraded” state, i.e: read-only in split-brain situation, at least the websites are accessible and can still be server dynamic content, without the ability to modify. It definitely depends on the situation.

    In regards to moving the slave, the problem is the slave will quickly start serving stale data as the other Master receives writes. Even if you use MMM to move the slave onto the new Master, you still need to perform some kind of consistency checks on the data, which MMM does *not* provide.

  • Istvan Podor

    September 9, 2009

    5:03 pm

    Alex: You are right. Actually I already recommended this to someone. So back to basics, great article :))

    With mmm I use maatkit to verify, actually with multi-master i have “cronjob-like” daily/weekly report (replication could become an enemy.. :) ). I would try this too where I work now, but I just have no resources :(

  • HervĂ© COMMOWICK

    November 2, 2009

    7:45 am

    Hello,
    it seems it lacks the mysqlchk_replication.sh script in your article, can you provide it ?

    Thanks in advance.

    Hervé.

  • David Cheng

    November 2, 2009

    6:35 pm

    Hello,

    Could you publish mysqlchk_replication.sh script in your article?

    Thanks a lot.

    –David

  • Laurent Spagnol

    November 6, 2009

    7:41 am

    Hi,

    Thanks for your great article.
    I am working on a similary solution.

    I’ll probably use “SQL Relay” as fronted, before HAproxy.
    “SQL Relay” will be used for routing W request on “master pool” and R request on “slave pool”
    http://sqlrelay.sourceforge.net/

    client -> SQL Relay -> HAproxy -> master-”master”
    | |
    | —–> master-”slave”
    |
    |——> HAproxy -> slave1
    |
    —–> slave2
    …..

    (Replication use “master instance” of HAproxy)

    What do you think about it ?

    L.S.

  • Alex Williams

    November 12, 2009

    1:29 pm

    Laurent: I’m not familiar with SQL Relay, although it looks interesting. It seems to perform the same functionality as the “backend cluster_db_read” which I have configured, with a few extra options. I guess it really depends on your needs. I would be curious to see the performance impact of an additional layer of routing added between haproxy, the web, and db servers.

    As for the replication check script, you can try this: http://forge.mysql.com/tools/tool.php?id=6

  • macir

    November 14, 2009

    8:09 am

    Hi Alex,

    thanx for this great document. I am newbie to haproxy. I am a bir confused, in other configuration there is “listen” section, but in your config there isn’t. And my haproxy starts without errors, but don’t work. Is there something incomplete in haproxy.cfg?

    thanks again…

  • Alex

    November 23, 2009

    7:26 pm

    Hi,

    Thank you. Yes there is missing the “global” and “defaults” section of the haproxy configuration.

    The config I posted is only an example of how to perform certain complex tasks and monitoring with haproxy. The ‘listen’ section can be split in two by using a ‘frontend’ and ‘backend’. This allows multiple frontends to forward to the same backend.

    Although it’s outdated, the following document has some good standard configurations you can use to start from:

    http://haproxy.1wt.eu/download/1.3/doc/haproxy-en.txt

  • Josh Brown

    November 25, 2009

    9:21 pm

    This is a great article. I am going to use a part of this, only instead of doing replication via MySQL Master/Slave, I am going to use DRBD with Heartbeat. I think DRBD/Hearbeat is a better choice because of the automatic reconciliation after a failure. Either way, love it.

  • Alex

    November 28, 2009

    5:52 pm

    Hi Josh,

    You have to be aware that DRBD has a limit of 2 nodes so that could be problematic if you want more than that. It’s great if you can deal with one server being “unuseable” all the time because the 2nd server basically acts as a failover only, so you’re not really load-balancing requests or distributing the load.

    Finally, DRBD can be really useful when both your servers are on the same local LAN because the replication is block-level. It won’t do well over a WAN or unreliable VPN connection.

  • Josh Brown

    November 30, 2009

    6:42 pm

    Thanks Alex. Luckily both of those cases I can accommodate. I didn’t quite give my overall use case, but basically I will have two HA Proxy MySQL vips, one for writes (2 nodes DRBD cluster), and N for reads (1 for now as a MySQL replication slave using the methods you spoke of above). Then using the JDBC layer in the application we can redirect all writes to one vip and all reads to the other.