In this guide, we will learn how to reset the MySQL / MariaDB root password in the easiest way in case you lose, forget or just want to change it.
Reset MySQL / MariaDB root password
To reset the reset MySQL / MariaDB root password, perform the following steps;
Note: You must execute the following command as root or with sudo privileges.
Stop MariaDB / MySQl service
Use one of the following two commands to log in to the server hosting the database and stop the database service;
systemctl stop mariadb
systemctl stop mysql
Restart the database in safe mode
Use the following command to start the database in safe mode;
mysqld_safe --skip-grant-tables --skip-networking &
This will bypass the authentication process and disable any connections to the database. Therefore, you can log in to the database without a password.
Use one of the following two commands to log in to the database as the root user;
mysql -u root
Reset root password
After logging in to the database, run the following command to reload the grant tables and reset the root password.
update mysql.user set password=PASSWORD("StrongPASSW0rd") where user='root';
Reload the authorization form and exit.
FLUSH PRIVILEGES; quit
Stop the database service
Since the database service is started to run in the background to reset the password, run the following command to stop it safely. This will prompt you for the new password set above.
mysqladmin -u root -p shutdown
Start the database service
This is now complete and you can now start the database normally.
systemctl start mariadb
You have successfully reset the MySQL / MariaDB root password and you can now access the database with all administrative privileges.