How do I restore the MySQL root user's privile

Question

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?

Answer 1

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;

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.

UPDATE

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.

UPDATE 2

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?