Fix MySQL Error 1045 Access Denied - Step-by-Step Guide

Symptoms

When connecting to MySQL, you receive the following error message:

ERROR 1045 (28000): Access denied for user 'username'@'hostname' (using password: YES/NO)

This error may appear when using command-line clients, application connections (e.g., PHP, Python, Java), or GUI tools like phpMyAdmin and MySQL Workbench. The connection attempt is rejected immediately, preventing any database operations.

Root Causes

The error has several common causes:

Incorrect username or password: The credentials provided do not match any user record in MySQL's user table.
Host mismatch: The user is defined only for specific hosts (e.g., 'localhost') but connection originates from a different host (e.g., '192.168.1.100').
User does not exist: The specified username does not exist in the MySQL server.
Password expired: The user's password has expired and must be reset.
Authentication plugin mismatch: MySQL 8.0+ uses caching_sha2_password by default; older clients may require mysql_native_password.
Privileges not flushed: After creating or modifying users, changes may not take effect until FLUSH PRIVILEGES is executed.

Step-by-Step Fix

Step 1: Verify Credentials

Double-check the username, password, and host. Test with the MySQL client:

mysql -u your_username -p -h localhost

If using a remote host, replace localhost with the server IP or hostname.

Step 2: Reset Root Password (if root access is lost)

Stop the MySQL service:
```
sudo systemctl stop mysql
```
Start MySQL in safe mode without grant tables:
```
sudo mysqld_safe --skip-grant-tables &
```
Connect without password:
```
mysql -u root
```

Flush privileges and reset password:

FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewStrongPassword';

Exit and restart MySQL normally:
```
sudo systemctl restart mysql
```

Step 3: Grant Proper Host Access

If the user exists but host is wrong, grant access from the required host:

GRANT ALL PRIVILEGES ON *.* TO 'username'@'hostname' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

To allow access from any host, use '%' as the host (use cautiously).

Step 4: Update Authentication Plugin (MySQL 8.0+)

For older clients, change the plugin:

ALTER USER 'username'@'hostname' IDENTIFIED WITH mysql_native_password BY 'password';
FLUSH PRIVILEGES;

Step 5: Check Password Expiry

If password expired, reset it:

ALTER USER 'username'@'hostname' IDENTIFIED BY 'newpassword';

Alternative Fixes

Use a different client: If using an older client, upgrade to a version that supports caching_sha2_password.
Create a new user: If existing user is corrupted, create a new user with required privileges.
Check MySQL logs: Review /var/log/mysql/error.log for more details on authentication failures.
Reset using init-file: Create a SQL file with password reset commands and restart MySQL with --init-file option.

Prevention

Use strong, unique passwords for each MySQL user.
Document user-host mappings to avoid host mismatch issues.
Regularly update MySQL to benefit from security and compatibility improvements.
Test connections after creating or modifying users with FLUSH PRIVILEGES.
Implement connection pooling to reduce authentication load and errors.
Monitor logs for repeated authentication failures to detect potential attacks.

By following these steps, you can resolve MySQL error 1045 and prevent future access issues. Always ensure you have proper backups before making changes to user authentication.