MySQL - reset a lost root password

There may well be a time where you lose or forget your MySQL root password. This may well, as you can imagine, turn out to be a complete disaster.

OK, so we shouldn't lose root passwords. But what happens if we do?

Resetting the MySQL root password is actually quite easy to do.

However, you do need to have sudo access as we are going to use the sudo command. Not every user will be able to reset the MySQL root password whenever they feel like it!

Stop

Let's go. Log into your VPS and stop mysql:

sudo /etc/init.d/mysql stop

Naturally you will have to enter your user password and, if you have sudo privileges, mysql will be stopped.

Reset password

Next we need to start MySQL in safe mode and ensure it does not read the database tables relating to MySQL user privileges.

sudo mysqld_safe --skip-grant-tables &

Note the ampersand (&) at the end of the command.

As we have started MySQL without referring to the GRANT table, we can log straight in as the MySQL root user:

mysql -u root

Now we are logged in, inform MySQL which database we want to use:

use mysql;

Next, simply change the MySQL root password and flush the privileges:

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

Quit, stop and start

The MySQL root password has been reset so now we need to quit from the safe mode:

quit

Then stop the safe version of MySQL and start the full version which will reference the user privileges we just updated:

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

log in

Now simply log in as the root user and use the newly set password:

mysql -u root -p

Simple as that.

PickledOnion.

Digg it | del.icio.us | reddit | StumbleUpon