Reset 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  &

This command is very dangerous if you Mysql server is connected to the network. While you are recovering your password, someone can login to your server remotely and do whatever he/she wants. To keep that safe, start your server with “–skip-networking” option, which will not open a network port for the server

$ mysqld_safe --skip-grant-tables --skip-networking &

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

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