阿坝州网站建设公司

MySQL误删root用户怎么恢复

本篇内容介绍了“MySQL误删root用户怎么恢复”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

成都创新互联公司主营晋州网站建设的网络公司,主营网站建设方案,重庆APP软件开发,晋州h5成都微信小程序搭建,晋州网站营销推广欢迎晋州等地区企业咨询


一个朋友在领导要求他删除root@127.0.0.1，root@'%'等用户，只保留root@localhost时，
他写了一条类似delete from mysql.user where user='root'的命令……
注意，他并没有写 “and host=”的条件，导致悲剧发生，并且还flush了授权。

以下模拟误删操作，尝试做恢复：

MySQL版本：
MySQL 5.5.49

模拟误删操作：

  1. mysql> DELETE FROM mysql.user WHERE user='root';

  2. Query OK, 1 row affected (0.01 sec)

  4. mysql> FLUSH PRIVILEGES;

  5. Query OK, 0 rows affected (0.01 sec)


解决思路：
新安装或者初始化一个新的实例（与误删操作的MySQL版本最好一致）
初始化好后，启动实例，并以root@localhost用户登录，然后设置密码：

新实例上：

  1. mysql> SELECT current_user();

  2. +----------------+

  3. | current_user() |

  4. +----------------+

  5. | root@localhost |

  6. +----------------+

  7. 1 row in set (0.00 sec)

  9. mysql> SET PASSWORD=password('123456');

  10. Query OK, 0 rows affected (0.00 sec)

  12. mysql> FLUSH PRIVILEGES;

  13. Query OK, 0 rows affected (0.00 sec)



将存放在mysql.user里的root@localhost用户信息查询出：

  1. mysql> SELECT * FROM mysql.user WHERE user='root' AND host='localhost' INTO OUTFILE '/tmp/root.txt';

  2. Query OK, 1 row affected (0.00 sec)



对于误删操作的实例：
首先将之前查询出的/tmp/root.txt文件传到该机上，此处传到同目录下，操作略。

然后要停掉mysqld，并绕过授权表启动：
可能无法通过mysqladmin shutdown来停止，此处直接kill掉mysqld_safe与mysqld，操作略。

然后启动：

  1. [root@vm02 ~]# mysqld_safe --skip-grant-tables &

  2. [1] 2957

  3. [root@vm02 ~]# 160819 17:00:30 mysqld_safe Logging to '/data/mysql_log/err-log.err'.

  4. 160819 17:00:30 mysqld_safe Starting mysqld daemon with databases from /data/mysql


进入mysql：

  1. [root@vm02 ~]# mysql

  2. Welcome to the MySQL monitor. Commands end with ; or \g.

  3. Your MySQL connection id is 3

  4. Server version: 5.5.49-log MySQL Community Server (GPL)

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

  8. Oracle is a registered trademark of Oracle Corporation and/or its

  9. affiliates. Other names may be trademarks of their respective

  10. owners.

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

  14. mysql> SELECT user(),current_user();

  15. +--------+----------------+

  16. | user() | current_user()  |

  17. +--------+----------------+

  18. | root@  | @               |

  19. +--------+----------------+

  20. 1 row in set (0.00 sec)


可以查看一下mysql.user表，已经没有了误删的root用户，只剩下xxx@'ip1'，yyy@'ip2'，这样的业务用户：

  1. mysql> SELECT user,host FROM mysql.user;

  2. +------+---------------+

  3. | user  | host          |

  4. +------+---------------+

  5. | xxx  | 192.168.1.185 |

  6. | yyy  | 192.168.1.187 |

  7. +------+---------------+

  8. 2 rows in set (0.00 sec)


将之前的新实例的mysql.user表中的root@localhost信息导入mysql.user：

  1. mysql> LOAD DATA INFILE '/tmp/root.txt' INTO TABLE mysql.user;

  2. Query OK, 1 row affected (0.04 sec)

  3. Records: 1 Deleted: 0 Skipped: 0 Warnings: 0

  5. mysql> FLUSH PRIVILEGES;

  6. Query OK, 0 rows affected (0.00 sec)

  8. mysql> SELECT user,host FROM mysql.user WHERE user='root' AND host='localhost';

  9. +------+---------------+

  10. | user | host          |

  11. +------+---------------+

  12. | root | localhost     |

  13. +------+---------------+

  14. 1 rows in set (0.00 sec)


退出到shell环境，关闭以skip-grant-tables方式启动的mysqld：
此时已经可以用mysqladmin来关闭mysqld了：

  1. [root@vm02 tmp]# mysqladmin -uroot -p123456 shutdown

  2. 160819 17:08:08 mysqld_safe mysqld from pid file /data/mysql/mysql-pid ended

  3. [1]+  Done                    mysqld_safe --skip-grant-tables  (wd: ~)

  4. (wd now: /tmp)

  5. [root@vm02 tmp]# ps -ef|grep mysql

  6. root       3938   1973  0 17:08 pts/0    00:00:00 grep mysql


再重新启动mysqld：

  1. [root@vm02 tmp]# mysqld_safe &

  2. [1] 3939

  3. [root@vm02 tmp]# 160819 17:08:53 mysqld_safe Logging to '/data/mysql_log/err-log.err'.

  4. 160819 17:08:53 mysqld_safe Starting mysqld daemon with databases from /data/mysql



已经可以正常使用了，密码是之前在初始化的新实例设置的：

  1. [root@vm02 tmp]# mysql -uroot -p123456

  2. Welcome to the MySQL monitor. Commands end with ; or \g.

  3. Your MySQL connection id is 2

  4. Server version: 5.5.49-log MySQL Community Server (GPL)

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

  8. Oracle is a registered trademark of Oracle Corporation and/or its

  9. affiliates. Other names may be trademarks of their respective

  10. owners.

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

  14. mysql> SELECT user(),current_user();

  15. +----------------+----------------+

  16. | user()            | current_user()  |

  17. +----------------+----------------+

  18. | root@localhost | root@localhost  |

  19. +----------------+----------------+

  20. 1 row in set (0.00 sec)



查看一下权限，可以对比一下，与之前的无异：

  1. mysql> SHOW GRANTS;

  2. +----------------------------------------------------------------------------------------------------------------------------------------+

  3. | Grants for root@localhost                                                                                                              |

  4. +----------------------------------------------------------------------------------------------------------------------------------------+

  5. | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' WITH GRANT OPTION |

  6. | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                                           |

  7. +----------------------------------------------------------------------------------------------------------------------------------------+

  8. 2 rows in set (0.00 sec)

“MySQL误删root用户怎么恢复”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注创新互联网站，小编将为大家输出更多高质量的实用文章！


名称栏目：MySQL误删root用户怎么恢复
当前URL：http://azwzsj.com/article/jcdhde.html

其他资讯