Reset MySQL or MariaDB Root Password on Ubuntu 20.04

In this article, we want to teach you how to Reset or Change MySQL or MariaDB Root Password on Ubuntu 20.04.

If you forgot your MySQL or MariaDB root password, you can get access and reset the password if you have access to the server and a user account with sudo privileges.

Reset MySQL or MariaDB Root Password on Ubuntu 20.04

In this article, we will show you how to reset the root password for MySQL and MariaDB databases that were installed with the APT package manager on Ubuntu 20.04.

Note: On fresh Ubuntu 20.04 installations, the default MySQL or MariaDB configuration usually allows you to access the database (with full administrative privileges) without setting up a password as long as you make the connection from the system’s root account. In this case, it may not be necessary to reset the password. Before you proceed with resetting your database root password, try to access the database with the “sudo mysql” command. Only if the default configuration for authentication was altered, and this results in an access denied error, follow the steps in this article.

Requirements for resetting the Root Password on ubuntu 20.04

You need to log in to your server as a non-root user with sudo privileges. To do this you can check our article about the Initial server setup with Ubuntu 20.04.

MySQL or MariaDB is installed on your server.

Identify the Database version and Stop the Server

Follow these steps to figure out which database server you are running.

First, check your version with the following command:

mysql --version

If you are running MySQL on Ubuntu 20.04, in your output you will see:

mysql Ver 8.0.26-0ubuntu0.20.04.3 for Linux on x86_64 ((Ubuntu))

If you are running MariaDB on Ubuntu 20.04, you will see something similar to that.

Remember the database you are running.

To change the root password, you need to shut down the database server.

For MySQL, shut down the database server with the following command:

sudo systemctl stop mysql

For MariaDB, you can use the following command:

sudo systemctl stop mariadb

When the database is stopped, you can restart it in safe mode to reset the root password.

Restart the Database Server without Permission check

Running MySQL and MariaDB without permission check allows you to access the database command line with root privileges without providing a valid password.

To do this, you need to stop the database from loading the grant tables. because it is insecure, you may also want to disable networking to prevent other clients from connecting to the temporarily vulnerable server.

Configure MariaDB to Start Without Grant Tables

To start the MariaDB server without grant tables, you can use the systemd unit file to set additional parameters for the MariaDB server service.

Run the following command:

sudo systemctl set-environment MYSQLD_OPTS="--skip-grant-tables --skip-networking"

Then, start the MariaDB server with the following command:

sudo systemctl start mariadb

You can check that your service is active and running with:

sudo systemctl status mariadb

Now you should be able to connect to the database as the MariaDB root user without a password:

sudo mysql -u root

You will see the MariaDB console:

MariaDB [(none)]>

Now that you have access to the database server, you can change the root password as shown in the rest of the article.

Configure MySQL to Start Without Grant Tables

To start the MySQL server without its grant tables, you need to edit MySQL’s service overrides.

Run the following command:

sudo systemctl edit mysql

Then, add the following content to the file:

[Service]
ExecStart=
ExecStart=/usr/sbin/mysqld --skip-grant-tables --skip-networking

When you are finished, save and close the file.

Reload the systemd configuration to apply the changes:

sudo systemctl daemon-reload

Now start the MySQL server with the following command:

sudo systemctl start mysql

Then, connect to the database as the root user:

sudo mysql -u root

You will see the MySQL console:

mysql>

At this point, you have access to the server, now you can change the root password.

Change the Root Password on Ubuntu 20.04

To reset the root password, you must load the grant tables now that you’ve got access to the server.

Reload the grant tables with the following command in your MySQL or MariaDB console:

mysql> FLUSH PRIVILEGES;

Here you can change the root password.

Change the MariaDB Password on Ubuntu 20.04

You can use the following command from your MariaDB console to set the MariaDB root password:

MariaDB [(none)]> ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

Remember to replace the new password with a strong password that you will remember it.

Then, you need to be sure that MariaDB will use its default authentication mechanism for the new password you set to the root account. To do this execute the following commands:

MariaDB [(none)]> UPDATE mysql.user SET authentication_string = '' WHERE user = 'root';
MariaDB [(none)]> UPDATE mysql.user SET plugin = '' WHERE user = 'root';

The password is now changed. type exit to exit the MariaDB console.

Change the MySQL Password on Ubuntu 20.04

MySQL allows using custom authentication mechanisms, so the following statement also makes sure that MySQL will use its default authentication mechanism to authenticate the root user using the new password.

To change the root password execute the following command:

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'new_password';

The password is now changed. type exit to exit the MySQL console.

Now you need to restart the database in normal settings.

Revert the Database Server to Normal Settings

For MariaDB, you need to unset the MYSQLD_OPTS environment variable you set previously with the following command:

sudo systemctl unset-environment MYSQLD_OPTS

Then, restart the service with the following command:

sudo systemctl restart mariadb

For MySQL, remove the modified systemd configuration with the following command:

sudo systemctl revert mysql

In your output you will see something similar to this:

Output
Removed /etc/systemd/system/mysql.service.d/override.conf.
Removed /etc/systemd/system/mysql.service.d.

To apply the changes reload the systemd configuration:

sudo systemctl daemon-reload

Finally, restart the service:

sudo systemctl restart mysql

Log in to your server as the root user to confirm that your password changed correctly:

mysql -u root -p

You will be asked to enter your new password.

Conclusion

At this point, you learn to Reset MySQL or MariaDB Root Password on Ubuntu 20.04.

Remember to choose a strong password for your MariaDB or MySQL root user. and remember it or keep it in a safe place.

Hope you enjoy it.