Lost MySQL Password

Have you ever come across a situation where you've lost your mysql root password?

First go to a command prompt/terminal, and stop the mysqld service. In Centos, this is achieved via:

service mysqld stop

Once this is stopped, we can then restart mysql in safe mode, with the option to not check passwords. Now be very wary of this, you either need to be very fast, or disconnect the box from the internet, as anyone can log into the server as root without using a password.

 mysqld_safe --skip-grant-tables

mysql

This will then log you into the mysql server, from here you simply have to update the user table, and set the root password to something you require.

use mysql;

update user set password=PASSWORD("newpassword") where user="root" and host="localhost";

This will then reset the root password to "newpassword". Quickly exit out of mysql and type the following command to stop the mysql service, and restart not longer in safe mode:

service mysqld restart

Now simply test that you can log into the server using your new password, in this example "newpass"

mysql -uroot -pnewpass

Congratulations, your root mysql password is now fully restored :)