How To Reset Your MariaDB-10.2 Root Password on Ubuntu 20.04

How To Reset Your MariaDB-10.2 Root Password on Ubuntu 20.04

Introduction

If you forget the root password of the MariaDB database in Ubuntu, you can still regain the access and reset it if you have access to the server with the help of sudo privileges user account.

Note: With fresh Ubuntu 18.04 installations, by default MariaDB configuration usually allows you to connect with the database (with full administrative privileges) without providing a password if you are using the system’s root account. In this scenario, it may not be necessary to reset the root password. Before proceeding to reset the database root password in Ubuntu, try to access the database with the below-mentioned command
# sudo mysql
If it gives access denied error, then follow the steps mentioned below.

Step 1 — Getting the Database Version and Stopping MariaDB service in Ubuntu

Check your version with the following command:
# mysql --version
Command output
mysql Ver 15.1 Distrib 10.2.36-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
In order to change the MariaDB root password, you have to shut down the database server.
That can be done by using the following command:
# sudo systemctl stop mariadb

Step 2 — Restart the Mariadb Server without Permission Checks

Running MariaDB without permission checks allows access to the database command line with root privileges without prompting password.

Configuring MariaDB service to start without Grant Tables
# sudo systemctl set-environment MYSQLD_OPTS="--skip-grant-tables --skip-networking"
Then, start the MariaDB service again:
# sudo systemctl start mariadb

The above command will not produce any output, but it will restart the database server,
You can check the server status
# sudo systemctl status mariadb

Now you can connect to the database as the MariaDB root user without a password
# sudo mysql -u root

It will immediately get you in shell prompt like below
MariaDB [(none)]>

Step 3 — Changing the Root Password

Reload the grant tables by using the below command.
FLUSH PRIVILEGES;

You can now change the root password.

Changing the MariaDB Password

For MariaDB execute the below statement to set the password for the root account, make sure to replace “your_password” with your desired password.
UPDATE mysql.user SET password = PASSWORD('your_password') WHERE user = 'root';

MariaDB custom authentication mechanisms, so execute the below two statements in order to use its default authentication mechanism for the new password you have assigned to the root account:
UPDATE mysql.user SET authentication_string = '' WHERE user = 'root';
UPDATE mysql.user SET plugin = '' WHERE user = 'root';

The password is set now.
Type exit to exit the MariaDB console
exit;

Step 4 — Reverting Ubuntu Database Server to Normal Settings

To start the MariaDB server in normal mode, you have to revert the changes.

Unset the MYSQLD_OPTS environment variable set in the previous command
# sudo systemctl set-environment MYSQLD_OPTS=""

Then, restart the MariaDB service
# sudo systemctl restart mariadb

Then, to apply the changes reload the systemd configuration
# sudo systemctl daemon-reload

Webner Solutions is a Software Development company focused on developing Insurance Agency Management Systems, Learning Management Systems and Salesforce apps. Contact us at dev@webners.com for your Insurance, eLearning and Salesforce applications.

Originally published at https://blog.webnersolutions.com on April 15, 2021.