MySQL is a very slow solution for remote access Skip-name-resolve

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.

**************************************

Here are the other users to add:

The problem is this:

I started a MySQL on a machine (61.183.23.23) and then opened an account that could be accessed from 127.0.0.1 or from 61.183.23.23. But there is a problem when you use the following two command line access, the speed difference is very large:

Mysql–h 127.0.0.1–u User
Mysql–h 61.183.23.23–u User

Then I use ping to determine the speed of the two IP is similar.

The IP speed of using 127.0.0.1 is much faster than the other one. Although it is said that the 61.183.23.23 need to go outside the network to walk a circle, but how the speed difference is so much worse.

Workaround:

MySQL uses skip-name-resolve to disable DNS queries.
MySQL will be in the user login process to the Client IP DNS counter-check, whether you use IP login or domain name login, this counter-check process is in. So if your MySQL server DNS problems or poor quality, then it is possible to cause the problem I encountered, DNS resolution problems.

Modify the MySQL configuration file

[Mysqld]
Skip-name-resolve

Add:

To make a reminder, increase skip-name-resolve may cause account failure, such as my original account is [email protected], and I actually use Mysql-h127.0.0.1-uyejianfeng can log in. But once you add the skip-name-resolve, you can't log in. Need to add an account [email protected]

MySQL is a very slow solution for remote access Skip-name-resolve