Resetting mysql root password

As I had mentioned in my previous post, I was composing a post about installing and configuring Cacti. It so happened that I had forgotten my mysql root password on my system. And, as mysql is an essential back-end application for Cacti to run, I had to retrieve it. But, even after multiple attempts I didn't seem to be able to recollect it!

So, I had to reset the root password. After reading a couple of forums I was able to do it. As it seemed non-trivial, I decided to put it up here.

Follow these steps to reset the mysql root password in a GNU/Linux machine.

1. Log into the GNU/Linux machine where the mysql is running.

2. Stop the mysql daemon

raghu@fossphosis:~$ sudo service mysql stop 
mysql stop/waiting

3. After stopping the mysql daemon, create a text file with the following content

UPDATE mysql.user SET Password=PASSWORD('$new_password') WHERE User='root';
FLUSH PRIVILEGES;

The UPDATE statement resets the password for all root accounts, and the FLUSH statement tells the server to reload the grant tables into memory so that it notices the password change.

4. Save this text file which now has the new password, call it maybe mysql-reset.

5. Start mysql with --init-file option:

raghu@fossphosis:$ sudo mysqld_safe --init-file=/home/raghu/mysql-reset &

By doing this we are getting the mysql server to execute the contents of the mysql-reset text file, wherein the root passwords will be reset and assigned with the new value specified in the text file i.e., $new_password.

6. After the server starts successfully, delete the mysql-reset text file, for it has the new password in plain text.

7. You should be now able to log in to mysql as root with the new password.

raghu@fossphosis:~$ sudo mysql -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 82
Server version: 5.1.41-3ubuntu12.6 (Ubuntu)


Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


mysql> 


This is a very helpful procedure for someone like me, who keeps forgetting the paswwords ;-)