'host-name' is blocked because of many connection
errors; unblock with 'mysqladmin flush-hosts'
My home run machine MySQL server was suddenly down as I tried to
check my blog and other sites today, the error I saw while trying
to open, this blog as well as other hosted sites using the MySQL
was:
Error establishing a database connection
The topology, where this error occured is simple, I have two
hosts:
1. Apache version 2.0.64 compiled support externally PHP scripts
interpretation via libphp - the host runs on (FreeBSD)
2. A Debian GNU / Linux squeeze running MySQL server version
5.1.61
The Apache host is assigned a local IP address
192.168.0.1
and the SQL server is running on a host with IP
192.168.0.2
To diagnose the error I've logged in to
192.168.0.2 and
weirdly the
mysql-server was appearing to run just
fine:
debian:~# ps ax |grep -i mysql
31781 pts/0 S 0:00 /bin/sh /usr/bin/mysqld_safe
31940 pts/0 Sl 12:08 /usr/sbin/mysqld --basedir=/usr
--datadir=/var/lib/mysql --user=mysql
--pid-file=/var/run/mysqld/mysqld.pid
--socket=/var/run/mysqld/mysqld.sock --port=3306
31941 pts/0 S 0:00 logger -t mysqld -p daemon.error
32292 pts/0 S+ 0:00 grep -i mysql
Moreover I could connect to the localhost SQL server with
mysql
-u root -p and it seemed to run fine. The error
Error
establishing a database connection meant that either something
is messed up with the database or 192.168.0.2 Mysql port 3306 is
not properly accessible.
My first guess was something is wrong due to some firewall rules,
so I tried to connect from
192.168.0.1 to
192.168.0.2
with telnet:
freebsd# telnet 192.168.0.2 3306
Trying 192.168.0.2...
Connected to jericho.
Escape character is '^]'.
Host 'webserver' is blocked because of many connection errors;
unblock with 'mysqladmin flush-hosts'
Connection closed by foreign host.
Right after the telnet was initiated as I show in the above output
the connection was immediately closed with the error:
Host 'webserver' is blocked because of many connection errors;
unblock with 'mysqladmin flush-hosts'Connection closed by foreign
host.
In the error
'webserver' is my Apache machine set hostname.
The error clearly states the problems with the 'webserver' apache
host unable to connect to the SQL database are due to 'many
connection errors' and a fix i suggested with
mysqladmin
flush-hosts
To temporary solve the error and restore my normal connectivity
between the Apache and the SQL servers I logged I had to issue on
the SQL host:
mysqladmin -u root -p flush-hosts Enter
password:
Thogh this temporar fix restored accessibility to the databases and
hence the websites errors were resolved, this doesn't guarantee
that in the future I wouldn't end up in the same situation and
therefore I looked for a permanent fix to the issues once and for
all.
The permanent fix consists in changing the default value set for
max_connect_error in
/etc/mysql/my.cnf, which by
default is not too high. Therefore to raise up the variable value,
added in
my.cnf in conf section
[mysqld]:
debian:~# vim /etc/mysql/my.cnf
...
max_connect_errors=4294967295
and afterwards restarted MYSQL:
debian:~# /etc/init.d/mysql restart
Stopping MySQL database server: mysqld.
Starting MySQL database server: mysqld.
Checking for corrupt, not cleanly closed and upgrade needing
tables..
To make sure the assigned
max_connect_errors=4294967295 is
never reached due to Apache to SQL connection errors, I've also
added as a cronjob.
debian:~# crontab -u root -e
00 03 * * * mysqladmin flush-hosts
In the cron I have omitted the
mysqladmin -u root -p
(user/pass) input options because for convenience I have already
stored the mysql root password in
/root/.my.cnf
Here is how
/root/.my.cnf looks like:
debian:~# cat /root/.my.cnf
[client]
user=root
password=a_secret_sql_password
Now hopefully, this would permanently solve SQL's
'failure to
accept connections' due to
too many connection errors
for future.