4

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!

user53864
  • 1,723
  • 11
  • 37
  • 66

4 Answers4

5

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
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
Sir_Yaro
  • 21
  • 2
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.

JamesRyan
  • 8,166
  • 2
  • 25
  • 36
  • I tried this `server mysql1 192.168.1.108:3306 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 01:39
1

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

Luis Gómez
  • 11
  • 1