Testing haproxy failed

jalilimhmd · December 30, 2019, 6:43am

Hi,
I configured 3 servers to act as proxy for mysql galera cluster.
And this is my haproxy config file on 3 servers.

global
    log 127.0.0.1   local0 notice
    user haproxy
    group haproxy

defaults
    log     global
    retries 3
    timeout connect 5000
    timeout client  50000
    timeout server  50000

# MySQL Load Balance Pool
listen mysql-cluster
    bind 0.0.0.0:3306
    mode tcp
    option mysql-check user haproxy_check
    balance roundrobin
    server st-cntrl 10.0.0.101:3306 check weight 1
    server st-ctl02 10.0.0.102:3306 check weight 1
    server st-ctl03 10.0.0.103:3306 check weight 1

The problem is when I test haproxy with command below, I get handshake error.

mysql -h 127.0.0.1 -u haproxy_root -p -e "SHOW DATABASES"

error:

ERROR 2013 (HY000): Lost connection to MySQL server at 'handshake: reading inital communication packet', system error: 11

BTW mysql and haproxy and keepalived are on same servers.
Both “haproxy_root” and “haproxy_check” are created in mysql and grant permission by commands below.

mysql -u root -p -e "GRANT ALL PRIVILEGES ON *.* TO 'haproxy_root'@'st-XXX'; FLUSH PRIVILEGES"

mysql -u root -p -e "GRANT ALL ON *.* to 'haproxy_check'@'st-XXX';"

What should I do to fix the issue.

Regards,
Mohammad

lukastribus · December 30, 2019, 8:47am

Read haproxy logs, check the server status.

It’s possible the health checks fails (for whatever reason). Try without the check keyword in the server configuration.

jalilimhmd · December 30, 2019, 10:41am

Thanks for reply.
I got these errors with the check keyword.

Dec 30 13:57:56 st-cntrl haproxy[1851]: [WARNING] 363/135641 (1851) : Reexecuting Master process
Dec 30 13:57:56 localhost haproxy[1851]: Proxy mysql-cluster started.
Dec 30 13:57:56 localhost haproxy[20487]: Stopping proxy mysql-cluster in 0 ms.
Dec 30 13:57:56 localhost haproxy[20487]: Proxy mysql-cluster stopped (FE: 158 conns, BE: 158 conns).
Dec 30 13:57:57 localhost haproxy[20657]: Server mysql-cluster/st-ctl03 is DOWN, reason: Layer7 invalid response, check duration: 1ms. 2 active and 0 backup servers left. 0 sessions active, 0 requeued, 0 remaining in queue.
Dec 30 13:57:58 localhost haproxy[20657]: Server mysql-cluster/st-cntrl is DOWN, reason: Layer7 timeout, check duration: 2001ms. 1 active and 0 backup servers left. 3 sessions active, 0 requeued, 0 remaining in queue.
Dec 30 13:57:58 localhost haproxy[20657]: Server mysql-cluster/st-ctl02 is DOWN, reason: Layer4 timeout, check duration: 2001ms. 0 active and 0 backup servers left. 3 sessions active, 0 requeued, 0 remaining in queue.
Dec 30 13:57:58 localhost haproxy[20657]: proxy mysql-cluster has no server available!
Dec 30 13:58:05 st-cntrl haproxy[1851]: [WARNING] 363/135756 (1851) : Former worker 20487 exited with code 0

And these errors without check keyword.

Dec 30 13:59:12 st-cntrl haproxy[1851]: [WARNING] 363/135756 (1851) : Reexecuting Master process
Dec 30 13:59:12 localhost haproxy[1851]: Proxy mysql-cluster started.
Dec 30 13:59:12 localhost haproxy[20657]: Stopping proxy mysql-cluster in 0 ms.
Dec 30 13:59:12 localhost haproxy[20657]: Proxy mysql-cluster stopped (FE: 192 conns, BE: 192 conns).
Dec 30 13:59:12 st-cntrl haproxy[1851]: [WARNING] 363/135912 (1851) : Former worker 20657 exited with code 0

What could be the problem ?

lukastribus · December 30, 2019, 10:56am

Maybe you need to specify the post-41 keyword, I assume you have a more recent mysql release?

mysql-check [ user <username> [ post-41 ]

so:
option mysql-check user haproxy_check post-41

https://cbonte.github.io/haproxy-dconv/1.6/configuration.html#4-option%20mysql-check

Not sure why haproxy immediately stops without the checks …

Can you provide the output of haproxy -vv, systemctl status haproxy and uname -a

jalilimhmd · December 30, 2019, 11:24am

Yes, I have recent mariadb 10.4.11.

Here is haproxy -vv result:

HA-Proxy version 1.8.8-1ubuntu0.9 2019/12/02
Copyright 2000-2018 Willy Tarreau <willy@haproxy.org>

Build options :
TARGET  = linux2628
CPU     = generic
CC      = gcc
CFLAGS  = -g -O2 -fdebug-prefix-map=/build/haproxy-O1awHi/haproxy-1.8.8=. -fstack-protector-strong -Wformat -Werror=format-security -Wdate-time -D_FORTIFY_SOURCE=2
OPTIONS = USE_GETADDRINFO=1 USE_ZLIB=1 USE_REGPARM=1 USE_OPENSSL=1 USE_LUA=1 USE_SYSTEMD=1 USE_PCRE=1 USE_PCRE_JIT=1 USE_NS=1

Default settings :
maxconn = 2000, bufsize = 16384, maxrewrite = 1024, maxpollevents = 200

Built with OpenSSL version : OpenSSL 1.1.1  11 Sep 2018
Running on OpenSSL version : OpenSSL 1.1.1  11 Sep 2018
OpenSSL library supports TLS extensions : yes
OpenSSL library supports SNI : yes
OpenSSL library supports : TLSv1.0 TLSv1.1 TLSv1.2 TLSv1.3
Built with Lua version : Lua 5.3.3
Built with transparent proxy support using: IP_TRANSPARENT IPV6_TRANSPARENT 
IP_FREEBIND
Encrypted password support via crypt(3): yes
Built with multi-threading support.
Built with PCRE version : 8.39 2016-06-14
Running on PCRE version : 8.39 2016-06-14
PCRE library supports JIT : yes
Built with zlib version : 1.2.11
Running on zlib version : 1.2.11
Compression algorithms supported : identity("identity"), deflate("deflate"), raw-deflate("deflate"), gzip("gzip")
Built with network namespace support.

Available polling systems :
  epoll : pref=300,  test result OK
   poll : pref=200,  test result OK
 select : pref=150,  test result OK
Total: 3 (3 usable), will use epoll.

Available filters :
    [SPOE] spoe
    [COMP] compression
    [TRACE] trace

And here is systemctl status haproxy result:

● haproxy.service - HAProxy Load Balancer
Loaded: loaded (/lib/systemd/system/haproxy.service; enabled; vendor preset: enabled)
Active: active (running) since Mon 2019-12-30 12:02:40 +0330; 2h 43min ago
 Docs: man:haproxy(1)
       file:/usr/share/doc/haproxy/configuration.txt.gz
Process: 26689 ExecReload=/bin/kill -USR2 $MAINPID (code=exited, status=0/SUCCESS)
Process: 26688 ExecReload=/usr/sbin/haproxy -f $CONFIG -c -q $EXTRAOPTS (code=exited, status=0/SUCCESS)
Process: 1649 ExecStartPre=/usr/sbin/haproxy -f $CONFIG -c -q $EXTRAOPTS (code=exited, status=0/SUCCESS)
Main PID: 1851 (haproxy)
Tasks: 2 (limit: 4915)
CGroup: /system.slice/haproxy.service
       ├─ 1851 /usr/sbin/haproxy -Ws -f /etc/haproxy/haproxy.cfg -p /run/haproxy.pid -sf 26525
       └─26690 /usr/sbin/haproxy -Ws -f /etc/haproxy/haproxy.cfg -p /run/haproxy.pid -sf 26525

Dec 30 14:37:07 st-cntrl systemd[1]: Reloaded HAProxy Load Balancer.
Dec 30 14:37:07 st-cntrl haproxy[1851]: [WARNING] 363/143707 (1851) : Former worker 25672 exited with code 0
Dec 30 14:42:17 st-cntrl systemd[1]: Reloading HAProxy Load Balancer.
Dec 30 14:42:17 st-cntrl systemd[1]: Reloaded HAProxy Load Balancer.
Dec 30 14:42:17 st-cntrl haproxy[1851]: [WARNING] 363/143707 (1851) : Reexecuting Master process
Dec 30 14:42:26 st-cntrl haproxy[1851]: [WARNING] 363/144217 (1851) : Former worker 25818 exited with code 0
Dec 30 14:43:19 st-cntrl systemd[1]: Reloading HAProxy Load Balancer.
Dec 30 14:43:19 st-cntrl haproxy[1851]: [WARNING] 363/144217 (1851) : Reexecuting Master process
Dec 30 14:43:19 st-cntrl systemd[1]: Reloaded HAProxy Load Balancer.
Dec 30 14:43:19 st-cntrl haproxy[1851]: [WARNING] 363/144319 (1851) : Former worker 26525 exited with code 0

And here is ‘uname -a’ result:

Linux st-cntrl 4.15.0-72-generic #81-Ubuntu SMP Tue Nov 26 12:20:02 UTC 2019 x86_64 x86_64 x86_64 GNU/Linux

lukastribus · December 30, 2019, 11:47am

Did you try with post-41?

jalilimhmd · December 30, 2019, 2:43pm

Yes, I did.
Now I’ve got serious problem. Mysql service won’t start.

This is with haproxy service started:

root@st-cntrl:~# service mysql start                 
Job for mariadb.service failed because the control process exited with error code.
See "systemctl status mariadb.service" and  "journalctl -xe" for details.

root@st-cntrl:~# systemctl status mariadb.service
● mariadb.service - MariaDB 10.4.11 database server
  Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
Drop-In: /etc/systemd/system/mariadb.service.d
       └─migrated-from-my.cnf-settings.conf
 Active: failed (Result: exit-code) since Mon 2019-12-30 18:02:44 +0330; 3s ago
   Docs: man:mysqld(8)
       https://mariadb.com/kb/en/library/systemd/
Process: 15651 ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION (code=exited, status=1/FAILURE)
Process: 16548 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||   VAR=`/usr/bin/galera_recovery`; [ $? -eq 0 ]   && systemctl set-environment _WSREP_START_  
Process: 16539 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)                                                         
Process: 16531 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS)                                                         
Main PID: 15651 (code=exited, status=1/FAILURE)                                                                                                                                                                                                                                                                                                                  
Dec 30 18:02:44 st-cntrl mysqld[16548]: 2019-12-30 18:02:42 0 [Note] InnoDB: 10.4.11 started; log sequence number 1583550091; transaction id 18045557                                
Dec 30 18:02:44 st-cntrl mysqld[16548]: 2019-12-30 18:02:42 0 [Warning] InnoDB: Skipping buffer pool dump/restore during wsrep recovery.                                             
Dec 30 18:02:44 st-cntrl mysqld[16548]: 2019-12-30 18:02:42 0 [Note] Plugin 'FEEDBACK' is disabled.                                                                              
Dec 30 18:02:44 st-cntrl mysqld[16548]: 2019-12-30 18:02:42 0 [Note] Server socket created on IP: '10.0.0.101'.                                                                  
Dec 30 18:02:44 st-cntrl mysqld[16548]: 2019-12-30 18:02:42 0 [ERROR] Can't start server: Bind on TCP/IP port. Got error: 98: Address already in use                             
Dec 30 18:02:44 st-cntrl mysqld[16548]: 2019-12-30 18:02:42 0 [ERROR] Do you already have another mysqld server running on port: 3306 ?                                          
Dec 30 18:02:44 st-cntrl mysqld[16548]: 2019-12-30 18:02:42 0 [ERROR] Aborting'                                                                                                  
Dec 30 18:02:44 st-cntrl systemd[1]: mariadb.service: Control process exited, code=exited status=1                                                                               
Dec 30 18:02:44 st-cntrl systemd[1]: mariadb.service: Failed with result 'exit-code'.                                                                                            
Dec 30 18:02:44 st-cntrl systemd[1]: Failed to start MariaDB 10.4.11 database server.

And this is with haproxy and keepalived service stoped:

root@st-cntrl:~# service mysql start                  
Job for mariadb.service failed because the control process exited with error code.
See "systemctl status mariadb.service" and "journalctl -xe" for details.
root@st-cntrl:~# systemctl status mariadb.service 
● mariadb.service - MariaDB 10.4.11 database server
Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
Drop-In: /etc/systemd/system/mariadb.service.d
       └─migrated-from-my.cnf-settings.conf
Active: failed (Result: exit-code) since Mon 2019-12-30 18:11:03 +0330; 5s ago
 Docs: man:mysqld(8)
       https://mariadb.com/kb/en/library/systemd/
Process: 17535 ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION (code=exited, status=1/FAILURE)
Process: 17368 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||   VAR=`/usr/bin/galera_recovery`; [ $? -eq 0 ]   && systemctl set-environment _WSREP_START_  
Process: 17359 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)                                                         
Process: 17351 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS)                                                         
Main PID: 17535 (code=exited, status=1/FAILURE)                                                                                                                                    
Status: "MariaDB server is down"                                                                                                                                                                                                                                                                                                                               
Dec 30 18:10:27 st-cntrl systemd[1]: Starting MariaDB 10.4.11 database server...                                                                                                 
Dec 30 18:10:30 st-cntrl mysqld[17368]: WSREP: Recovered position f1e2d4b2-261e-11ea-9fb6-0f1225d0af82:345327                                                                    
Dec 30 18:10:30 st-cntrl mysqld[17535]: 2019-12-30 18:10:30 0 [Note] /usr/sbin/mysqld (mysqld 10.4.11-MariaDB-1:10.4.11+maria~bionic) starting as process 17535 ...                  
Dec 30 18:11:03 st-cntrl systemd[1]: mariadb.service: Main process exited, code=exited, status=1/FAILURE                                                                         
Dec 30 18:11:03 st-cntrl systemd[1]: mariadb.service: Failed with result 'exit-code'.                                                                                            
Dec 30 18:11:03 st-cntrl systemd[1]: Failed to start MariaDB 10.4.11 database server.

lukastribus · December 30, 2019, 3:09pm

Well you cannot have one the same machine listening to port 3306 both mysql and haproxy. Either one of the other. Decide.

jalilimhmd · January 5, 2020, 10:42am

Ok, I got everything’s right.
I installed HAproxy on two separate nodes and three mysql server on there other node.
My first question Is, does this architecture correct ?
And my second question is I want to know where did I configured wrong that I give “Access denied” error from my second HAproxy server. I granted access to mysql.

root@st-hap01:~# mysql -h 127.0.0.1 -u haproxy_root -p -e "show variables like 'server_id'"
Enter password: 
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1     |
+---------------+-------+
root@st-hap01:~# mysql -h 127.0.0.1 -u haproxy_root -p -e "show variables like 'server_id'"
Enter password: 
ERROR 1045 (28000): Access denied for user 'haproxy_root'@'10.0.0.99' (using password: YES)

And this is mysql users:

MariaDB [(none)]> select user,host,password from mysql.user;
+---------------+-----------+-------------------------------------------+
| User          | Host      | Password                                  |
+---------------+-----------+-------------------------------------------+
| haproxy_check | 10.0.0.99 | *7FCFF8A4388C0668B95E75D920D31480B709E916 |
| haproxy_check | 10.0.0.98 | *7FCFF8A4388C0668B95E75D920D31480B709E916 |
| haproxy_root  | 10.0.0.99 | *60E34B079D38AC45EAAF0B43817E3938A96619AD |
| haproxy_root  | 10.0.0.98 | *60E34B079D38AC45EAAF0B43817E3938A96619AD |
+---------------+-----------+-------------------------------------------+

You see that passwords are the same.

And my haproxy.conf from two HAproxy servers:

global
        log 127.0.0.1 local0
        log 127.0.0.1 local0 notice
        maxconn 4096
        user haproxy
        group haproxy
        daemon

defaults
        log global
        option  tcplog
        option  dontlognull
        retries 3
        option  redispatch
        timeout queue 1m
        timeout client 1m
        timeout connect 10s
        timeout server 1m
        timeout check 5s

# MySQL Load Balance Pool
listen mysql-cluster
        bind 127.0.0.1:3306
        mode tcp
        option mysql-check user haproxy_check post-41
        balance roundrobin
        server st-cntrl 10.0.0.101:3306  
        server st-ctl02 10.0.0.102:3306  
        server st-ctl03 10.0.0.103:3306

Regards,
Mohammad

jalilimhmd · January 6, 2020, 2:17pm

My problem solved.
It was Galera cluster nodes disconnected from cluster.

Topic		Replies	Views
MySQL proxy errors Help!	3	893	October 22, 2021
Loadbalancing read/write Help!	3	1243	August 24, 2020
Use HAProxy for Mysql master-master in Kubernetes Help!	7	1030	February 24, 2022
ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0 Help!	0	228	April 22, 2024
HAPROXY solution with same device Help!	3	753	November 8, 2017

Testing haproxy failed

Related topics