How to Reset MariaDB or MySQL Root Password

MySQL is one of the most popular database systems out there. It’s an open-source relational database management system that follows a client-server model. MySQL is at the core of many of the online services we enjoy on a regular basis. MariaDB is a community-developed fork of MySQL that enjoys commercial support.

In the MySQL ecosystem, root is the default user created during installation. Similar to Linux, it’s the user with the highest privilege. By default, root access is protected with a password. What to do when you forgot the root password of MySQL? If there’s access to any other user with sudo privilege, then we can recover access to root. In this guide, we will show you how to reset MariaDB or MySQL root password on Ubuntu.

Prerequisites

Proceeding forward, we will assume that you have set up the following:

• An Ubuntu 20.04 server properly configured with MySQL or MariaDB. The following guide will demonstrate setting up an Ubuntu server.  Follow this tutorial to install and configure MySQL or this one if you prefer to work with MariaDB.
• Access to a non-root sudo user. This guide demonstrates how to grant user access to sudo privilege.

It’s recommended to try out the steps beforehand on a test server. CloudSigma offers high-performance computing power at the best price. You can give the CloudSigma platform a try for free from this link.

Database Version

When on Ubuntu, there are two variants of MySQL: MySQL (original) or MariaDB (a MySQL fork). The commands necessary to recover the root password differ depending on whether it’s a MySQL or MariaDB server.

First, run the following command to determine the database type and version:

For MariaDB, the output will contain “MariaDB” after the version number. In the case of MySQL, it won’t mention anything after the version number.

Stopping MySQL/MariaDB Server

The default behavior of the MySQL or MariaDB server is to start at system boot. For the first few steps, we need to perform some modifications of the service, so we need to stop it. For managing services, systemd init system is the industry standard. Systemd uses systemctl as the command-line tool to perform various actions on a target service. You can follow our tutorial to learn how to manage systemd services and units with systemctl.

Next, check the status of the MySQL/MariaDB service:

To stop the service, run the appropriate command:

Tweaking MySQL/MariaDB Startup Behavior

• Tweaking MariaDB startup

The database engine loads the grant tables at the start. It manages what user can access what part of the database system. As we’ve lost access to the root user, we need this feature disabled temporarily.

Run the following command to change the environment variable MYSQLD_OPTS. It will disable loading the grant tables and networking:

The environment variable is set. Time to start the MySQL/MariaDB server:

There won’t be any output. To confirm that the server started successfully, check the service status:

• Tweaking MySQL startup

As demonstrated in the previous method, we need to change the value of MYSQLD_OPTS to start MySQL/MariaDB without grant tables and networking. In the case of MySQL, the value of MYSQLD_OPTS will be set using the service startup script. Open the MySQL service startup script:

It will open a new file with the nano text editor. It will include the MySQL service overrides. By default, it will be empty. Add the following lines:

Note that we’ve declared ExecStart two times. The first instance clears any default value. The second one sets the new startup command with parameters to disable grant tables and networking.

Next, save the file and exit the editor. Reload systemd to apply these changes:

The MySQL/MariaDB server is ready to be started:

Next, verify the status:

Logging in as Root

The database server is now operating in a limited mode. The grant table and networking is disabled. The server now can be accessed without any password. However, it prohibits running commands that will alter data. Connect to the MySQL/MariaDB server as the root user:

Now that we have access to the root user, we need to load the grant tables to alter data:

Changing the Password

We can now change the password for the root user. The commands necessary to do so will depend on whether it’s MySQL or MariaDB.

• Changing root password on MariaDB

Run the following query to set a new password for the root user:

MariaDB supports custom authentication mechanisms. The following two statements will reset the MariaDB authentication mechanism to the default:

• Changing root password on MySQL

The following query will change the MySQL root password:

Reverting Database Changes

The root password is now reset successfully. It’s time to revert back the changes.

• Reverting MariaDB

In the case of MariaDB, we only need to reset the value of MYSQLD_OPTS to the previous value. Run the following command:

To take the changes into effect, restart MariaDB:

• Reverting MySQL

In the case of MySQL, we had to modify the systemd configuration. One way is to remove the modified systemd configuration:

If the systemd configuration is important, then you have to manually edit and remove the ExecStart entries. Once updated, reload MySQL:

Finally, restart MySQL:

Verification

It’s time to test if the database server is accessible as the root user with the new password:

Final Thoughts

The administrative access to MySQL or MariaDB is now restored. The new password should be strong to prevent unwanted access to administrative power. MySQL is a multi-user system. This MySQL guide on user management demonstrates creating new users and granting specific permissions. Here are some more resources to help you when working with MySQL:

1. SQLite vs MySQL vs. PostgreSQL: Relational Database Management Systems Compared
2. Deploying Laravel, Nginx, and MySQL with Docker Compose
3. How to Install the LEMP stack (Linux, Nginx, MySQL PHP) on Ubuntu 20.04

Happy computing!