How to reset MariaDB root password on CentOS 7

MariaDB the famous fork of MySQL database. It is free and open-source under GNU GPL license. Due to the acquisition of MySQL by Oracle corporation, Red Hat decided to include MariaDB as the default RDBMS in their RHEL 7 operating system.

We have already wrote an article on Installation of MariaDB 10.3 database on CentOS 7. Here, we are considering a very common scenario in which the root (superuser of MySQL databases) password has been forgotten. We will cover the complete step by step procedure to reset a new password of MariaDB root user on CentOS 7.

Although, we are using MariaDB 10.3 on CentOS 7.6, but the same procedure is good enough for other forks of MySQL (such as Percona) as well. And the same procedure can be used on other distros of Linux (like Ubuntu, Fedora, etc.) with minor variations according to the platform.

Reading Advice:

Learning MySQL and MariaDB: Heading in the Right Direction with MySQL and MariaDB

 

 

System Specification:

We are using the same virtual machine that we have configured in our previous article Installing MariaDB 10.3 Server on CentOS 7.

• Hostname - mariadb-01.example.com
• IP Address - 192.168.116.130/24
• Operating System - CentOS 7.6
• MariaDB Server - 10.3.12

 

Reset MariaDB 10.3 root password on CentOS 7:

Check the version of our MariaDB Server.

# mysql --version
mysql  Ver 15.1 Distrib 10.3.12-MariaDB, for Linux (x86_64) using readline 5.1

We have enabled the mariadb.service, therefore MariaDB instance has been started automatically during system start-up. During this auto-start process, privileges tables have been loaded to memory.

To reset the password of root user we need to start the MariaDB instance without loading privileges tables.

Therefore, first of all we must stop MariaDB service.

# systemctl stop mariadb.service

Now, we can start MariaDB database in safe mode without loading the privileges tables as follows.

# mysqld_safe --skip-grant-tables --skip-networking &

Connect with MariaDB instance as root user.

# mysql -u root
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.3.12-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.

MariaDB [(none)]>

This time, it didn't ask for any password.

We are successfully login to MariaDB instance as root user without any password.

Before we can set the new password for root user, we have to load the privileges tables into memory.

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.002 sec)

Now, we can set a new password for MariaDB root user.

MariaDB [(none)]> ALTER USER 'root'@'localhost' IDENTIFIED BY 'a';
Query OK, 0 rows affected (0.001 sec)

Reload the privileges tables again.

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.000 sec)

New password for MariaDB root user has been set.

Exit from MariaDB prompt.

MariaDB [(none)]> exit
Bye

Currently, MariaDB instance is running in safe mode, therefore, we must restart it in normal mode.

Kill MariaDB instance process as follows.

# kill $(cat /var/lib/mysql/mariadb-01.example.com.pid)

Start MariaDB service in normal mode.

# systemctl start mariadb

Login to MariaDB database as root user using new password.

# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.3.12-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.

MariaDB [(none)]>

Our new root password is working fine.

We have successfully reset password of MariaDB root user on CentOS 7.