How to Reset MySQL or MariaDB Root Password

Introduction

If you forget your root MySQL or MariaDB password, don't worry! It can be reset easily with this tutorial.

Prerequisites To recover your MySQL or MariaDB password, make sure you have root access to the server.

Step 1 - Stop the MySQL/MariaDB Service

Before you can reset the root password, you must first stop the MySQL/MariaDB service.

• For MySQL, use:

  systemctl stop mysql

• For MariaDB, use:

  systemctl stop mariadb

After that, you will access it manually to reset the root password.

Step 2 - Restart MySQL/MariaDB

Now that the server is stopped, run the commands below to start it back up with the --skip-grant-tables option. This bypasses the security measures (permission checking) put in place and allows for the root password to be reset.

mysqld_safe --skip-grant-tables &

The output for the correct run is similar to the following:

mysqld_safe Logging to '/var/log/mysql/error.log'.
mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

If you see an error message like that:

mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists.

please create the mentioned directory and change the owner of it:

mkdir -v /var/run/mysqld
chown -v mysql /var/run/mysqld

After that, run mysqld_safe --skip-grant-tables & again.

Step 3 - Log into the MySQL/MariaDB Shell

Now you can connect to the database server as the root user, without being asked for a password:

mysql

Step 4 - Set a New Root Password

To reload the grant table run the command below:

FLUSH PRIVILEGES;

Now, you can change your MySQL/MariaDB password.

Run the following commands if you have MySQL 5.7.6 and newer or MariaDB 10.1.20 and newer:

ALTER USER 'root'@'localhost' IDENTIFIED BY '<new_password>';

Run the following commands if you have MySQL 5.7.5 and older or MariaDB 10.1.20 and older:

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('<new_password>');

Make sure to replace <new_password> with your new password of choice.

You should see a confirmation that the command was executed successfully.

Query OK, 0 rows affected (0.00 sec)

Step 5 - Restart the Database Server

Stop the database server using the following command:

mysqladmin -p shutdown

You will be prompted to enter the new root password.

Start the database server normally:

• For MySQL, use:

  systemctl start mysql

• For MariaDB, use:

  systemctl start mariadb

Now, you can confirm that the new password has been applied correctly by running:

mysql -p

You will be prompted to enter the new root password. Enter it, and you should be logged into your database server.

Conclusion

Now, you know how to reset your MySQL/MariaDB root password. Make sure your new root password is strong and secure and keep it in a safe place.

License: MIT