This post will describe the series of steps that are needed to reset the MySQL root password when either the root password is known or it is not known. In theory, the later should never happen. However…

“In theory, there is no difference between theory and practice. In practice, there is.”
-Yogi Berra

Resetting Root Password When it is Already Known or Not Set

To reset the root password when it is not set:

mysqladmin -u root password YOUR_NEW_ROOT_PASSWORD

The reset the root password when it is already known use the following command:

mysqladmin -u root -p CURRENT_PASSWORD password NEW_PASSWORD

Resetting the MySQL Root Password When it is Not Known

To reset the root password when it is not known the following steps should be followed. It is important to note that the process will involve shutting down the MySQL server. Additionally, these instructions are for MySQL on Ubuntu. However, they should be similar on other platforms.

Shutdown the MySQL server:

/etc/init.d/mysql stop

Alternatively, the following can be used:

service mysql stop

Load the MySQL daemon in safe mode and send it to the background:

mysqld_safe --skip-grant-tables &

Start the MySQL console:

mysql -u root

Select the database containing all of the privileges and user information:

mysql> use mysql;

Overwrite the old password with the new one:

mysql> update user set password=PASSWORD("NEW_ROOT_PASS") where User='root';

Flush the current privileges:

mysql> flush privileges;

Quit the MySQL console:

mysql> quit

Restart the MySQL daemon:

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

Log in as root to test that it was successfully changed:

mysql -u root -p

Record the new password in a place where it will not be lost next time.