MySQL5.6 disable DNS query_mysql

MySQL56 shut down DNS query author: chszs, not allowed to be reproduced by the blogger. For a license, you must indicate the author and the blog homepage: http: blogcsdnnetchspolicysql server. the following warning is displayed in the log record: 2015-11-0908: 37: 021489 [Warnin MySQL 5.6 disable DNS query.

The following warning is displayed in the log of the MySQL server:

2015-11-09 08:37:02 1489 [Warning] IP address '104.223.72.XXX' has been resolved to the host name '104.223.72.XXX.static.quadranet.com', which resembles IPv4-address itself.

Analysis: 104.223.72.XXX is an IP address in the United States. XX firewall is blocked in China, and DNS resolution is not normal. Because the MySQL server maintains a Host cache in the memory, it is used to store client information, including IP addresses, Host names, and error messages. For non-local TCP connections, the MySQL server queries this cache. For TCP connections that use local loopback addresses (such as 127.0.0.1 or: 1), TCP connections that use Unix socket files, named pipes, and TCP connections that share memory, will not use the Host cache.

Note: Loopback, the local Loopback interface (or address), also known as the return address. This type of interface is the most widely used virtual interface, which is used on almost every vro. In Windows, 127.0.0.1 is used as the local loopback address.

For each new client connection, the MySQL server uses the client IP address for retrieval to check whether the client Host name is in the Host cache. If the Host cache does not exist, the MySQL server will try to parse the Host name. First, the MySQL server will resolve the IP address corresponding to the host name and compare it with the original IP address of the client to ensure that it is the same IP address. The MySQL server then writes the information to the Host cache. If the Host cache is full, the MySQL server uses the least recently used algorithm to delete some cached data.

The MySQL server performs host name resolution using the thread-safe gethostbyaddr_r () function call and gethostbyname_r () function call, as long as the operating system supports this. Otherwise, the thread executing the search locks a mutex variable and calls the gethostbyaddr () and gethostbyname () functions. In this case, if the host name is not in the cache and no other thread can parse the host name, it must wait until the mutex variable is released.

The MySQL server uses the Host cache for the following purposes:

1) the relationship between the cached IP address and the host name avoids DNS queries for each client connection. On the contrary, for a given host, you only need to perform DNS queries for the first connection.

2) the Host cache also protects error messages during connection. Some errors may be "blocking" information. if this information continuously occurs on a given client, the server will block further connections from this client. The MySQL system variable max_connect_errors describes this situation, meaning the number of times the MySQL server allows client connection errors before blocking client connection.

To remove a blocked Host, refresh the Host cache and run the following statement:

mysql> flush hosts

Or run the following command in the Shell environment:

# mysqladmin flush-hosts

The Host cache is enabled by default in MySQL. to disable the Host cache, you can add the-skip-host-cache parameter option when starting MySQL.

To disable DNS host name search, you can add the-skip-name-resolve parameter option when starting MySQL. In this case, the MySQL server uses only the IP address to match the connection. If the DNS query is slow or there are many clients, disabling DNS query can improve the performance of the MySQL server.

Solution:

Modify the my. cnf configuration file, locate [mysqld], and add:

skip-name-resolve

Restart the MySQL service.

Proof:

mysql> show variables like "%skip%";+------------------------+-------+| Variable_name          | Value |+------------------------+-------+| skip_external_locking  | ON    || skip_name_resolve      | ON    || skip_networking        | OFF   || skip_show_database     | OFF   || slave_skip_errors      | OFF   || sql_slave_skip_counter | 0     |+------------------------+-------+6 rows in set (0.00 sec)