Reset mySQL root password

Many times, there may be a situation when you lost your mysql root user password and you need to reset it.

It takes only few minutes to reset the root user password if you have exact commands or steps to be followed.

I am writing this blog to help out and save time of all users who are facing issue with mysql login. Here are the steps I used to reset the mySQL root user password.

Step1: First step is to login to mysql without a password. To do so, add skip-grant-tables option in mysqld config file  under [mysqld] to allow login without password.

nano /etc/mysql/mysql.conf.d/mysqld.cnf

mysql-login-without-password

Step2: restart the mysql service

service mysql restart

Step 3: login to mysql without password

mysql -uroot

Step 4: Flush existing privileges and set new password for root user

mysql> FLUSH PRIVILEGES;

mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('NEW_PASSWORD');

In this sql, ‘root’ is the user and ‘localhost’ is the hostname. You can check all users and host names by following sql:

SELECT user, host FROM mysql.user;

mysql> exit

Step 5: You have reset the password. No you can try login with new password:

mysql -uroot -p

Fix socket issue

If you are getting an error message “Plugin ‘auth_socket’ is not loaded”, you should follow these steps to fix this socket issue:

use mysql

mysql> update user set authentication_string=password('NEW_PASSWORD') where user='root';

mysql> update user set plugin="mysql_native_password" where User='root';

flush privileges;

exit;

Check password

mysql -u root -p

Leave a Reply

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

Back to Top
Shares