MySQL could not is resolved:name or service not known_mysql

Problem: MySQL DNS anti-solution: Skip-name-resolve

The error log has a similar warning:

1.120119 16:26:04 [Warning] IP address ' 192.168.1.10 ' could the not to resolved:name or service not known
2.120119 16:26:04 [Warning] IP address ' 192.168.1.14 ' could the not to resolved:name or service not known
3.120119 16:26:04 [Warning] IP address ' 192.168.1.17 ' could the not to resolved:name or service not known

Show Processlist finds a large number of connections similar to the following:

1.|592|unauthenticated user|192.168.1.10:35320| null| Connect| |login| null|
2.|593|unauthenticated user|192.168.1.14:35321| null| Connect| |login| null|
3.|594|unauthenticated user|192.168.1.17:35322| null| Connect| |login| null|

The role of the Skip-name-resolve parameter: No more reverse parsing (IP does not reverse the domain name), which can speed up the database response time.

Modify configuration file Add and reboot required:

Copy Code code as follows:

[Mysqld]
Skip-name-resolve

In fact, in [mysqld] the following line to join the Skip-name-resolve restart the MySQL service.

The following is a more detailed explanation:

Phenomenon:

When the program connects MySQL, the MySQL error.log inside prompts:

[Warning] IP address ' 10.0.0.220 ' could is not a resolved:name or service not known

Reason:

The MySQL database server does not have a/etc/hosts configured and does not have a DNS service, causing the parsing to fail when the MYSQLD thread resolves the IP's corresponding host name.

Resources:

MySQL Domain name resolution:

When a new client tries to create a connection with mysqld, MYSQLD produces a new thread to process the request. The new thread first checks to see if the host name of the requesting connection is in the MySQL hostname buffer, and if not, the thread attempts to resolve the host name of the requesting connection.

The logic of parsing is as follows:

A. The MySQL thread resolves the acquired IP address into the host name by GETHOSTBYADDR (), and then resolves the acquired host name to the IP address through the gethostbyname (), guaranteeing the accuracy of the host name and IP address correspondence;

B. If the operating system supports GETHOSTBYADDR_R () and Gethostbyname_r () calls using the security process, the MYSQLD thread can use them to optimize host name resolution;

C. If the operating system does not support thread calls, the MYSQLD process first makes a mutex and then calls Gethostbyaddr () and gethostbyname () to resolve the host name. At this point, before the first process releases the host name of the host name buffer pool, the other process cannot resolve the host name again; <-------the MySQL manual here says host name, which means the same IP address and the corresponding first hostname relationship.

When you start the mysqld process, you can use the--skip-name-resolve parameter to disable DNS host name resolution, and after disabling this feature, you can use only IP addresses in the MySQL authorization form.

If your environment DNS is very slow or there are many hosts, you can improve the responsiveness of the database by disabling DNS resolution--skip-name-resolve or by increasing the host_cache_size size.

Disabling host name buffering: using the--skip-host-cache parameter; refreshing the host name buffer: Executing flush hosts or executing mysqladmin flush-hosts;

Disabling TCP/IP connections: Using the--skip-networking parameter.

Experiment:
# 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) ### IP resolution to Hostname_online, not h_tt_ %, access was denied.

# 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 and hostname information in the host pool buffer pool. IP corresponds to hostname_online at this time

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 resolves the same IP pair in/etc/hosts When the first host name relationship is due, it will no longer resolve the following IP-corresponding host name relationship

# 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: Verify that the first host name relationship that resolves the same IP corresponds, no longer resolves the same IP:

Sql>grant usage on *.* to root@ ' h_tt_% ' identified by ' root ';

Sql>flush hosts;

# grep h_tt/etc/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; MySQL can be accessed from two IP.

Conclusion

This experiment verifies the explanation of "how MySQL Uses DNS" in the MySQL manual described above.

That is, mysqld thread parsing/etc/hosts is the IP as the only identification, in time an IP corresponding to a number of host names, but the mysqld thread only resolves the first corresponding relationship, regardless of the following IP corresponding to the different host name records, MYSQLD process will not be resolved, are null and void.

"Applicable environment:"

There is no DNS server, host is very many, or do not want to maintain the/etc/hosts inside the manually configured IP and hostname corresponding list, you can execute the host name as "%" or disable IP and host name resolution (--SKIP-NAME-RESOLVE) in MySQL authorization.