I'm attempting to add a mysql check for haproxy in which if the service wasn't available it would return an error page just as the below config does with the http servers. I'm just not sure of the best approach to take. Basically if all the web servers go down it should return the error file, and if just the mysql server goes down it should return the error file, even if all the webservers are active. Any help is appreciated.

 frontent ft_app

# sorry page to return when worst case happens
 errorfile 503 /etc/haproxy/error.html

# detect capacity issues in production farm
 acl MAIN_not_enough_capacity nb_srv(bk_app_main) le 2
# failover traffic to backup farm
 use_backend bk_app_backup if MAIN_not_enough_capacity

 default_backend bk_app_main

backend bk_app_main
 server s11 check
 server s12 check

backend bk_app_backup
 option allbackups
 server s21 check
 server s22 check backup

 listen mysql
 mode tcp
 option mysql-check
 server mysql-1 check
  • You do realise that MySQL doesn't use HTTP, right? The web application should detect that MySQL isn't available, and then render an appropriate error page. Nothing to do with HAProxy. – womble Jun 27 '16 at 1:57
  • Edited the config to reflect the correct check. Haproxy has the ability to check mysql service status which is what I'm after. – themanwhoknowstheman Jun 27 '16 at 2:06
  • That still has nothing to do with presenting an error page to a HTTP user agent. This is not something you're going to solve in HAProxy. It needs to be solved in the web application. – womble Jun 27 '16 at 2:22
  • Easy, there, @wobble. This is a perfectly sensible request -- you're missing the big picture. The idea is to require that MySQL be healthy before taking the web servers at their word that they are also healthy. If the database is unhealthy then presume the web servers are also unhealthy and show the error page in response to requests, as though the web servers had failed their health checks. – Michael - sqlbot Jun 27 '16 at 10:50
  • Apologies, @womble. The misspelling of your nickname, above, was autocorrect -- not disrespect. – Michael - sqlbot Jun 27 '16 at 11:27

The nbsrv(<back-end>) internal state fetch can be used to evaluate whether the MySQL Server is alive, just as you are using it now to count available servers in the main group.

The simplest solution is to create a dummy back-end with no servers.

backend dead-end

Then, in the front-end config, set the following before any other use_backend statements:

use_backend dead-end if { nbsrv(mysql) lt 1 }

That's it.

Or, if you like your logic the other way...

use_backend dead-end unless { nbsrv(mysql) gt 0 }

If the number of healthy servers in the back-end called "mysql" (note that this string "mysql" is not intrinsically meaningful -- it is matching the string "mysql" that is appearing on the line listen mysql) is less than 1 (or, unless it is greater than 0), then the "dead-end" back-end will be used. And, since this back-end has no servers configured at all, it also by definition has no healthy servers, so the 503 error file would be displayed.

This seems to provide exactly the desired behavior: if the database is down, then pretend the web servers are down.

You can configure a distinct errorfile 503 inside this back-end if you want the error page to be different than the one on the front-end, which would still be used if the database were up but the web servers were down.

You could also write the use_backend and nbsrv() as two lines, with a named ACL, but I find those to be confusing to a lot of people, who try to require an ACL to match more conditions by adding lines to it -- which of course does the opposite -- it requires it essentially to match fewer conditions, since matching any single line causes the ACL to evaluate to true. And an anonymous ACL just makes a lot more sense to me, any time the condition is tested in only one place.

From a security perspective, you may want to consider whether you really intended to define mysql as a listen -- it seems like it should be a backend unless you really do want this machine to accept connections on port 3306 and relay then to the real MySQL Server.

| improve this answer | |
  • 1
    This is a great answer to a great question! Thanks! +1 – gf_ Jun 30 '16 at 6:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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