Mysql SKIP-NAME-RESOLVE errors cause User Permissions

Log on to mysql to view the process information.
Copy codeThe Code is as follows:
Show processlist;

A large number of processes are found to be in the login status.
By default, the skip-name-resolve option is not used when mysql is started. In this way, the connection from other hosts will be slow, mysql performs reverse dns query on this ip address, resulting in a large number of connections in the login status ....
.
There are two solutions to this problem.

First, add the skip-name-resolve parameter to restart mysql.

The second is to add the following statement to/etc/hosts: 192.168.0.2 server2, where 192.168.0.2 is the Intranet ip address of the newly added server, and server2 is the host name of the new server.


In an article about how to log on to the mysql server using the mysql client. INI file (my. add the "SKIP-NAME-RESOLVE" parameter settings to the cnf file so that the client does not use host resolution when logging on to the server, to improve the logon speed.

Here we will introduce the negative effects of this method and the undiscoverable errors caused by improper use of this method.

First, review the principle of adding the "SKIP-NAME-RESOLVE" parameter to the my. ini file to increase the access speed:

When this parameter is not set, after the client sends a login request, the server needs to parse who the request is. After the resolution, it is found that the attacker logs on from another computer, that is to say, if the server is not a local server, the server will go to mysql. check whether the user exists in the user table. Assume that the server IP address is 192.168.0.1 and the client IP address is 192.168.0.2. Then, the query order is 'root' @ '192. 168.0.2 'indicates whether the user exists. If yes, the user logs in and loads the permission list. If the user does not exist, check whether the user 'root' @ '%' exists. If yes, load the permission list. Otherwise, the logon fails.

After the SKIP-NAME-RESOLVE parameter is set, the client login request is parsed in the same way as above, but the parsing process on the server has changed: the server automatically resolves the user logging on to the local machine as 'root' @ '127. 0.0.1 '; instead of 'root' @ 'localhost'; in this case, it is broken because we log on to the server for some maintenance operations, but apparently, 'root' @ '127. 0.0.1 'is the default user 'root' @' %, this user does not have sufficient permissions to execute some masterpiece that can be executed only by super administrator 'root' @ 'localhost. Because no permission is assigned.

So the conclusion is: If you log on to the mysql server on the server, you must cancel the SKIP-NAME-RESOLVE parameter settings, restart the server, and then log on to the server. After the settings are complete, set this parameter again; or, grant 'root' @ '127. 0.0.1 'assign super administrator permissions, but it is obviously unwise to do so, because anyone can use this user to perform administrator operations on any machine only if they know the password.

I once executed the database creation script on the mysql server and created tables, triggers, and stored procedures at the same time. As a result, it always fails. After a morning of tossing, I finally found that this parameter caused me to 'root' @ '127. 0.0.1 'this user has logged on to the server and has no permission to create a trigger. After the SKIP-NAME-RESOLVE parameter is canceled, the operation is successful and the parameter is set back. Restart. OK.

Therefore, you must pay attention to the timing when setting this parameter: you must first create all users with the super administrator, and then assign the permissions before setting this parameter to take effect.