Reset MySQL And MariaDB Root Password on Ubuntu 22.04 | Easy Steps

This guide, brought to you by Orcacore, will walk you through the process of Reset MySQL And MariaDB Root Password on Ubuntu 22.04.

Losing or forgetting your MySQL or MariaDB root password can be a stressful situation. Fortunately, if you have server access and a user account with sudo privileges, you can regain access and reset the password. This article provides a comprehensive guide to resetting 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 often grants access to the database (with full administrative privileges) without requiring a password, provided the connection originates from the system’s root account. In such instances, resetting the password might be unnecessary. Before proceeding, attempt to access the database using the command sudo mysql. If the default authentication configuration has been altered, resulting in an "access denied" error, then follow the steps outlined in this article. This article will show you Reset MySQL And MariaDB Root Password on Ubuntu 22.04.

1. Requirements of Resetting MySQL and MariaDB Root Password

To successfully complete this guide, you must be logged into your server as a non-root user with sudo privileges. If you haven’t already, follow our guide on the Initial Server Setup with Ubuntu 22.04 to create such a user.

2. Check Database version and Stop the Server

The first step is to identify the database server you are running. Use the following command to check the version:

mysql --version

If you are running MySQL on Ubuntu 22.04, your output will resemble this:

If you are running MariaDB, the output will be similar. Note which database you are using.

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

For MySQL, use the following command:

sudo systemctl stop mysql

For MariaDB, use this command:

sudo systemctl stop mariadb

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

3. Restart 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 achieve this, you need to prevent the database from loading the grant tables. Because it’s insecure, disabling networking is also recommended to prevent other clients from connecting to the vulnerable server.

Configure MariaDB to Start Without Grant Tables

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

Run this command:

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

Then, start the MariaDB server:

sudo systemctl start mariadb

Verify that the service is active and running:

sudo systemctl status mariadb

Now, connect to the database as the MariaDB root user without a password:

sudo mysql -u root

You should see the MariaDB console:

MariaDB [(none)]>

You now have access to the database server and can change the root password as described later in this 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

Add the following content to the file:

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

Save and close the file. Reload the systemd configuration:

sudo systemctl daemon-reload

Start the MySQL server:

sudo systemctl start mysql

Connect to the database as the root user:

sudo mysql -u root

You should see the MySQL console:

mysql>

You now have access to the server, and you can change the root password.

4. Reset MySQL and MariaDB Root Password on Ubuntu 22.04

To Reset MySQL And MariaDB Root Password on Ubuntu 22.04, you must load the grant tables now that you have access to the server. Reload the grant tables with the following command in your MySQL or MariaDB console:

mysql> FLUSH PRIVILEGES;

Now you can change the root password.

Change the MariaDB Root Password

Use the following command from your MariaDB console to set the MariaDB root password:

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

Replace new_password with a strong password you can remember.

Ensure MariaDB uses its default authentication mechanism for the new password. 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

MySQL allows using custom authentication mechanisms. The following statement ensures that MySQL uses its default authentication mechanism to authenticate the root user using the new password.

To change the root password, execute this 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.

5. Revert the Database Server to Normal Settings

For MariaDB, unset the MYSQLD_OPTS environment variable with the following command:

sudo systemctl unset-environment MYSQLD_OPTS

Then, restart the service:

sudo systemctl restart mariadb

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

sudo systemctl revert mysql

Your output should look similar to this:

Apply the changes by reloading 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 prompted to enter your new password.

Conclusion

At this point, you have learned to Reset MySQL And MariaDB Root Password on Ubuntu 22.04. Resetting the MySQL or MariaDB root password on Ubuntu 22.04 allows you to regain access to the database if you’ve forgotten the password. This process typically involves stopping the database service, starting it in safe mode, and then changing the password using SQL commands.

Hope you enjoy it. Please subscribe to us on Facebook, Instagram, and YouTube.

Also, you may like to read the following articles:

Turn Off MySQL Strict Mode on Linux

Duplicate a MySQL Database with a Different Name

Upgrade MariaDB on AlmaLinux 9

Install LEMP Stack on Debian 12

Install MariaDB on Debian 12 Bookworm

Alternative Solutions for Resetting MySQL and MariaDB Root Password

While the method described above is effective, here are two alternative approaches to Reset MySQL And MariaDB Root Password on Ubuntu 22.04:

1. Using mysql_secure_installation (MySQL Only)

The mysql_secure_installation script provides an interactive way to improve the security of your MySQL installation, and it can also be used to reset the root password. This method is primarily applicable to MySQL and may not work as intended with MariaDB.

Steps:

1. Stop the MySQL service:

   sudo systemctl stop mysql

2. Start MySQL in safe mode without networking:

   sudo mysqld_safe --skip-grant-tables --skip-networking &

   Note: This command starts the MySQL server in the background. The & at the end runs the command in the background, allowing you to continue using the terminal.

3. Connect to MySQL as root without a password in another terminal:

   mysql -u root

4. Run the mysql_secure_installation script:

   mysql> CALL mysql.mysql_upgrade();
   mysql> exit;

   Then run the command in terminal:

   sudo mysql_secure_installation

5. Answer the prompts: The script will ask a series of questions, including whether you want to set a new root password. Answer "yes" when prompted and provide your new password. Other questions relate to removing anonymous users, disallowing remote root login, and removing the test database. These are all security best practices, so it’s generally recommended to answer "yes" to these prompts as well.

6. Restart the MySQL service:

   sudo systemctl restart mysql

This method offers a more interactive and user-friendly way to reset the root password, especially for users less comfortable with directly executing SQL commands.

2. Using a Recovery Key (If Available)

Some MySQL/MariaDB installations may have a recovery key configured during the initial setup. This key can be used to bypass authentication and reset the root password.

Steps:

1. Locate the recovery key file: The location of this file depends on the specific configuration. It might be in /etc/mysql/, /var/lib/mysql/, or a similar directory. The file name might contain "recovery" or "rescue."

2. Stop the MySQL/MariaDB service:

   sudo systemctl stop mysql  # or sudo systemctl stop mariadb

3. Start the database server with the recovery key:

   sudo mysqld_safe --init-file=/path/to/recovery_key_file & #Replace /path/to/recovery_key_file with the actual path.

4. Connect to the database server as root without a password (in another terminal):

   mysql -u root

5. Execute the SQL commands to change the root password:

   mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password'; #Replace new_password with your desired password.
   mysql> FLUSH PRIVILEGES;
   mysql> exit;

6. Stop and Restart the MySQL/MariaDB service normally:

    sudo systemctl stop mysql  # or sudo systemctl stop mariadb
    sudo systemctl start mysql  # or sudo systemctl start mariadb

This method is the simplest and fastest if a recovery key is already configured. However, it requires proactive setup of the recovery key during the initial database installation. If you are having trouble with Reset MySQL And MariaDB Root Password on Ubuntu 22.04, these alternative solutions can help.