a different way to recover your lost mysql root password

Yesterday my friend at the office asked me for help to reset a mysql root password because he had forgotten¬† it somehow.¬† He told me that he had tried the usual way to do it but it just didn’t work. Tutorials for the problem are so many in the internet. If you ask google using these keywords (mysql root password reset), there are many of them will show up on your screen. Most of them will tell you to do these steps (which is the usual way) :

Step # 1: Stop the MySQL server process
# /etc/init.d/mysql stop

Step # 2: Start the MySQL (mysqld) .server/daemon process with the –skip-grant-tables option so that it will not prompt for password
# mysqld_safe –skip-grant-tables &

Step # 3: Connect to mysql server as the root user
# mysql -u root

Step # 4: Setup new root password
mysql> use mysql;
mysql> UPDATE user SET Password=PASSWORD(‘new-password’) WHERE User=’root’;
mysql> flush privileges;
mysql> quit
Step # 5: Stop MySQL server
# /etc/init.d/mysql stop

Step # 6: Start MySQL server and test it with the new-password

# /etc/init.d/mysql start
# mysql -u root -p

I’ve also tried those steps, but it just didn’t work. For a few hours i searched the internet looking for a different way or some other clues about that subject. I finally found some clues, it had something to do with mysql old password. After reading the page, i had an idea to do a little change on steps #4 above, like this :

Step # 4: Setup new root password
mysql> use mysql;
mysql> UPDATE user SET Password=OLD_PASSWORD(‘new-password’) WHERE User=’root’;
mysql> flush privileges;
mysql> quit

I make it bold for you to see the difference easier. And it worked like a charm.

Reblog this post [with Zemanta]