Header Shadow Image


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:

https://i1.wp.com/www.microdevsys.com/WordPressImages/MySQL-Galera-Connection-Refused-Haproxy-Red-Stats.PNG?ssl=1

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:

https://i2.wp.com/www.microdevsys.com/WordPressImages/MySQL-Galera-Connection-Refused-Haproxy-Green-Stats.PNG?ssl=1

Enjoy the Fix!


     
  Copyright © 2003 - 2013 Tom Kacperski (microdevsys.com). All rights reserved.

Creative Commons License
This work is licensed under a Creative Commons Attribution 3.0 Unported License