Reset MySQL And MariaDB Root Password on Ubuntu 22.04

In this guide, we want to teach you How To Reset MySQL And MariaDB Root Password on Ubuntu 22.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.

Steps To Reset MySQL or MariaDB Root Password on Ubuntu 22.04

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

Note: On fresh Ubuntu 22.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 of Reset MySQL And MariaDB Root Password on Ubuntu 22.04

To complete this guide, you must log in to your server as a non-root user with sudo privileges. To do this, you can follow our guide the Initial Server Setup with Ubuntu 22.04.

Identify the Database version and Stop the Server on Ubuntu 22.04

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 22.04, in your output you will see:

Output
mysql  Ver 8.0.30-0ubuntu0.22.04.1 for Linux on x86_64 ((Ubuntu))

If you are running MariaDB on Ubuntu 22.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 checks 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 on Ubuntu 22.04

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 the following command:

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 on Ubuntu 22.04

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.

Reset the MySQL and MariaDB Root Password on Ubuntu 22.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 Root Password on Ubuntu 22.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 so 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 Root Password on Ubuntu 22.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 has changed correctly:

mysql -u root -p

You will be asked to enter your new password.

Conclusion

At this point, you have learned to Reset MySQL And MariaDB Root Password on Ubuntu 22.04.

Hope you enjoy it.

For more guides and articles, you can visit the Linux Tutorials.