Sunday, July 26, 2009

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

* 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/ --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;

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

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