2 mySQL clusters in HAProxy

Question

We use HAProxy (1.5) to proxy mysql to 4 Galera Nodes. We use roundrobin and works good for High Availability and Load Balancing.

See /etc/haproxy/haproxy.cfg

global
    user haproxy
    group haproxy
defaults
    mode http
    log global
    retries 2
    timeout connect 3000ms
    timeout server 10h
    timeout client 10h
listen stats
    bind *:8404
    stats enable
    stats hide-version
    stats uri /stats
listen mysql-cluster
    bind 127.0.0.1:3306
    mode tcp
    option mysql-check user haproxy_check
    balance roundrobin
    server dbcl_01_dc1 xx.xx.xx.xx:3306 check
    server dbcl_03_dc6 1xx.xx.xx.xx:3306 check
    server dbcl_04_do xx.xx.xx.xx:3306 check
    server dbcl_05_dc4 xx.xx.xx.xx:3306 check

This works great but we have a fear of the Cluster failing us some day and we would like haproxy to roll over to another mysql server should none of the above 4 galera nodes be available. We would only want this last server being used as dooms day scenario as its data is one hour behind the production cluster and more importantly a different dataset. The idea is we automatically roll over to our non-clustered mysql data from one hour behind and keep our customers operating.

Does anybody know if this is possible with HAProxy? So First 4 Servers in roundrobin and if they are not available then choose non clustered single database server as last resort.

Answer 1

You can try something with backup to help you configure with failover

listen mysql-cluster
    bind 127.0.0.1:3306
    mode tcp
    option mysql-check user haproxy_check
    balance roundrobin
    server dbcl_01_dc1 xx.xx.xx.xx:3306 check
    server dbcl_03_dc6 xx.xx.xx.xx:3306 check
    server dbcl_04_dc2 xx.xx.xx.xx:3306 check
    server dbcl_05_dc4 xx.xx.xx.xx:3306 check
    // Solution
    server dbbk_01_dc1 xx.xx.xx.xx:3306 check backup

In this case if all the 4 servers in the cluster goes down traffic will get routed to the backup server.

However, you can also try multiple backup servers as part of the configuration

listen mysql-cluster
    bind 127.0.0.1:3306
    mode tcp
    option mysql-check user haproxy_check
    balance roundrobin
    server dbcl_01_dc1 xx.xx.xx.xx:3306 check
    server dbcl_03_dc6 xx.xx.xx.xx:3306 check
    server dbcl_04_dc2 xx.xx.xx.xx:3306 check
    server dbcl_05_dc4 xx.xx.xx.xx:3306 check
    // Solution
    server dbbk_01_dc1 xx.xx.xx.xx:3306 check backup
    server dbbk_02_dc2 xx.xx.xx.xx:3306 check backup

In the above solution HAProxy picks up first server as backup until it goes down, and as a failover it uses the second server to serve the traffic if first backup server goes down.

If there is huge traffic surge and you want multiple backups to handle all your traffic you can also setup something like this with option allbackups which routes traffic to all the backups.

There is official documentation with much more complex settings.