[MySQL] Host is blocked because of many connection errors

[problem description]:

The application server found that the database could not be connected. Check the log and find an error:

SelectSQL get mysql connection failed, err:Error: ER_HOST_IS_BLOCKED Host 'xx.xx.xx.xx' is blocked because of many connection errors.

[solution]:

Read the log and found that the error is related to connection errors. Check the max_connect_errors parameters:

SELECT @ @ global.max_connect_errors

+-+

| | @ @ global.max_connect_errors |

+-+

| | 10 |

+-+

1 row in set (0. 00 sec) turned out to be 10, which is really too small.

Looking at the 5.5 documents, the official solution is:

However, once a host is blocked, flushing the host cache is the only way to unblock it.

Execute in mysql:

Mysql > FLUSH HOSTS; or execute instructions:

Mysqladmin flush-hosts can unlock the "host" via [flushing the host cache].

Then increase the number of connection errors to avoid such problems again: (for Dynamic Variable)

Mysql > SET @ @ global.max_connect_errors=100000

Finally, add / modify my.cnf by adding:

Max_connect_errors = 100000

Later, after looking at 5.5 documents, it was found that the default value was 10, while in versions after 5.6.6, the default value had been adjusted to 100.

Reference documentation:

MySQL 5.5 Reference Manual / Chapter 5 MySQL Server Administration / 5.1.1 Server Option and Variable Reference

Author's official account on Wechat (continuously updated)