In this guide, we are going to learn how the simplest way of how to reset MySQL/MariaDB root password just in case you have lost, forgotten or just want to change it.
Reset MySQL/MariaDB root Password
To reset reset MySQL/MariaDB root password, step through the following;
NOTE: You must execute the following commands as root or with sudo privileges.
Stop MariaDB/MySQl Service
Login to the server hosting DB and stop DB service with either of the he following commands;
systemctl stop mariadb
systemctl stop mysql
Restart the Database in a safe mode
Start the database in safe mode using the command below;
mysqld_safe --skip-grant-tables --skip-networking &
This bypasses authentication processes and disable any connection to database. Hence you can login to database without password.
Login to Database
Login to database as root using either of the following commands;
mysql -u root
Reset root Password
Once you are logged in to database, run the command below to reload grant tables and reset root password.
update mysql.user set password=PASSWORD("StrongPASSW0rd") where user='root';
Reload grant tables and exit.
FLUSH PRIVILEGES; quit
Stop the Database Service
Since the database service was launched to run in background in order to reset password, run the command below to safely stop it. This will prompt you to enter the new password set above.
mysqladmin -u root -p shutdown
Start the Database Service
Now that all is done, you can now start the database normally.
systemctl start mariadb
You have successfully reset MySQL/MariaDB root password and can now access the database with all administrative rights.