HAproxy status showing DOWN

Question

I have setup MariaDB Galera cluster which i have tested and it working fine on the following servers: db1 192.169.0.1 db2 192.169.0.2 db3 192.169.0.3

They are all running on CentOS-6.5, and the MariaDB version is 10.0

My goal was to use HAproxy to perform load balancing. I have installed and configured HAproxy on a separate server

db4 192.168.0.4 

with no cluster setup or MariaDB installed on it, only HAproxy. The problem is, the HAproxy does not seem to be working i.e, doing the load balancing. Its starts ok and i can access it via the web interface:

http://192.168.0.4:9000/haproxy

but the status for the servers shows that they are down, even if they are actually up and running on their respective machines. The HAproxy config is as follows:

global
log 127.0.0.1 local0 notice
user haproxy
group haproxy

defaults
log global
retries 2
timeout connect 1000
timeout server 5000
timeout client 5000

listen mariadb-cluster
bind 0.0.0.0:3306
mode tcp
option mysql-check user haproxy
balance roundrobin
server db1 192.168.0.1:3306 check
server db2 192.168.0.2:3306 check
server db4 192.168.0.3:3306 check

listen webinterface
bind 0.0.0.0:9000
mode http
stats enable
stats uri /haproxy
stats realm Strictly\ Private
stats auth admin:password

db1, db2, db3 and db4 are just hostnames for each server. So when i run the command #hostname on the first server, it will display db1.

Solution

I believe that you've checked the log, where you can see connections coming in and being, or not directed to the node/server they are supposed too.

I don't know if it was a typo or not but I believe you have included db4 (the haproxy machine) as a node, right? It was supposed to be db3)

Check also if you can access port 3306 from HAproxy machine to each db node

If not please check if the haproxy user you have defined for the checking process have mysql permissions. If not, login to one of your node servers and:

mysql> GRANT USAGE ON *.* TO 'haproxy'@'%'; 

(for security purposes you should restrain the '%' to the IP address(es) where HAproxy is running)

I have a similar configuration to yours, but added options for leading with node weight and max connections per node. I also prefer using "leastcon" instead of "round robin", so please evaluate if it fits your purpose.

haproxy.cfg

global
    log         127.0.0.1 local0
    chroot      /var/lib/haproxy
    pidfile     /var/run/haproxy.pid
    maxconn     512
    user        haproxy
    group       haproxy
    daemon
    stats socket /var/lib/haproxy/stats mode 666

defaults
        log     global
        mode    http
        option  tcplog
        option  dontlognull
        retries 3
        option redispatch
        maxconn 1024
        timeout connect     3s
        timeout client      50s
        timeout server      50s
        timeout check       10s

listen website_cluster 0.0.0.0:3306
        mode tcp
        balance leastconn
        option tcpka
        option httpchk
        option mysql-check user haproxy
        stick store-request src
        stick-table type ip size 200k expire 30m
        server db1 192.168.0.1:3306 weight 40 check port 3306 inter 5000 rise 1 fall 3 maxconn 120
        server db2 192.168.0.2:3306 weight 30 check port 3306 inter 5000 rise 1 fall 3 maxconn 75
        server db3 192.168.0.3:3306 weight 30 check port 3306 inter 5000 rise 1 fall 3 maxconn 75

In MariaDB's site there is also a tutorial that might also help you getting through: here

Comments

[bruin]

I had the exactly the same issue and change from mysql-check to tcp-check does resolve the issue. But my configuration is a bit different from yours that my haproxy runs on the same node as mariadb server where the mysql client is also installed...so to me the reason is still not clear. Btw, as I tested, if I do not enable the check at all, the problem also goes away (although disable the healthy check is not a good idea in production).

[bruin]

Did more tests. For using mysql-check, my problems was that I did not set the empty password for the haproxy_check account. After setting the password, mysql-check also works fine. It's worth to note that I also encountered another issue with regarding replication of mysql.user table, that when I create the haproxy_check account from one node, it does not automatically replicated to the rest nodes. It turns out that I need to use different SQL statement for doing so, or at least verify that the account info exists the same on all nodes. As tested, the following two commands are "replicable":

[bruin]

1. create user: CREATE USER haproxy_check@'10.0.0.%'; 2. grant privileges: GRANT USAGE ON *.* TO 'haproxy_check'@'10.0.0.%' IDENTIFIED BY '' WITH GRANT OPTION; FLUSH PRIVILEGES; BTW, I am using MariaDB v10.1.19 with HAProxy v1.5.14 on CentOS 7.2.