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://www.microdevsys.com/WordPressImages/MySQL-Galera-Connection-Refused-Haproxy-Red-Stats.PNG

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://www.microdevsys.com/WordPressImages/MySQL-Galera-Connection-Refused-Haproxy-Green-Stats.PNG

Enjoy the Fix!

Subscribe
Notify of
0 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments

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

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

 

0
Would love your thoughts, please comment.x
()
x
The IT Development and Technology Mini Vault | MicroDevSys.com
Privacy Overview

This website uses cookies so that we can provide you with the best user experience possible. Cookie information is stored in your browser and performs functions such as recognising you when you return to our website and helping our team to understand which sections of the website you find most interesting and useful.