MariaDB Root Password Reset on macOS: Using Skip Grant Tables

Lost your MariaDB root password on macOS? This guide shows you how to securely reset it using the `skip-grant-tables` method in your local development environment.

Welcome, fellow SysAdmin! Forgetting a database root password is a common pitfall, especially in local development environments where quick setups are the norm. When you can no longer log into your MariaDB instance on macOS, and no other privileged users are available, resetting the root password becomes a critical task. This guide will walk you through the process of resetting your MariaDB root password on a macOS local environment using the skip-grant-tables method, which allows you to bypass authentication temporarily to regain control.

Symptom & Error Signature

When your MariaDB root password is lost or incorrect, you typically encounter the following issues:

• Inability to connect via the command line: Attempts to connect using the mysql client will fail.
• Applications failing to connect: Local applications, such as web servers, database clients (DBeaver, Sequel Pro, TablePlus), or development frameworks, will report connection errors.

Typical Error Output:

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

Or, if you omit -p and try to connect without a password:

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

Root Cause Analysis

The underlying reason for this issue is simply that the password you are providing (or not providing) does not match the one stored in MariaDB's mysql.user table for the root user, or the root user's authentication plugin has changed. Since the root user typically has all privileges, without its password, you're locked out of administrative tasks.

To reset the password, we need to:

1. Temporarily start the MariaDB server without loading the grant tables (which control user permissions and passwords). This allows any user to connect without a password.
2. Connect to the database as root (without a password).
3. Update the root user's password using SQL commands.
4. Flush the privilege tables so MariaDB reloads them with the new password.
5. Restart MariaDB in its normal operating mode.

Step-by-Step Resolution

Follow these steps carefully to regain access to your MariaDB instance.

1. Stop the MariaDB Service

First, you must stop the currently running MariaDB service. On macOS, if you installed MariaDB via Homebrew, you'll use brew services.

brew services stop mariadb

Ensure the MariaDB service is completely stopped before proceeding. You can verify its status:

brew services list | grep mariadb

The status should show stopped.

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

Now, we'll start MariaDB in a special "safe mode" that bypasses the grant tables, allowing you to connect as root without a password. We'll also disable networking for security during this step.

sudo mysqld_safe --skip-grant-tables --skip-networking &

This command starts the mysqld_safe process in the background (&), preventing it from blocking your terminal.

While mysqld_safe --skip-grant-tables is running, your MariaDB server is completely unsecured. Anyone can connect to it without a password and modify its contents. The --skip-networking flag mitigates this by preventing external connections, but you should proceed quickly and carefully.

3. Connect to MariaDB as Root (without password)

With MariaDB running in safe mode, you can now connect as the root user without needing a password.

mysql -u root

You should now be presented with the MariaDB monitor prompt.

Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MariaDB connection id is 10
Server version: 10.x.x-MariaDB Homebrew

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)]> 

4. Flush Privileges and Reset the Root Password

Inside the MariaDB monitor, execute the following SQL commands.

First, flush the privileges to ensure MariaDB reloads them once we make changes.

FLUSH PRIVILEGES;

Next, reset the root user's password. The method depends slightly on your MariaDB version and authentication plugin. The ALTER USER statement is the most modern and recommended approach.

Replace 'YourNewStrongPassword' with a strong, unique password.

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

Choose a strong, complex password. Combine uppercase and lowercase letters, numbers, and symbols. Avoid easily guessable passwords.

If you are using an older MariaDB version (e.g., prior to 10.4) or encounter issues with ALTER USER, you might need to use the UPDATE statement, explicitly setting the mysql_native_password plugin if it's not the default:

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

After updating the password, flush privileges again to ensure the changes are active.

FLUSH PRIVILEGES;

5. Exit MariaDB Client and Stop Safe Mode Instance

Exit the MariaDB monitor:

exit;

Now, you need to stop the mysqld_safe instance that was started in safe mode. First, find its Process ID (PID):

ps aux | grep -i 'mysqld.*skip-grant-tables' | grep -v grep

This command will show you the running mysqld_safe process. Look for the process ID (the second column). For example, if the output looks like:

_mysql          12345   0.0  0.1 4305596 17928 s001  S+   10:30AM   0:00.08 /opt/homebrew/opt/mariadb/bin/mysqld_safe --skip-grant-tables --skip-networking

The PID is 12345. Then, kill that specific process:

sudo kill 12345 # Replace 12345 with the actual PID you found

Alternatively, a more general approach, though potentially less precise if other mysqld processes are running:

sudo pkill mysqld_safe

6. Restart MariaDB Service Normally

With the safe mode instance stopped, restart your MariaDB service via Homebrew. This will launch it in its regular, secured operating mode.

brew services start mariadb

You can check the status of your MariaDB service to ensure it's running correctly:

brew services list | grep mariadb

It should now show started.

7. Verify New Password

Finally, test your new root password by attempting to connect to MariaDB normally.

mysql -u root -p

Enter your new password when prompted. If successful, you will be granted access to the MariaDB monitor, confirming that your password has been successfully reset.

Congratulations! You have successfully reset your MariaDB root password on macOS. Remember to keep your new password secure.