How to Reset MySQL/MariaDB root Password

Introduction

Have you ever forgotten your MySQL/MariaDB root password? It can be a frustrating experience, especially if you don’t know how to reset it. Fortunately, there is a simple method that can help you reset your forgotten MySQL/MariaDB root password quickly and easily. In this article, we’ll discuss the steps involved in resetting your lost MySQL/MariaDB root password. With this information, you will be able to reset your forgotten MySQL root password in no time.

Prerequisites

Reset MySQL/MariaDB root Password via command line

1. Stopping MySQL or Maria DB Server :

# sudo systemctl stop mysql

or

# sudo systemctl stop mariadb

2. Start the MySQL/MariaDB server without loading the grant tables:

# sudo mysqld_safe --skip-grant-tables

Example with output :

# sudo mysqld_safe --skip-grant-tables
[1] 1936
[root@node1 ~]# 210919 07:46:09 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
210919 07:46:09 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

3. Login to MySQL database using root:

# mysql -u root

Example with output :

# mysql -u root
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 1234567
Server version: 5.5.68-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

4. Connect to database called mysql :

MariaDB [(none)]> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

5. Changing the Root Password

For MySQL 5.7.6 and newer as well as MariaDB 10.1.20 and newer, use the following command. The following command tested at mysql version 8.0.4 :

MariaDB [mysql]> ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

For MySQL 5.7.5 and older as well as MariaDB 10.1.20 and older, use:

MariaDB [mysql]> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password');

Note : Make sure to replace new_password with your new password of choice.

If ALTER USER statement doesn’t work for you, try to modify the user table directly:

MariaDB [mysql]> update user SET PASSWORD=PASSWORD("new_password") WHERE USER='root';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

MariaDB [mysql]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> exit
Bye

6. Restart the database server normally :

MariaDB [mysql]> mysqladmin -u root -p shutdown

7. Start MySQL or Maria DB Server :

# sudo systemctl start mysql

or

# sudo systemctl start mariadb

Conclusion

Keeping track of your MySQL root passwords can be a daunting task. Not only do you need to remember the passwords themselves, but also the best practices for keeping them secure. Fortunately, there are a number of software solutions available that can help you keep track of your passwords and ensure that they remain secure.