How to recover MySQL root password?

In this post we will learn how to recover MySQL root password in the event you have forgotten it. Let’s see how to recover MySQL root password in few steps

Log in to your account using SSH.

To recover MySQL root password. You must login to your account using SSH as the root user. If you don’t know how to connect with ssh click here to read more about how to connect with SSH

Stop the MySQL server

For CentOS and Fedora, type:

service mysqld stop

For Debian and Ubuntu, type

service mysql stop

Restart the MySQL server with the —skip-grant-tables option. To do this, type the following command:

mysqld_safe --skip-grant-tables &

Make sure you type the ampersand (&) at the end of the command. This runs the command in the background and allows you to type the commands in the following steps.
Running MySQL with the —skip-grant-tables option enabled is highly insecure, and should only be done for a brief period while you reset the password. The steps below show you how to stop the mysqld_safe server instance safely and start the MySQL server securely after you have reset the root password.

Log into MySQL using the following command

mysql

At the mysql> prompt, reset the password. To do this, type the following command, replacing NEW-PASSWORD with the new root password

UPDATE mysql.user SET Password=PASSWORD('NEW-PASSWORD') WHERE User='root';

At the mysql> prompt, type the following commands:

FLUSH PRIVILEGES;
exit;

Stop the MySQL server using the following command. You will be prompted to enter the new MySQL root password before the MySQL server shuts down:

mysqladmin -u root -p shutdown

Start the MySQL server normally. To do this, type the appropriate command for your Linux distribution:

For Debian and Ubuntu, type:

service mysql start

For CentOS and Fedora, type

service mysqld start

If you have any questions or thoughts to share, use the comment form below to reach us.