How to Reset MySQL/MariaDB root Password

Learn how to easily reset the root password for MySQL or MariaDB. Follow our step-by-step guide and regain access to your database in no time. Ensure the security of your database with this essential knowledge.

If you are IT professional, especially if you are someone who responsible for organization database administration, ensuring the security and integrity of your MySQL or MariaDB server is of utmost importance. This should be part of your IT procedure. However, there may be instances where you forget or lose the root password, the key to the kingdom. In such situations, it becomes crucial to know how to reset the root password to regain access to your database.

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 will guide you through the step-by-step process of resetting the root password for MySQL or MariaDB, allowing you to regain control of your database and continue managing it securely.

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 MariaDB server with the --skip-grant-tables option. This option allows you to bypass the password authentication during startup. You can do this by adding the following line to your MariaDB configuration file (e.g., /etc/my.cnf or /etc/mysql/my.cnf) in the [mysqld] section:

skip-grant-tables

3.Save the configuration file and start the MariaDB server. Again, the command may vary depending on your system. For example, you can use systemctl start mariadb or service mysql start.

4. Connect to the MariaDB server as the root user without providing a password. Open a terminal and run the following command:

# 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.

5. Once you’re connected to the MariaDB server, you can update 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 :

UPDATE mysql.user SET Password=PASSWORD('new_password') WHERE User='root';

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

UPDATE mysql.user 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:

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

6. After executing the SQL statement, flush the privileges to ensure the changes take effect. Exit the MariaDB server prompt by typing exit;:

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

MariaDB [mysql]> exit;
Bye

7. Stop the MariaDB server again.
8. Remove or comment out the skip-grant-tables line in the MariaDB configuration file.
9. Save the configuration file and start the MariaDB server once more.
10. You should now be able to connect to the MariaDB server using the newly set password:

mysql -u root -p

It will prompt you to enter the password. Provide the password you set in step 5.

Conclusion

Losing access to the root password of your MySQL or MariaDB server can be a nerve-wracking experience. However, with the right knowledge and steps at hand, resetting the root password becomes a manageable task. In this article, we have walked you through a comprehensive guide on how to reset the root password for MySQL or MariaDB, covering various scenarios and methods. Remember to always prioritize the security of your database by using strong passwords and following best practices for access management. By implementing the steps outlined in this article, you can regain control of your database and continue managing it with confidence and peace of mind.