Recovering MySQL root password

Have you ever dealt with different Mysql Installations on different machines?
Have you ever mixed up root passwords of different installations?

I recently had that happened to me, I decided to look further into it and find a good way to do a password reset for my root account.

Here is what we will do:

1. Stop the mysql daemon.


2. Start mysql with "--skip-grant-table" option.


3. login with your root account with no password.


4. update the mysql.users table with your new password.


5. restart the mysql default daemon.

Step1: stop the running mysql daemon

/etc/init.d/mysql stop

Output:

* Stopping MySQL database server mysqld  [ OK ]

Note: Make sure it is stopped with the following command

ps aux | grep mysql

You should see only one entry for the grep command you just typed.

Step2: we start mysql with "--skip-grant-table" option.

mysqld_safe --skip-grant-table

You should find output similar when you hit

ps aux | grep mysql7090 pts/1    S      0:00 /bin/sh /usr/bin/mysqld_safe --skip-grant-table7129 pts/1    Sl     0:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking --port=3306 --socket=/var/run/mysqld/mysqld.sock --skip-grant-table7131 pts/1    S      0:00 logger -p daemon.err -t mysqld_safe -i -t mysqld7246 pts/2    S+     0:00 grep --color=auto mysql

You should login successfully without any password.

mysql -uroot

Step4: change your root password

use mysql;
update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root';
flush privileges;
quit

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

You should be able to login normally with your new password.