mysql恢复所有权限,如何恢复MySQL root用户的全部权限？

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'

解决方案

If the GRANT ALL doesn't work, try:

Stop mysqld and restart it with the --skip-grant-tables option.

Connect to the mysqld server with just: mysql (i.e. no -p option, and username may not be required).

Issue the following commands in the mysql client:

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

FLUSH PRIVILEGES;

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