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:

mysql>

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.