Why MySQL connection is blocked of many connection errors?

Question

As you can see I have a problem on a database connection. It gives me this error:

...is blocked because of many connection errors

I searched some answers but I couldn't solve my problem.

I don't know if I gave all the information that you need, so if you need something else, just tell me. I have a database connection from different computers and I had a user created to access the database but it had % in the hosts row, so I wanted to change it with an IP address for security issues and it gave me this error so now I'm stuck.

Bitner

i've tried to reconfigure mysql connection , i've tried to create a new user nothing worked i dont know what ells to do – Bitner

Answer 1

MySQL blocks clients which error made while connecting to protect MySQL from malformed client.

So first, you need to find what sort of error is....

You might check MySQL error log in data directory. (typically hostname.err)

Or, you can increase max_connect_errors (what is current value?) maximum value depends on architecture. on 32 bit, 4294967295. 18446744073709547520 for 64 bit. (Manual)

mysql> SET GLOBAL max_connect_errors = 100000000;

But this is not real solution if error is frequently occurred.

FLUSH HOSTS can help you to eliminate blocked host right now.

mysql> FLUSH HOSTS;

If want to run from outside mysql console then use mysqladmin command:

# mysqladmin flush-hosts

Drava

yeah flush hosts could help me but i run this line of code ( command ) in the database program console? im using navicat btw – Bitner

yes. I guess you can run it using not only mysql console but also any mysql client (even if GUI). why don't you try. – Drava

@Bitner default value of max_connection_errors is only 10. I guess after you flushed, it reached 10. (but I'm not sure...) I suggest that increasing max_connection_errors. dev.mysql.com/doc/refman/5.5/en/blocked-host.html – Drava

I can't connect mysql command line. how can I use "flush hosts"? – Dawnedawson

@AhmetMelihBaşbuğ mysqladmin -u username -p flush-hosts replace "username" with your MySQL user and enter the password when it asks. – Guillerminaguillermo

@AhmetMelihBaşbuğ simply run FLUSH HOSTS; – Garzon

One important hint: Setting the max_connect_errors via SET GLOBAL will not persist after a restart so this change might need to be added to the MySQL conf too. MariaDB increased this value in v10 from a default of 10 to 100 to have some kind of reference for a reasonable value. – Vertievertiginous

Answer 2

First flush hosts local MySQL using following command:

mysqladmin -u [username] -p flush-hosts
**** [MySQL password]

or

mysqladmin flush-hosts -u [username] -p
**** [MySQL password]

network MySQL server:

mysqladmin -h <ENDPOINT URL> -P <PORT> -u <USER> -p flush-hosts
mysqladmin -h [END POINT URL] -P 3306 -u [DB USER] -p flush-hosts 

In additional suggestion you can permanently solve blocked of many connections error problem by editing my.ini file[Mysql configuration file]

change variables max_connections = 10000;

or

login into MySQL using command line -

mysql -u [username] -p
**** [MySQL password]

put the below command into MySQL window

SET GLOBAL max_connect_errors=10000;
set global max_connections = 200;

check veritable using command-

show variables like "max_connections";
show variables like "max_connect_errors";

Donnell