如何恢复MySQL root用户的全部权限? [英] How can I restore the MySQL root user’s full privileges?

问题描述

我不小心从MySQL根用户中删除了某些特权，包括更改表的能力.有什么方法可以使该用户恢复到其原始状态(具有所有特权)?

I accidentally removed some of the privileges from my MySQL root user, including the ability to alter tables. Is there some way I can restore this user to its original state (with all privileges)?

UPDATE mysql.user SET Grant_priv = 'Y', Super_priv = 'Y' WHERE User = 'root';
# MySQL returned an empty result set (i.e. zero rows).
FLUSH PRIVILEGES ;
# MySQL returned an empty result set (i.e. zero rows).


#1045 - Access denied for user 'root'@'localhost' (using password: YES)
GRANT ALL ON *.* TO 'root'@'localhost'

推荐答案

如果GRANT ALL不起作用，请尝试:

If the GRANT ALL doesn't work, try:

1. 停止mysqld，然后使用--skip-grant-tables选项重新启动.
2. 仅使用mysql连接到mysqld服务器(即没有-p选项，并且可能不需要用户名).

3. 在mysql客户端中发出以下命令:

1. Stop mysqld and restart it with the --skip-grant-tables option.
2. Connect to the mysqld server with just: mysql (i.e. no -p option, and username may not be required).

3. Issue the following commands in the mysql client:

UPDATE mysql.user SET Grant_priv='Y', Super_priv='Y' WHERE User='root';

FLUSH PRIVILEGES;

在那之后，您应该能够运行GRANT ALL ON *.* TO 'root'@'localhost';并使它工作.

After that, you should be able to run GRANT ALL ON *.* TO 'root'@'localhost'; and have it work.

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