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
Failed to start HAProxy Load Balancer Help!	16	67854	November 10, 2021
Php application slow where use haproxy Help!	6	2464	October 10, 2022
Haproxy causes "Communications link failure" with JDBC and MySQL Help!	12	5225	March 6, 2017
Layer4 health check failures while using option http-check Help!	19	3387	November 27, 2024
HAPROXY solution with same device Help!	3	777	November 8, 2017

Testing haproxy failed

Related topics