MySQL method to delete useless users

(1) View the current existing account
mysql> select user,host,password  from  ; Or the following command
#mysql> sELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM ; 
+---------------------------------------+
| query                                 |
+---------------------------------------+
| User: 'root'@'127.0.0.1';             |   #Don't delete| User: 'wenqiang'@'172.16.1.%';        |
| User: 'root'@'localhost';             |   #Don't delete| User: 'root'@''; |
+---------------------------------------+
4 rows in set (0.00 sec)

mysql> drop user wenqiang@'172.16.1.%';     #Delete unwanted users


(2) Try not to use it when authorizing users%(This symbol means that the user can log in from anywhere)
Authorization format：grant Permissions on database.* to username@User's network segment identified by "password";
# Only allow root users to log in to mysql from 10.0.0.10 and grant access to the entire library, and set password 123456 for root usersmysql> grant all on *.* to root@'10.0.0.10' identified by '123456'; 

#In this case, only authorization is allowed, root user can log in from locally using an empty passwordmysql> grant all on *.* to root@'localhost';

#In this case, root user can log in from the local area and set password abc123 at the same time (root user can set multiple sets of login passwords)mysql> grant all on *.* to root@'localhost' identified by 'abc123';

(1)Log in like this，And add onelocalhostPermissions
[root@localhost ~]# mysql -uroot -p -h 127.0.0.1 #Login via 127.0.0.1mysql> grant all on *.* to root@'localhost' identified by 'abc123';  #Add a localhost authorizationmysql> flush privileges;

Newly addedrootUsers may not have the highest permissions，See the first3.1

(1)Log inmysql
[root@localhost ~]# mysql -uroot -p -h localhost
mysql> grant all on *.* to root@'localhost' identified by '123456';        
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) #Report an error
(2)Check out which users are currently
mysql> SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM ;
+---------------------------------------+
| query                                 |
+---------------------------------------+
| User: 'root'@'127.0.0.1';             |  #Use mysql -uroot -p -h127.0.0.1 to log in to mysql authorization without any problem| User: 'wenqiang'@'172.16.1.%';        |
| User: 'root'@'localhost';             |  #I found that the root user logged in from localhost cannot authorize the ordinary user| User: 'root'@''; |
+---------------------------------------+
4 rows in set (0.00 sec)

(3)View thisrootUser'sGrant_privThe option isYstillN(NIt means that there is no right to authorize ordinary users)
mysql> select * from  where User='root' and Host='localhost'\G;
*************************** 1. row ***************************
                  Host: localhost
                  User: root
              Password: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
           Select_priv: Y
           Insert_priv: Y
           Update_priv: Y
           Delete_priv: Y
           Create_priv: Y
             Drop_priv: Y
           Reload_priv: Y
         Shutdown_priv: Y
          Process_priv: Y
             File_priv: Y
            Grant_priv: N     #No authorization permission       References_priv: Y
            Index_priv: Y
            Alter_priv: Y
          Show_db_priv: Y
            Super_priv: Y
 Create_tmp_table_priv: Y
      Lock_tables_priv: Y
          Execute_priv: Y
       Repl_slave_priv: Y
      Repl_client_priv: Y
      Create_view_priv: Y
        Show_view_priv: Y
   Create_routine_priv: Y
    Alter_routine_priv: Y
          Create_user_priv: Y
            Event_priv: Y
          Trigger_priv: Y
Create_tablespace_priv: Y
              ssl_type: 
            ssl_cipher: 
           x509_issuer: 
          x509_subject: 
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: mysql_native_password
 authentication_string: 
      password_expired: N
1 row in set (0.00 sec)

ERROR: 
No query specified

(3)BundleGrant_privOptionalNChange toYThat's it
mysql> update  set Grant_priv='Y' where User='root' and Host='localhost';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

(4)退出重新Log inmysql，Authorize the ordinary user again(Must exit firstmysql)
mysql> grant all on *.* to root@'localhost' identified by '123456';    
Query OK, 0 rows affected (0.00 sec)