many connection errors

MySQL & MariaDB Percona XtraDB Cluster 5.x
1

Team,

I have configured percona cluster under hardware load balancer and its working properly but some time its showing error " blocked because of many connection errors unblock with mysqladmin flush-hosts in percona"

Please give me permanent solution.

Thanks
Ashok

2

Last time I saw this, it was caused by the load balancer “pinging” the MySQL server to check if it was alive, which resulted in what MySQL deemed a connection error each time, and eventually blocking the load balancer’s IP. So I would look into that and see if you can modify the way the load balancer is checking the health of the MySQL server.

3

As you know health check is required in Load balacner. As you know if any servers down then how LB will determined to check back server normal.

Please help me any permanent solution.

Thanks
Ashok

4

The issue is not with MySQL, it is just how your load balancer is checking the health, by attempting to “connect” to MySQL, instead of using MySQL’s actual “ping” functionality.

To get around this, you could set max_connect_errors to a larger number, which would prevent MySQL from blocking the load balancer. However that would apply to all connections, so it could cause issues with other things if you get flooded with connections.

Another alternative would be to run mysqladmin flush-hosts via a cron task, which would clear out the error count before the IP got blocked. But again that could cause issues if you get flooded with connection attempts from another source.

Outside of that, you’d have to modify the way your load balancer is checking MySQL health.

5

Thanks a lot, I applied same thing on servers as cronjob and also change health check way to mysql servers.