Troubleshooting MySQL Error: Access Denied for 'root'@'localhost' (using password YES)

Introduction

Encountering an “Access denied for user ‘root’@‘localhost’ using password YES” error while trying to connect to your MySQL or MariaDB server is a common and often frustrating hurdle for system administrators and developers alike. This error indicates that the database server is rejecting your login attempt for the root user from the localhost address, despite a password being provided. This guide will walk you through the typical scenarios leading to this issue and provide a comprehensive, step-by-step resolution.

Symptom & Error Signature

When attempting to connect to your MySQL/MariaDB server using the mysql client or when an application tries to establish a database connection, you will typically see an error message similar to one of the following:

From the mysql client:

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

From application logs (example for a PHP application):

PHP Warning: mysqli::__construct(): (HY000/1045): Access denied for user 'root'@'localhost' (using password: YES) in /var/www/html/index.php on line X

From Docker container logs:

[Entrypoint] MySQL init process in progress...
[ERROR] Access denied for user 'root'@'localhost' (using password: YES)

Root Cause Analysis

The “Access denied for user ‘root’@‘localhost’ using password YES” error specifically means that the MySQL/MariaDB server received a password, but it was either incorrect, or the root user is not configured to authenticate with a password from localhost. Here’s a breakdown of the most common underlying causes:

1. Incorrect root password: This is the most straightforward cause. The password you are providing simply does not match the one stored in the MySQL user table for root@localhost.
2. auth_socket plugin in use: On modern Linux distributions (like Ubuntu and Debian), MySQL/MariaDB often configures the root user to use the auth_socket authentication plugin by default for root@localhost. This plugin allows the Unix root user to connect to MySQL as root without a password, provided they are connecting from localhost and their Unix user ID matches. When you supply a password, the server still attempts to use auth_socket and fails if the Unix user doesn’t match or if you explicitly try to authenticate with a password against an auth_socket user.
3. User root@localhost not configured for password authentication: Similar to the auth_socket issue, the root user might exist but is specifically configured to not use mysql_native_password or caching_sha2_password (the standard password authentication plugins).
4. Corrupted or improperly configured mysql.user table: Less common, but possible if there were manual interventions or issues during database upgrades, leading to an invalid entry for root@localhost.
5. Environment inconsistencies (e.g., Docker): In Docker environments, misconfigured entry points or volume mounts might lead to a fresh database initialization that doesn’t respect expected root passwords, or an application might be trying to connect before the database is fully initialized and secured.

The key distinction in “using password YES” is that the server received credentials but rejected them, rather than not receiving any password at all.

Step-by-Step Resolution

The most robust way to resolve this issue is to reset the root password for localhost and ensure it’s configured to use a standard password authentication plugin.

#### 1. Stop the MySQL/MariaDB Service

First, you need to stop the running MySQL/MariaDB service to start it in safe mode.

sudo systemctl stop mysql
# For MariaDB, it might be:
# sudo systemctl stop mariadb

[!IMPORTANT] Verify the service status after stopping. If it’s still running, subsequent steps will fail or operate on the wrong instance.

sudo systemctl status mysql
# Look for "Active: inactive (dead)"

#### 2. Start MySQL/MariaDB in Safe Mode (Skip Grant Tables)

Starting the database with --skip-grant-tables allows you to connect to the MySQL server as root without a password, bypassing all permission checks. This is crucial for resetting the password. We’ll also use --skip-networking to prevent remote connections during this vulnerable state.

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

[!WARNING] Running mysqld_safe directly might not work as expected on all Systemd-based systems, or it might leave processes orphaned. A more controlled approach is to use systemctl overrides. However, for a quick fix like this, mysqld_safe & is commonly used. Be sure to kill it later.

To ensure it starts correctly and cleanly, you might prefer this:

Alternative (more robust) method for Systemd systems:

Create a temporary Systemd override:

sudo systemctl edit mysql

Add the following lines to the editor that opens:

[Service]
ExecStart=
ExecStart=/usr/sbin/mysqld --skip-grant-tables --skip-networking --socket=/var/run/mysqld/mysqld.sock --pid-file=/run/mysqld/mysqld.pid

Save and exit (usually Ctrl+X, Y, Enter). Then restart MySQL:

sudo systemctl daemon-reload
sudo systemctl start mysql

[!NOTE] Ensure you remember to remove this override after fixing the issue, as shown in step 6.

#### 3. Connect to MySQL as root

Now, connect to the MySQL server. Since you started it with --skip-grant-tables, you won’t be prompted for a password.

mysql -u root

You should now see the MySQL prompt: mysql>.

#### 4. Reset root Password and Update Authentication Plugin

Inside the MySQL prompt, you need to perform two key actions:

1. Flush privileges: Reloads the grant tables.
2. Alter the root user: Set a new password and ensure the mysql_native_password or caching_sha2_password plugin is used. MySQL 8.0+ defaults to caching_sha2_password, while older versions and MariaDB often use mysql_native_password. Use the plugin appropriate for your MySQL/MariaDB version and client compatibility.

mysql> FLUSH PRIVILEGES;

-- For MySQL 8.0+ (recommended for modern clients):
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'YourStrongNewPasswordHere';

-- For MariaDB or older MySQL versions (or if caching_sha2_password causes client issues):
-- mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'YourStrongNewPasswordHere';

mysql> FLUSH PRIVILEGES;
mysql> quit;

[!WARNING] Replace YourStrongNewPasswordHere with a robust, unique password. Do not use this placeholder password in production!

#### 5. Restart MySQL/MariaDB Service Normally

After resetting the password, you must stop the MySQL/MariaDB service and restart it in its normal operating mode to re-enable grant tables and networking.

If you used mysqld_safe &:

# Find the mysqld_safe process
ps aux | grep mysqld_safe
# Kill it (replace PID with the actual process ID)
sudo kill PID

Then restart the service:

sudo systemctl restart mysql
# For MariaDB:
# sudo systemctl restart mariadb

If you used the Systemd override method:

sudo systemctl stop mysql
sudo systemctl daemon-reload # Reload Systemd to pick up the original service file
sudo systemctl start mysql

#### 6. Remove Systemd Override (if used)

If you created a temporary Systemd override in step 2, remove it now to ensure your service runs with its default configuration.

sudo systemctl revert mysql
sudo systemctl daemon-reload

#### 7. Verify New Password

Finally, try connecting to your MySQL server with the new password.

mysql -u root -p
Enter password: YourStrongNewPasswordHere

You should now successfully connect to the MySQL prompt.

[!IMPORTANT] If you are using auth_socket (e.g., you can connect as sudo mysql without a password), and you want to continue using password authentication for root, ensure that the ALTER USER command specifically sets a password. If your goal is to only use auth_socket for root, then you should avoid setting a password and simply connect via sudo mysql or a client designed to use the socket. However, for most external applications, password-based authentication is preferred.

Consider creating dedicated, less privileged users for your applications instead of using root.

-- Example: Create a new user for an application
CREATE USER 'your_app_user'@'localhost' IDENTIFIED BY 'YourAppPassword';
GRANT SELECT, INSERT, UPDATE, DELETE ON your_database.* TO 'your_app_user'@'localhost';
FLUSH PRIVILEGES;