MySQL Galera while using HAProxy prints following error: DOWN, reason: Layer4 connection problem, info: Connection refused
MySQL Galera while using HAProxy gives the following:
#
Dec 11 22:45:43 localhost haproxy[24265]: Proxy mysql-back started.
Dec 11 22:45:43 localhost haproxy[24265]: Server mysql-back/mysql01.nix.mds.xyz is DOWN, reason: Layer4 connection problem, info: "Connection refused", check duration: 0ms. 2 active and 0 backup servers left. 0 sessions active, 0 requeued, 0 remaining in queue.
Dec 11 22:45:43 localhost haproxy[24265]: Server mysql-back/mysql01.nix.mds.xyz is DOWN, reason: Layer4 connection problem, info: "Connection refused", check duration: 0ms. 2 active and 0 backup servers left. 0 sessions active, 0 requeued, 0 remaining in queue.
Dec 11 22:45:44 localhost haproxy[24271]: Server mysql-back/mysql02.nix.mds.xyz is DOWN, reason: Layer4 connection problem, info: "Connection refused", check duration: 0ms. 1 active and 0 backup servers left. 0 sessions active, 0 requeued, 0 remaining in queue.
Dec 11 22:45:44 localhost haproxy[24271]: Server mysql-back/mysql02.nix.mds.xyz is DOWN, reason: Layer4 connection problem, info: "Connection refused", check duration: 0ms. 1 active and 0 backup servers left. 0 sessions active, 0 requeued, 0 remaining in queue.
Dec 11 22:45:45 localhost haproxy[24271]: Server mysql-back/mysql03.nix.mds.xyz is DOWN, reason: Layer4 connection problem, info: "Connection refused", check duration: 0ms. 0 active and 0 backup servers left. 0 sessions active, 0 requeued, 0 remaining in queue.
Dec 11 22:45:45 localhost haproxy[24271]: backend mysql-back has no server available!
Dec 11 22:45:45 localhost haproxy[24271]: Server mysql-back/mysql03.nix.mds.xyz is DOWN, reason: Layer4 connection problem, info: "Connection refused", check duration: 0ms. 0 active and 0 backup servers left. 0 sessions active, 0 requeued, 0 remaining in queue.
Dec 11 22:45:45 localhost haproxy[24271]: backend mysql-back has no server available!
#
With the following configuration:
# cat /etc/haproxy/haproxy.cfg
global
log 127.0.0.1 local0 debug
stats socket /var/run/haproxy.sock mode 0600 level admin
# stats socket /var/lib/haproxy/stats
maxconn 4000
user haproxy
group haproxy
daemon
debug
defaults
mode tcp
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 stats
bind :9000
mode http
stats enable
stats hide-version
stats realm Haproxy\ Statistics
stats uri /haproxy-stats
stats auth admin:somepass
frontend mysql-in
mode tcp
bind mysql-c01:3306
option tcplog
default_backend mysql-back
backend mysql-back
mode tcp
option tcplog
option mysql-check user haproxy
balance roundrobin
default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
server mysql01.nix.mds.xyz mysql01.nix.mds.xyz:3306 maxconn 1024 check port 3306
server mysql02.nix.mds.xyz mysql02.nix.mds.xyz:3306 maxconn 1024 check port 3306
server mysql03.nix.mds.xyz mysql03.nix.mds.xyz:3306 maxconn 1024 check port 3306
HAProxy stats are red:
Checking using tcpdump;
tcpdump -w trace.dat -s 0 port not 22
tcpdump -r trace.dat -nnvvveXXS > trace.dat.txt
reveals these messages:
22:54:53.222232 00:50:56:86:da:36 > 00:50:56:86:b0:e5, ethertype IPv6 (0x86dd), length 94: (hlim 64, next-header TCP (6) payload length: 40) fdc8:29db:a9ed:0:250:56ff:fe86:da36.43910 > fdc8:29db:a9ed:0:250:56ff:fe86:b0e5.3306: Flags [S], cksum 0xde1a (incorrect -> 0x68ac), seq 2474721840, win 28800, options [mss 1440,sackOK,TS val 2107016514 ecr 0,nop,wscale 7], length 0
0x0000: 0050 5686 b0e5 0050 5686 da36 86dd 6000 .PV….PV..6..`.
0x0010: 0000 0028 0640 fdc8 29db a9ed 0000 0250 …(.@..)……P
0x0020: 56ff fe86 da36 fdc8 29db a9ed 0000 0250 V….6..)……P
0x0030: 56ff fe86 b0e5 ab86 0cea 9381 4230 0000 V………..B0..
0x0040: 0000 a002 7080 de1a 0000 0204 05a0 0402 ….p………..
0x0050: 080a 7d96 8542 0000 0000 0103 0307 ..}..B……..
Which indicates that HAProxy is attempting to make the connections using IPv6. Further confirmed through:
# nc -vz4 mysql03 3306
Ncat: Version 7.50 ( https://nmap.org/ncat )
Ncat: Connected to 192.168.0.114:3306.
Ncat: 0 bytes sent, 0 bytes received in 0.02 seconds.
# nc -vz6 mysql03 3306
Ncat: Version 7.50 ( https://nmap.org/ncat )
Ncat: Connection refused.
In this case, turning off IPv6 resolved the issue, since IPv6 is not configured on MySQL nor through Galera either.
cat /etc/default/grub
GRUB_TIMEOUT=5
GRUB_DISTRIBUTOR="$(sed 's, release .*$,,g' /etc/system-release)"
GRUB_DEFAULT=saved
GRUB_DISABLE_SUBMENU=true
GRUB_TERMINAL_OUTPUT="console"
GRUB_CMDLINE_LINUX="ipv6.disable=1 crashkernel=auto rd.lvm.lv=centos/root rd.lvm.lv=centos/swap rhgb quiet"
GRUB_DISABLE_RECOVERY="true"
Then running:
grub2-mkconfig -o /boot/grub2/grub.cfg
To recompile the kernel. Likewise, disable using /etc/sysctl.conf:
net.ipv6.conf.all.disable_ipv6 = 1
net.ipv6.conf.default.disable_ipv6 = 1
And run:
sysctl -p
This resulted in a few additional messages:
Dec 11 23:57:00 localhost haproxy[6635]: Server mysql-back/mysql01.nix.mds.xyz is DOWN, reason: Layer7 wrong status, code: 0, info: "Access denied for user 'haproxy'@'mysql01.nix.mds.xyz' (using password: NO)", check duration: 1ms. 2 active and 0 backup servers left. 0 sessions active, 0 requeued, 0 remaining in queue.
Dec 11 23:57:00 localhost haproxy[6635]: Server mysql-back/mysql01.nix.mds.xyz is DOWN, reason: Layer7 wrong status, code: 0, info: "Access denied for user 'haproxy'@'mysql01.nix.mds.xyz' (using password: NO)", check duration: 1ms. 2 active and 0 backup servers left. 0 sessions active, 0 requeued, 0 remaining in queue.
Dec 11 23:57:01 localhost haproxy[6640]: Server mysql-back/mysql02.nix.mds.xyz is DOWN, reason: Layer7 wrong status, code: 0, info: "Access denied for user 'haproxy'@'mysql01.nix.mds.xyz' (using password: NO)", check duration: 4ms. 1 active and 0 backup servers left. 0 sessions active, 0 requeued, 0 remaining in queue.
Dec 11 23:57:01 localhost haproxy[6640]: Server mysql-back/mysql02.nix.mds.xyz is DOWN, reason: Layer7 wrong status, code: 0, info: "Access denied for user 'haproxy'@'mysql01.nix.mds.xyz' (using password: NO)", check duration: 4ms. 1 active and 0 backup servers left. 0 sessions active, 0 requeued, 0 remaining in queue.
Dec 11 23:57:02 localhost haproxy[6640]: Server mysql-back/mysql03.nix.mds.xyz is DOWN, reason: Layer7 wrong status, code: 0, info: "Access denied for user 'haproxy'@'mysql01.nix.mds.xyz' (using password: NO)", check duration: 4ms. 0 active and 0 backup servers left. 0 sessions active, 0 requeued, 0 remaining in queue.
Dec 11 23:57:02 localhost haproxy[6640]: backend mysql-back has no server available!
Dec 11 23:57:02 localhost haproxy[6640]: Server mysql-back/mysql03.nix.mds.xyz is DOWN, reason: Layer7 wrong status, code: 0, info: "Access denied for user 'haproxy'@'mysql01.nix.mds.xyz' (using password: NO)", check duration: 4ms. 0 active and 0 backup servers left. 0 sessions active, 0 requeued, 0 remaining in queue.
Dec 11 23:57:02 localhost haproxy[6640]: backend mysql-back has no server available!
Which was temporary:
Dec 11 23:58:01 localhost haproxy[9156]: 192.168.0.104:59906 [11/Dec/2021:23:57:01.888] mysql-in mysql-back/mysql01.nix.mds.xyz 1/0/60009 126 cD 22/22/22/7/0 0/0
Dec 11 23:58:30 localhost haproxy[9156]: 192.168.0.104:59930 [11/Dec/2021:23:57:02.161] mysql-in mysql-back/mysql02.nix.mds.xyz 1/1/88075 274 cD 20/20/20/7/0 0/0
Dec 11 23:58:33 localhost haproxy[9156]: 192.168.0.104:59928 [11/Dec/2021:23:57:02.161] mysql-in mysql-back/mysql03.nix.mds.xyz 1/1/91445 200 cD 20/20/20/6/0 0/0
Status is now green:
Enjoy the Fix!