Centos – HAProxy thesql configuration

centoshaproxyload balancing

I tried few times, but I cannot figure it out why my mariadb load balancer with haproxy is not working.
I have :

  • 1 HAProxy (CentOS) : 10.181.102.249
  • 1 Master (CentOS) : 10.183.241.150
  • 2 Slaves (CentOS) : 10.182.2.50 & 10.181.164.36

HAproxy conf :

global
    log         127.0.0.1 local2

    chroot      /var/lib/haproxy
    pidfile     /var/run/haproxy.pid
    maxconn     4000
    user        haproxy
    group       haproxy
    daemon

    stats socket /var/lib/haproxy/stats

defaults
    mode                    http
    log                     global
    option                  dontlognull
    option                  redispatch
    retries                 3
    timeout http-request    10s
    timeout queue           1m
    timeout connect         10s
    timeout client          1m
    timeout server          1m
    timeout http-keep-alive 10s
    timeout check           10s
    maxconn                 3000

listen mysql-read 0.0.0.0:3307;
    mode tcp
    balance roundrobin
    option tcpka
    option httpchk
    server db2 10.182.2.50:3306 check port 9201 inter 1s rise 1 fall 1
    server db3 10.181.164.36:3306 check port 9201 inter 1s rise 1 fall 1
    #server db1 10.183.241.150:3306 check port 9201 inter 1s rise 1 fall 1
    server db1 10.183.241.150:3306 backup

listen mysql-write 0.0.0.0:3306
    mode tcp
    option mysql-check user haproxy
    balance roundrobin
    server db1 10.183.241.150:3306 check

listen stats :1981
    mode http
    stats enable
    stats uri /
    stats realm Strictly\ Private
    stats auth test:test

I disabled firewalld, and be sure that iptables is not installed, as well selinux not working, to make my test first to know if this is working.

but when I am trying to connect :

mysql -hlocalhost -uuser -P 3307 -p -e "show variables like 'server_id'";

or

mysql -hlocalhost -uuser -p -e "show variables like 'server_id'";

I get :

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

From a remote server to the haproxy as well the same.

If I list port from haproxy server i get :

[root@haproxy ~]# netstat -tnlp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name    
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      21261/haproxy       
tcp        0      0 0.0.0.0:3307            0.0.0.0:*               LISTEN      21261/haproxy       
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      1294/sshd           
tcp        0      0 127.0.0.1:25            0.0.0.0:*               LISTEN      1533/master         
tcp        0      0 0.0.0.0:1981            0.0.0.0:*               LISTEN      21261/haproxy       
tcp6       0      0 :::22                   :::*                    LISTEN      1294/sshd           
tcp6       0      0 ::1:25                  :::*                    LISTEN      1533/master 

Any ideas ?

Thanks.

Best Answer

As your error message shows, the mysql command is trying to connect via a UNIX socket, not a TCP socket, and consequently ignores the port number. This is what mysql does if you give localhost as the host argument. Try using -h 127.0.0.1 instead.