We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Reset root permissions?

raterus
raterus asked
on
Medium Priority
2,222 Views
Last Modified: 2008-01-09
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!
Comment
Watch Question

Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:
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
Top Expert 2005

Author

Commented:
I got the same error message when I ran that command,
'Access denied for user 'root'@'localhost' (using password: NO)'
Aleksandar BradarićSoftware Developer
CERTIFIED EXPERT

Commented:
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.
Software Developer
CERTIFIED EXPERT
Commented:

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.