How To Recover/Reset a forgotten MySQL root password

What we need to do is to stop the mysql server and then start it again without reading the user table. To be on the safe side, we won’t allow any network connections while we are doing this operation.

All commands are made as root, if you don’t feel comfortable with this, apply sudo where appropriate.
I am also assuming that you are running Debian 🙂

Stop Mysql Server:

To start the mysql daemon in “safe mode” (read the link for more information) without reading the user table into memory, making it possible to login without password we can issue this command:

Running above command is a security issue since it will allow remote connections and practically anyone can do what they want with your mySQL server.
So, how do we solve this? By adding another parameter to the startup command:

Now the mySQL deamon will only accept local connections.

Ok, time to connect and change the password. Use the mysql client without giving any password

When connected, setup the new password for the root user:

Ok, a new password is set and it’s time to shutdown the mySQL and start it again in “normal” mode.

Now start the mysql server, so that it could read the password information and allow remote connections again (according to your configuration)

All done!

3 comments for “How To Recover/Reset a forgotten MySQL root password

  1. February 25, 2013 at 22:55

    I believe the second line in the third to last command block should be as follows:

    update user set password=PASSWORD(“NEWPASSWORD”) where user=”root”;

    The closing parenthesis on the PASSWORD command is missing as-is.

    • February 25, 2013 at 23:28

      You are correct!
      and the post is updated, thanks for the heads up.

Leave a Reply

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