How To Reset Root Password on MariaDB v10+

If you have been using the MySQL database server for a long time, you might be a little confused when you switch to MariaDB. Especially in terms of resetting root password, even though we know that MariaDB is a drop-in replacement for MySQL database server.

So in many ways, both have a lot of similarities, especially in terms of command and configuration database server.

So, how to reset the root password in MariaDB v10+ ? Resetting the root password on MariaDB v10 + database server is very different from MySQL.

There are more complex commands for resetting the MariaDB v10 root password, especially for MariaDB v10 + which includes MariaDB v10.0, MariaDB v10.1, MariaDB v10.2, MariaDB v10.3 and the latest stable version of MariaDB v10.4.

Yes, the command is very complex and more complicated than resetting the MySQL root password.

However, it will not be difficult if you follow the following simple tutorial. This tutorial can be used on almost all Linux server distributions including Fedora/RHEL/CentOS and Debian/Ubuntu, as long as you have root privileges on your server.

So, you don’t need to panic even if you don’t have phpMyAdmin installed on your Linux server, you can still reset MariaDB’s root password via the command-line interface (CLI).

Resetting MariaDB Root Password

1.) Just like changing the root password in MySQL, we need to enter safe mode on the MariaDB database server. Simply run the following command to enter MariaDB safe mode.

$ mysqld_safe --skip-grant-tables --skip-networking &

2.) Now you can login to the MariaDB database server without a root password.

$ mysql -u root

3.) Now let’s set root password for MariaDB.

$ SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newrootpassword');

Don’t panic, you will get the error message “ERROR 1290 (HY000): The MariaDB server is running with the –skip-grant-tables option so it cannot execute this statement”. In this step we are not yet allowed to change the MariaDB root password. So, simply run the flush privileges command.

$ FLUSH PRIVILEGES;

4.) Well, here we will try to reset MariaDB’s root password again.

$ SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newrootpassword');

However, MariaDB’s root password hasn’t changed and we will get the error message “ERROR 1131 (42000): You are using MariaDB as an anonymous user and anonymous users are not allowed to modify user settings”.

Yes, in step-4, MariaDB has detected us as anonymous users and logged in to MariaDB CLI without a password. So we are not allowed to modify user settings, including resetting the root password.

5.) Now run the following command to see the authentication string from the MariaDB root user.

$ select user, host, password, plugin, authentication_string from mysql.user where user='root';

Please look at the password field, here you will see the hashed root password “*28DB364D8AF2FCF468C176E4B6B58D4552813E4B”.

Yes, the MariaDB root password has been automatically strengthened by the hashing method. The password hashing method in both MySQL and MariaDB provides better security and minimizes the risk of passwords being intercepted.

6.) Next, you can really update and reset the root password for your MariaDB database server. You need to run the following two commands.

$ update mysql.user set password=password('newrootpassword') where user='root';
$ FLUSH PRIVILEGES;

MariaDB [(none)]> update mysql.user set password=password('hTyshdg712') where user='root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

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

7.) Well, in step-7 we need to run this command again.

$ select user, host, password, plugin, authentication_string from mysql.user where user='root';

Please pay attention to the password field (*3D94ACDC056AC7E526703AFC472115049BA0109F)! Here our MariaDB root password has been changed and marked with a different hashed root password from step-4 above.

8.) Run the following command to reset MariaDB root password.

$ SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newrootpassword');

Well, once again MariaDB has detected us as anonymous users. Don’t panic, what we need to do now is exit from the MariaDB command-line interface. Run this command:

$ quit

9.) Now, you can login to MariaDB database server using the new password that you set in the previous step (step-6).

$ mysql -u root -p

Once again, run the following command and quit.

$ select user, host, password, plugin, authentication_string from mysql.user where user='root';
$ quit

10.) Exit from MariaDB safe mode and shutdown MariaDB database server.

$ mysqladmin -u root -p shutdown

To kill the MariaDB safe mode process ID manually, we need to run the following command.

$ ps aufx | grep mysql | grep -v grep

11.) Now let’s start MariaDB database server

For Fedora/RHEL/CentOS

$ service mariadb start
$ systemctl start mariadb
$ systemctl start mariadb.service

For Debian/Ubuntu

$ service mysql start
$ systemctl start mysql
$ systemctl start mysql.service

Node35 Editorial Team

We are a bunch of people who are still continue to learn Linux servers. Only high passion keeps pushing us to learn everything.