Losing or forgetting the root password for your MySQL or MariaDB database server can be a frustrating experience. However, in Ubuntu 20.04, there are specific steps you can follow to reset the root password and regain access to your database. In this article, we will explore the step-by-step process for resetting the root password for both MySQL and MariaDB on Ubuntu 20.04.

MYSQL STEP

Stop the MySQL service by running the following command:

sudo systemctl stop mysql

Open the MySQL service unit file for editing:

sudo systemctl edit mysql

Add the following lines to the editor:

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

Save and close the editor. This will create an override for the MySQL service and reload the systemd daemon to apply the changes:

sudo systemctl daemon-reload

Start the MySQL service with the new configuration:

sudo systemctl start mysql

Log in to the MySQL server as the root user without a password:

sudo mysql -u root

Inside the MySQL prompt, flush the privileges to ensure the changes take effect:

FLUSH PRIVILEGES;

Set a new password for the root user. Replace 'new_password' with your desired password:

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

Revert the changes made to the MySQL service by running:

sudo systemctl revert mysql

Reload the systemd daemon again:

sudo systemctl daemon-reload

Restart the MySQL service to apply the new password:

sudo systemctl restart mysql

You can now log in to MySQL as the root user using the new password:

mysql -u root -p

MariaDB Step

Stop the MariaDB service:

sudo systemctl stop mariadb

Set an environment variable to start MariaDB with skip-grant-tables and skip-networking options:

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

Start the MariaDB service with the new configuration:

sudo systemctl start mariadb

Log in to the MariaDB server as the root user without a password:

sudo mysql -u root

Inside the MariaDB prompt, flush the privileges:

FLUSH PRIVILEGES;

Set a new password for the root user:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

Update the authentication_string column to ensure it's empty:

UPDATE mysql.user SET authentication_string = '' WHERE user = 'root';

Update the plugin column to ensure it's empty:

UPDATE mysql.user SET plugin = '' WHERE user = 'root';

Set the new password using caching_sha2_password authentication plugin:

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

Unset the environment variable to revert the changes:

sudo systemctl unset-environment MYSQLD_OPTS

Restart the MariaDB service:

sudo systemctl restart mariadb

You can now log in to MariaDB as the root user using the new password:

mysql -u root -p

Remember to replace 'new_password' with your desired password in both the MySQL and MariaDB steps.

By following the step-by-step instructions provided in this article, you should be able to successfully reset the root password for both MySQL and MariaDB on Ubuntu 20.04. Remember to choose a strong and secure password to ensure the safety of your database.