A Simple Way to Reset MySQL/MariaDB root Password

In this guide, we are going to learn a simple way to reset MySQL/MariaDB root password just in case you have lost, forgotten or just want to change it.

Resetting 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

or

mysql -u root

Reset MariaDB/MySQL root Password

Once you are logged in to database, run the command below to reload grant tables and reset root password.

FLUSH PRIVILEGES;

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.