MySQL could not is resolved:name or service not known_mysql

Source: Internet
Author: User
Tags flush mysql manual

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

The error log has a similar warning:

1.120119 16:26:04 [Warning] IP address ' ' could the not to resolved:name or service not known
2.120119 16:26:04 [Warning] IP address ' ' could the not to resolved:name or service not known
3.120119 16:26:04 [Warning] IP address ' ' 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|| null| Connect| |login| null|
2.|593|unauthenticated user|| null| Connect| |login| null|
3.|594|unauthenticated user|| 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:


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

The following is a more detailed explanation:


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

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


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.


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.

# grep Hostname_online

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

Sql> Flush hosts;

# mysql-h

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 Hostname_online h_tt_1

# mysql-h

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

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 h_tt_1 Hostname_online

Sql> Flush hosts;

# mysql-h

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.2h_tt_1

Access to MySQL is denied; MySQL can be accessed from two IP.


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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.