[ MariaDB ] How to Reset root User Password

In this tutorial, we will learn about how to reset root user password in MariaDB database system. When we install the MariaDB first time in the system, it does not require password to login to the database as root user. But this is never recommended in production. We always create new users each having its own credential. Similarly, we can also set the initial root user password and if required we can also reset the root user password. I have installed the MariaDB database in the Linux operating system. Let us look at different ways to reset the root user password in this tutorial.

[ MariaDB ] How to Reset root User Password

There are multiple ways to set the initial root user database password. We can use same methods to reset the root user password later in MariaDB. Let us look at each method one by one.

Also read: How to Create Database in MariaDB [Step by Step Guide]

Prerequisite

• Linux operating system installed.
• MariaDB preinstalled in the System.
• User with sudo access/root user required

Method-1: Using MySQL Admin Command

The most common method to set the initial root user password or reset the root user password is by using mysql admin -u root password <new-password> command. In  order to use this method, login to your system and switch to user which has the sudo access.  Please make sure that you are not connected to MariaDB database when following this method. You must be in the Linux command line console to perform this activity as shown below. Once confirmed, follow the below steps to set the root user password.

[root@linuxnasa ~]#

Step-1: Check root User Initial Password

In this step, let us  check if initial root user password is set or not using below command. When a pop up appear for entering the password, simply hit Enter key.  If you are able to login to the database, this means there is no initial password set for the root user.

[root@linuxnasa ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 10.1.20-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

Step-2: Exit from Database

In this step, exit from the database and return back to the Linux command line using below command.

MariaDB [(none)]> exit;

Step-3: Set the root User Password

In this step, we will use mysqladmin -u root password <new-password> command to set the initial password for root user. In the below command, I have given the password as “pass123word”. You can give any other password of your choice.

[root@linuxnasa~]# mysqladmin -u root password pass123word

Step-4: Verify the New Password

In this step, to verify if the new password is set successfully, we will again login to the database using below command. Press the enter key when ask for the password like we did earlier. This time you will not be able to login to the database, instead you will see the below error.

[root@linuxnasa ~]# mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

Next,  again try to login and this time enter the new password for root user which we have configured  as shown below. This time database login will be success.

[root@linuxnasa ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 7
Server version: 10.1.20-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

Method-2: Using MySQL Database and Update the MySQL Table

This method will work only if Method-1 has worked successfully . In this method, we will set or reset the root user password by directly making the changes in the user table of ‘mysql’ database. To perform this method, it is mandatory to login to the MariaDB database. Follow the below steps in sequence.

Step-1: Login to Database

In this step, login to MariaDB database. If you are login for the first time, you can login directly without password. If you have set the root user password, login using that password. To login, use below command.

[root@linuxnasa ~]# mysql -u root -p

Step-2: Switch to mysql Database

In this step, list all the available databases in the MariaDB. When you install the MariaDB, it comes up with few default databases which holds the MariaDB default configuration data. One of the database you will see in the output is “mysql” . This database stores the default root user configuration along with other configuration.

MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| linuxtutorial |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)

Next, switch to “mysql” database using below command.

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

Step-3:  Set root User Password

In this step, first let us list all the tables in “mysql” database using below command. You will notice that there is a table called ‘User’. We will update the record on this table to set the root user password.

MariaDB [mysql]> show tables;

Next,  update the password record for the root user password in user table using below command. In the below command, “new123password” is the new password that I have given. You can give any password of your choice.

MariaDB [mysql]> update user set password=PASSWORD("new123password") where User='root';
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0

Next, flush privileges using below command.

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

Next, exit from the database using below command.

MariaDB [mysql]> exit;

Step-4: Verify the New root User Password

In this step, we will login to the MariaDB database again using the new password. If there are no error, you will be logged in to the database successfully.

[root@linuxnasa ~]# 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.1.20-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

Summary

Reference: mysql.com