Cyberithub

6 Simple Steps to Change/Reset MariaDB root password on RHEL/CentOS 7/8

Are you looking for the Steps to Reset MariaDB root password on RHEL/CentOS 7/8 ? Have you forgotten the MariaDB root password ? If you are looking for the answer to these questions then you came to the right place. There is always a possibility that one can forget the root password which will further prevent the root login to MariaDB Server. So to handle this situation one can always reset the root password by starting the Server without permission checking and then reset mariadb root password. This can be easily performed in 6 Simple Steps as described in below tutorial.

6 Simple Steps to Change/Reset MariaDB root password on RHEL/CentOS 7/8 1

Simple Steps to Change/Reset MariaDB root password on RHEL/CentOS 7/8

Also Read: Step by Step Guide to Install Apache 2.4.6 web server on RHEL 7/CentOS 7

Step 1: Check MariaDB Service Status

In the first step we need to check the status of MariaDB service using either service mariadb status or systemctl status mariadb command as shown below. You need to make sure that MariaDB service is in stopped state before moving on to next step.

[root@localhost ~]# service mariadb status
Redirecting to /bin/systemctl status mariadb.service
● mariadb.service - MariaDB database server
Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
Active: active (running) since Mon 2020-02-24 08:10:50 UTC; 7 months 19 days ago
Process: 929 ExecStartPost=/usr/libexec/mariadb-wait-ready $MAINPID (code=exited, status=0/SUCCESS)
Process: 831 ExecStartPre=/usr/libexec/mariadb-prepare-db-dir %n (code=exited, status=0/SUCCESS)
Main PID: 928 (mysqld_safe)
CGroup: /system.slice/mariadb.service
├─ 928 /bin/sh /usr/bin/mysqld_safe --basedir=/usr
└─1156 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/log/mariadb/mariadb.log --pid-fil...

Feb 24 08:10:45 ip-172-30-0-247.ec2.internal systemd[1]: Starting MariaDB database server...
Feb 24 08:10:45 ip-172-30-0-247.ec2.internal mariadb-prepare-db-dir[831]: Database MariaDB is probably initialized in /var/lib/mysql already, nothing is done.
Feb 24 08:10:45 ip-172-30-0-247.ec2.internal mariadb-prepare-db-dir[831]: If this is not the case, make sure the /var/lib/mysql is empty before running mariad...db-dir.
Feb 24 08:10:46 ip-172-30-0-247.ec2.internal mysqld_safe[928]: 200224 08:10:46 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
Feb 24 08:10:46 ip-172-30-0-247.ec2.internal mysqld_safe[928]: 200224 08:10:46 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
Feb 24 08:10:50 ip-172-30-0-247.ec2.internal systemd[1]: Started MariaDB database server.
Hint: Some lines were ellipsized, use -l to show in full.

NOTE:

Please note that here I am using root user to run all the below commands. You can use any user with sudo access to run all these commands. For more information Please check Step by Step: How to Add User to Sudoers to provide sudo access to the User.

Step 2: Stop MariaDB Service

As we can see in above step, MariaDB service is currently running so we need to stop it using either service mariadb stop  or systemctl stop mariadb command to proceed further.

[root@localhost ~]# service mariadb stop
Redirecting to /bin/systemctl stop mariadb.service

NOTE:

Please also check the MariaDB version using mysql --version command before going through the steps to reset mariadb root password as different version might have different steps to reset the password.

Step 3: Reset MariaDB root Password 

Once MariaDB is stopped, we need to start the Server without permission checking by using mysqld_safe --skip-grant-tables & command and then login using mysql command as shown below. Here --skip-grant-tables option will skip the permission checking and ampersand(&) will start the process in background so that you can continue with the prompt.

[root@localhost ~]# mysqld_safe --skip-grant-tables &
[1] 3158
201014 07:35:31 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
201014 07:35:31 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 1
Server version: 5.5.56-MariaDB MariaDB Server

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

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

MariaDB [(none)]>

NOTE:

If your Server expected to have Client Connections then you might want to use --skip-networking option as well along with --skip-grant-tables option to avoid any connection to the currently started MariaDB Service.

Here we are updating root password with the new password Test@123$ using below UPDATE query. Check more about UPDATE query on MariaDB Official Website.

MariaDB [(none)]> UPDATE mysql.user SET Password=PASSWORD('Test@123$') WHERE User='root';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0

After updating the password we need to flush the privileges using flush privileges query to refresh the changes done in the Server.

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

Then exit the prompt by using exit query as shown below.

MariaDB [(none)]> exit;
Bye

Step 4: Shutdown MariaDB Server

In the next step, you need to shutdown the MariaDB Server using mysqladmin -u root -p shutdown command as shown below. Here it will prompt for the root password which will be the new password we have updated in the previous step.

[root@localhost ~]# mysqladmin -u root -p shutdown
Enter password:
[1]+ Done mysqld_safe --skip-grant-tables

Step 5: Start MariaDB Service

After shutting down the Server which we started without permission checking, we need to start the mariadb server again but this time with permission checking using either service mariadb start or systemctl start mariadb command as shown below.

[root@localhost ~]# service mariadb start
Redirecting to /bin/systemctl start mariadb.service

Once started you can check the status of MariaDB service using service mariadb status command as shown below. We need to make sure there is no error here.

[root@localhost ~]# service mariadb status
Redirecting to /bin/systemctl status mariadb.service
● mariadb.service - MariaDB database server
Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
Active: active (running) since Wed 2020-10-14 07:37:50 UTC; 5s ago
Process: 3388 ExecStartPost=/usr/libexec/mariadb-wait-ready $MAINPID (code=exited, status=0/SUCCESS)
Process: 3355 ExecStartPre=/usr/libexec/mariadb-prepare-db-dir %n (code=exited, status=0/SUCCESS)
Main PID: 3386 (mysqld_safe)
CGroup: /system.slice/mariadb.service
├─3386 /bin/sh /usr/bin/mysqld_safe --basedir=/usr
└─3548 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/log/mariadb/mariadb.log --pid-fil...

Oct 14 07:37:48 ip-172-30-0-247.ec2.internal systemd[1]: Starting MariaDB database server...
Oct 14 07:37:48 ip-172-30-0-247.ec2.internal mariadb-prepare-db-dir[3355]: Database MariaDB is probably initialized in /var/lib/mysql already, nothing is done.
Oct 14 07:37:48 ip-172-30-0-247.ec2.internal mariadb-prepare-db-dir[3355]: If this is not the case, make sure the /var/lib/mysql is empty before running maria...db-dir.
Oct 14 07:37:49 ip-172-30-0-247.ec2.internal mysqld_safe[3386]: 201014 07:37:49 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
Oct 14 07:37:49 ip-172-30-0-247.ec2.internal mysqld_safe[3386]: 201014 07:37:49 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
Oct 14 07:37:50 ip-172-30-0-247.ec2.internal systemd[1]: Started MariaDB database server.
Hint: Some lines were ellipsized, use -l to show in full.

Step 6: Login to MariaDB with New Password

Now we need to test the new reset mariadb root password by logging in to the Server using mysql -u root -p command as shown below. Once you provide the root password it should allow you to login just the way it is described below.

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

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

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

MariaDB [(none)]>

 

 

 

Popular Recommendations:-

Best Steps to Install Java on RHEL 8/CentOS 8

5 Examples to Turn Off SELinux Temporarily or Permanently on RHEL 8/CentOS 8

Useful C Program to List Network Interfaces using only 30 Lines of Code

Best Explanation of Wrapper Classes in Java: Autoboxing and Unboxing with Examples

5 Best Ways to Become root user or Superuser in Linux (RHEL/CentOS/Ubuntu)

How to Install PHP on RedHat/CentOS 7 with Easy Steps

7 Easy Steps to Install PHP on RHEL 8/CentOS 8

Easy Steps to Install Java on Ubuntu 20.04

Leave a Comment