How to recover MySQL root password?

How to recover MySQL root password?

Let’s learn how to recover MySQL root password in case you have lost it. Know how to recover MySQL root password.

Log in to account using SSH.

You must login to your account using SSH as the root user to recover MySQL root password.

Stop the MySQL service

Use following command for CentOS and Fedora:

service mysqld stop

Use following command for Debian and Ubuntu:

service mysql stop

Use following command with the —skip-grant-tables option, to restart the MySQL server.

mysqld_safe --skip-grant-tables &
  • Type the ampersand (&) correctly at the end of the command, this will runs the command in the background and allows you to type the commands in the following steps.
  • Executing MySQL command with the —skip-grant-tables option enabled is very insecure, and should only be done while you reset the password.
  • The steps below shows 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, to reset the password 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, type the appropriate command for your Linux distribution:
For Debian and Ubuntu:

service mysql start

For CentOS and Fedora:

service mysqld start

Nishant Vaity

Knowledgeable and skilled Technology Lead with an ability of software development and supervision. Possess a Bachelor of Science (BS) in Information Technology along with 11+ years of experience with hands-on coding and team management. By profession, I am a Software Engineer, Technology Mentor & Entrepreneur. Passionate about the technologies I use and always eager to share & learn more from that passion.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: