unblock with ‘mysqladmin flush-hosts’

How to Solve this error:

1. View the number of connections

   Max_used_connections / max_connections * 100% ≈ 85%

   General service The maximum setting of 1000 or less is almost the same

2. View the maximum error connection settings
3. Is it a lock table

 

 

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;

– See more at: http://pc-freak.net/blog/host-blocked-connection-errors-unblock-mysqladmin-flushhosts/#sthash.5Gx5ukgE.dpuf

http://pc-freak.net/blog/host-blocked-connection-errors-unblock-mysqladmin-flushhosts/