ERROR 2013 when connecting to mysql via HAProxy

Question

I am trying to use HAProxy as a load balancer for 2 mysql nodes. I have HAProxy listening on 3307 and it routes requests to the DBs on 3306. When the client is trying to connect to the Proxy (mysql -u ... -h .. -P3307 -p...) it gets the following error:

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

When the client is trying to connect directly to the DB it works fine. I have no idea why its failing when traffic goes via the Proxy.

Here's what I've already checked:

/etc/hosts.allow includes "mysqld: ALL : allow "
MySQL config file (my.cnf) doesn't have a bind address line (open to all).

I am using HAProxy 1.5dev, Mysql 5.1.x and the whole enchilada is running in ec2 on Amazon linux.

Here's the HAProxy config file:

    global
     log 127.0.0.1   local0
     log 127.0.0.1   local1 notice
     #log loghost    local0 info
     user haproxy
     group haproxy
     maxconn 4096
     daemon
     #debug
     #quiet

    defaults
     log     global
     mode    tcp
     option  tcplog
     option  dontlognull
     retries 3
     option redispatch
     maxconn 4096
     contimeout      5000
     clitimeout      50000
     srvtimeout      50000

    frontend mysql_cluster
     bind 10.0.0.150:3307
     default_backend mysql_cluster

    backend mysql_cluster
     mode tcp
     balance roundrobin
     option tcpka
     option httpchk
     server lb1 10.0.0.140:3306 check port 9200 inter 5s rise 2 fall 2
     server lb2 10.0.0.214:3306 check port 9200 inter 5s rise 2 fall 2 

    listen stats 10.0.0.150:8081
     mode http
     option httpclose
     balance roundrobin
     stats uri /
     stats realm Haproxy\ Statistics
     stats auth ***:***

Has anyone tackled this problem before? Any ideas how to solve this?

Any help much appreciated

Are you running replication or anything like that on MySQL? How are they in sync to load balance between them? — Paul Kroon, Sep 29 '11 at 18:20
probably a red herring, but you don't need the `option httpchk` on a tcp backend, also in my own config I've commented out the `option tcpka` line. It was long enough ago that I can't remember why :) — Richard Benson, Sep 30 '11 at 09:51

score 2 · Answer 1 · answered Sep 30 '11 at 23:01

2

OK, I solved it. I had a problem with my xinetd script.

It failed to bind on 9200 - fixed it in /etc/services file.

answered Sep 30 '11 at 23:01

A.RG

81
1
2
4

score 0 · Answer 2 · answered Jun 20 '17 at 12:11

It usually occurs when your xinetd health check service responses error.
you can debug xinetd by telnet like this:

telnet IPADDR PORT     # IPADDR: one of the IPs of your nodes
                       # PORT: the port that node is listening.

the output tells you about the problem.

score 0 · Answer 3 · answered May 30 '19 at 13:43

On my setup, I wasnt running xinetd, but found that the mysql-check option was causing issues. So I was at least able to get haproxy up and running by commenting it out. I suspect that mysql-check has something to do with either wrong mysql user being used or missing grants.

score -1 · Answer 4 · answered Jun 01 '17 at 04:51

Thanks dear , great,

HAproxy need xinetd to be configured properly to work else below error appears ,

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

Xinetd to be restated if running already .

https://docs.openstack.org/ha-guide/shared-database-manage.html

ERROR 2013 when connecting to mysql via HAProxy

4 Answers4