Haproxy mysql failover load balancing

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.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!

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.

Check more discussion of this question.

Related posts:

1. nginx and haproxy, do they share similar roles for load balancing?
2. How to properly config HAproxy with windows terminal servers
3. Is it possible to use one haproxy process to load-balance more than one protocol/port?
4. Load balancing setup
5. Is there a Windows equivalent of the HAProxy for virtual load balancing?