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 whatmysql
does if you givelocalhost
as the host argument. Try using-h 127.0.0.1
instead.