Haproxy for Multimaster MySQL cluster

haproxy.cfg

#---------------------------------------------------------------------
# Global settings
#---------------------------------------------------------------------
global
    # to have these messages end up in /var/log/haproxy.log you will
    # need to:
    #
    # 1) configure syslog to accept network log events.  This is done
    #    by adding the '-r' option to the SYSLOGD_OPTIONS in
    #    /etc/sysconfig/syslog
    #
    # 2) configure local2 events to go to the /var/log/haproxy.log
    #   file. A line like the following can be added to
    #   /etc/sysconfig/syslog
    #
    #    local2.*                       /var/log/haproxy.log
    #
    log         127.0.0.1 local2

    chroot      /var/lib/haproxy
    pidfile     /var/run/haproxy.pid
    maxconn     4096
    user        haproxy
    group       haproxy
    daemon

    # turn on stats unix socket
    stats socket /var/lib/haproxy/stats

#---------------------------------------------------------------------
# common defaults that all the 'listen' and 'backend' sections will
# use if not designated in their block
#---------------------------------------------------------------------
defaults
    mode                    tcp
    log                     global
    option                  tcplog
    option                  dontlognull
    # option forwardfor     except 127.0.0.0/8
    option                  redispatch
    retries                 3
    timeout http-request    10s
    timeout queue           1m
    timeout connect         1h
    timeout client          1m
    timeout server          1m
    timeout http-keep-alive 10s
    timeout check           10s
    maxconn                 2000


listen stats *:80
       mode http
       option httpclose
       option http-server-close
       balance roundrobin
       stats uri /
       stats realm Haproxy\ Statistics
       stats auth admin:admin


# option tcpka: enable the keepalive (tcp level, not app level) 
# default mode, HAProxy will work at TCP level, a full-duplex connection 
# will be established between the client and the server. 

# Based on
# http://www.mysqlperformanceblog.com/2012/06/20/percona-xtradb-cluster-reference-architecture-with-haproxy/
# https://gist.github.com/aw/1071146
# http://www.alexwilliams.ca/blog/2009/08/10/using-haproxy-for-mysql-failover-and-redundancy/
# http://www.sebastien-han.fr/blog/2012/04/08/mysql-galera-cluster-with-haproxy/
# http://mysql.wingtiplabs.com/documentation/hap225xe/fail-over-mysql-with-haproxy
# http://crishantha.com/wp/?p=1315
# https://code.google.com/p/haproxy-docs/wiki/stats_show_node
# https://code.google.com/p/haproxy-docs/wiki/PerformanceTuning

# Kernel TCP Tuning for HAProxy
# There are a lot of configuration changes that effect performance, 
# but there are (as always) some easy tweaks to get more out of your 
# server farm. Firstly, consider adding “option httpclose” to all 
# your HTTP groups. In Snapt this is called “Force HTTP Close”. 
# This will stop keepalives, but that will be to your advantage. 
# Also add “option abortonclose” – this will close aborted requests.
# Then, check your “maxconn” setting. This limits the maximum number 
# of connections HAProxy will accept (at a time), so make sure it’s 
# high enough. You can use this in groups as well as globally. 
#
# Only use what you require when choosing a balance method. Remember 
# that roundrobin is going to be much faster, so if there is no 
# requirement for a more advanced method don’t use it 
# (in performance sensitive situations).
#
# These ensure that TIME_WAIT ports either get reused or closed fast.
#net.ipv4.tcp_fin_timeout = 10
#net.ipv4.tcp_tw_recycle = 1
#net.ipv4.tcp_tw_reuse = 1
#net.ipv4.ip_local_port_range = 5000 65023
#net.ipv4.tcp_max_syn_backlog = 10240
#net.ipv4.tcp_max_tw_buckets = 400000
#net.ipv4.tcp_max_orphans = 60000
#net.ipv4.tcp_synack_retries = 3
##net.core.somaxconn = 10000

frontend mysql_cluster
       bind *:3306
       mode tcp
       option clitcpka
       timeout client 6h
       default_backend mysql_cluster


##########
# Monitors
frontend status_replication_monitor
       mode http
       option httpclose
       option http-server-close
       bind *:9200
       acl mysql-01_no_replication nbsrv(spr-prod-mysql-01_status_repl) eq 0
       acl mysql-02_no_replication nbsrv(spr-prod-mysql-02_status_repl) eq 0
       monitor-uri /dbs
       monitor fail if mysql-01_no_replication mysql-01_no_replication


frontend status_spr-prod-mysql-01_monitor
       mode http
       option httpclose
       option http-server-close
       bind *:9201
       acl mysql-01_no_port nbsrv(spr-prod-mysql-01_status_port) eq 0
       monitor-uri /dbs
       monitor fail if mysql-01_no_port

frontend status_spr-prod-mysql-02_monitor
       mode http
       option httpclose
       option http-server-close
       bind *:9202
       # If the replication is broken, then all connections will be redirected to node01
       # Well I think this is not necessary, because if a node is not synced, will be killed  
       # by the cluster .....
       acl mysql-02_no_replication nbsrv(spr-prod-mysql-02_status_repl) eq 0
       acl mysql-02_no_port nbsrv(spr-prod-mysql-02_status_port) eq 0
       monitor-uri /dbs
       monitor fail if mysql-02_no_port mysql-02_no_replication 

#######################################
# Check server mysql replication status
backend spr-prod-mysql-01_status_repl
        mode tcp
        balance roundrobin
        option srvtcpka
        option  httpchk
        server  spr-prod-mysql-01 spr-prod-mysql-01:3306 check port 9200 inter 15s rise 2 fall 2 

backend spr-prod-mysql-02_status_repl
        mode tcp
        balance roundrobin
        option srvtcpka
        option  httpchk
        server  spr-prod-mysql-02 spr-prod-mysql-02:3306 check port 9200 inter 15s rise 2 fall 2 

###################
# Check server port
backend spr-prod-mysql-01_status_port
        mode tcp
        balance roundrobin
        option srvtcpka
        option  mysql-check user haproxy
        server  spr-prod-mysql-01 spr-prod-mysql-01:3306 check inter 15s rise 2 fall 2

backend spr-prod-mysql-02_status_port
        mode tcp
        balance roundrobin
        option srvtcpka
        option  mysql-check user haproxy
        server  spr-prod-mysql-02 spr-prod-mysql-02:3306 check inter 15s rise 2 fall 2

##########
# Backends
backend mysql_cluster
       mode tcp
       option srvtcpka
       # balance roundrobin
       timeout server 6h
       balance leastconn
       option  httpchk GET /dbs
       server  spr-prod-mysql-01 spr-prod-mysql-01:3306 check port 9201 inter 3s rise 1 fall 2 addr 127.0.0.1
       server  spr-prod-mysql-02 spr-prod-mysql-02:3306 check port 9202 inter 3s rise 1 fall 2 addr 127.0.0.1

# Only for testing!
backend mysql_onenode
       disabled
       mode tcp
       option srvtcpka
       balance roundrobin
       #option  mysql-check user haproxy
       option  httpchk
       server  spr-prod-mysql-01 spr-prod-mysql-01:3306 check port 9200 inter 15s rise 3 fall 3
       server  spr-prod-mysql-02 spr-prod-mysql-02:3306 check port 9200 inter 15s rise 3 fall 3 backup