HAProxy with 'backup' failure

Question

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?

Solution

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.

Comments

[A.RG]

I have the latest version (1.4.8). For some reason it just refuses to accept the 'backup' keyword. What version are you using?

[chocripple]

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" ?