How to recover from a lost MySQL root password

How to recover from a lost MySQL root password

Summary

How can you recover from a lost MySQL or MariaDB root password?

Synopsis

If you run into the situation where you've lost the root password for your MySQL instance the account can be recovered so long as you still have root access to the server itself.

Discussion

The recovery occurs over a couple of steps. First, we need to stop the existing service, then we need to stop the mysql service, then we need to start it in a special mode, then we need to change the root password, stop the service again, and then start it back up. Once these steps are complete we will have a service up and running with a root account that has the password you have set.

1. Stop mysql

#RHEL 6

service mysqld stop

#RHEL 7

systemctl stop mysqld


2. Start mysql while skipping the grants table

mysqld_safe --skip-grant-tables &


3. Log into mysql and change the root password

#From command line

mysql -u root mysql

#Once in mysql

> UPDATE user SET password=PASSWORD("passwordhere") WHERE user="root";   
> FLUSH PRIVILEGES;
> exit


4. Stop mysqld

#From command line

/etc/init.d/mysql stop


5. Start mysqld

#RHEL 6

service mysqld start

#RHEL 7 

systemctl start mysqld


Once this is complete you should be able to log into mysql with the new root password you set in the previous steps.

Was this article helpful? Yes No
No ratings
Version history
Revision #:
2 of 2
Last update:
‎Sep 25, 2019 07:35 PM
Updated by:
 
Contributors