Shulou(Shulou.com)05/31 Report--

This article shows you the sample analysis of host is blocked and flush hosts in MySQL, which is concise and easy to understand, which will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

Background:

MySQL-5.5.41, the environment used for development is wrong to connect to the MySQL client.

Scene:

The business side reports an error when connecting to MySQL, and the application outputs the same error message:

Click (here) to collapse or open

ERROR 1129 (HY000): Host 'x.x.x.x' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'

And colleagues of the developer tried to modify the parameters of max_connect_errors.

After adjusting to 1000, flush hosts will solve the problem, but the same error will be reported a minute later.

Analysis:

The cause of this problem is very straightforward, and it is clearly stated in the error message, "because of many connection errors".

Then I took a look at aborted connections, and it was as described, too many.

I also checked the parameters of host once in the middle, because in 5.7, setting host_cache_size to 0 can avoid this problem.

However, 5.5 does not have this parameter at all. _ (: "∠") _

Consider the root cause of the problem:

In host_cache, blocked errors are logged if the number of such errors is too large, and the number of such errors exceeds the value of max_connect_errors after the last successful connection

Then MySQL throws the error and prompts that flush hosts is needed

5.7. set size to 0 (dynamic parameter), which in disguise makes mysql think that the number of blocked has always been 0, so this error will not be reported again.

From the root cause, shutting down host cache is also a solution.

So how do I turn off host cache?

Neither MySQL5.5 nor 5.7has an explicit parameter in show variables to turn off host cache.

I flipped through the document and found that it can only be closed by adding skip-host-cache at startup.

Handling method:

In the end, the restart time was negotiated, and skip-host-cache was added to the MySQL configuration file of the development machine to completely shut down the cache, and then let the developers mess around. ╮ (╯ _ ╰) ╭

The above is the example analysis of host is blocked and flush hosts in MySQL. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.