How to Recover Mysql root password

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 -p

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;
 mysql> Ctrl+D [to exit]

Now stop the mysql server

$ sudo /etc/init.d/mysql stop

Now start the mysql server normally, so that it could read the password information.

$ sudo /etc/init.d/mysql start