2

I try to set up a HA cluster with Percona xtraDB Cluster for MySQL replication and HAproxy for load balancing.

HAproxy works really good with apache, when apache crash or if it's not responding, HAproxy switch on another node.

My problem start with MySQL, I set up also MySQL in the same HAproxy but when when MySQL is not responding, HAproxy don't switch on another node. However HAproxy sees that MySQL does not respond cause when I check in HAproxy stats MySQL server is down.

My question is, is it possible to set up two resources in HAproxy ? Can he check if apache is up and switch on another node if not AND check if MySQL is up and switch on another node if not ?

HAproxy.conf:

[...]
frontend redmine
    bind 192.168.7.149:80
    reqadd X-Forwarded-Proto:\ http
    default_backend redmine

frontend redmineS
    bind 192.168.7.149:443 ssl crt /etc/ssl/private/quipment.fr.pem
    reqadd X-Forwarded-Proto:\ https
    default_backend redmine

backend redmine 192.168.7.149:80
    redirect scheme https if !{ ssl_fc }
    mode http
    balance roundrobin
    option http-server-close
    timeout http-keep-alive 3000
    option forwardfor
    cookie PHPSESSID prefix
    cookie SRVNAME insert
    server xtra1 192.168.7.151:80 cookie x1 check
    server xtra2 192.168.7.147:80 cookie x2 check
    server xtra3 192.168.7.152:80 cookie x3 check

listen mysql-cluster 192.168.7.149:3306
    mode tcp
    balance roundrobin
    option  httpchk
    server xtra1 192.168.7.151:3306 check port 9200 inter 1000 rise 3 fall 1
    server xtra2 192.168.7.147:3306 check port 9200 inter 1000 rise 3 fall 1
    server xtra3 192.168.7.152:3306 check port 9200 inter 1000 rise 3 fall 1
[...]
  • Can you possible post your HAProxy config? – Exsisto Jun 19 '15 at 15:07
  • Is that your complete HAproxy.conf? You seem to be missing a bind statment in the listen section for MySQL – GregL Jun 19 '15 at 15:37
  • No, I did not put the section and global, defaults and stats. I try to add bind in Listen section but but it still does not work – CharlesS Jun 19 '15 at 15:44
  • 1
    Can you also include your haproxy stats output (or hatop output if you prefer that) so we can see what haproxy thinks is going on? Also there's at least one apparent error in your configuration (you're using option httpchk to check the admin page for your MySQL server - you probably want to use option mysqlchk on the MySQL port to check the actual MySQL server) – voretaq7 Jun 19 '15 at 15:53
2

Your HAProxy is not testing for MySQL health as on your MySQL section you are telling to do tests for http (with the line option httpchk), you should use option mysql-check so it should read as

listen mysql-cluster 192.168.7.149:3306
    mode tcp
    balance roundrobin
    option  mysql-check user a_test_user
    server xtra1 192.168.7.151:3306 check inter 1000 rise 3 fall 1
    server xtra2 192.168.7.147:3306 check inter 1000 rise 3 fall 1
    server xtra3 192.168.7.152:3306 check inter 1000 rise 3 fall 1

And you should have in your MySQL database the user a_test_user allowing connections from HAProxy.

  • Ok so i create 'mysqlcheck' user with all privileges in my database. I can connect to my database with this user from HAproxy server. I changed my HAproxy.conf accordingly but it still does not work :( – CharlesS Jun 19 '15 at 16:47
  • if the user is 'mysqlcheck' does your line read as 'option mysql-check user mysqlcheck'? Also I missed the port part when copying and pasting: please remove the port 9200 from the server, otherwise the check is done against port 9200 instead of 3306. I've edited my answer to remove that port paramenter. – Pablo Martinez Jun 19 '15 at 16:54
  • Yes I had already deleted the port 9200. Yes mysql-check option looks like: 'option mysql-check user mysqlcheck' – CharlesS Jun 19 '15 at 17:01
  • it appear in HAproxy log : "Access denied for user 'mysqlcheck'@'haproxy.local' Why mysqlcheck'@'haproxy.local ? – CharlesS Jun 19 '15 at 17:03
  • Probably haproxy.local is an entry in the /etc/hosts. Remember that in MySQL you have to specify the hostname when creating the user. so in your case probably the best option is "CREATE USER 'mysqlcheck'@'%';" . If that does not work you can check which user is trying to log in MySQL (e.g as described in percona.com/blog/2012/12/28/auditing-login-attempts-in-mysql ) – Pablo Martinez Jun 19 '15 at 17:11

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.