Hi again Cyril,
I'm trying the option "mysql-check" as you comment me. I've found that
I must create a user into the mysql server for permit the haproxy
access:
insert into user (Host,User) values ('192.168.1.3','haproxy'); (The
users table has the ndbcluster engine, for that the permissions are
replicated inmediatly)
Then I modify my haproxy.conf like this:
listen MySQL_Node 192.168.1.3:3306
mode tcp
option mysql-check user haproxy
option tcplog
balance roundrobin
server mysqlnode1 192.168.1.1 check port 3306 weight 50
server mysqlnode2 192.168.1.2 check port 3306 weight 50
I start the haproxy in debug mode. The problem is that, I don't know
why, but the haproxy puts DOWN the mysqlnode1 after 20 seconds (2
seconds/haproxy request x 10 max connections mysql errors) with the
connections problems, mysql server on mysqlnode1 still block the
haproxy request. However the mysqlnode2 is still UP.
I don't know why it produces this behaviour if both machine are equal
(in fact mysqlnode2 is a clone virtual machine from mysqlnode1). Do
you know what can be the problem?
Another quick question, I've seen in several blogs that it is more
interesting to put an script in the xinetd for monitor the mysql
health. What do you think about it?
Thanks,
David
El día 8 de marzo de 2012 23:50, David Seira <[email protected]> escribió:
> 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é
