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
- A server running CentOS or Oracle Linux
- MySQL/MariaDB root access.
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.