Haproxy mysql failover load balancing

Question

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!

Answer 1

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.

Answer 2

You have master-master replication working, but even so I don't think using HAProxy for MySQL load balancing is the right choice.

HAProxy is great, but when operating as a TCP level load balancer it cannot have any notion of the SQL state. Behavior with long-running SQL transactions is unclear and error-prone; this is something you shouldn't be happy with.

Your master-master setup has the write capacity of a single node (since all writes need to be duplicated). Thus what you are scaling up with your setup are the reads & connections. A more common and IMHO much better setup is:

1. Hang some read-only MySQL slaves from the master-master pair.
2. Change your application code to send all writes to the master, and almost all reads (where possible) to one of the read-only slaves.
3. Be smart about establishing connections to MySQL. Preferably re-use connections from a connection pool.

Something like MySQL proxy or other connection handling middleware might also work well in your case.

"High Performance MySQL" is a really good book with practical suggestions on how to scale MySQL. If you read this book I think you'll see more clearly which designs are common and proven for your specific situation.