I have setup mysql master-master replication and now I am trying to load balance mysql servers with Haproxy.
- Load balancer: 192.168.1.5
- mysql1: 192.168.1.7
- mysql2: 192.168.1.8
The below haproxy configuration is working fine and it's rotating nodes like roundrobin.
# this config needs haproxy-1.1.28 or haproxy-1.2.1 global log 127.0.0.1 local0 log 127.0.0.1 local1 notice #log loghost local0 info maxconn 4096 #chroot /usr/share/haproxy chroot /etc/haproxy user haproxy group haproxy pidfile /var/run/haproxy.pid daemon #debug #quiet defaults log global #mode http mode tcp #option httplog option dontlognull retries 3 option redispatch maxconn 2000 #contimeout 5000 contimeout 3600000 #clitimeout 50000 clitimeout 3600000 #srvtimeout 50000 srvtimeout 3600000 listen mysql_cluster 0.0.0.0:3307 mode tcp balance roundrobin option mysql-check user root #option httpchk GET /mysqlchk/?port=3306 option tcpka server mysql1 192.168.1.107:3306 server mysql2 192.168.1.108:3306
This is NOT what I wanted.
What I want is
Active-Passive setup. Like, a configure so that it should send all the requests to
192.168.1.107 by default and failover to
192.168.1.107 does not exist. I have seen some links doing this by mentioning as backup but it didn't work for me anything.
When I tried replacing last two lines of the above configuration with,
server mysql1 192.168.1.107:3306 check port 9200 inter 12000 rise 3 fall 3 server mysql2 192.168.1.108:3306 check port 9200 inter 12000 rise 3 fall 3 backup
I am facing below error message while restarting haproxy and it's stopping itself.
Jan 20 16:18:18 localhost haproxy: proxy mysql_cluster has no server available! Jan 20 16:18:18 localhost haproxy: proxy mysql_cluster has no server available!
Anybody has any reliable working configuration of haproxy for mysql load balancing to use it on production with some haproxy stats?. I need like an active-passive configuration which I am asking above which redirect to backup node if no nodes are available. I am going to implement this on new ubuntu production server.
Any help is greatly appreciated!. Thanks!