Slow remote connection to MySQL

The following articles mainly introduce the actual solution to slow remote connection to MySQL, that is, using skip-name-resolve to cancel DNS reverse resolution, the following describes the specific content of the article, hoping to help you in your future studies.
 
PHP remote connection to MySQL is slow. Sometimes it takes 4-20 seconds to remotely connect to MySQL, and the local connection to MySQL is normal. The main cause of this problem is that the reverse DNS resolution is enabled for MySQL installed by default, in MY. INI (in WINDOWS) or MY. add skip-name-resolve to [MySQLd] of the CNF (UNIX or LINUX) file.

However, this will cause a problem: when connecting to MySQL, you cannot use localhost to connect, but instead use an IP address. If you grant permissions to users by localhost, you must modify the user logon permission.

Slow connection to MySQL.

2 servers, 1 running iis + php, and 1 running MySQL. Configure the environment as before and test the page. Everything is OK.

When I ran the application, I found that php was slow to access MySQL. This was never found before. Although the two servers are not in the same network segment, the ping value is basically between 1 and 2 ms, tcp connection should not be a problem. google will find the answer later, in my. in the [MySQLd] section of the INI file, add skip-name-resolve, save the file, and restart MySQL. Everything is OK, and the speed is the same as flying.

The configuration of the new version of MySQL is not as foolish as it was before. It took me a long morning to solve this problem. I finally came back at night and learned something.

On the MySQL 5 Server in Windows 2003, the local machine is connected to the MySQL service very quickly. There are two Linux machines in the LAN, one of which is quickly connected, and the other one enters the password and takes several seconds to connect.

Solution:

Add the following configuration in the configuration of the MySQL server to speed up.

 
 

  
  
[MySQLd]  
  
  
skip-name-resolve 
 
 

Appendix :( How MySQL uses DNS)

When a new thread connects to MySQLd, MySQLd will spawn a new thread to handle the request. this thread will first check if the hostname is in the hostname cache. if not the thread will call gethostbyaddr_r () and gethostbyname_r () to resolve the hostname.

If the operating system doesn't support the above thread-safe call, the thread will lock a mutex and call gethostbyaddr () and gethostbyname () instead. note that in this case no other thread can resolve other hostnames that is not in 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 have a very slow DNS and have hosts, you can get 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.

PHP remote connection to MySQL is slow. Sometimes it takes 4-20 seconds to remotely connect to MySQL, and the local connection to MySQL is normal. The main cause of this problem is that the reverse DNS resolution is enabled for MySQL installed by default, in MY. INI (in WINDOWS) or MY. add skip-name-resolve to [MySQLd] of the CNF (UNIX or LINUX) file.

Slow MySQL remote connection

When I connect to MySQL from other machines in the LAN, I find that MySQL is very slow. I don't know why, and there will always be several seconds of delay.

Later, I found a solution on the Internet, and added it to my. ini.

 
 

  
  
[MySQLd]  
  
  
skip-name-resolve   
  
  
skip-grant-tables 
 
 

This is faster!

 
 

  
  
skip-name-resolve  
 
 

To disable DNS resolution, and the connection speed is much faster. However, in this case, the host name cannot be used in the MySQL authorization table, but only in ip Format.

If you use the-skip-grant-tables system, no access control is required for any user's access. However, you can use MySQLadmin flush-privileges or MySQLadmin reload to enable access control; by default, the show databases statement is open to all users,

If the MySQL server does not have a remote account, add skip-grant-tables to my. ini.