I have setup mysql master-master replication and now I am trying to load balance mysql servers with Haproxy.

  • Load balancer:
  • mysql1:
  • mysql2:

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

    log   local0
    log   local1 notice
    #log loghost    local0 info
    maxconn 4096
    #chroot /usr/share/haproxy
    chroot /etc/haproxy
    user haproxy
    group haproxy
    pidfile /var/run/haproxy.pid

    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
    mode tcp
    balance roundrobin
    option mysql-check user root
    #option httpchk GET /mysqlchk/?port=3306
    option tcpka
    server mysql1 
    server mysql2

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 by default and failover to if 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 check port 9200 inter 12000 rise 3 fall 3
server mysql2 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.

  • I tried this server mysql1 inter 12000 rise 3 fall 3 backup and haproxy accepted and restarted fine. But when I checked stop primary mysql server it could not redirect to backup and I'm facing ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0 – user53864 Jan 23 '12 at 1:39

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.

  • Yes, I have already tried MySQL Proxy which just rotates nodes like roundrobin but when I checked haproxy, it seems to be redirecting and connecting faster than mysql proxy. – user53864 Jan 22 '12 at 13:23
  • 1
    Yes, as you said I do have slaves for each master and I didn't mention here thinking not relevant. I am ready to use MySQL Proxy(said to be still in Beta) but my problem is configuration part to setup as active/passive. If anybody paste the configuration here, that would really help a lot!. Thanks for great reply! – user53864 Jan 22 '12 at 13:28

This config does exactly what you want :)

       log local0
       maxconn 4096
       user haproxy
       group haproxy

       log global
       mode tcp
       option tcplog
       option dontlognull
       retries 3
       option redispatch
       maxconn 2000
       contimeout 4000
       clitimeout 50000
       srvtimeout 30000
       stats enable
       stats scope .

frontend mysql_cluster
       #bind *:3307
       default_backend mysql_cluster

backend mysql_cluster
       mode tcp
       option mysql-check
       balance roundrobin
       server db01_1.1.1.1      weight 1        check port 3306
       server db02_2.2.2.2      weight 100      check port 3306 backup

listen stats
       mode http
       option httpclose
       balance roundrobin
       stats uri /
       stats realm Haproxy\ Statistics
       #stats auth user:pass
  • lomg running queries will still suffer the "Lost connection to MySQL server during query" problem with haproxy. – Glenn Plas Feb 18 '15 at 12:57
  • How does HAProxy detect if its a Read or a Write call and route to Master or slave accordingly? – Bijay Rungta Nov 18 '15 at 17:19

For a true master-master MySQL cluster in combination with haproxy, try using codership Galera or percona XtraDB Cluster.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.