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 #-------------------------------------------------------------------------------
其实做到以上下面的就不用看了
############################################################################
如果遇到以上处理权限不足的补充处理
----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.
----add end by nod 18-06-21------