如何恢复 MySQL root 用户的权限? [英] How do I restore the MySQL root user's privileges?

问题描述

我不小心删除了 MySQL 根用户的大部分权限，包括授予权限的能力.有什么办法可以让这个用户恢复到原来的状态?

I accidentally removed most of the privileges from my MySQL root user, including the ability to grant privileges. Is there some way I can restore this user to its original state?

推荐答案

您仍然可以对数据库 mysql<中的表 user 执行 UPDATE/代码>:

You might still be able to do an UPDATE on table user in database mysql:

USE mysql;
UPDATE user SET Grant_priv='1' WHERE User='root';
FLUSH PRIVILEGES;

如果步骤 3 不起作用，重新启动 MySQL 服务器将具有相同的效果.如果步骤 2 不起作用，您需要从备份中恢复 mysql.user.如果您没有备份，请进行备份，重新安装 MySQL，然后有选择地恢复备份，将 mysql 数据库中的表排除在外.

If step 3 doesn't work, restarting the MySQL server will have the same effect. If step 2 doesn't work, you need to restore mysql.user from backup. If you don't have a backup, make a backup, reinstall MySQL and then selectively restore your backup, leaving out tables within the mysql database.

更新

您在第一步中收到拒绝访问的消息.在这一点上，您可以使用带有备份的替代解决方案.没有办法(我知道或可以轻松想象)您正在以任何其他方式恢复这些表.

You're getting Access Denied during the first step. At this point, you're down to the alternative solutions with backups. There's no way (that I know of or can easily imagine) that you're recovering those tables any other way.

更新 2

确切的错误消息基本上是说，除了失去 root 的 grant 权限之外，您还放弃了 root 对 mysql 数据库的访问权限.无法访问该数据库且没有 grant 权限，我能看到的唯一方法是以某种方式获取 mysql 数据库的新副本.

The exact error message is basically saying that in addition to losing root's grant privileges, you've dropped root's access to the mysql DB. Without having access to that DB and without having grant privileges, the only way I can see back is to somehow obtain a fresh copy of the mysql DB.

您是否有机会设置复制从属设备?

Any chance you have a replication slave set up?

这篇关于如何恢复 MySQL root 用户的权限?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！