How To Reset The MySQL Root User Password and Privileges

Problem: I was developing using PHPMyAdmin when I added a new root user (with a host value of %), and removed the rights of the default root user.

When you do that, you’ll get a gut-wrenching feel that you just made a huge boo-boo, find that suddenly everything will not work, and kick yourself in the ass.

Fortunately, you have a fix. It’s in the mysqld –skip-grant-tables command, but you can’t just run it directly. First of all, fire up terminal (applications > utilities > terminal), you’ll be using this exclusively to fix your problem.

For me, the I was using MAMP, and the path to my mysqld is within MAMP, so it was found in /Applications/MAMP/Library/libexec/mysqld

You can’t just perform a mysqld –skip-grant-tables command from the terminal though, because you’ll just see this error message:

[ERROR] Fatal error: Please read “Security” section of the manual to find out how to run mysqld as root!

So what you need to do is to run this command instead:

mysqld -u root --skip-grant-tables

And you’re good! That terminal window is now running the mysqld instance, so open a new window by pressing CMD+T.

In this new window, type and run mysql. You should be able to get in now, because MySQL is now open to everyone. If you got into MySQL fine, you should see this:


Great! You can’t grant any privileges now that you skipped the grant tables, but you can modify the users database. Run these commands – what they do is to use the mysql database, remove the root user, and insert a new root user (but with all privileges), and then flush the privileges to reset it to the new state.

mysql> use mysql;
mysql> delete from user where user='root';
mysql> insert into user (Host, User, Password, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv, File_priv, Grant_priv, References_priv,Index_priv, Alter_priv, Super_priv, Create_user_priv) VALUES ('localhost', 'root', PASSWORD('root'),'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y');
mysql> flush privileges;
mysql> quit;

And that’s it! You have just reset the root user, its password and its privileges, and you can now enter PHPMyAdmin. If you want to change the password, just change the part that says PASSWORD(‘root’) to PASSWORD(‘whateveryouwant’).

On a side note, if you are having trouble with a running mysqld process that you need to kill, simply type top to see the list of running processes on your system, then take note of the process number of mysqld. For e.g. mine was 3977.

Now type :q to exit, and type sudo bash to go to superuser mode. Next type kill -9 3977 (or whatever number your mysqld process is taking), and you’re good to go.

13 responses to “How To Reset The MySQL Root User Password and Privileges”

  1. Hey!

    I get “-bash: mysqld: command not found” when I typ “mysqld -u root –skip-grant-tables” and I'm sure I'm in the right place (/Applications/MAMP/Library/libexec). I can see “mysqld” among the files when I list the directory. Any idea what I am doing wrong?

  2. try typing in a ./ before the command, like this:

    ./mysqld -u root –skip-grant-tables

    or trying the full path to the command, e.g.

    /Applications/MAMP/Library/libexec/mysqld -u root –skip-grant-tables

  3. Thank you for your quick answer! I solved it by backing up my databases and reinstalled MAMP. Damn, I will not do the same mistake twice! 😀

    I added ./ and now it worked! Strange…

  4. heh yep..mysql can be frustrating…as I've found out too lol

    ./ tells it that u want it to look into the current directory to run the command/program, so that may be why u couldn't run it before (otherwise it'll just look into your path, which is why it couldn't find it)

  5. Hi,

    I’m installing wordpress on my MacBook Pro; I have MySQL on there already but I’d forgotten the root password 🙁 I’m not running MAMP. Just wanted to let you know that your instructions worked perfectly! Thanks! Props to you!


  6. Thousand thanks!!! Your tip just saved a lot of projects i was working on! You’re the best !!!!!!

  7. Thank you so much for this, was searching around for this for ages but this was easy to follow and worked like a charm 🙂

  8. I adjusted the string to where mysqld is located on my computer, but I get the following error:

    /Applications/MAMP/Library/bin/mysqld -u root –skip-grant-tables

    130508 7:05:48 [Warning] Setting lower_case_table_names=2 because file system for /Applications/MAMP/db/mysql/ is case insensitive
    130508 7:05:48 [Warning] One can only use the –user switch if running as root

    130508 7:05:48 [Note] Plugin ‘FEDERATED’ is disabled.
    130508 7:05:48 InnoDB: The InnoDB memory heap is disabled
    130508 7:05:48 InnoDB: Mutexes and rw_locks use InnoDB’s own implementation
    130508 7:05:48 InnoDB: Compressed tables use zlib 1.2.3
    130508 7:05:48 InnoDB: Initializing buffer pool, size = 128.0M
    130508 7:05:48 InnoDB: Completed initialization of buffer pool
    130508 7:05:48 InnoDB: highest supported file format is Barracuda.
    130508 7:05:48 InnoDB: Waiting for the background threads to start
    130508 7:05:49 InnoDB: 1.1.5 started; log sequence number 39729833
    /Applications/MAMP/Library/bin/mysqld: Too many arguments (first extra is ‘–skip-grant-tables’).
    Use –verbose –help to get a list of available options
    130508 7:05:49 [ERROR] Aborting

    130508 7:05:49 InnoDB: Starting shutdown…
    130508 7:05:49 InnoDB: Shutdown completed; log sequence number 39729833
    130508 7:05:49 [Note] /Applications/MAMP/Library/bin/mysqld: Shutdown complete

    please help

  9. The command work fine:
    /Applications/MAMP/Library/bin/mysqld -u root –skip-grant-tables

    while the command not:
    /Applications/MAMP/Library/bin/mysqld -u root -skip-grant-tables

Leave a Reply

Your email address will not be published. Required fields are marked *