5

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:

  1. /etc/hosts.allow includes "mysqld: ALL : allow "

  2. 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

Improve this question
4
  • Can you add your haproxy config file? Commented Sep 28, 2011 at 20:02
  • Are you running replication or anything like that on MySQL? How are they in sync to load balance between them? Commented Sep 29, 2011 at 18:20
  • Paul - DB instances are synced using drbd Commented Sep 30, 2011 at 0:01
  • 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 :) Commented Sep 30, 2011 at 9:51

4 Answers 4

Reset to default
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.

Improve this answer
0

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.

Improve this answer
0

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.

Improve this answer
-1

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

Improve this answer

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.