MySQL Fixing Host is blocked because of many connection errors

I've recently been experiencing a problem with some MySQL servers, "Host 'host_name' is blocked because of many connection errors.
Unblock with 'mysqladmin flush-hosts". This happens when a MySQL client makes several attempts to open a connection but does not close it. Usually, it's because of an attack or some type of networking issue between one server and the MySQL server. However that may not always be the case, and legitimate connections will get added to the "count". When the limit is reached (by default it's 10), the host will be blocked and anything on that host will instantly break down.

Such a situation is unacceptable, and MySQL developers refuse to resolve the problem on legitimate connections.

There is a great workaround or solution to this situation. It's called MySQL Proxy. It's a small program that sits between a MySQL client and a MySQL server. If you are running a split system where your MySQL servers are on different hardware than (for example) your Webservers, this nifty little proxy server will finally put an end to the blocked connections error.

System Requirements

• Any Unix/Linux Compatible Operating System (I assume your using FreeBSD of course)

Install

With FreeBSD, the setup is really very simple. Install the port located in /usr/ports/databases/mysql-proxy. Then edit your /etc/rc.conf file as needed. This work around only works if you install the MySQL proxy on the same system that hosts the MySQL server. It works on the concept that the MySQL server will never block "localhost" since it's not utilizing TCP/IP, but instead using UNIX Sockets.

Your /etc/rc.conf file should contain the following:

mysql_proxy_enable="YES"
mysql_proxy_address="10.7.7.9:3306"
mysql_proxy_backend_addresses="localhost:3306"
mysql_proxy_args="--user=mysql"

The first line is self explanatory, and tells FreeBSD to enable this service. The second line specifies the interface address and port to use to accept MySQL connections on. My recommendation is to set this to what ever the MySQL server is listening on to avoid having to reprogram all your clients. In this example, my MySQL server is *was* listening on 10.7.7.9 on port 3306 (keeping in mind that MySQL will listen by default on all interfaces). 10.7.7.9 was the interface that my clients would use to connect to.

The 3rd line is where the magic happens. Here we are telling MySQL Proxy to use "localhost" as the "MySQL Server" to proxy. The final line specifies what user this service should run as. I recommend setting it to the same user that the MySQL server runs as. Save the file, but don't start MySQL proxy yet.

In order for stuff to work, we need to change the MySQL server configuration to listen only on localhost (127.0.0.1) on port 3306. Since MySQL Proxy will already be using port 3306 on the network interface. Edit your "my.cnf" file and add or change the following:

bind-address = 127.0.0.1

Save the file, then restart MySQL server. It's should now only listen for requests on the local loop-back interface and not the network interface.

Next, start up MySQL Proxy

service mysql-proxy start

Now, check to see if everything is working. It's possible that you may need to update some privileges to allow "localhost" access to the databases where you specifically allowed only external hosts. After all, as far as MySQL server is concerned, all these external hosts are now local connections.

Problem Solved!

A simple solution to a complex problem. Complex because MySQL provides no method of determining what is actually causing hosts to get blocked. From now on, MySQL will see only "local" connections being forwarded by MySQL Proxy, and since MySQL is programmed to treat all local connections as socket connections, they will never get blocked.