Guide: Simple Root Password Reset for MySQL or MariaDB on Ubuntu 20.04

In this article, we will go through the process of restoring access to MySQL or MariaDB in case the root password is lost. If this happens, don’t panic—if you have server access and an account with root privileges in the operating system, you can reset the password.

Important Note

The default configuration of MySQL and MariaDB in Ubuntu 20.04 allows logging into the database as root without entering a password. In this case, resetting the password is unnecessary. To check if this setting is active, enter the following command in the terminal:

sudo mysql

If you receive an "Access denied" error, it means the settings have been changed, and you need to follow the instructions below.
In later versions, such as Ubuntu 22.04, MySQL’s behavior may depend on security settings, and a password might be required even for local connections. Therefore, before resetting the password, it is essential to check the current version and authentication settings.

Step 1: Determine the Database Version and Stop It

The password reset method depends on which DBMS is installed on the system. To find out the current version, run:
mysql --version

The output should be one of the following:

MariaDB

mysql Ver 15.1 Distrib 10.3.25-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

MySQL

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

Now, stop the database service:

For MariaDB:

sudo systemctl stop mariadb

For MySQL:

sudo systemctl stop mysql

After stopping the service, proceed to restart it in a special mode.

Step 2: Start the Database in Recovery Mode

To reset the password, we need to start the server without checking privilege tables (--skip-grant-tables). This mode is insecure, so the server will run in single-user mode without network connections.

For MariaDB
1. Set environment variables:

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

2. Start the service:

sudo systemctl start mariadb

3. Check if it is running:

sudo systemctl status mariadb

4. Connect to the database:

sudo mysql -u root

For MySQL
1. Edit the systemd configuration:

sudo systemctl edit mysql

2. Add the following lines in the opened file:

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

3. Save the file (CTRL+X, then Y) and apply the changes:

sudo systemctl daemon-reload

4. Start MySQL:

sudo systemctl start mysql

5. Connect to the database:

sudo mysql -u root

Once connected, proceed to the next step.

Step 3: Reset the Old Password

Before changing the password, refresh the privilege tables:

FLUSH PRIVILEGES

Now, update the password.

For MariaDB
Run:

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

If you need to reset the authentication mechanism, execute:

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

For MySQL
Run:

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

After successfully executing the commands, exit the console and restore the normal operation mode of the server.

Step 4: Restart and Restore Normal Operation Mode

For MariaDB

1. Remove the environment variables:

sudo systemctl unset-environment MYSQLD_OPTS

2. Restart the service:

sudo systemctl restart mariadb

For MySQL

1. Reset the configuration changes:

sudo systemctl revert mysql

Expected output:

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

2. Reload systemd and restart MySQL:

sudo systemctl daemon-reload
sudo systemctl restart mysql

Now, test the login with the new password:

mysql -u root -p

Done! Access to the database is restored. We recommend securely storing your password to avoid similar issues in the future.

Bonus #1: Common Errors During Password Reset

1. Error: "Access denied for user 'root'@'localhost'" even after reset
Cause: The password changes may not have taken effect, or the root user is authenticated through another method (e.g., auth_socket in Ubuntu).
Solution:
→ Run FLUSH PRIVILEGES after changing the password to refresh privilege tables.
→ Check the current authentication method with:

SELECT user, host, plugin FROM mysql.user

→ If auth_socket is used, change it to mysql_native_password:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'new_password'

→ Run FLUSH PRIVILEGES again.

2. Failure to start MySQL/MariaDB with --skip-grant-tables
Cause: Configuration file errors or systemd incompatibility.
Solution:
→  Check server error logs:

journalctl -xe | grep mysql

→ Ensure MySQL/MariaDB is stopped before starting in recovery mode:

sudo systemctl stop mysql
sudo systemctl stop mariadb

→ Start the server with --skip-networking for security:

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

3. systemd Issues and Possible Fixes
Cause: systemd may fail to apply configuration changes or use cached startup parameters.
Solution:
→ Force systemd to reload configuration:

 sudo systemctl daemon-reexec

→ If changes were made with systemctl edit mysql, ensure they are saved and applied:

sudo systemctl daemon-reload
sudo systemctl restart mysql

→ If MySQL/MariaDB does not start, check for override files:

 systemctl cat mysql

→ If override files are causing issues, reset them:

 sudo systemctl revert mysql

Bonus #2: Database Administration Best Practices

1. Regular Updates

→ Keep MySQL/MariaDB up to date.
→ Create backups before updating.
→ Check for compatibility before upgrading.

2. Backup Configuration

→ Use mysqldump, Percona XtraBackup, or Mariabackup.
→ Store backups in secure, geographically distributed locations.

3. Restrict Network Access→ Allow access only from trusted IPs.
→ Use a firewall (ufw or iptables).
→ Disable remote root access:

UPDATE mysql.user SET Host='localhost' WHERE User='root'

4. Monitor the Server

→ Enable error and slow query logs.
→ Use monitoring tools (e.g., Prometheus, Grafana).

5. Manage User Privileges

→ Create separate users for each application.
→ Avoid using the root account for daily tasks.

6. Password Security

→ Use strong passwords and a password manager.
→ Enable two-factor authentication if available.

By following these recommendations, you can ensure database security and stability, minimize access loss risks, and improve system performance.