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:

  1. 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.
  2. Performance: MariaDB often includes performance enhancements over MySQL, such as improved storage engines and optimizations for complex queries.
  3. 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.
  4. Security: Enhanced security features, including improved user account management and encryption options.
  5. Scalability and High Availability: Supports replication and clustering features to ensure high availability and scalability for large-scale deployments.
  6. 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.
  7. 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.

How to reset MariaDB root password in Linux
How to reset MariaDB root password in Linux

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.

Recommended Training: The Ultimate MySQL Bootcamp: Go from SQL Beginner to Expert from Colt Steele

1187016 51b3
show?id=oLRJ54lcVEg&bids=1628165

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

Output:

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

Output:

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.

FLUSH PRIVILEGES;

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

ALTER USER 'root'@'localhost' IDENTIFIED BY 'a';

Reload the privileges tables again.

FLUSH PRIVILEGES;

New password for MariaDB root user has been set.

Exit from MariaDB prompt.

exit

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

Output:

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.

Frequently Asked Questions (FAQs)

Why would I need to reset the MariaDB root password?
You might need to reset it if you forgot the password, inherited a system without credentials, or encountered authentication errors.

Do I need root access to the Linux system to reset MariaDB’s root password?
Yes, you need sudo or root privileges on the Linux system to modify MariaDB’s authentication settings.

Will resetting the MariaDB root password affect my databases?
No, resetting the password only changes authentication—your databases and data remain intact.

Is it necessary to stop MariaDB before resetting the root password?
Yes, you must stop the MariaDB service temporarily to bypass authentication for the reset process.

What should I do after resetting the password?
Restart MariaDB normally, log in with the new password, and ensure all applications using the old credentials are updated.

Final Thoughts

Resetting the MariaDB root password in Linux is a straightforward but critical task that ensures continued administrative access to your database system. By stopping the MariaDB service, starting it in safe mode, and updating the root password securely, you can regain control without compromising data integrity.

Always remember to restart the service normally and verify access after resetting the password. For better security, ensure the new password is strong and stored securely, and consider disabling remote root access if not needed.

Need a dependable Linux system administrator? I specialize in managing, optimizing, and securing Linux servers to keep your operations running flawlessly. Check out my services on Fiverr!

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

Looking for something?

Leave a Reply