InterWeb Task Force

The problem
Recently I had to recover a lost MySQL root password from a system I dont very often use. Somewhere in the mists of time I must have forgotten it.

The solution
This was actually pretty simple. Its just a case of restarting mysql without the authentication tables loaded

sudo /etc/init.d/mysql stop
sudo mysqld_safe --skip-grant-tables &

This stops MySQL and reloads it without the authentication (aka grant) tables, meaning we can connect to MySQL without a password. Beware this locks out all of your customers and applications until the password reset process is completed. Now we need to go in an reset the password

sudo su -
mysql -u root -p

It will prompt you for a password, just hit enter. You should now be inside the MySQL terminal and ready to change the root password:

update user set password=PASSWORD(”NEW-PASSWORD”) where User=’root’;

Of course, replace NEW-PASSWORD with your chosen root password. Now all that remains is to restart MySQL in the normal manner in order for it to pick up the authentication tables correctly and let your customers and applications back in

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

I hope you found this article useful!