Mysql-explanation of host name and IP address resolution-[Warning] IPaddressxxxcouldnotbe_MySQL

Mysql-explanation of host name and IP address resolution-[Warning] IPaddressxxxcouldnotberesolved: Nameorservicenotknown bitsCN.com

Symptom:

When the program connects to mysql, mysql error. log prompts:

[Warning] IP address '10. 0.0.220 'cocould not be resolved: Name or service not known

Cause:

The Mysql database server does not have/etc/hosts configured or DNS services. as a result, the mysqld thread fails to parse the host name corresponding to the IP address.

References:

Mysql domain name resolution:

When a new client tries to create a connection with mysqld, mysqld generates a new thread to process this request. The new thread first checks whether the host name of the request to establish a connection is in the Mysql host name buffer. if not, the thread tries to parse the host name of the request connection.

The parsing logic is as follows:

A. The Mysql thread uses gethostbyaddr () to resolve the obtained IP address to a host name, and then uses gethostbyname () to resolve the obtained host name to an IP address to ensure the accuracy of the ing between the host name and the IP address;

B. If the operating system supports gethostbyaddr_r () and gethostbyname_r () calls of the security process, the Mysqld thread can use them to optimize host name resolution;

C. If the operating system does not support secure thread calls, the Mysqld process first implements a mutex lock and then calls gethostbyaddr () and gethostbyname () to parse the host name. At this time, before the first process releases the host name of the host name buffer pool, other processes cannot parse the host name again; <------- host name stated here in the MySQL manual, it indicates the relationship between the same IP address and the corresponding first host name.

When the mysqld process is started, you can use the -- skip-name-resolve parameter to disable the DNS host name resolution function. after disabling this function, you can only use IP addresses in the MySQL authorization table.

If the DNS in your environment is very slow or there are many hosts, you can improve the database response efficiency by disabling the DNS resolution function-skip-name-resolve or increasing the HOST_CACHE_SIZE.

How to disable host name Buffering: Use the -- skip-host-cache parameter; refresh the host name buffer: execute flush hosts or execute mysqladmin flush-hosts;

Disable TCP/IP connection: Use the -- skip-networking parameter.

Lab:

# Grep 192.168.1.1/etc/hosts

192.168.1.1 hostname_online

SQL> grant usage on *. * to root @ 'H _ tt _ % 'identified by 'root ';

SQL> flush hosts;

# Mysql-h 192.168.1.1-uroot-proot

ERROR 1045 (28000): Access denied for user 'root' @ 'hostname _ online' (using password: YES) # resolve the IP address to hostname_online instead of h_tt _ %, access rejected.

# Grep 192.168.1.1/etc/hosts

192.168.1.1 hostname_online

192.168.1.1 h_tt_1

# Mysql-h 192.168.1.1-uroot-proot

ERROR 1045 (28000): Access denied for user 'root' @ 'hostname _ online' (using password: YES) #### mysqld does not refresh the IP address and host name information in the host pool. in this case, the IP address corresponds to hostname_online.

SQL> flush hosts;

# Mysql-h 192.168.1.1-uroot-proot

ERROR 1045 (28000): Access denied for user 'root' @ 'hostname _ online' (using password: YES) #### mysqld does not parse the host name relationship corresponding to the same IP address in/etc/hosts

# Grep 192.168.1.1/etc/hosts

192.168.1.1 h_tt_1

192.168.1.1 hostname_online

SQL> flush hosts;

# Mysql-h 192.168.1.1-uroot-proot

SQL> exit

[Experiment:] after verifying the relationship between the first host name corresponding to the same IP address, the same IP address will not be resolved:

SQL> grant usage on *. * to root @ 'H _ tt _ % 'identified by 'root ';

SQL> flush hosts;

# Grep h_tt/etc/hosts

192.168.1.1hostname _ online 192.168.1.1h _ tt_1

192.168.1.1h _ tt_1 192,168.1 .2h _ tt_1

Access to mysql is denied; access to mysql from both IP addresses is allowed.

[Conclusion]

This experiment verifies the explanation of "How mysql Uses DNS" in the MySQL manual above.

That is, mysqld thread resolution/etc/hosts is a unique identifier, which corresponds to multiple host names in a timely manner. However, mysqld thread only resolves the first ing relationship, the Mysqld process does not parse the logs of different host names corresponding to this IP address.

[Applicable environment :]

No DNS server, there are many hosts, or you do not want to maintain the list of IP addresses and host names manually configured in/etc/hosts, the host name can be "%" or the IP address and host name resolution function (-- skip-name-resolve) can be disabled during mysql authorization ).

BitsCN.com