MySQL - reset a lost MySQL root password

The MySQL root password allows full access to the MySQL database and allows for all actions to be undertaken including creating new users, new databases, setting access rules and so on.

Losing one can be a difficult issue to encounter. Luckily, resetting the root password is easy as long as you have sudo access to the Slice.


Not the Slice root user

A common issue is confusing the Slice root user with the MySQL root user.

The Slice root user is the server's main user. The MySQL root user has complete control over MySQL only. The two 'root' users are not connected in any way.

Stop MySQL

The first thing to do is stop MySQL. Assuming you are using Ubuntu Hardy the command is as follows:

sudo /etc/init.d/mysql stop

Safe mode

Next we need to start MySQL in safe mode - that is to say, we will start MySQL but skip the user privileges table. Again, note that you will need to have sudo access for these commands so you don't need to worry about any user being able to reset the MySQL root password:

sudo mysqld_safe --skip-grant-tables &

Note: The ampersand (&) at the end of the command is required.

Login

All we need to do now is to log into MySQL and set the password.

mysql -u root

Note: No password is required at this stage as when we started MySQL we skipped the user privileges table.

Next, instruct MySQL which database to use:

use mysql;

Reset password

Enter the new password for the root user as follows:

update user set password=PASSWORD("mynewpassword") where User='root';

and finally, flush the privileges:

flush privileges;

Restart

Now the password has been reset, we need to restart MySQL by logging out:

quit

and simply stopping and starting MySQL:

sudo /etc/init.d/mysql stop
...
sudo /etc/init.d/mysql start

Done.

Login

Test the new password by logging in:

mysql -u root -p

You will be prompted for your new password.

Summary

Resetting the MySQL root password is very simple using the above procedure and can save many headaches.

PickledOnion

Article Comments:

Jonathan Zuber commented Thu Jan 15 17:27:02 UTC 2009:

Good run through - a note which I had problems with:

I could not start safe mode and running /etc/init.d/mysql stop would not stop mysql.

I had to type:

ps -e | grep mysql | grep -v grep

to see all the mysql process' running, which showed :

XXXX ? 00:00:00 mysqld_safe XXXX ? 00:00:00 mysqld

then I did a:

sudo kill "XXXX" (XXXX is the pid)

THEN I was able to do the:

sudo mysqld_safe --skip-grant-tables &

and the rest of the tutorial.

Thanks!

Deepa commented Wed May 18 01:33:26 UTC 2011:

I ran into this problem today and one of the slice host tech support guys suggested this and it works like a charm! Amazing!

Want to comment?


(not made public)

(optional)

(use plain text or Markdown syntax)