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.108
if 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[523]: proxy mysql_cluster has no server available!
Jan 20 16:18:18 localhost haproxy[523]: 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!
Best Answer
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.