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.
Top comments (0)