|
Recently, a MySQL server was encountered due to some special factors“ERROR 1129 (00000): Host 'xxx' is blocked because of many connection errors. Unblock with 'mysqladmin flush-hosts'”After the problem was solved, in the process of learning more about the parameter max_connect_errors, some contradictory descriptions of different network data did confuse me a little (about this error, the essential reason is that the same IP generated too many interrupted database connections (exceeding the maximum value of max_connect_errors) in a short period of time, and the following is a process of exploring my problems, analyzing problems, and clarifying doubts. First of all, I searched some information on the Internet, many of which swear to introduce that if the number of password input attempts exceeds max_connect_errors variables, MySQL will block this client login, and then I found the official information about the introduction of max_connect_errors, as shown below, MySQL 5.6/5.7 is the same If more than this many successive connection requests from a host are interrupted without a successful connection, the server blocks that host from further connections. You can unblock blocked hosts by flushing the host cache. To do so, issue a FLUSH HOSTS statement or execute a mysqladmin flush-hosts command. If a connection is established successfully within fewer than max_connect_errors attempts after a previous connection was interrupted, the error count for the host is cleared to zero. However, once a host is blocked, flushing the host cache is the only way to unblock it. The default is 100. As shown above, the translation is roughly as follows: If the MySQL server receives consecutive requests from the same host, and all of these consecutive requests are interrupted without successfully establishing a connection, when the cumulative value of these consecutive requests is greater than the max_connect_errors set value, the MySQL server will block all subsequent requests from this host. I believe that when you see this information at the beginning, you will also be attacked“many successive connection requests from a host are interrupted without a successful connection”Confused, in fact, this is because the database connection is aborted due to network abnormalities. I searched for such information on the Internet: There seems to be confusion around that variable. It does not really block hosts for repeated invalid passwords but for aborted connections due to network errors. Well, then we can experiment and verify it ourselves to find out which one is correct. Create a test account in the MySQL database, and then we set the max_connect_errors variable to 3. Then we use another test machine to connect to the MySQL database with the wrong password, as shown below, even if the previous three wrong passwords are entered, the fourth input does not encounter the above error.Then you can rule out that this variable has something to do with the wrong password entry. [root@mytestlnx02 tmp]# mysql -h10.20.57.24 -utest -p Enter password: ERROR 1045 (28000): Access denied for user 'test'@'mytestlnx02' (using password: YES) [root@mytestlnx02 tmp]# mysql -h10.20.57.24 -utest -p Enter password: ERROR 1045 (28000): Access denied for user 'test'@'mytestlnx02' (using password: YES) [root@mytestlnx02 tmp]# mysql -h10.20.57.24 -utest -p Enter password: ERROR 1045 (28000): Access denied for user 'test'@'mytestlnx02' (using password: YES) [root@mytestlnx02 tmp]# mysql -h10.20.57.24 -utest -p Enter password: ERROR 1045 (28000): Access denied for user 'test'@'mytestlnx02' (using password: YES) [root@mytestlnx02 tmp] # In fact, if an IP enters an incorrect password, MySQL will record it in the host_cache table under the performance_schema database. It is cumulatively recorded in COUNT_AUTHENTICATION_ERRORS fields as follows:
According to the official information, the host_cache field is statistically regarded as“Blockage”of connection errors (assessed based on max_connect_errors system variables). Only protocol handshake errors are counted and are only used for authenticated hosts (HOST_VALIDATED = YES). SUM_CONNECT_ERRORS The number of connection errors that are deemed “blocking” (assessed against themax_connect_errorssystem variable). Only protocol handshake errors are counted, and only for hosts that passed validation (HOST_VALIDATED = YES). MySQLThe client needs to initiate a three-time handshake protocol to establish a connection with the database, under normal circumstances, this time is very short, but once the network abnormality, network timeout and other factors appear, it will cause the handshake protocol to be unable to complete, MySQL has a parameter connect_timeout, it is the time for the MySQL server process mysqld to wait for the connection to be established, in seconds. If the protocol handshake is still not completed after the connect_timeout time frame, the MySQL client will receive an exception with an exception message similar to: Lost connection to MySQL server at 'XXX', system error: errno, the variable defaults to 10 seconds:
Let's construct a case where the database connection is interrupted caused by network timeout, we use the netem and tc commands in Linux to simulate the network transmission delay case in a complex environment, after the following settings, at this time from the test server to access the MySQL server, there will be a delay of 11 seconds: [root@gettestlnx02 ~]# ping 10.20.57.24 PING 10.20.57.24 (10.20.57.24) 56(84) bytes of data. 64 bytes from 10.20.57.24: icmp_seq=1 ttl=62 time=0.251 ms 64 bytes from 10.20.57.24: icmp_seq=2 ttl=62 time=0.330 ms 64 bytes from 10.20.57.24: icmp_seq=3 ttl=62 time=0.362 ms 64 bytes from 10.20.57.24: icmp_seq=4 ttl=62 time=0.316 ms 64 bytes from 10.20.57.24: icmp_seq=5 ttl=62 time=0.281 ms 64 bytes from 10.20.57.24: icmp_seq=6 ttl=62 time=0.377 ms ^C --- 10.20.57.24 ping statistics --- 6 packets transmitted, 6 received, 0% packet loss, time 5716ms rtt min/avg/max/mdev = 0.251/0.319/0.377/0.047 ms [root@gettestlnx02 ~]# tc qdisc add dev eth0 root netem delay 11000ms [root@gettestlnx02 ~]# ping 10.20.57.24 PING 10.20.57.24 (10.20.57.24) 56(84) bytes of data. 64 bytes from 10.20.57.24: icmp_seq=1 ttl=62 time=11000 ms 64 bytes from 10.20.57.24: icmp_seq=2 ttl=62 time=11000 ms 64 bytes from 10.20.57.24: icmp_seq=3 ttl=62 time=11000 ms 64 bytes from 10.20.57.24: icmp_seq=4 ttl=62 time=11000 ms 64 bytes from 10.20.57.24: icmp_seq=5 ttl=62 time=11000 ms 64 bytes from 10.20.57.24: icmp_seq=6 ttl=62 time=11000 ms 64 bytes from 10.20.57.24: icmp_seq=7 ttl=62 time=11000 ms
We connect to the MySQL database on the test server gettestlnx02 as shown below (note that if you are connecting to this server via ssh, it will be quite slow to operate on gettestlnx02 at this time.) Of course, you can also simulate network latency on the MySQL server, or you can make both connect_timeout and network latency smaller) [root@gettestlnx02 ~]# mysql -h10.20.57.24 -utest -p Enter password: ERROR 2013 (HY000): Lost connection to MySQL server at 'reading authorization packet', system error: 0 [root@gettestlnx02 ~] # As shown above, due to the network delay of more than 10 seconds, the connection to MySQL failed, at this time, when you query the host_cache table on the MySQL server, then you will see that the SUM_CONNECT_ERRORS has become 1, and the COUNT_HANDSHAKE_ERRORS has also changed1.
Then we toss like this three times repeatedly, and you will see that the SUM_CONNECT_ERRORS becomes 3, and the COUNT_HANDSHAKE_ERRORS becomes 3. Then we use netem and tc commands to cancel the network latency simulation on the test server, and then go to the test connection to the MySQL database, as shown in the following test: [root@gettestlnx02 ~]# tc qdisc del dev eth0 root netem delay 11000ms [root@gettestlnx02 ~]# mysql -h10.20.57.24 -utest -p Enter password: ERROR 1129 (HY000): Host '192.168.27.180' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts' [root@gettestlnx02 ~] #
At this time, it can be constructed“ERROR 1129 (HY000): Host '192.168.27.180' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'”Wrong. solution Resolved ERROR 1129 (00000): Host 'xxx' is blocked because of many connection errors. There are many ways to get the error Unblock with 'mysqladmin flush-hosts', but some are temporary. The temporary plan is that the indicators do not address the root cause. The key is to fix network errors (which often require consulting network administrators or system administrators) Workaround: 1, set the value of the variable max_connection_errors to a larger value
This temporary solution is just a delay triggering condition for the IP to be prohibited, and in complex cases or high concurrency, it is necessary to set a large value, otherwise it will easily be triggered again. In addition, variables only take effect on the current environment, and will expire if they are restarted. 2: useflush hosts mysql> flush hosts; Query OK, 0 rows affected (0.00 sec) mysql> select * from performance_schema.host_cache; Empty set (0.00 sec) mysql> Of course, you can also use the mysqladmin flush-hosts command to clean up the hosts cache information [root@DB-Server ~]# mysqladmin --port=3306 -uroot -p flush-host Enter password: So what is host cache? The official introduction is as follows: The MySQL server maintains a host cache in memory that contains information about clients: IP address, host name, and error information. The server uses this cache for nonlocal TCP connections. It does not use the cache for TCP connections established using a loopback interface address (127.0.0.1 or ::1), or for connections established using a Unix socket file, named pipe, or shared memory. In simple terms, the MySQL server maintains a cache in memory containing client information: IP address, hostname, error message, etc. The server caches non-local TCP connection information. It does not cache TCP connections made using loopback interface addresses (127.0.0.1 or::1), or connections made using Unix socket files, named pipelines, or shared memory. Host cache information can be queried through the host_cache table in the performance_schema database. 3: Set the variable host_cache_size to0 In fact, I would say that this is the most unreliable solution, just to make the MySQL server not log the host cache information. This method can be completely ignored.
| 
Posted on 4/8/2019 11:01:48 AM
|
|
|
|
