Category Archives: MySQL

Database ErrorHost ‘192.168.1.10’ is blocked because of many connection errors; unblock with ‘mysqladmin flush-hosts’

Error Database Error host is blocked in MariaDB or MySQL

Error:

Database ErrorHost '192.168.1.10' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'

Cause:

MySQL/MariaDB has receive many fail/error connection requests from the given host if the number exceeds the value of the max_connect_errors parameter defined value. Then the host is blocked by Mysql/MariaDB to avoid connection for security purpose.

Solution: Three options to overcome this problem:

Increase the size of the max_connect_errors parameter:

-- Check the current Value
MariaDB [test]> show variables like 'max_connect_errors';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_connect_errors | 100   |
+--------------------+-------+
1 row in set (0.001 sec)

--Increase the value of Max_connect_errors value:

MariaDB [test]> SET GLOBAL max_connect_errors=10000;
Query OK, 0 rows affected (0.000 sec)

Second option: Run the FLUSH HOSTS command:

Note: On checking this command don’t disconnect the existing connection

-- Run Flush hosts by connecting with ROOT user
mysql -u root -p -e 'flush hosts'
OR
FLUSH HOSTS;

Example:

C:\WINDOWS\system32>mysql -u root -p
Enter password: ********
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 23
Server version: 10.6.5-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> flush hosts;
Query OK, 0 rows affected (0.000 sec)

Tested existing connection but it remain same:

Session 1:
 select * from information_schema.processlist;


ID|USER|HOST           |DB           |COMMAND|TIME|STATE  
--+----+---------------+-------------+-------+----+-------
17|TEST|localhost:53155|             |Sleep  |  13|       
16|TEST|localhost:53123|             |Sleep  |  62|       
15|TEST|localhost:53122|             |Sleep  | 166|       
10|root|localhost:54038|classicmodels|Query  |   0|Filling
 9|root|localhost:54036|             |Sleep  |   3|       
 8|root|localhost:54034|             |Sleep  |2789|       
 7|root|localhost:53851|             |Sleep  |  20|       
 6|root|localhost:53839|test         |Sleep  |2758|       

Session 2: FLUSH HOSTS;

Session 1: Remain the same session:
ID|USER|HOST           |DB           |COMMAND|TIME
--+----+---------------+-------------+-------+----
23|root|localhost:53507|             |Sleep  | 234
17|TEST|localhost:53155|             |Sleep  | 936
16|TEST|localhost:53123|             |Sleep  | 985
15|TEST|localhost:53122|             |Sleep  |1088
10|root|localhost:54038|classicmodels|Query  |   0
 9|root|localhost:54036|             |Sleep  | 508
 8|root|localhost:54034|             |Sleep  |3711
 6|root|localhost:53839|test         |Sleep  | 372

Third Option: Check the host_cache table in the performance_Schema of the database. You can delete or truncate the table as you need. Delete the specified row which you got in error.