Shulou(Shulou.com)06/01 Report--

When the server is tested in the local area network, the access speed of the database is still very fast. However, when the server is put on the external network, the access speed of the database becomes very slow.

Later, a solution was found on the Internet and added to my.cnf.

[mysqld]

Skip-name-resolve

It's faster this way!

Skip-name-resolve

Option to disable DNS parsing, which makes the connection much faster. However, in this case, the hostname cannot be used in the authorization table of MySQL, but only in ip format.

There is also the question of permissions. When the user sets a restriction to access only a certain database, if the database is deleted, the specified database is rebuilt, and the user is still not allowed to access the data, which is probably the time to delete the database. The user's access rights are also cascaded and deleted. For more information, you can view the records of mysql.db.

If you use-skip-grant-tables system will not do any access control for any user, but 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 the my.cnf

Troubleshoot network problems.

As far as MySQL itself is concerned, the problem lies in mysql dns anti-parsing.

Mysql > show processlist

| | 20681949 | unauthenticated user | 10.10.4.193 Connect 52497 | NULL | Connect | | Reading from net | NULL |

| | 20681948 | unauthenticated user | 10.10.4.193 Connect 52495 | NULL | Connect | | Reading from net | NULL |

It is found that there are a lot of unauthenticated user attempts to log in and use mysql, which can cause the system to be very slow when this happens without limit.

Consult the mysql official website to learn that this is a special setting on an official system, so just think of it as a bug of mysql. No matter whether the link is through hosts or IP mode, he will do a reverse check on DNS. Mysqld will try to reverse-check IP-> dns, because the reverse-check parsing is too slow, it will not be able to cope with excessive queries.

Solution:

/ usr/local/mysql/bin/mysqld_safe-skip-name-resolve-user=mysql&

If you add-- skip-name-resolve, you can turn off the dns reverse search function of mysql.

Or modify the mysql configuration file.

Edit / etc/my.cnf

Add in the [mysqld] paragraph

Skip-name-resolve

Restart mysql

Add the following sentence to the / etc/my.cnf configuration file, disable DNS echo parsing, and you can greatly speed up the MySQL connection.

[mysqld]

Add this sentence below

Skip-name-resolve

# Note that adding-skip-name-resolve is mentioned in some articles. It has been verified that adding-skip-name-resolve under CentOS5 will cause the mysql daemon to fail to start. It is estimated to be the same in other linux systems. If there is no test under windows, skip-name-resolve should be fine.

* *

Here are some additions from other netizens:

The problem is as follows:

I started a mysql on a machine (61.183.23.23) and opened an account that can be accessed from 127.0.0.1 or 61.183.23.23. But one problem is that there is a big difference in speed when using the following two command lines to access:

Mysql-h 127.0.0.1-u user

Mysql-h 61.183.23.23-u user

Then I use ping to determine that the speed of the two IP is about the same.

Using 127.0.0.1 IP is much faster than the other one. Although it is said that the 61.183.23.23 here needs to take a walk around the public network, how can the speed difference be so different?

Solution:

Mysql uses skip-name-resolve to disable queries for DNS.

Mysql will perform a DNS reverse check on the client IP during the user login process, regardless of whether you log in using IP or domain name. So if there is a problem with the DNS of the server where your mysql is located or the quality is not good, then it may cause the problem I encountered, the problem with DNS parsing.

Modify mysql configuration file

[mysqld]

Skip-name-resolve

Add:

As a warning, adding skip-name-resolve may lead to account invalidation, for example, my original account is yejianfeng@localhost, and then I can actually log in using mysql-h227.0.0.1-uyejianfeng. But once you add skip-name-resolve, you can't log in. You need to add the account yejianfeng@127.0.0.1.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.