Incorrect timing of mysql SKIP NAME RESOLVE user permissions
- 2020-05-10 23:04:06
- OfStack
Log in to mysql to see the process information
A large number of processes were found with a state of login
By default, mysql does not use the skip-name-resolve option when it is started. In this case, the connection from other hosts will be slow, because mysql will do the dns reverse query on this ip, resulting in a large number of connections in the login state...
.
There are two ways to solve this problem
1 is to add the skip-name-resolve parameter and restart mysql
2 is the addition of a sentence 192.168.0.2 server2 in /etc/hosts, where 192.168.0.2 is the Intranet ip of the newly added server and server2 is the hostname of the new server
Landed in mysql mysql client server login too slow solution 1 of the article, I introduced how to through the my. ini file (under linux is my cnf file) add "SKIP - NAME - RESOLVE" parameter Settings, make the client when the login server host parsing the 1 don't pass through, landing directly approach, in order to increase the speed of login.
Here's a look at some of the downsides of this approach, as well as some of the undetectable errors that can result from using it at an inappropriate time.
First of all, review 1 the principle of adding "SKIP-NAME-RESOLVE" parameter to the my.ini file to improve the access speed:
When not set the parameters, the client after the login request, the server to parse the requestor is who, after parsing, found the login from another computer login, that is to say, is not a server machine, then, the server will to mysql user table to find whether have this user, assume that the server is IP 192.168.0.1, and client is IP 192.168.0.2; Then, the order of query is to find whether the user 'root'@'192.168.0.2' exists. If so, it will match this user to log in and load the permission list. If there is no such user, find out if the user 'root'@'%' exists, and if so, load the list of permissions. Otherwise, the login fails.
After setting the SKIP-NAME-RESOLVE parameter, the parsing form of the client login request is the same as the above one, but the parsing process of the server is changed: the server will automatically parse the user who logs in on the server as 'root'@'127.0.0.1'; Instead of 'root'@'localhost'; This is bad because we logged in to the server for some maintenance, but obviously, 'root'@'127.0.0.1' is the default user for 'root'@'%', and this user does not have enough permission to perform some super admin 'root'@'localhost' to perform the work. Because no permissions were assigned.
So the conclusion is: if you want to log in mysql server on the server machine, either cancel the parameter setting of SKIP-NAME-RESOLVE, restart the server and log in again, and then set the parameter after the setting is completed; Either assign super administrator rights to 'root'@'127.0.0.1', which is obviously unwise since anyone can use this user to perform administrator operations on any machine, provided they know the password.
I once executed the database creation script on the mysql server, creating tables, triggers, stored procedures, and so on. It always failed, and after a morning of thrillings, I finally found out that this parameter caused me to log into the server as 'root'@'127.0.0.1', and that this user did not have the authority to create the trigger. Later, after canceling the SKIP-NAME-RESOLVE parameter, the execution was successful and the parameter was set back. Restart. OK.
Therefore, when setting this parameter 1 must pay attention to the timing: first use the super administrator to create all the users, and then after the rights are assigned, set this parameter to take effect.
show processlist;
A large number of processes were found with a state of login
By default, mysql does not use the skip-name-resolve option when it is started. In this case, the connection from other hosts will be slow, because mysql will do the dns reverse query on this ip, resulting in a large number of connections in the login state...
.
There are two ways to solve this problem
1 is to add the skip-name-resolve parameter and restart mysql
2 is the addition of a sentence 192.168.0.2 server2 in /etc/hosts, where 192.168.0.2 is the Intranet ip of the newly added server and server2 is the hostname of the new server
Landed in mysql mysql client server login too slow solution 1 of the article, I introduced how to through the my. ini file (under linux is my cnf file) add "SKIP - NAME - RESOLVE" parameter Settings, make the client when the login server host parsing the 1 don't pass through, landing directly approach, in order to increase the speed of login.
Here's a look at some of the downsides of this approach, as well as some of the undetectable errors that can result from using it at an inappropriate time.
First of all, review 1 the principle of adding "SKIP-NAME-RESOLVE" parameter to the my.ini file to improve the access speed:
When not set the parameters, the client after the login request, the server to parse the requestor is who, after parsing, found the login from another computer login, that is to say, is not a server machine, then, the server will to mysql user table to find whether have this user, assume that the server is IP 192.168.0.1, and client is IP 192.168.0.2; Then, the order of query is to find whether the user 'root'@'192.168.0.2' exists. If so, it will match this user to log in and load the permission list. If there is no such user, find out if the user 'root'@'%' exists, and if so, load the list of permissions. Otherwise, the login fails.
After setting the SKIP-NAME-RESOLVE parameter, the parsing form of the client login request is the same as the above one, but the parsing process of the server is changed: the server will automatically parse the user who logs in on the server as 'root'@'127.0.0.1'; Instead of 'root'@'localhost'; This is bad because we logged in to the server for some maintenance, but obviously, 'root'@'127.0.0.1' is the default user for 'root'@'%', and this user does not have enough permission to perform some super admin 'root'@'localhost' to perform the work. Because no permissions were assigned.
So the conclusion is: if you want to log in mysql server on the server machine, either cancel the parameter setting of SKIP-NAME-RESOLVE, restart the server and log in again, and then set the parameter after the setting is completed; Either assign super administrator rights to 'root'@'127.0.0.1', which is obviously unwise since anyone can use this user to perform administrator operations on any machine, provided they know the password.
I once executed the database creation script on the mysql server, creating tables, triggers, stored procedures, and so on. It always failed, and after a morning of thrillings, I finally found out that this parameter caused me to log into the server as 'root'@'127.0.0.1', and that this user did not have the authority to create the trigger. Later, after canceling the SKIP-NAME-RESOLVE parameter, the execution was successful and the parameter was set back. Restart. OK.
Therefore, when setting this parameter 1 must pay attention to the timing: first use the super administrator to create all the users, and then after the rights are assigned, set this parameter to take effect.