Step by step guide to reset root password in Mysql

Working with MySQL or MariaDB database, you might have faced a situation where you might have forgotten root or another user’s password or being SysAdmin, you might have had to deal with users who have forgotten their password. In this tutorial, we will learn reset root password in Mysql or resetting any other user account’s password (same steps can also be used to reset user’s password in MariaDB as well).

( Recommended Read : Installing & configuring MariaDB on CentOS/RHEL )

 

Step 1- Stop Mysql service

Firstly we need to stop the mysql service. If using Ubuntu , run the following command,

$ sudo service mysql stop

For Centos/RHEL, run the following command,

$ sudo service mysqld stop

 

Step 2- Start MySQL with custom option

We will now restart the MySQL server in safe mode with option ‘–skip-grant-tables’,

$ mysqld_safe –skip-grant-tables &

 Note – Running MySQl with ‘–skip-grant-tables’ is nor recommended & should only be done to reset the password.

 

Step 3 – Login to Mysql & resetting passowrd

We will now access the MySQL server by executing the following command,

$ mysql

Now run the following command to reset the passowrd,

$ UPDATE mysql.user SET Password=PASSWORD(‘updated-password’) WHERE User=’root’;

here, we have used “ User=’root’ ” to change the root user account password but we can also reset the password for another account by replacing ‘root’ with that user account. Now run the following command to exit out of mysql safely,

$ FLUSH PRIVILEGES;

$ exit;

Restart MySQL server to & then we will be able to login to root account with the updated password. For Centos/RHEL, run

$ service mysqld restart

For Ubuntu, run the following,

$ sudo service mysql restart

( Recommended Read :  MariaDB administration commands for beginners )

 

Feel free to submit your queries/questions using the comment box down below.

 

 

If you think we have helped you or just want to support us, please consider these :-

Connect to us: Facebook | Twitter | Google Plus

Become a Supporter – Donate us some of you hard earned money:

Linux TechLab is thankful for your continued support.

Shusain

Passionate about Linux & open source. Loves to learn, read & write about Linux as well as new technologies.

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *