Shulou(Shulou.com)06/01 Report--

The level of originality is limited, please point out if there is any mistake.

Source code version 5.7.14

Today, a friend in the group made the following mistake:

ERROR 1129 (HY000): Host '10.0.0.8' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'

To find the cause of this problem, first find the error code in the source code

Click (here) to collapse or open

{"ER_HOST_IS_BLOCKED", 1129, "Host\'%-.64s\'is blocked because of many connection errors; unblock with\ 'mysqladmin flush-hosts\"} and find the wrong location as follows:

Located in

Of the check_connection () function under sql_connect.cc

Click (here) to collapse or open

If (! (specialflag & SPECIAL_NO_RESOLVE))

{

Int rc

Char * host

LEX_CSTRING main_sctx_host

Rc= ip_to_hostname (& net- > vio- > remote)

Main_sctx_ip.str

& host, & connect_errors); / / accept the return value of ip_to_hostname to rc

.

If (rc = = RC_BLOCKED_HOST) / / determine whether rc is RC_BLOCKED_HOST 1

{

/ * HOST_CACHE stats updated by ip_to_hostname (). , /

My_error (ER_HOST_IS_BLOCKED, MYF (0))

Thd- > m_main_security_ctx.host_or_ip () .str)

Return 1

}

} here if rc = = RC_BLOCKED_HOST RC_BLOCKED_HOST is a macro defined as 1

# define RC_BLOCKED_HOST 1

If it is true, the error will be thrown. Next we need to see that rc is the return value of the function ip_to_hostname.

In the ip_to_hostname function of hostname.cc

Click (here) to collapse or open

If (! (specialflag & SPECIAL_NO_HOST_CACHE))

{

Mysql_mutex_lock (& hostname_cache- > lock); / / notice here that the whole process of searching in cache has MUTEX.

Host_entry * entry= hostname_cache_search (ip_key); / / search in cache

If (entry)

{

Entry- > massively lastseen = now

* connect_errors= entry- > m_errors.m_connect

If (entry- > m_errors.m_connect > = max_connect_errors) / / max_connect_errors is the parameter we set.

{

Entry- > massively errors.masking hostworthy employees

Entry- > set_error_timestamps (now)

Mysql_mutex_unlock (& hostname_cache- > lock); / / unlock here

DBUG_RETURN (RC_BLOCKED_HOST)

} here we have found some facts related to this error:

1. Max_connect_errors is related to this error report.

2. SPECIAL_NO_RESOLVE is related to this error report.

3. SPECIAL_NO_HOST_CACHE is related to this error report.

The max_connect_errors parameter does not need to be interpreted. If! (specialflag & SPECIAL_NO_RESOLVE) returns false, it will not be called.

Ip_to_hostname does IP domain name anti-resolution. If! (specialflag & SPECIAL_NO_HOST_CACHE) returns false, if will not be called.

What will be wrong in the future. In other words, the bit and then reverse here is related to whether the error is triggered, so we need to see if it is necessary.

The relationship between specialflag and SPECIAL_NO_RESOLVE and SPECIAL_NO_HOST_CACHE.

The following are the source code parameters

MYSQLD.CC

Click (here) to collapse or open

Case (int) OPT_SKIP_HOST_CACHE:

Opt_specialflag | = SPECIAL_NO_HOST_CACHE

Break

Case (int) OPT_SKIP_RESOLVE:

Opt_skip_name_resolve= 1

Opt_specialflag | = SPECIAL_NO_RESOLVE

Break; is actually controlled by OPT_SKIP_HOST_CACHE and OPT_SKIP_RESOLVE, which is our parameter.

Skip-host-cache

Skip-name-resolve

So we can at least solve the problem through skip-host-cache and skip-name-resolve, but it also brings a problem.

Domain names will not be used when establishing users. Friends who have a foundation in UNIX network programming should know that the client connects through socket.

What the server gets is the IP address and the port of the client. If anti-parsing of IP- > DNS is turned off, users like user@domain will be

What cannot be connected can only be user@ip, which is extremely important. It could have been connected:

Create user testuuu@'test' identified by '123'

/ etc/hosts is set to:

192.168.190.60 test

Root@test1 ~] # mysql-utestuuu-p123-h292.168.190.93-P13001

Warning: Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 17

Server version: 5.7.14-7-debug-log Source distribution

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

Affiliates. Other names may be trademarks of their respective

Owners.

Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

Mysql > exit

However, after setting skip-host-cache and skip-name-resolve,

[root@test1] # mysql-utestuuu-p123-h292.168.190.93-P13001

Warning: Using a password on the command line interface can be insecure.

ERROR 1045 (28000): Access denied for user 'testuuu'@'192.168.190.60' (using password: YES)

Obviously there is no reverse parsing through 192.168.190.60.

In fact, a lot of parsing errors are reported in the function ip_to_hostname function, which would not have happened if skip-name-resolve

It includes the following:

Sql_print_warning ("IP address'% s' could not be resolved:% s", ip_key,gai_strerror (err_code))

Sql_print_warning ("IP address'% s' has been resolved"to the hostname'% s', which resembles"IPv4-address itself.", ip_key,hostname_buffer)

Sql_print_warning ("Host name'% s' could not be resolved:% s", hostname_buffer,gai_strerror (err_code))

Sql_print_warning ("Hostname'% s' does not resolve to'% sails.", hostname_buffer,ip_key)

If you encounter a similar problem, set up skip-name-resolve first in case of emergency.

Note:

1 、

The following users may also appear throughout the parsing period

Unauthenticated user

That is, the TCP/IP handshake has been successful, the data has begun to interact, and the thread has been established, but the user has not been authenticated by MYSQL permissions (self-understanding whether this conclusion is correct or not).

Mysql > show processlist

+- -+

| | Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined |

+- -+

| | 5 | unauthenticated user | 192.168.190.60 Connect 12770 | NULL | Connect | 35 | login | NULL | 0 | 0 |

| | 6 | root | localhost | NULL | Query | 0 | starting | show processlist | 0 | 0 |

+- -+

If this occurs, users should give priority to the problem of slow DNS parsing, such as enabling mysql anti-parsing and not setting an appropriate / etc/hosts.

2. During the test, skip-name-resolve was not closed. First, user@ip was used to connect, and then the user was deleted to establish a user@domain.

And / etc/hosts is set. At this time, the client cannot connect to the server and keeps reporting the wrong password. This problem will be solved after flush hosts. This is

Because the domain name obtained when I first used IP anti-resolution is not the same as the domain name I set up / etc/hosts, although I got the correct IP, but in the

In host cache, the resulting domain is not consistent, that is to say, username@domain1 is inversely parsed to username@domain2

Of course, the corresponding permission user record cannot be found in the mysql.user. At this time, the host cache is emptied after the flush hosts, so it can be redone.

It is normal after generating the corresponding host cache, which is shown in the previous code

Host_entry * entry= hostname_cache_search (ip_key); / / search in cache

There is also a manifestation.

3. Select * from performance_schema.host_cache. The class capacity of IP- > DOMAIN is recorded here.

As follows:

Mysql > select * from performance_schema.host_cache\ G

* * 1. Row *

IP: 192.168.190.60

HOST: test

HOST_VALIDATED: YES

.

FIRST_SEEN: 2017-05-31 17:17:40

LAST_SEEN: 2017-05-31 17:17:40

FIRST_ERROR_SEEN: NULL

LAST_ERROR_SEEN: NULL

4. The function of inverse analysis.

As mentioned earlier, the purpose of anti-parsing IP- > DOMAIN is when the following users are established

Create user testuuu@'test' identified by '123'

MYSQL can judge his permission information by ip address, because what is stored in MYSQL.USER is the domain name (DOMAIN), and in order to speed up the speed of anti-resolution, there is HOST CACHE.

That's

Connect for the first time

1. Get the client IP address (socket connection client IP address information)

2. Perform DNS reverse parsing (such as / etc/hosts)

3. Store anti-parsing information to host cache

4. Get the anti-parsed domain from host cache for permission verification

If it is not the first time to connect and skip the second step, it is precisely because the second step has been skipped, resulting in the problem in note 2

Flush host is here to clean up host cache so as to solve this problem and redo DNS anti-parsing.

In fact, the whole process of locating the problem is relatively simple, but I haven't found out when entry- > m_errors.m_connect is added, and in the source code

There are still a lot of places I don't understand because I didn't look at it carefully (because it takes a lot of time). Here I can only throw bricks and record it at the same time.

My analytical process.

The following is an explanation of the MYSQL official manual:

9.12.6.2 DNS Lookup Optimization and the Host Cache

The server handles entries in the host cache like this:

1. When the first TCP client connection reaches the server from a given IP address, a new entry is

Created to record the client IP, host name, and client lookup validation flag. Initially, the host name is

Set to NULLand the flag is false. This entry is also used for subsequent client connections from the

Same originating IP.

2. If the validation flag for the client IP entry is false, the server attempts an IP-to-host name DNS

Resolution. If that is successful, the host name is updated with the resolved host name and the

Validation flag is set to true. If resolution is unsuccessful, the action taken depends on whether the error

Is permanent or transient. For permanent failures, the host name remains NULLand the validation flag

Is set to true. For transient failures, the host name and validation flag remain unchanged. (Another DNS

Resolution attempt occurs the next time a client connects from this IP.)

3. If an error occurs while processing an incoming client connection from a given IP address, the server

Updates the corresponding error counters in the entry for that IP. For a description of the errors

Recorded, see Section 23.9.16.1, "The host_cache Table".

The server uses the host cache for several purposes:

? By caching the results of IP-to-host name lookups, the server avoids doing a DNS lookup for each client

Connection. Instead, for a given host, it needs to perform a lookup only for the first connection from that

Host.

? The cache contains information about errors that occur during the connection process. Some errors are

Considered "blocking." If too many of these occur successively from a given host without a successful

Connection, the server blocks further connections from that host. The max_connect_errorssystem

Variable determines the number of permitted errors before blocking occurs. See Section B.5.2.6, "Host"

'host_name' is blocked'.

To unblock blocked hosts, flush the host cache by issuing a FLUSH HOSTSstatement or executing a

Mysqladmin flush-hostscommand.

Author Wechat:

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.