Why MySQL connection is blocked of many connection

问题:

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.

回答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

回答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";