A slow way to resolve a remote connection to MySQL (mysql_connect open connection slow)

Network traffic is normal after pinging and route, and local connections on the MySQL machine are found to be fast,
So the network problem is basically ruled out. I've had a problem like this before, but then I don't know how suddenly it's okay,
This time again encounter such a problem, so want to see is not MySQL configuration problem. After querying MySQL-related documents and web searches,
Finding a configuration that seems to solve the problem is to add the following configuration parameters to the MySQL configuration file:

[Mysqld]

Skip-name-resolve

Under Linux the config file is/etc/my.cnf, under Windows Config file is the My.ini file under the MySQL installation directory.
Note that the configuration is added under [Mysqld], after the configuration is changed and saved,
Then restart MySQL and connect the test remotely, everything is back to the same. The official explanatory information for this parameter is as follows:

How MySQL uses DNS

When a new thread connects to Mysqld, Mysqld would spawn a new thread to handle the request.
This thread would first check if the hostname is in the hostname cache.
If not the thread would call Gethostbyaddr_r () and Gethostbyname_r () to resolve the hostname.

If the operating system doesn ' t support the above Thread-safe calls,
The thread would lock a mutex and call gethostbyaddr () and gethostbyname () instead.
Note that in this case no other thread can resolve other hostnames
The hostname cache until the first thread is ready.

You can disable DNS host lookup by starting mysqld With–skip-name-resolve.
In this case you can however only use IP names in the MySQL privilege tables.

If you had a very slow DNS and many hosts, you can get to more performance
By either disabling DNS Lookop with–skip-name-resolve or by
Increasing the Host_cache_size define (default:128) and recompile mysqld.

You can disable the hostname cache with–skip-host-cache.
You can clear the hostname cache with FLUSH HOSTS or mysqladmin flush-hosts.

If you don ' t want to allow connections over TCP/IP,
You can do this by starting mysqld with–skip-networking.

According to the documentation, if your MySQL host queries DNS is slow or has many client hosts, it can cause slow connections.
Because our development machine is not able to connect the external network, so the DNS resolution is impossible to complete,
So you can see why the connection is so slow. Also, be aware that after you increase the configuration parameter,
The host field in the MySQL authorization table is not able to use the domain name and can only use the IP address.
Because this is the result of a forbidden domain name resolution.

A slow way to resolve a remote connection to MySQL (mysql_connect open connection slow)