• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2164
  • Last Modified:

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?

Thanks!
0
raterus
Asked:
raterus
  • 2
1 Solution
 
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


Kent
0
 
raterusAuthor Commented:
I got the same error message when I ran that command,
'Access denied for user 'root'@'localhost' (using password: NO)'
0
 
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.
0
 
Aleksandar BradarićSoftware DeveloperCommented:
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now