"Reprint" MySQL Remote connection slow solution

Original address: http://blog.163.com/[email protected]/blog/static/16674283420110224207826/

PHP remote connection MySQL slow, sometimes remote connection to MySQL time 4-20 seconds, local connection MySQL normal, the main reason for this problem is that the default installation of MySQL turned on the DNS reverse resolution, under My.ini (Windows system) or my.cnf (Unix or Linux system) file under [mysqld] to add skip-name-resolve this sentence. A workaround to connect MySQL slowly.

2 servers, one running iis+php, one running MySQL, and the same as ever. Configure the environment, test the page everything OK

Running the application found that PHP access to MySQL is very slow, this situation in the past has never been found, although 2 servers are not in the same network segment, but the ping value is basically between 1,2ms, TCP connection should not be the crux of the problem, Google later found the answer, in the My.ini file [Mysqld] section add: Skip-name-resolve, save the file, restart MySQL, everything OK, speed like flying

The new version of MySQL configuration is not like the previous fool, this problem toss me a morning's time, the evening back finally is solved, hey, and learned something.

Windows 2003 under the MySQL 5 server, the computer connected to the MySQL service very fast, the LAN has two Linux machines, there is a connection quickly, the other one to enter the password after several seconds to connect.

Workaround:

Add one of the following configurations to the MySQL server configuration, which is fast.

[Mysqld]
Skip-name-resolve

Appendix: (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 that's not in the hostname cache until the first Threa D 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-r Esolve 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 MySQL slow, sometimes remote connection to MySQL time 4-20 seconds, local connection MySQL normal, the main reason for this problem is that the default installation of MySQL turned on the DNS reverse resolution, under My.ini (Windows system) or my.cnf (Unix or Linux system) file under [mysqld] to add skip-name-resolve this sentence.

A workaround for MySQL remote connection slowness
In the LAN connection to other machines of MySQL, found that the speed is very slow, do not know what reason, there are always a few seconds of delay.

Later found the solution on the Internet, My.ini inside add

[Mysqld]
Skip-name-resolve
Skip-grant-tables

It's going to be fast!

Skip-name-resolve

Option to disable DNS resolution, the connection speed will be much faster. However, it is not possible to use the hostname in the MySQL authorization table and only use the IP format.

If using the –skip-grant-tables system will not make any access control to any user's access, but can use mysqladmin flush-privileges or mysqladmin reload to turn on access control; By default, show The databases statement is open to all users,

If the MySQL server does not have a remote account open, add Skip-grant-tables to the My.ini

Attached, please open according to the situation

Skip-name-resolve Generally, we can just do this one.
Skip-grant-tables