Hi Cyril, Thanks for your quick response.
I will take a look to the "option mysql-check". The MySQL load balanced is for used as a backend storage for a radius server with MySQL tables with MyISAM engine. All the request are equal. Thanks, David El día 8 de marzo de 2012 20:58, Cyril Bonté <[email protected]> escribió: > 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é
