MySQL 误删用户

误删除root用户&误删除所有用户

  1 #-------------------------------------------------------------------------------
  2 #忘记root密码 登录并修改root密码 beign
  3 #-------------------------------------------------------------------------------
  4 
  5 mysqld_safe --skip-grant-tables --skip-networking &
  6 [root@db01-sa ~]# mysql
  7 Welcome to the MySQL monitor.  Commands end with ; or g.
  8 Your MySQL connection id is 1
  9 Server version: 5.6.38 Source distribution
 10 
 11 Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
 12 
 13 Oracle is a registered trademark of Oracle Corporation and/or its
 14 affiliates. Other names may be trademarks of their respective
 15 owners.
 16 
 17 Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
 18 
 19 mysql> use mysql;
 20 Reading table information for completion of table and column names
 21 You can turn off this feature to get a quicker startup with -A
 22 
 23 Database changed
 24 mysql> update user set password=PASSWORD('123') where user='root' and host='localhost';
 25 Query OK, 1 row affected (0.98 sec)
 26 Rows matched: 1  Changed: 1  Warnings: 0
 27 
 28 [root@db01-sa ~]# /etc/init.d/mysqld restart
 29 Shutting down MySQL.. SUCCESS! 
 30 Starting MySQL.180620 13:09:10 mysqld_safe mysqld from pid file /application/mysql/data/db01-sa.pid ended
 31 .... SUCCESS! 
 32 [1]+  Done                    mysqld_safe --skip-grant-tables --skip-networking
 33 
 34 [root@db01-sa ~]# mysql -uroot -p123
 35 Warning: Using a password on the command line interface can be insecure.
 36 Welcome to the MySQL monitor.  Commands end with ; or g.
 37 Your MySQL connection id is 2
 38 Server version: 5.6.38 Source distribution
 39 
 40 Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
 41 
 42 Oracle is a registered trademark of Oracle Corporation and/or its
 43 affiliates. Other names may be trademarks of their respective
 44 owners.
 45 
 46 Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
 47 
 48 mysql> 
 49 
 50 #-------------------------------------------------------------------------------
 51 #忘记root密码 登录并修改root密码 end
 52 #-------------------------------------------------------------------------------
 53 
 54 
 55 #-------------------------------------------------------------------------------
 56 #误删除所有用户 beign
 57 #-------------------------------------------------------------------------------
 58 
 59 #模拟情况
 60 select user,host,password from mysql.user;
 61 mysql> drop user root@'localhost';
 62 
 63 #开始处理
 64 [root@db01-sa ~]# /etc/init.d/mysqld stop
 65 [root@db01-sa ~]#mysqld_safe --skip-grant-tables --skip-networking &
 66 [root@db01-sa ~]# mysql
 67 
 68 
 69 
 70 mysql> INSERT INTO mysql.user VALUES ('localhost','root',
 71     -> '','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y',
 72     -> 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y',
 73     -> '','','','',0,0,0,0,'mysql_native_password','','N');
 74 Query OK, 1 row affected (0.00 sec)
 75 
 76 mysql> update mysql.user set password=PASSWORD('123') where user='root';
 77 Query OK, 1 row affected (0.00 sec)
 78 Rows matched: 1  Changed: 1  Warnings: 0
 79 
 80 
 81 mysql> select user,host,password from mysql.user;
 82 +------+-----------+-------------------------------------------+
 83 | user | host      | password                                  |
 84 +------+-----------+-------------------------------------------+
 85 | root | localhost | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
 86 +------+-----------+-------------------------------------------+
 87 row in set (0.08 sec)
 88 
 89 
 90 [root@db01-sa ~]# /etc/init.d/mysqld restart
 91 Shutting down MySQL..180620 13:19:15 mysqld_safe mysqld from pid file /application/mysql/data/db01-sa.pid ended
 92  SUCCESS! 
 93 Starting MySQL..... SUCCESS! 
 94 [1]+  Done                    mysqld_safe --skip-grant-tables --skip-networking
 95 [root@db01-sa ~]# mysql
 96 ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
 97 [root@db01-sa ~]# mysql -uroot -p123
 98 Warning: Using a password on the command line interface can be insecure.
 99 Welcome to the MySQL monitor.  Commands end with ; or g.
100 Your MySQL connection id is 2
101 Server version: 5.6.38 Source distribution
102 
103 Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
104 
105 Oracle is a registered trademark of Oracle Corporation and/or its
106 affiliates. Other names may be trademarks of their respective
107 owners.
108 
109 Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
110 
111 mysql> select user,host,password from mysql.user;
112 +------+-----------+-------------------------------------------+
113 | user | host      | password                                  |
114 +------+-----------+-------------------------------------------+
115 | root | localhost | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
116 +------+-----------+-------------------------------------------+
117 1 row in set (0.00 sec)
118 
119 #-------------------------------------------------------------------------------
120 #误删除所有用户 end
121 #-------------------------------------------------------------------------------

View Code

其实做到以上下面的就不用看了

############################################################################

如果遇到以上处理权限不足的补充处理

----add begin by nod 18-06-21------

备注：可能会有权限不够的情况
处理方式：


INSERT INTO mysql.user VALUES ('localhost','root',
'','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y',
'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y',
'','','','',0,0,0,0,'mysql_native_password','','N');


mysql> update mysql.user set password=PASSWORD('123') where user='root';


[root@db01-sa ~]# /etc/init.d/mysqld restart
Shutting down MySQL..180621 08:08:27 mysqld_safe mysqld from pid file /application/mysql/data/db01-sa.pid ended
 SUCCESS! 
Starting MySQL..... SUCCESS! 
[1]+  Done                    mysqld_safe --skip-grant-tables --skip-networking
[root@db01-sa ~]# mysql -uroot -p123
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 1
Server version: 5.6.38 Source distribution

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

View Code

----add end by nod 18-06-21------