haproxy-mysql-failover.cfg

# HAProxy configuration - haproxy-mysql-failover.cfg

global
    log 127.0.0.1   local0
    maxconn 4096
    user haproxy
    group haproxy
    daemon

defaults
    log global
    mode    http
    option  dontlognull
    retries 3
    option redispatch
    maxconn 2000
    contimeout  5000
    clitimeout  50000
    srvtimeout  50000
    stats enable
    option httpclose

listen admin_stats :8000
    option  httplog
        stats realm Global\ statistics
        stats uri /
        stats auth admin:pass
 
##
## FRONTEND ##
##

# Load-balanced IPs for DB writes
#
frontend db_write :3307
    mode tcp
    default_backend cluster_db_write

# Monitor DB server availability
#
frontend monitor_db01
    #
    # set db01_backup to 'up' or 'down'
    #
    bind 127.0.0.1:9301
    mode http
    #option nolinger

    acl no_repl_db01 nbsrv(db01_repl) eq 0
    acl no_repl_db02 nbsrv(db02_repl) eq 0
    acl no_db01 nbsrv(db01_status) eq 0
    acl no_db02 nbsrv(db02_status) eq 0

    monitor-uri /dbs
    monitor fail unless no_repl_db01 no_repl_db02 no_db02
    monitor fail if no_db01 no_db02

frontend monitor_db02
    #
    # set db02_backup to 'up' or 'down'
    #
    bind 127.0.0.1:9302
    mode http
    #option nolinger

    acl no_repl_db01 nbsrv(db01_repl) eq 0
    acl no_repl_db02 nbsrv(db02_repl) eq 0
    acl no_db01 nbsrv(db01_status) eq 0
    acl no_db02 nbsrv(db02_status) eq 0

    monitor-uri /dbs
    monitor fail unless no_repl_db01 no_repl_db02 no_db01
    monitor fail if no_db01 no_db02

frontend monitor_splitbrain
    #
    # set db01_splitbrain and db02_splitbrain to 'up'
    #
    bind 127.0.0.1:9300
    mode http
    #option nolinger

    acl no_repl01 nbsrv(db01_repl) eq 0
    acl no_repl02 nbsrv(db02_repl) eq 0
    acl db01 nbsrv(db01_status) eq 1
    acl db02 nbsrv(db02_status) eq 1

    monitor-uri /dbs
    monitor fail unless no_repl01 no_repl02 db01 db02

##
## BACKEND ##
##

# Check every DB server replication status
#   - perform an http check on port 9201 (replication status)
#   - set to 'down' if response is '503 Service Unavailable'
#   - set to 'up' if response is '200 OK'
#
backend db01_repl
    mode tcp
    balance roundrobin
    option tcpka
    #option httpchk HEAD / HTTP/1.0
    option httpchk HEAD /repl HTTP/1.0
    server db01 EC2.DB1.COM:3306 check port 9200 inter 1s rise 2 fall 3

backend db02_repl
    mode tcp
    balance roundrobin
    option tcpka
    option httpchk HEAD /repl HTTP/1.0
    server db02 EC2.DB2.COM:3306 check port 9200 inter 1s rise 2 fall 3

# Check Master DB server mysql status
#   - perform an http check on port 9201 (mysql status)
#   - set to 'down' if response is '503 Service Unavailable'
#   - set to 'up' if response is '200 OK'
#
backend db01_status
    mode tcp
    balance roundrobin
    option tcpka
    #option httpchk HEAD / HTTP/1.0
    option httpchk HEAD /status HTTP/1.0
    server db01 EC2.DB1.COM:3306 check port 9200 inter 1s rise 2 fall 3

backend db02_status
    mode tcp
    balance roundrobin
    option tcpka
    option httpchk HEAD /status HTTP/1.0
    server db02 EC2.DB2.COM:3306 check port 9200 inter 1s rise 2 fall 3

# DB write cluster

#   Failure scenarios:
#   - replication 'up' on db01 & db02   = writes to db01
#   - replication 'down' on db02        = writes to db01
#   - replication 'down' on db01        = writes to db02
#   - replication 'down' on db01 & db02 = go nowhere, split-brain, cluster FAIL!
#   - mysql 'down' on db02              = writes to db01_backup
#   - mysql 'down' on db01              = writes to db02_backup
#   - mysql 'down' on db01 & db02       = go nowhere, cluster FAIL!
#
backend cluster_db_write
    #
    # - max 1 db server available at all times
    # - db01 is preferred (top of list)
    # - db_backups set their 'up' or 'down' based on results from monitor_dbs
    #
    mode    tcp
    option  tcpka
    no option  allbackups
    balance roundrobin
    option  httpchk GET /dbs
    server  db01 EC2.DB1.COM:3306 weight 1 check port 9200 inter 1s rise 2 fall 1
    server  db02 EC2.DB2.COM:3306 weight 1 check port 9200 inter 1s rise 2 fall 1 backup
    server  db01_backup EC2.DB1.COM:3306 weight 1 check port 9301 inter 1s rise 2 fall 2 addr 127.0.0.1 backup
    server  db02_backup EC2.DB2.COM:3306 weight 1 check port 9302 inter 1s rise 2 fall 2 addr 127.0.0.1 backup
    server  db01_splitbrain EC2.DB1.COM:3306 weight 1 check port 9300 inter 1s rise 1 fall 2 addr 127.0.0.1 backup
    server  db02_splitbrain EC2.DB2.COM:3306 weight 1 check port 9300 inter 1s rise 1 fall 2 addr 127.0.0.1 backup