iTranslated by AI
MySQL Connection-Related Errors
I was wondering when the error Host '〜' is blocked because of many connection errors appears in MySQL, so I decided to look into the connection-related behavior.
Blocking due to consecutive connection failures per client
The manual https://dev.mysql.com/doc/refman/8.0/en/blocked-host.html states the following (DeepL translation):
The value of the
max_connect_errorssystem variable determines how many consecutive interrupted connection requests are permitted. Aftermax_connect_errorsfailed requests without a successful connection,mysqlddetermines that something is wrong (such as someone trying to break in) and blocks the host from further connections until the host cache is flushed using aFLUSH HOSTSstatement, aTRUNCATE TABLEstatement that truncates the Performance Schemahost_cachetable, or amysqladmin flush-hostscommand.
At first glance, this might look like it includes authentication failures, but it appears they are not.
Therefore, a client won't be blocked regardless of how many times it fails authentication.
The default value of max_connect_errors is 100 (it was 10 before MySQL 5.6), and since I don't want to try that many times, I'll set it to 3.
mysql> set global max_connect_errors=3
Let's try connecting from a random client with an incorrect password.
~% mysql -u test -h 192.168.68.1 -pinvalid
ERROR 1045 (28000): Access denied for user 'test'@'hogehoge' (using password: YES)
~% mysql -u test -h 192.168.68.1 -pinvalid
ERROR 1045 (28000): Access denied for user 'test'@'hogehoge' (using password: YES)
~% mysql -u test -h 192.168.68.1 -pinvalid
ERROR 1045 (28000): Access denied for user 'test'@'hogehoge' (using password: YES)
~% mysql -u test -h 192.168.68.1 -pinvalid
ERROR 1045 (28000): Access denied for user 'test'@'hogehoge' (using password: YES)
~% mysql -u test -h 192.168.68.1 -pinvalid
ERROR 1045 (28000): Access denied for user 'test'@'hogehoge' (using password: YES)
It is not blocked even after five attempts.
Looking at performance_schema.host_cache, it is recorded in COUNT_AUTHENTICATION_ERRORS.
mysql> select * from host_cache\G
*************************** 1. row ***************************
IP: 192.168.68.103
HOST: hogehoge
HOST_VALIDATED: YES
SUM_CONNECT_ERRORS: 0
COUNT_HOST_BLOCKED_ERRORS: 0
COUNT_NAMEINFO_TRANSIENT_ERRORS: 0
COUNT_NAMEINFO_PERMANENT_ERRORS: 0
COUNT_FORMAT_ERRORS: 0
COUNT_ADDRINFO_TRANSIENT_ERRORS: 0
COUNT_ADDRINFO_PERMANENT_ERRORS: 0
COUNT_FCRDNS_ERRORS: 0
COUNT_HOST_ACL_ERRORS: 0
COUNT_NO_AUTH_PLUGIN_ERRORS: 0
COUNT_AUTH_PLUGIN_ERRORS: 0
COUNT_HANDSHAKE_ERRORS: 0
COUNT_PROXY_USER_ERRORS: 0
COUNT_PROXY_USER_ACL_ERRORS: 0
COUNT_AUTHENTICATION_ERRORS: 5
COUNT_SSL_ERRORS: 0
COUNT_MAX_USER_CONNECTIONS_ERRORS: 0
COUNT_MAX_USER_CONNECTIONS_PER_HOUR_ERRORS: 0
COUNT_DEFAULT_DATABASE_ERRORS: 0
COUNT_INIT_CONNECT_ERRORS: 0
COUNT_LOCAL_ERRORS: 0
COUNT_UNKNOWN_ERRORS: 0
FIRST_SEEN: 2020-09-07 00:14:15
LAST_SEEN: 2020-09-07 00:14:19
FIRST_ERROR_SEEN: 2020-09-07 00:14:15
LAST_ERROR_SEEN: 2020-09-07 00:14:19
1 row in set (0.00 sec)
So, when does it actually get blocked? It seems to happen when the initial connection protocol does not complete.
If you try connecting to port 3306 and then immediately disconnecting three times, the host will be blocked on the next connection attempt.
~% ruby -rsocket -e 'TCPSocket.new("192.168.68.103", 3306)'
~% ruby -rsocket -e 'TCPSocket.new("192.168.68.103", 3306)'
~% ruby -rsocket -e 'TCPSocket.new("192.168.68.103", 3306)'
~% mysql -u test -h 192.168.68.1 -pinvalid
ERROR 1129 (HY000): Host '192.168.68.103' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'
~%
host_cache looks like this:
mysql> select * from host_cache\G
*************************** 1. row ***************************
IP: 192.168.68.103
HOST: hogehoge
HOST_VALIDATED: YES
SUM_CONNECT_ERRORS: 3
COUNT_HOST_BLOCKED_ERRORS: 1
COUNT_NAMEINFO_TRANSIENT_ERRORS: 0
COUNT_NAMEINFO_PERMANENT_ERRORS: 0
COUNT_FORMAT_ERRORS: 0
COUNT_ADDRINFO_TRANSIENT_ERRORS: 0
COUNT_ADDRINFO_PERMANENT_ERRORS: 0
COUNT_FCRDNS_ERRORS: 0
COUNT_HOST_ACL_ERRORS: 0
COUNT_NO_AUTH_PLUGIN_ERRORS: 0
COUNT_AUTH_PLUGIN_ERRORS: 0
COUNT_HANDSHAKE_ERRORS: 3
COUNT_PROXY_USER_ERRORS: 0
COUNT_PROXY_USER_ACL_ERRORS: 0
COUNT_AUTHENTICATION_ERRORS: 0
COUNT_SSL_ERRORS: 0
COUNT_MAX_USER_CONNECTIONS_ERRORS: 0
COUNT_MAX_USER_CONNECTIONS_PER_HOUR_ERRORS: 0
COUNT_DEFAULT_DATABASE_ERRORS: 0
COUNT_INIT_CONNECT_ERRORS: 0
COUNT_LOCAL_ERRORS: 0
COUNT_UNKNOWN_ERRORS: 0
FIRST_SEEN: 2020-09-07 00:21:52
LAST_SEEN: 2020-09-07 00:22:04
FIRST_ERROR_SEEN: 2020-09-07 00:21:52
LAST_ERROR_SEEN: 2020-09-07 00:22:04
1 row in set (0.00 sec)
The number of connections that were disconnected immediately after connecting is counted in SUM_CONNECT_ERRORS and COUNT_HANDSHAKE_ERRORS. It seems that the host is blocked when SUM_CONNECT_ERRORS reaches max_connect_errors. I took a quick look at the source code, and it seems that SUM_CONNECT_ERRORS and COUNT_HANDSHAKE_ERRORS are set to the same value... though they might be different.
The number of connection attempts made after being blocked is counted as COUNT_HOST_BLOCKED_ERRORS.
By the way, loopback addresses such as 127.0.0.1 and ::1 are not subject to blocking. This mechanism seems to be for blocking suspicious connections from external sources that do not follow the MySQL protocol; it is powerless against attacks that properly follow the MySQL protocol.
Note that this relies on the mechanism that caches name resolution for clients, so it won't happen in the first place if skip_name_resolve is enabled.
In reality, many environments probably have skip_name_resolve enabled. It's faster because it skips name resolution.
So, I'm still a bit skeptical about how much this mechanism really contributes to security.
Consecutive authentication failures per username
I learned about this while researching this time, but if the connection_control plugin is installed, repeatedly attempting connections that "properly follow the MySQL protocol but fail authentication" will gradually increase the time it takes to return an error.
~% time mysql -u test -h 127.0.0.1
ERROR 1045 (28000): Access denied for user 'test'@'localhost' (using password: NO)
real 0m0.060s
user 0m0.027s
sys 0m0.009s
~% time mysql -u test -h 127.0.0.1
ERROR 1045 (28000): Access denied for user 'test'@'localhost' (using password: NO)
real 0m0.027s
user 0m0.012s
sys 0m0.009s
~% time mysql -u test -h 127.0.0.1
ERROR 1045 (28000): Access denied for user 'test'@'localhost' (using password: NO)
real 0m0.042s
user 0m0.011s
sys 0m0.012s
~% time mysql -u test -h 127.0.0.1
ERROR 1045 (28000): Access denied for user 'test'@'localhost' (using password: NO)
real 0m1.017s
user 0m0.000s
sys 0m0.013s
~% time mysql -u test -h 127.0.0.1
ERROR 1045 (28000): Access denied for user 'test'@'localhost' (using password: NO)
real 0m2.036s
user 0m0.016s
sys 0m0.004s
~% time mysql -u test -h 127.0.0.1
ERROR 1045 (28000): Access denied for user 'test'@'localhost' (using password: NO)
real 0m3.016s
user 0m0.004s
sys 0m0.008s
~% time mysql -u test -h 127.0.0.1
ERROR 1045 (28000): Access denied for user 'test'@'localhost' (using password: NO)
real 0m4.049s
user 0m0.017s
sys 0m0.014s
~% time mysql -u test -h 127.0.0.1 -p******** -e ''
mysql: [Warning] Using a password on the command line interface can be insecure.
real 0m5.027s
user 0m0.009s
sys 0m0.009s
~%
Errors are returned immediately for the first three attempts, but from the fourth attempt onwards, the delay increases by one second each time. Since it takes time even with the correct password, it seems to be sleeping before the authentication process.
Unlike host blocking, this is effective even for 127.0.0.1, and it tracks the number of authentication failures per username rather than per client. Even with the same client, a different username will return an error immediately, and connections from different clients will share the failure count if they use the same username. It even works for usernames that don't exist in MySQL.
For example, if authentication fails five times for the username test from Client A, trying to connect with the same username test from Client B will result in a 3-second wait, while connecting with the username test2 from Client A will not result in a wait.
By default, the connection_control plugin is not installed, so there is no delay no matter how many times authentication fails.
Aborted_clients and Aborted_connects
There are status variables called Aborted_clients and Aborted_connects. I always find myself looking up the difference and forgetting it immediately, so I'll make a note of it here.
mysql> show status like 'aborted%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| Aborted_clients | 0 |
| Aborted_connects | 0 |
+------------------+-------+
2 rows in set (0.00 sec)
Well, the answer is written in the manual: https://dev.mysql.com/doc/refman/8.0/en/communication-errors.html
Aborted_clients is incremented when a TCP connection is disconnected without a termination protocol after a successful connection. This happens, for example, if the client program crashes, the connection is forcefully terminated with KILL, or the connection is closed because no queries were issued within the wait_timeout period.
Aborted_connects is incremented when a connection attempt fails. This includes cases where the protocol is invalid (such as connecting and immediately disconnecting) or when authentication fails. This also applies to connections from 127.0.0.1.
Discussion