Hi David,
Le 08/03/2012 20:37, David Seira a écrit :
Hi list,
I'm trying to installing HAProxy for make load balancing between
several MySQL Nodes in a MySQL Cluster.
I've configured the haproxy.conf like this:
listen mysql 0.0.0.0:3306
mode tcp
balance roundrobin
maxconn 4096
server mysqlnode1 192.168.1.1 check weight 50
server mysqlnode2 192.168.1.2 check weight 50
The problem comes when I try to make a request to the haproxy:
mysql -uuser -ppass -h 192.168.1.3 test -e "select * from test1;"
It works well but if I reboot the service (haproxy) the request
doesn't work; I obtain the next error:
"Host is blocked because of many connection errors"
However if I make the same request but for the mysqlnode1 directly, it
works well. I must reboot the mysql service in both host to be solved.
It seems that the haproxy doesn't relay correctly the request to the servers.
No, the request is correctly sent to the servers but there's an issue in
your configuration.
MySQL requires a complete and valid handshake otherwise it's considered
as an error. After too many errors errors (10 times is the default),
MySQL decides to block the client host.
You don't have to reboot/restart mysql to solve this, "mysqladmin
flush-hosts" (or the request "FLUSH HOSTS") cleans up the blocked hosts.
Now, why does this happen ?
You've enabled tcp checks on each servers. This means that haproxy opens
periodically (every 2 seconds by default) connections to the mysql
servers and closes them immediatly : no handshake is sent, mysql will
block the client host, which is your haproxy server.
Maybe I forgot another option in the haproxy.conf?
Yes, have a look at "option mysql-check", haproxy will then send a
complete handshake for each check and your haproxy server won't be blocked.
Has anyone configured haproxy for balancing several mysql servers?
Be careful, load balancing mysql servers is not an easy thing. It
depends a lot on the requests types, the data stored, and the DB schema
itself sometimes.
--
Cyril Bonté
