Recovering a lost Mysql root password

Posted: 11th August 2010 by Babar Shafiq in Security
Tags: , ,

Lost MySQL root password Recovery

/etc/init.d/mysql stop
mysqld_safe --skip-grant-tables &

This stops MySQL and reloads it without the authentication (grant) tables, so we can connect to MySQL without a password. Beware this locks out all of your applications until the password reset process is completed. Now we need to go in an reset the password

su -
mysql -u root -p

It will prompt you for a password, just hit enter. You should now be inside the MySQL terminal and ready to change the root password:

update user set password=PASSWORD(”NEW-PASSWORD”) where User=’root’;

Of course, replace NEW-PASSWORD with your chosen root password. Now all that remains is to restart MySQL in the normal manner in order for it to pick up the authentication tables correctly and let your customers and applications back in

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