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 a simple configuration of 2 MySQL being load balanced by HAProxy. For an unfortunate reason I need to use them in Passive\Active mode. So I thought I'd configure one DB as 'backup' and go to sleep. But I was wrong. Whenever I add the 'backup' to the server line HAProxy throws a communication link error (essentially saying 'no DB available" (with the 'backup' it works great). It just doesnt consider that server as a valid option any more...

I have tried this configuration:

listen mysql 10.0.0.109:3307

    mode tcp
    balance roundrobin
    option httpchk
    server db01 10.0.0.236:3306
    server db02 10.0.0.68:3306 backup

and also this configuration:

frontend mysql_proxy

    bind 10.0.0.109:3307
    default_backend mysql

backend mysql

mode tcp
balance roundrobin
option httpchk
server db01 10.0.0.236:3306  
server db02 10.0.0.68:3306 backup

Nothing worked!

Can anyone point me in the right direction?

Thanks

share|improve this question

2 Answers 2

what haproxy version are you using ?, if you are using version 1.4, it's support mysql health check "option mysql-check" e.g:

mode tcp
balance roundrobin
option tcplog
option mysql-check user haproxy
server svr1 svr1:3306 check inter 2s rise 2 fall 2
server svr2 svr2:3306 check backup inter 2s rise 2 fall 2

my setup, it's working fine with mysql master - master (using active standy).

share|improve this answer
    
I have the latest version (1.4.8). For some reason it just refuses to accept the 'backup' keyword. What version are you using? –  A.RG Oct 13 '11 at 17:09
    
haproxy-1.4.15, are you sure 1.4.8 ?, the latest is 1.4.18 based on haproxy.1wt.eu/#down. could you remove "option httpchk" ? –  chocripple Oct 14 '11 at 2:43

Addressing the original question as to why the example does not work there are 2 things which stand out:

  1. the check keyword is not added to the server declarations, which tells haproxy to use the health check directive on this server.
  2. the httpchk keyword is best used together with a port on the server directive to define where the HTTP health check service is running. This method assumes you've setup a service somewhere that reports the health of the master.

For example, I think something like this would work (untested)

backend mysql
  mode tcp
  balance roundrobin
  option  httpchk GET /master_status
  server db01 10.0.0.236:3306 weight 100 check port 3305 inter 1s rise 3 fall 2
  server db02 10.0.0.68:3306 weight 100 check port 3305 inter 1s rise 3 fall 2 backup

In this example, haproxy will query the /master_status url on port 3305 on each server. If the server returns a 2xx status code, the backend will be available through haproxy. Otherwise, it will be offline.

To solve the problem of the health check service, you can use the rubygem mysql_health. You would run this service on each database server that participates in the haproxy cluster.

https://github.com/osterman/mysql_health

To start the health check service, you would run something like:

/usr/bin/mysql_health --server:pid-file=/var/run/mysql_health.pid \
                      --log:file /var/log/mysql_health.log \
                      --server:daemonize \
                      --server:port 3305 \
                      --check:master \
                      --check:database mysql

There are many more command line options available. Use the --help argument for more details.

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.