MySQL skip-name-resolve error in the use of the opportunity to create user rights _mysql

Source: Internet
Author: User
Tags mysql client
Log in to MySQL to view the process information
Copy Code code as follows:

Show Processlist;

Found a large number of processes in the state for login
The original default when the MySQL startup is not using the skip-name-resolve option, so the connection from other hosts will be slower, because MySQL will do this IP DNS reverse query, resulting in a large number of connections in the login state ....
.
There are two ways to solve this problem

One is to add skip-name-resolve parameter restart MySQL

The second is to add a sentence in the/etc/hosts 192.168.0.2 server2 192.168.0.2 is the new server's intranet Ip,server2 is the new server's host name


In a solution to the MySQL client landing too slowly on the MySQL server, I described how to add the "skip-name-resolve" parameter setting in the My.ini file (Linux is a my.cnf file). So that the client can log on to the server, not through the host to resolve this pass, the direct landing method, in order to improve the speed of login.

Here is an introduction to the negative effects of this approach, and the unreasonable timing of the use of this method will cause an error that can not be found.

First, review the principle of adding "skip-name-resolve" parameters to the My.ini file to improve access speed:

When this parameter is not set, the client after the landing request, the server to resolve the requester who, after parsing, found that the login is from another computer login, that is, not the server, then, The server goes to the Mysql.user table to find out if there is a user, assuming the server IP is 192.168.0.1 and the client's IP is 192.168 0.2; the order of the queries is to find ' root ' 192.168.0.2 ' This user exists, if it exists, then match this login and load the permissions list. If there is no such user, look for the existence of the user ' root ' @ '% ', and if so, load the permissions list. Otherwise, the login fails.

When the Skip-name-resolve parameter is set, the client's login request is resolved in the same way as the above, but the parsing process on the server is changed: the server automatically resolves the user who logs on the computer to ' root ' @ ' 127.0.0.1 ' instead of ' root ' @ ' localhost '; that's bad because we're logged on to the server for some maintenance, but it's obvious that the ' root ' @ ' 127.0.0.1 ' user is the user who defaults to ' root ' @ '% '. This user does not have enough authority to perform a masterpiece that some super admin ' root ' @ ' localhost ' can execute. Because permissions are not assigned.

So the conclusion is: to join you in the server on the computer login MySQL server, or first cancel the Skip-name-resolve parameter settings, restart the server and login, after the setup is complete, then set the parameters; or give ' root ' 127.0.0.1 ' allocates super admin privileges, but it's obviously unwise to do so because anyone on any machine can use the user to perform an admin operation, provided the password is known.

I once executed a database creation script on a MySQL server and created tables, triggers, stored procedures, and so on. The result, always failed, after a morning toss, and finally found this parameter caused me to ' root ' @ ' 127.0.0.1 ' This user logged on to the server, this user does not have the right to create triggers. Later, when the Skip-name-resolve parameter is canceled, the execution succeeds and the parameter is set back. Reboot. Ok.

Therefore, when setting this parameter must pay attention to the timing: First use the Super administrator to create all the users, and then assign the rights after the allocation of this parameter is effective.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.