HAproxy mysql failover

Question

I try to set up a HA cluster with Percona xtraDB Cluster for MySQL replication and HAproxy for load balancing.

HAproxy works really good with apache, when apache crash or if it's not responding, HAproxy switch on another node.

My problem start with MySQL, I set up also MySQL in the same HAproxy but when when MySQL is not responding, HAproxy don't switch on another node. However HAproxy sees that MySQL does not respond cause when I check in HAproxy stats MySQL server is down.

My question is, is it possible to set up two resources in HAproxy ? Can he check if apache is up and switch on another node if not AND check if MySQL is up and switch on another node if not ?

HAproxy.conf:

[...]
frontend redmine
    bind 192.168.7.149:80
    reqadd X-Forwarded-Proto:\ http
    default_backend redmine

frontend redmineS
    bind 192.168.7.149:443 ssl crt /etc/ssl/private/quipment.fr.pem
    reqadd X-Forwarded-Proto:\ https
    default_backend redmine

backend redmine 192.168.7.149:80
    redirect scheme https if !{ ssl_fc }
    mode http
    balance roundrobin
    option http-server-close
    timeout http-keep-alive 3000
    option forwardfor
    cookie PHPSESSID prefix
    cookie SRVNAME insert
    server xtra1 192.168.7.151:80 cookie x1 check
    server xtra2 192.168.7.147:80 cookie x2 check
    server xtra3 192.168.7.152:80 cookie x3 check

listen mysql-cluster 192.168.7.149:3306
    mode tcp
    balance roundrobin
    option  httpchk
    server xtra1 192.168.7.151:3306 check port 9200 inter 1000 rise 3 fall 1
    server xtra2 192.168.7.147:3306 check port 9200 inter 1000 rise 3 fall 1
    server xtra3 192.168.7.152:3306 check port 9200 inter 1000 rise 3 fall 1
[...]

Answer 1

Your HAProxy is not testing for MySQL health as on your MySQL section you are telling to do tests for http (with the line option httpchk), you should use option mysql-check so it should read as

listen mysql-cluster 192.168.7.149:3306
    mode tcp
    balance roundrobin
    option  mysql-check user a_test_user
    server xtra1 192.168.7.151:3306 check inter 1000 rise 3 fall 1
    server xtra2 192.168.7.147:3306 check inter 1000 rise 3 fall 1
    server xtra3 192.168.7.152:3306 check inter 1000 rise 3 fall 1

And you should have in your MySQL database the user a_test_user allowing connections from HAProxy.