How to reset MariaDB root password in Linux

Share on Social Media

Learn how to reset MariaDB root password in Linux with this comprehensive guide. Follow our step-by-step instructions to regain access to your database securely and efficiently. #centlinux #linux #mysql

What is MariaDB?

MariaDB is an open-source relational database management system (RDBMS) that is a fork of MySQL. It was developed by the original developers of MySQL after concerns arose about Oracle Corporation’s acquisition of MySQL. MariaDB aims to maintain compatibility with MySQL while improving performance, adding features, and ensuring ongoing community contributions.

Key features of MariaDB include:

Compatibility: MariaDB is designed to be highly compatible with MySQL, ensuring that applications and tools developed for MySQL can work with MariaDB with minimal or no changes.
Performance: MariaDB often includes performance enhancements over MySQL, such as improved storage engines and optimizations for complex queries.
Storage Engines: It supports a variety of storage engines, including InnoDB, MyISAM, and others. MariaDB also introduces new storage engines like Aria and ColumnStore for specific use cases.
Security: Enhanced security features, including improved user account management and encryption options.
Scalability and High Availability: Supports replication and clustering features to ensure high availability and scalability for large-scale deployments.
Community-Driven Development: MariaDB is developed by the community and led by the MariaDB Foundation, ensuring that it remains open-source and incorporates contributions from a broad range of developers.
Open Source: MariaDB is released under the GNU General Public License, making it free to use, distribute, and modify.

MariaDB is widely used in web applications, enterprise software, and other database-driven solutions due to its robustness, performance, and open-source nature.

Recommended Training for You: Mastering SQL with MariaDB: An Essential Beginner’s Guide

Problem Definition

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.

MariaDB Server 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 root password

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

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

If you are new to Linux and facing difficulty in working at Linux Bash prompt. We recommend that, you should read The Linux Command Line, 2nd Edition: A Complete Introduction by William Shotts.

Final Thoughts

Resetting the MariaDB root password in Linux can be a straightforward process if you follow the correct steps. This guide should help you regain access to your database efficiently and securely. If you encounter any issues or prefer professional assistance, I’m here to help.

I offer expert services for MariaDB configuration and troubleshooting. Visit my Fiverr profile to learn more and get started: MySQL Database Administrator

Ensure your database remains secure and functional with professional support today!