Fix MySQL connection error – Host ” is blocked because of many connection errors; unblock with ‘mysqladmin flush-hosts’

Wednesday, 2nd July 2014

fix-mysql-too-many-connection-errors-explained

If you get a MySQL error like:

Host '' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'

This most likely means your PHP / Java whatever programming language application connecting to MySQL is failing to authenticate with the application created (existing) or that the application is trying too many connections to MySQL in a rate where MySQL server can't serve all the requests.

Some common errors for Too many Connection errors are:
 

  • Networking Problem
  • Server itself could be down
  • Authentication Problems
  • Maximum Connection Errors allowed.

The value of the max_connection_errors system variable determines how many successive interrupted connection requests are permitted to myqsl server.
 

Well anyways if you get the:

Host '' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'

You can consider this a sure sign application connections to MySQLis logging a lot of error connections, for some reason.
This error could also appear on very busy websites where high amount of separete connections are used – I've seen the error occur on PHP websites whether mysql_pconnect(); is selected in favour of the prooved working mysql_connect();

The first thing to do before changing / increasing default set of max connection errors is to check how many max connection errors are set within MySQL?

For that connect with MySQL CLI and issue:
 

mysql> SHOW VARIABLES LIKE '%error%';


+——————–+————————————————————-+
| Variable_name      | Value                                                           |
+——————–+————————————————————-+
| error_count        | 0                                                                     |
| log_error          | /var/log/mysql//mysqld.log                                |
| max_connect_errors | 10000                                                      |
| max_error_count    | 64                                                               |
| slave_skip_errors  | OFF                                                             |
+——————–+————————————————————-+


A very useful mysql cli command in debugging max connection errors reached problem is

mysql> SHOW PROCESSLIST;

 

To solve the error, try to tune in /etc/my.cnf, /etc/mysql/my.cnf or wherever my.cnf is located:

[mysqld]
max_connect_errors
variable

and

wait_timeout var. Some reasonable variable size would be:

max_connect_errors = 100000
wait_timeout = 60

If such (anyways) high values is still not high enough you can raise mysql config connection timeout

 

to

max_connect_errors = 100000000

Also if you want to try raise max_connect_errors var without making it permanenty (i.e. remember var setting after MySQL service restart), set it from MySQL cli with:

SET GLOBAL max_connect_errors


If you want to keep the set default max_connection_errors and fix it temporary, you can try to follow the error

Host '' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'

suggestion and issue in root console:

mysqladmin flush-hosts

Same could also be done from MySQL Cli with cmd:
 

FLUSH HOSTS;

Share this on:

Download PDFDownload PDF

Tags: , , , , , , , , , , , , , , ,

3 Responses to “Fix MySQL connection error – Host ” is blocked because of many connection errors; unblock with ‘mysqladmin flush-hosts’”

  1. web yazılım says:
    Opera 8.50 8.50 Opera 8.50 8.50 Windows XP Windows XP
    Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; en) Opera 8.50

    İyi paylaşım devamını beklerim

    View CommentView Comment
  2. wasi says:
    Google Chrome 52.0.2743.82 Google Chrome 52.0.2743.82 Windows 10 x64 Edition Windows 10 x64 Edition
    Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/52.0.2743.82 Safari/537.36

    Hi,

    Can you tell me where does mysql stores the current count of connection errors? I know total allowed errors is saved in max_connect_errors, I want to know current count of connection errors to check anyone is trying to connect with wrong password or not.

    View CommentView Comment
    • admin says:
      Opera 37.0.2178.54 Opera 37.0.2178.54 Mac OS X 10.8.5 Mac OS X 10.8.5
      Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_5) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/50.0.2661.102 Safari/537.36 OPR/37.0.2178.54

      Play with show connections; un mysql cli

      try

      help show;

      and check the arguments.

      Regards,

      Georgi

      View CommentView Comment

Leave a Reply

CommentLuv badge