MariaDB Root Password Reset on Ubuntu 22.04 LTS using Skip Grant Tables

Lost your MariaDB root password on Ubuntu 22.04? This guide shows you how to securely reset it using skip-grant-tables method.

This comprehensive guide details the process of resetting the MariaDB root user password on an Ubuntu 22.04 LTS server. This procedure is critical when you've forgotten or lost access to the MariaDB root password, preventing you from performing essential database administration tasks. We will leverage the skip-grant-tables method, a powerful but temporary workaround to bypass authentication, allowing you to regain control over your MariaDB instance.

Symptom & Error Signature

When the MariaDB root password is lost or incorrect, you typically encounter authentication failures when attempting to connect to the database server.

Typical Error Output:

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

Or, if you try to connect without a password and unix_socket authentication is not configured or fails:

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

These errors indicate that the MariaDB server rejected your login attempt, usually due to an incorrect password or a misconfigured authentication method for the 'root' user.

Root Cause Analysis

The primary root cause for needing this procedure is a lost or forgotten MariaDB root password. Without this password, you cannot authenticate to the MariaDB server to perform administrative actions such as creating new databases, managing users, or configuring replication.

The skip-grant-tables method works by instructing the MariaDB server to start without loading the grant tables (which store user privileges and passwords). This effectively disables authentication, allowing any user to connect to the database server as root without a password. While this is a severe security risk, it's a necessary temporary measure for password recovery. Once connected, you can update the root password, then restart MariaDB under normal operating conditions.

Step-by-Step Resolution

Follow these steps carefully to reset your MariaDB root password.

1. Stop the MariaDB Service

First, you need to stop the running MariaDB service to ensure a clean restart with the modified configuration.

sudo systemctl stop mariadb

Verify that the service has stopped:

sudo systemctl status mariadb

The output should indicate inactive (dead).

2. Edit MariaDB Configuration to Skip Grant Tables

Now, we will modify the MariaDB configuration to start the server without grant tables. This is usually done by adding skip-grant-tables to the MariaDB configuration file. Ubuntu's MariaDB installations often use /etc/mysql/mariadb.conf.d/50-server.cnf or a similar path.

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

Under the [mysqld] section, add the following line:

[mysqld]
# ... other configurations ...
skip-grant-tables

Ensure skip-grant-tables is on its own line directly under [mysqld] or any other appropriate section for server-wide settings. Do not uncomment it if it's already there but commented out. If the file is different, search for common MariaDB config files like /etc/mysql/my.cnf or files within /etc/mysql/conf.d/.

Save the file and exit the editor (Ctrl+X, Y, Enter for nano).

3. Start MariaDB in Safe Mode

With the skip-grant-tables directive in place, start the MariaDB service. It will now run without requiring authentication.

sudo systemctl start mariadb

Verify that the service is running:

sudo systemctl status mariadb

It should show active (running).

4. Connect to MariaDB as Root (Without Password)

Now that MariaDB is running without authentication, you can connect as the root user without providing a password.

mysql -u root

You should be directly logged into the MariaDB prompt, indicating successful connection without authentication.

5. Reset Root Password

Once connected, you need to perform two critical steps: flush privileges and then set the new password.

While skip-grant-tables is active, anyone can connect to your database. Act swiftly and follow these steps precisely to minimize the exposure window.

First, flush the privileges. This command reloads the grant tables into memory, making changes effective immediately after they are made, even though skip-grant-tables is still active.

FLUSH PRIVILEGES;

Next, set the new password for the root user. MariaDB on Ubuntu 22.04 LTS typically uses the unix_socket authentication plugin for the root user by default, which allows the system root user to access MariaDB root without a password. However, if you're trying to set a password for remote access or change the local password to a specific string, you'll need to update the mysql.user table.

Method A: Using ALTER USER (Recommended for modern MariaDB)

This method is cleaner and more explicit. Replace 'YourStrongPasswordHere' with your desired new password.

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

If you encounter issues or want to explicitly define the authentication plugin for password-based login, you might need to specify it: ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' BY 'YourStrongPasswordHere'; Or, if you prefer to rely on unix_socket for local root and set a password for remote access (which isn't covered by root@localhost), you'd typically create a new user for remote access. For this guide, we assume you want to set a password for root@localhost.

Method B: Using UPDATE mysql.user (Older method, might be necessary for specific setups)

UPDATE mysql.user SET Password=PASSWORD('YourStrongPasswordHere') WHERE User='root' AND Host='localhost';
FLUSH PRIVILEGES; -- Re-run flush privileges after an UPDATE

Choose a strong, unique password for your MariaDB root user. Avoid common words, personal information, or easily guessable patterns. Consider using a password manager to generate and store it securely.

Exit the MariaDB prompt:

exit

6. Stop MariaDB and Revert Configuration

Now that the password has been reset, it's crucial to stop MariaDB and remove the skip-grant-tables directive to restore normal, secure operation.

sudo systemctl stop mariadb

Edit the configuration file again to remove the line you added earlier:

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

Remove the skip-grant-tables line:

[mysqld]
# ... other configurations ...
# skip-grant-tables  <-- REMOVE OR COMMENT OUT THIS LINE

Save the file and exit.

Forgetting to remove skip-grant-tables will leave your MariaDB server completely unsecured, allowing anyone to access your databases without a password. This is a critical security vulnerability.

7. Start MariaDB in Normal Mode

Finally, start the MariaDB service in its normal, secure operating mode.

sudo systemctl start mariadb

Verify the service status:

sudo systemctl status mariadb

It should show active (running).

8. Verify New Password

Attempt to log in to MariaDB as the root user using the new password you just set.

mysql -u root -p

Enter your YourStrongPasswordHere when prompted. If successful, you will be granted access to the MariaDB prompt.

Enter password: YourStrongPasswordHere
Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MariaDB connection id is 1234
Server version: 10.6.7-MariaDB-2ubuntu1.1 Ubuntu 22.04

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

MariaDB [(none)]> 

Congratulations! You have successfully reset your MariaDB root password on Ubuntu 22.04 LTS.