MariaDB Root Password Reset on Ubuntu 20.04 LTS Using Safe Mode (Skip Grant Tables)

Lost your MariaDB root password on Ubuntu 20.04 LTS? This guide details the secure, step-by-step process to reset it using the `skip-grant-tables` method.

Losing access to your MariaDB root user can halt critical operations, preventing application connectivity, database administration, and schema changes. This guide provides a robust and secure method to regain control by resetting the MariaDB root password on an Ubuntu 20.04 LTS server. We will leverage MariaDB's safe mode (skip-grant-tables) to temporarily bypass authentication, allowing you to re-establish a secure root password.

Symptom & Error Signature

The most common symptom is being unable to log into the MariaDB server as the root user, typically via the command line. This often manifests in:

• Terminal Output:

  mysql -u root -p
  Enter password: 
  ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
  

  Or, if you don't even remember attempting a password:

  mysql -u root
  ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
  

• Application Logs: Web applications (e.g., WordPress, Nextcloud) or custom services that rely on the MariaDB root user (or a user whose password was forgotten and is tied to the root user's fate) will report database connection errors. These can vary but often include phrases like:

  [E] SQLSTATE[HY000] [1045] Access denied for user 'your_app_user'@'localhost' (using password: YES)
  

  Or for more generic connection failures:

  Failed to connect to database: Can't connect to MySQL server on '127.0.0.1' (111)
  

Root Cause Analysis

The underlying reason for this issue is straightforward: the MariaDB root password has been forgotten, incorrectly set, or otherwise become unknown. Common scenarios include:

• Human Error: Simply forgetting the password after an extended period of not needing it, or a new team member not being provided with the correct credentials.
• Initial Setup Oversight: During server provisioning or initial MariaDB installation, the root password might not have been recorded or set securely.
• Configuration Drift: In rare cases, changes to the MariaDB configuration (my.cnf) or direct manipulation of the mysql.user table might have inadvertently locked out the root user.
• Compromise (Less Likely for this specific symptom): While unlikely to directly cause a forgotten password, a system compromise could potentially involve a malicious password change. This guide assumes the issue is password retrieval, not compromise remediation.

Step-by-Step Resolution

This procedure involves stopping the MariaDB service, restarting it in a special "safe mode" that bypasses grant table authentication, resetting the password, and then restarting the service normally.

This procedure requires sudo (root) access to your Ubuntu server. Proceed with caution and ensure you understand each step. Leaving MariaDB running in skip-grant-tables mode makes your database completely unsecured and accessible to anyone.

1. Stop the MariaDB Service

First, gracefully stop the running MariaDB server.

sudo systemctl stop mariadb

Verify that the service has stopped:

sudo systemctl status mariadb

The output should indicate Active: inactive (dead).

2. Start MariaDB in Safe Mode (skip-grant-tables)

To bypass the password authentication, we need to instruct MariaDB to start without loading the grant tables. We'll do this by temporarily modifying its Systemd service configuration.

We recommend using systemctl edit --full to modify the service unit file directly. This creates an override file that takes precedence, simplifying cleanup later.

sudo systemctl edit --full mariadb.service

This command will open the full mariadb.service unit file in your default editor (e.g., nano or vi). Locate the [Service] section and the ExecStart line.

Change the ExecStart line to include --skip-grant-tables --skip-networking=0. The --skip-networking=0 part ensures that MariaDB still listens on the network interface (useful if you need to connect from outside localhost, though for password reset, localhost is sufficient and safer).

Original (example):

ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS --bind-address=0.0.0.0

Modified:

ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS --skip-grant-tables --skip-networking=0

Save and exit the editor. For nano, press Ctrl+X, then Y to confirm saving, and Enter.

Now, reload the Systemd manager configuration to recognize the changes and start MariaDB:

sudo systemctl daemon-reload
sudo systemctl start mariadb

Verify MariaDB is running:

sudo systemctl status mariadb

You should see Active: active (running).

3. Connect to MariaDB Without a Password

With skip-grant-tables enabled, you can now connect to MariaDB as the root user without needing a password.

mysql -u root

You should be granted immediate access to the MariaDB prompt.

4. Flush Privileges and Reset Password

Once inside the MariaDB prompt, the first crucial step is to flush the privileges. This loads the grant tables into memory, allowing you to make authentication changes.

FLUSH PRIVILEGES;

Now, reset the root user's password. MariaDB 10.3+ (the default for Ubuntu 20.04) supports ALTER USER which is the recommended method.

Option A: Using ALTER USER (Recommended for MariaDB 10.3+)

Replace YourStrongNewPassword with a new, secure password.

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

If you have a root user defined with a different host (e.g., 'root'@'%'), you might want to reset that one too:

ALTER USER 'root'@'%' IDENTIFIED BY 'YourStrongNewPassword';

Option B: Using UPDATE mysql.user (Older versions or if ALTER USER fails)

If ALTER USER gives an error, or for older MariaDB/MySQL versions, you can directly update the mysql.user table.

UPDATE mysql.user SET authentication_string = PASSWORD('YourStrongNewPassword') WHERE User = 'root' AND Host = 'localhost';
FLUSH PRIVILEGES;

The PASSWORD() function is deprecated in newer MariaDB versions but might be necessary in some edge cases. ALTER USER is preferred.

After executing the password change, exit the MariaDB prompt:

exit;

5. Restore MariaDB Service Configuration

It is absolutely critical to remove the skip-grant-tables option immediately after resetting the password. Failing to do so leaves your MariaDB server completely vulnerable.

Edit the mariadb.service unit file again:

sudo systemctl edit --full mariadb.service

Locate the ExecStart line and remove --skip-grant-tables and --skip-networking=0. Revert it to its original state or simply remove the added options.

Reverted to original (example):

ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS --bind-address=0.0.0.0

Save and exit the editor.

Reload the Systemd manager configuration:

sudo systemctl daemon-reload

6. Restart MariaDB Service Normally

Now, restart the MariaDB service. It will start without skip-grant-tables and will enforce the new password.

sudo systemctl restart mariadb

Verify the service status:

sudo systemctl status mariadb

7. Verify the New Root Password

Finally, test the new password by attempting to log in as the root user normally:

mysql -u root -p

Enter YourStrongNewPassword when prompted. You should now be able to log in successfully.

Update any applications or scripts that use the MariaDB root user (or affected users) with the new password to restore their functionality. Consider creating dedicated users with least-privilege access for applications instead of using root directly.