MySQL - Host 'host_name' is blocked because of many connection errors. Unblock with 'mysqladmin flush-hosts' automatically with a simple script.

In day to day MySQL administration you may come across with host 'host_name or ip address' is blocked because of many connection errors, according to http://dev.mysql.com/doc/refman/5.0/en/blocked-host.html there are many possibilities trigger this scenario.

In my case, I have a program connect to MySQL DB runs thousand and thousand insert record and it looks there are to many open with no close connection, later I will talk to the programmer.


I am not going into detail on MySQL configuration but to share with you readers on creating a simple script to run mysqladmin flush-hosts command with cron job.

The only challenge with mysqladmin flush-hosts command is that it requires password, unless your mysql server has no authentication.

These are the steps:

1) Install expect program for you favorite distro, mine is rhel

yum -y install expect



What is expect?

Expect is a program that "talks" to other interactive programs according to a script. Following the script, Expect knows what can be expected from a program and what the correct response should be. An interpreted language provides branching and high-level control structures to direct the dialogue. In addition, the user can take control and interact directly when desired, afterward returning control to the script.



2) Create a cron job, I will need to run every hour for that  I create /etc/cron.hourly/flush,
giving 700 permission to the file with entries as below:

#!/usr/bin/expect
set timeout 20
spawn /usr/bin/mysqladmin -p flush-hosts

expect "Enter password: "
send "p@ssw0rd\r\n"
expect eof

3) I am using p@ssw0rd for mysql root password for the sake of this how to, you will use your own password.

4) You may test by running /etc/cron.hourly/flush before taking into effect in cron.