MySQL enables Skip-name-resolve to appear warning and user rights error resolution

120726 11:57:22 [Warning] ' user ' entry ' root@localhost.localdomain ' ignored in--skip-name-resolve mode.

120726 11:57:22 [Warning] ' user ' entry ' @localhost. Localdomain ' ignored in--skip-name-resolve mode.

Skip-name-resolve is to disable DNS resolution to prevent network DNS resolution services from raising errors that access MySQL, which should generally be enabled.

When enabled, in the MySQL authorization table can not use the host name, only the use of IP, this warning occurs because the MySQL table already exists localhost.localdomain account information.

We'll just have to remove it.

The code is as follows
Mysql>use MySQL; mysql> Delete from user where host= ' localhost.localdomain '; Query OK, 2 rows Affected (0.00 sec)

Reboot MySQL and find the warning is gone.

MySQL skip-name-resolve the wrong time to use to create user rights

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.