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.
Step2: restart the mysql service
service mysql restart
Step 3: login to mysql without password
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;
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:
mysql> update user set authentication_string=password('NEW_PASSWORD') where user='root'; mysql> update user set plugin="mysql_native_password" where User='root';
mysql -u root -p