MySQL appears unblock with ' mysqladmin flush-hosts '

Source: Internet
Author: User
Tags flush



A friend sent a message, said a system application log in when prompted to connect timeout, let help handle it.
Ask him if the application and the database are normal, reply that the database seems to have no problem, but the application log is unable to connect to the database.



Database version is: 5.5.53




Let him telnet the database is a pass, the reply does not make sense, and a message prompt:


# telnet  8.8.9.9 3306
Trying 8.8.9.9...
Connected to 8.8.9.9.
Escape character is ‘^]‘.
gHost ‘kapp‘ is blocked because of many connection errors; unblock with ‘mysqladmin flush-hosts‘Connection closed by foreign host.





As you can see, the reasons are:



Congestion caused by the same IP in a short period of time resulting in too many (exceeding the maximum value of MySQL database max_connect_errors) interrupted database connections






Let him view the following parameters concurrently to result:


> show variables like ‘max_connect_errors‘;
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_connect_errors | 2     |
+--------------------+-------+





Max_connect_errors is a security-related counter value in MySQL that is responsible for blocking excessive attempts by clients that fail to prevent brute-force password violations.
The value of max_connect_errors does not have much to do with performance, the default is 10, and I do not know why it was changed to 2. means that if a client tries to connect to this MySQL server, but fails (such as a password error, and so on) 2 times, MySQL will unconditionally force this client connection to be blocked.
If you want to reset the value of this counter, you must restart the MySQL server or execute the mysql> flush hosts; Command. When this client successfully connects to a MySQL server, the max_connect_errors for this client are zeroed out.
If the max_connect_errors setting is too small, the Web page may prompt that the database server cannot be connected.






Here is the solution, then paste it.



Solution 1: Modify the value of the Max_connection_errors
(1) Go to MySQL database to view max_connection_errors:
> Show variables like '%max_connect_errors% ';
(2) Modify the value of the max_connection_errors:
> Set global max_connect_errors = 100;
(3) See if the modification was successful
> Show variables like '%max_connect_errors% ';



Solution 2: Clean up the Hosts file by using the Mysqladmin flush-hosts command
(1) Use the command in the found directory to modify: Mysqladmin-u xxx-p flush-hosts
Note: The Master/slave master-slave database is configured to modify the main library and the library.
Or
> Flush hosts;




Workaround 3: Restart Mysqld
You can also increase the parameter in the configuration file before restarting.
# VI/ETC/MY.CNF
max_connect_errors = 100






MySQL appears unblock with ' mysqladmin flush-hosts '


Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.