How to Reset MariaDB Root Password

by
How to Reset MariaDB Root Password

In this blog post, we will show you how to reset the MariaDB root password. We reset the MariaDB root password primarily because we forgot it, need to enhance security by changing it from a default/weak one (or a randomly generated one we didn’t save), or after system changes like upgrades or migrations that might have altered credentials, allowing us to regain administrative control over our database. The process usually involves starting the server in a special mode that disables password checks (such as –skip-grant-tables), logging in as root without a password, and then issuing SQL commands to set a new, strong password.

Resetting the MariaDB root password is a straightforward process that takes less than 5 minutes. Let’s get started!

Prerequisites

Step 1. Stop the MariaDB database server

Stopping the MariaDB database service differs between newer and older Linux OS distributions. Let’s see the differences:

 NEWER OS Distributions

 sudo systemctl stop mariadb

 ------------------------------

 OLDER OS Distributions

 sudo /etc/init.d/mysql stop

 OR

 sudo /etc/init.d/mysqld stop

 OR 

 sudo /etc/init.d/mariadb stop

Step 2. Start the MariaDB server with the “skip-grant-tables” option in safe mode

This option disables the permission system, allowing anyone to connect without a password. It should only be used for recovery in a secure, local environment. First, we have to create the /var/run/mysqld/ directory with the correct permissions:

mkdir /var/run/mysqld

chown -R mysql:mysql /var/run/mysqld/

Since we already stopped the MariaDB service using the commands from the previous step, we can start the MariaDB service in safe mode:

mysqld_safe --skip-grant-tables &

This will disable the MariaDB authentication and & run the process in the background.

Step 3. Log in to MariaDB as the root user

Now, we can log in to MariaDB without a root password:

#NEWER OS distributions

mysql -u root -p

OR 

mariadb -u root -p

------------------------------------------------------------------------------

#OLDER OS distributions

mysql -u root -p

Step 4. Reset MariaDB root password

To reset the MariaDB root password, we can use the following commands, depending on whether we have a newer or older MariaDB version:

#NEWER MariaDB versions

FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'YourNewStrongMySQLRootPassword';
EXIT;

------------------------------------------------------------------------------

#OLDER MariaDB versions

UPDATE user SET Password=PASSWORD('YourNewStrongMySQLRootPassword') WHERE User='root';
FLUSH PRIVILEGES;
EXIT;

Step 5. Start the MariaDB service

After the password has been reset, start the MariaDB server in normal mode:

NEWER OS Distributions

 sudo pkill -9 mariadb  # First kill all mariadb processes started before with --skip-grant-tables

 sudo systemctl start mariadb

 ----------------------------

 OLDER OS Distributions

 sudo pkill -9 mysql  # First kill all mysql processes started before with --skip-grant-tables

 sudo /etc/init.d/mariadb start

  OR 

 sudo /etc/init.d/mysql start

  OR
 sudo /etc/init.d/mysqld start

Now you can connect to MariaDB as a root user using the new password.

Type “mariadb -u root -p” OR “mysql -u root -p” on older OS distributions in the terminal, and enter the new password you set in Step 4 when prompted.

That’s it all! You have learned how to reset MariaDB root password on different MariaDB versions.

If you are one of our MariaDB hosting customers, you don’t have to follow this tutorial and reset the MariaDB root password yourself. Our experienced Linux admins will do that for you. They are available 24×7 and will address your request immediately. Submit a ticket.

PS. If you liked this post, please share it with your friends or leave a comment below. Thanks.

Leave a Comment