I have a database cluster of 3 nodes using Percona XtraDB. The three nodes are configured on three different systems. I have used HAProxy load balancer to pass requests to these nodes. Two of the 3 nodes are configured as backup in HAProxy. When I fire a request to the load balancer connection URL, I can see the request go to node A by default. If node A is down and I request a new database connection, I see the request being routed to node B. This is as per the desired design. However, if a connection request is sent to HAProxy using a Java program (jdbc URL), the request is routed to node A, after serving a few requests if node A goes down, I wish node B/ node C to serve the request. In the current scenario I see "Connection Failed". Is there any configuration which will ensure that in case of failure of a node, the database connection will not fail and future requests will be routed to the next available node? My current HAProxy configuration file is as follows:
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 <ip-address>:3306 check
server MySQL-NodeB <ip-address>:3306 check backup
server MySQL-NodeC <ip-address>:3306 check backup
