Haproxy + percona 5.7 xtradb error

Question

i configure Hello, I configure haproxy by digitalocean manual, roundrobin for percona 5.7 bases, but on the haproxy server, when I try to connect to the database I getting error.

On the haproxy server:

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

And i get error:

ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 2

Haproxy config:

    lobal
        log 127.0.0.1   local0
        log 127.0.0.1   local1 notice
        #log loghost    local0 info
        maxconn 1024
        #chroot /usr/share/haproxy
        user haproxy
        group haproxy
        daemon
        #debug
        #quiet


defaults
        log     global
        mode    http
        option  tcplog
        option  dontlognull
        retries 3
        option redispatch
        maxconn 1024
        timeout connect 5000ms
        timeout client 50000ms
        timeout server 50000ms

listen galera_cluster
        bind 127.0.0.1:3306
        mode tcp
        option  httpchk
        balance leastconn
        server galera-node01 192.168.0.101:3306 check port 9200
        server galera-node02 192.168.0.102:3306 check port 9200
        server galera-node03 192.168.0.103:3306 check port 9200

If I connect directly to the database 192.168.0.101, everything works, I get a response from the database, but when I make the request through to haproxy 127.0.0.1 I get this error:

ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 2

My xinetd config on mysql:

# default: on
# description: mysqlchk
service mysqlchk
{
# this is a config for xinetd, place it in /etc/xinetd.d/
        disable = no
        flags           = REUSE
        socket_type     = stream
        type            = UNLISTED
        port            = 9200
        wait            = no
        user            = nobody
        server          = /usr/bin/clustercheck
        server_args = percona percona
        log_on_failure  += USERID
        only_from       = 0.0.0.0/0
        #
        # Passing arguments to clustercheck
        # <user> <pass> <available_when_donor=0|1> <log_file> <available_when_readonly=0|1> <defaults_extra_file>"
        # Recommended: server_args   = user pass 1 /var/log/log-file 0 /etc/my.cnf.local"
        # Compatibility: server_args = user pass 1 /var/log/log-file 1 /etc/my.cnf.local"
        # 55-to-56 upgrade: server_args = user pass 1 /var/log/log-file 0 /etc/my.cnf.extra"
        #
        # recommended to put the IPs that need
        # to connect exclusively (security purposes)
        per_source      = UNLIMITED
}

If i telnet to PXC node on port 9200, i got:

telnet 192.168.0.101 9200
Trying 192.168.0.101...
Connected to 192.168.0.101.
Escape character is '^]'.
HTTP/1.1 503 Service Unavailable
Content-Type: text/plain
Connection: close
Content-Length: 57

Percona XtraDB Cluster Node is not synced or non-PRIM.
Connection closed by foreign host.

Jervin Real · Answer 1 · 2016-10-14T23:26:07.800

0

The most common reason for this is that all nodes in the cluster is down. If you have enabled your HAProxy stats, check that all nodes are up. If not, you mysqlchk service is likely not being able to connect to the cluster nodes properly.

Check your mysqlchk xinetd service should have the proper server_args configured. Once these are set, restart xinetd, and telnet to port 9200 to validate

[root@node02 log]# cat /etc/xinetd.d/mysqlchk
# default: on
# description: mysqlchk
service mysqlchk
{
# this is a config for xinetd, place it in /etc/xinetd.d/
        ...
        server          = /usr/bin/clustercheck
        server_args = percona percona
        ...
        # Passing arguments to clustercheck
        # <user> <pass> <available_when_donor=0|1> <log_file> <available_when_readonly=0|1> <defaults_extra_file>"
        # Recommended: server_args   = user pass 1 /var/log/log-file 0 /etc/my.cnf.local"
}

UPDATE:

A more thorough procedure, including making sure mysqlchk is configured, can be found here https://www.percona.com/doc/percona-xtradb-cluster/5.6/howtos/virt_sandbox.html

edited Oct 14 '16 at 23:26

answered Oct 13 '16 at 06:46

Jervin Real

26
3

If I connect directly to the database 192.168.0.101, everything works, I get a response from the database, but when I make the request through to haproxy 127.0.0.1 I get this error: – dodcens Oct 13 '16 at 08:40
Can you telnet to port 9200 on one of the PXC nodes to validate that mysqlchk works? – Jervin Real Oct 13 '16 at 10:30
I got a: telnet 192.168.0.101 9200 Trying 192.168.0.101... Connected to 192.168.0.101. Escape character is '^]'. HTTP/1.1 503 Service Unavailable Content-Type: text/plain Connection: close Content-Length: 57 Percona XtraDB Cluster Node is not synced or non-PRIM. Connection closed by foreign host. – dodcens Oct 13 '16 at 11:35
Ok, so that means your mysqlchk is not configured properly - see my answer above. – Jervin Real Oct 14 '16 at 23:23
in the top, i add my mysqlchk config, he is the same as your – dodcens Oct 17 '16 at 08:19
The 2 values on server_args represents MySQL username and password. Do you have a MySQL account on ALL the PXC nodes with username percona and password percona? – Jervin Real Oct 17 '16 at 12:21

Haproxy + percona 5.7 xtradb error

1 Answers1