Yesterday we showed you how to change the root password for MySQL or MariaDB. This post shows you how to reset or recover a forgotten or lost root password for these database servers.
This tutorial is only necessary if you’ve forgotten or lost the root password to MySQL or MariaDB and you cannot login.
Before installing MySQL server, you’re given a chance to set the root password. This is the password that you’ll need to logon to the server. For MariaDB, you may have to run the command below after installing.
Continue below to learn how to reset or recover a lost password for the root user.
Step 1: Stop the database server
Before you can reset the root password, you must first stop MySQL or MariaDB server. Run the commands below to do that. One of the commands below to work on your systems.
sudo systemctl stop mysql sudo systemctl stop mariadb /etc/init.d/mysqld stop
Step 2: Start MySQL or MariaDB without permissions checks
Now that the server is stopped.. run the commands below to start it back up with the –skip-grant-tables option. This allows to bypass the security measures put in place and allows for the root password to be reset.
sudo mysqld_safe --skip-grant-tables &
Step 3: Reset the Root Password
After starting the server with the bypass security measures, run the commands to logon
sudo mysql -u root
Then use the commands below to reset the password.
USE mysql; UPDATE user SET password=PASSWORD('new_password_here') WHERE User='root' AND Host ='localhost'; FLUSH PRIVILEGES; exit;
Replace new_password_here with the password you want to change to for the root user.
Finally, restart the server and test the new password you just created. One of the commands below should work on your systems.
sudo systemctl stop mariadb sudo systemctl start mysqldb /etc/init.d/mysqld stop /etc/init.d/mysqld start
Try logging in again with the new password
sudo mysql -u root -p
This article showed you how to reset a forgotten or lost root password for MySQL or MariaDB. This is a last ditch effort when you can’t remember the root password and the only option is to recovery it.
Don’t use this method to change the password for the root user you already know. We’ve written tutorials on do that. Only use this if you don’t know the root password and you wish to logon to the database server as root.
Enjoy and please come back soon.
You may also like the post below: