Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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!

share|improve this question

4 Answers 4

up vote 1 down vote accepted

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.

share|improve this answer
    
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 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.

share|improve this answer
    
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 :)

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
share|improve this answer

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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