help to configure a double failover with 3 node cluster

Hi, we are looking to configure a double failover scheme using a 3 node cluster.

Node 1, Node 2, Node 3

So node 1 will take all the traffic in normal conditions,
if node 1 fails then node 2 will take all the traffic,
if node 2 fails then node 3 will take all the traffic.

So far I can implement the first two requirements using the “Backup” option for nodes 2 and 3.

but I have found that if node 1 and 2 fails, then node 3 does not take over the traffic, and the server connection is lost. Is there any reason why node 3 does not operate?

(the HAProxy stats web page does show correctly the three nodes with 2 and 3 as backup)


below is our configuration file

global
stats socket /var/run/haproxy.sock mode 0600 level admin

log 127.0.0.1 local2 debug
#chroot /var/lib/haproxy
pidfile /var/run/haproxy.pid
maxconn 4000
daemon

defaults
mode tcp
log global
option tcplog
timeout connect 10000 # default 10 second time out if a backend is not found
timeout client 300000
timeout server 300000
maxconn 20000

For Admin GUI

listen stats
bind :8080
mode http
stats enable
stats uri /stats

listen mysql *:3306
mode tcp
balance roundrobin
option mysql-check user haproxyUser
option log-health-checks
server MySQL-NodeA 10...:3306 check
server MySQL-NodeB 10.
..:3306 check backup
server MySQL-NodeC 10...**:3306 check backup

If the node2 and node3 will fail in pretty much the same time, then the node1 will detect the situation as potential split brain and will go into non-Primary state, hence no queries will be accepted. To solve this, you should configure also an arbitrator node, maybe on the same host as HAproxy.