Reset root permissions?

Posted on 2007-07-20
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?

Question by:raterus
    LVL 45

    Expert Comment

    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

    LVL 33

    Author Comment

    I got the same error message when I ran that command,
    'Access denied for user 'root'@'localhost' (using password: NO)'
    LVL 17

    Expert Comment

    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.
    LVL 17

    Accepted Solution


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Suggested Solutions

    This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
    Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now