Problem of hosts blocked in MySQL because of 'max_connect_errors' variable

Problem : "Host 'host_name' is blocked" error in mysql version 4.0 and later

This is a situation encountered in remote database connections to a typical mysql server (versions greater than 4.0).The mysql can be either installed in linux or windows platform.But such an error is thrown up when a user is trying to connect using sqlyog to that machine through the network.Some more factors that you should check is given first.



Checklist:

1. Make sure that firewall is not running in blocked state in the client or the server machine.This can block the connection attempts.
2. Make sure that no more clients other than the allowed no: of clients are trying to connect to the server at a given point of time.

If you get a 'Too many connections' error when you try to connect to the mysqld server, this means that all available connections are in use by other clients.



The number of connections allowed is controlled by the max_connections system variable. Its default value is 100. If you need to support more connections, you should restart mysqld with a larger value for this variable.

mysqld actually allows max_connections+1 clients to connect. The extra connection is reserved for use by accounts that have the SUPER privilege. By granting the SUPER privilege to administrators and not to normal users (who should not need it), an administrator can connect to the server and use SHOW PROCESSLIST to diagnose problems even if the maximum number of unprivileged clients are connected. See See reference in MySQL site

The maximum number of connections MySQL can support depends on the quality of the thread library on a given platform. Linux or Solaris should be able to support 500-1000 simultaneous connections, depending on how much RAM you have and what your clients are doing. Static Linux binaries provided by MySQL AB can support up to 4000 connections.

Now we can come to the error message which is the heart of the problem.

Error :Host 'host_name' is blocked.

If you get the following error, it means that mysqld has received many connect requests from the host 'host_name' that have been interrupted in the middle:

The actual error listing goes like this.

Host 'host_name' is blocked because of many connection errors.Unblock with 'mysqladmin flush-hosts'

The number of interrupted connect requests allowed is determined by the value of the max_connect_errors system variable. After max_connect_errors failed requests, mysqld assumes that something is wrong (for example, that someone is trying to break in), and blocks the host from further connections until you execute a mysqladmin flush-hosts command or issue a FLUSH HOSTS statement

By default, mysqld blocks a host after 10 connection errors. You can adjust the value by starting the server like this:

shell> mysqld_safe --max_connect_errors=10000 & 

If you get this error message for a given host, you should first verify that there isn't anything wrong with TCP/IP connections from that host. If you are having network problems, it does you no good to increase the value of the 'max_connect_errors' variable.

It is also handy to issue a flush hosts command.Refer the following link for the syntax:

http://dev.mysql.com/doc/refman/5.0/en/flush.html

After these steps,reconnect using 'Sqlyog'.Wishing good days.

Source Documentation : MySQL Developer Zone