HAProxy community

Sticky tables during HAProxy reload



I work in a company in which we use HAProxy for all our loadbalancing; frontend but also between application servers and replicated database servers. It’s with the latter that we’ve encountered some issues.

Our databaseservers work with master-master replication and in between these servers and the application servers there are two haproxy loadbalancers (active/passive) using sticky tables so that the same database server is used unless it is marked as down, then it should switch to the other database server. Most of the time this doens’t cause any issues but we’ve encountered that when we reload haproxy due to a new configuration the sticky tables are reset meaning that when for any reason what so ever the backup databaseserver was marked as the active one by haproxy after the reload this will switch back to the not backup one and this can and has caused issues for us.
The reason that this can cause issues is because we use connection pools on our application servers meaning that if haproxy does a switchover it can happen that the application server still has open connection to the previously active/now passive server. This in case breaks replication because the application servers can then write to both database servers.

Below you can find our HAProxy configuration, does anybody have any tips on how we can ensure that the sticky-tables are stored when doing a haproxy reload?

Thank you for your help!


    log local2

    chroot      /var/lib/haproxy
    pidfile     /var/run/haproxy.pid
    user        haproxy
    group       haproxy

    stats socket /var/lib/haproxy/stats  mode 666 level user
    stats socket /var/lib/haproxy/stats-admin mode 600 level admin


    ca-base  /etc/pki/tls/certs
    crt-base /etc/pki/tls/certs

    tune.ssl.default-dh-param 4096

    mode                    tcp
    log                     global
    retries                 2
    timeout connect         100ms
    timeout server          3600s
    timeout client          3600s

    log-format %ci:%cp\ |\ {|}\ |\ %ft\ %b/%s\ 0/%Tw/%Tc/0/%Tt\ %ST\ %B\ %ac/%fc/%bc/%sc/%rc\ %sq/%bq\ %hs\ ""

listen mysql-cluster
    bind *:3306
    mode tcp
    option mysql-check user haproxy_check

    stick-table type ip size 100
    stick on dst
    server         db01a <ip>:3306 check inter 500 fall 5 on-marked-down shutdown-sessions
    server         db01b <ip>:3306 check inter 500 fall 5 backup on-marked-down shutdown-sessions


Configure the peer section with the local peer name, this way the new process will connect to the old process on reload and transfer the contents of the stick table:



This works, thanks!!