How can I restore the MySQL root user’s full privileges?

Asked 2009-11-10T16:07:10.613

122

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'

mysql privileges mysql-error-1045

Edit Report

2 Answers

Just insert or update mysql.user with value Y in each column privileges.

answered 2012-04-05T19:16:06.057

GRANT ALL ON *.* TO 'user'@'localhost' with GRANT OPTION;

Just log in from root using the respective password if any and simply run the above command to whatever the user is.

For example:

GRANT ALL ON *.* TO 'root'@'%' with GRANT OPTION;

answered 2012-11-24T06:21:48.640

How can I restore the MySQL root user’s full privileges?

2 Answers

Your Answer