Reset root permissions?

Help!  I've locked the root account out of my database.

The stupid thing I did was in webmin, I was looking at a list of user permissions in my database, and saw a few entries for "root".  I noticed there was one which gave root access to all databases, and one that gave access to only on database.  I stupidly removed access to the one database, now when I try to do anything, I get this error.

Access denied for user 'root'@'localhost'

I've tried restarting mysql with "skip-grant-tables" which allows me to view/edit all the databases, but I still can't run the command (GRANT ALL on *.* to 'root'@'localhost'), to put the permissions back.  (mysql gives me the error that I can't run GRANT statements since I'm using skip-grant-tables"

Can someone help me restore access to all db tables to my root account, keeping all my databases and existing users intact?

LVL 33
Who is Participating?
Aleksandar BradarićConnect With a Mentor Software DeveloperCommented:
Kent OlsenData Warehouse Architect / DBACommented:
Hi raterus,

I must admit that I've never done this or had to recover the root (admin) access, but it should be possible.  Try using mysqladmin.

  mysqladmin -u root password new-password

raterusAuthor Commented:
I got the same error message when I ran that command,
'Access denied for user 'root'@'localhost' (using password: NO)'
Aleksandar BradarićSoftware DeveloperCommented:
You have to stop the server and restart it with the `--skip-grant-tables` option. When your in, change the password for root with:
UPDATE mysql.user SET password=password('your_new_password') WHERE user = 'root';

Then stop the server again and when you start it, you should be able to log in with the new password.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.