How to Recover Mysql root password

One of the very worst scenarios for a system administrator is when they forget a password for the root account. That is the same case when a Database admin does the same for the root account for Mysql Database. So to help here, these are the following steps which one can perform to recover the root password back for Mysql database.

Stop Mysql Server:

$ sudo /etc/init.d/mysql stop

Then, start the mysql server with skip-grant-tables options. –skip-grant-tables option will force the mysql process not to read the user table into memory, so that when you try to login to the Mysql server, it won’t ask for the password.

$ sudo mysqld_safe --skip-grant-tables &

Now connect to mysql server using mysql client without giving any password

$ mysql -u root

Now setup the new password for the root user:

mysql> use mysql;
mysql> update user set password=PASSWORD("NEWPASSWORD") where user="root";
mysql> flush privileges;

Now stop the mysql server and start the mysql server normally, so that it could read the password information .and login to Mysql server using your new password.

$ sudo /etc/init.d/mysql stop

$ sudo /etc/init.d/mysql start

$mysql -u root -p