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.
