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.1global 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 #quietdefaults 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 3600000listen 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!
Remove the check port 9200s then your backup option should work. You seem to be mixing different examples, the mysql-check works on the standard port wheares there is another common example that uses an http check where they setup a xinetd process answering on port 9200 which runs a seperate check script.
- nginx and haproxy, do they share similar roles for load balancing?
- How to properly config HAproxy with windows terminal servers
- Is it possible to use one haproxy process to load-balance more than one protocol/port?
- Load balancing setup
- Is there a Windows equivalent of the HAProxy for virtual load balancing?