Note: This tutorial will work only for resetting the MariaDB root password, not for the MySQL server.
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