Reprint:http://www.itokit.com/2012/0515/73932.html
When the server is put on the LAN for testing, the database access speed is still very fast. However, when the server is placed outside the network, the database access speed becomes very slow.
Later found the solution on the Internet, my.cnf inside add
[Mysqld]
Skip-name-resolve
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.
There is also the issue of permissions, when the user set restrictions can only access a database, if the database is deleted, and then rebuild the specified database, restrict the user or not access to the data, presumably the time to delete the database, the user's access is also cascade deleted, detailed can see Mysql.db records
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.CNF
Troubleshoot network issues.
As far as MySQL itself is concerned, the problem is in MySQL DNS anti-parsing
Mysql>show processlist;
| 20681949 | Unauthenticated user | 10.10.4.193:52497 | NULL | Connect | | Reading from net | NULL |
| 20681948 | Unauthenticated user | 10.10.4.193:52495 | NULL | Connect | | Reading from net | Null
There are a number of unauthenticated user attempts to log in to use MySQL, which can cause the system to be very slow when this happens indefinitely.
Check the official website of MySQL, this is a special system on the official set, he as a MySQL bug, regardless of the way the link is through the hosts or IP mode, he will do the DNS counter-check. Mysqld will try to reverse IP---DNS, because the anti-check resolution too slow, you will not be able to cope with excessive queries.
Workaround:
/usr/local/mysql/bin/mysqld_safe--skip-name-resolve--user=mysql&
Add--skip-name-resolve so a parameter can, turn off the DNS counter-check function of MySQL.
or modify the MySQL configuration file.
Edit/ETC/MY.CNF
In the [MYSQLD] paragraph, add
Skip-name-resolve
Restart MySQL
Add the following sentence in the/ETC/MY.CNF configuration file, disable DNS echo resolution, can greatly speed up the MySQL connection.
[Mysqld]
Add the following sentence
Skip-name-resolve
#注意有些文章中写道加入 –skip-name-resolve, it has been verified that adding –skip-name-resolve under CentOS5 causes the MySQL daemon to fail to start. The estimate is the same under other Linux systems, without testing under Windows, Skip-name-resolve should be able to.
MySQL is very slow to resolve skip-name-resolve at remote access and appears Reading from net