Home / server / Questions / 352253
Accepted
user53864
Asked: 2012-01-22 07:33:48 +0800 CST

Haproxy mysql failover load balancing

  • 772

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!

mysql load-balancing haproxy ubuntu-10.04

4 Answers

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

    • 5

  2. This config does exactly what you want :)

    global
       log 127.0.0.1 local0
       maxconn 4096
       user haproxy
       group haproxy
       daemon

defaults
       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 3.3.3.3:3307
       #bind *:3307
       default_backend mysql_cluster

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


listen stats 3.3.3.3:10000
       mode http
       option httpclose
       balance roundrobin
       stats uri /
       stats realm Haproxy\ Statistics
       #stats auth user:pass
    • 2
  3. 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.

    • 1

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

    • 1