Fix MySQL ERROR 1045 Access Denied

Symptoms

When connecting to a MySQL database, you receive the following error:

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

This error may appear in command-line clients, application logs, or during installation scripts. Common scenarios include:

• Cannot log in as root or any user
• Application fails to connect to the database
• Password reset attempts fail

Root Causes

The error indicates that MySQL's authentication system rejected the connection. Primary causes include:

• Incorrect password: The password supplied does not match the stored hash.
• Host mismatch: The user is restricted to specific hosts (e.g., 'localhost' vs '%').
• Authentication plugin mismatch: Client does not support the server's plugin (e.g., caching_sha2_password vs mysql_native_password).
• Corrupted user table: The mysql.user table is damaged or has inconsistent data.
• Empty password: No password set but client sends one, or vice versa.

Step-by-Step Fix

Prerequisites

• Access to the server where MySQL runs
• Root or sudo privileges

Method 1: Reset Root Password (Skip Grant Tables)

1. Stop MySQL service:

   sudo systemctl stop mysql

2. Start MySQL in safe mode without privilege checks:

   sudo mysqld_safe --skip-grant-tables &

   Or on some systems:

   sudo mysqld --skip-grant-tables --skip-networking &

3. Connect without password:

   mysql -u root

4. Flush privileges to reload grant tables:

   FLUSH PRIVILEGES;

5. Reset password (choose one):

   ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewStrongPassword123!';

   For older MySQL versions:

   UPDATE mysql.user SET authentication_string=PASSWORD('NewStrongPassword123!') WHERE User='root';

6. Flush and exit:

   FLUSH PRIVILEGES;
   EXIT;

7. Stop safe mode and restart normally:

   sudo killall mysqld
   sudo systemctl start mysql

Method 2: Check Host and User

1. Connect with correct host:

   mysql -u username -p -h localhost

   Try '127.0.0.1' or the server IP.

2. List users and hosts:

   SELECT User, Host FROM mysql.user;

3. If the user@host combination is missing, create it:

   CREATE USER 'username'@'host' IDENTIFIED BY 'password';
   GRANT ALL PRIVILEGES ON *.* TO 'username'@'host';
   FLUSH PRIVILEGES;

Method 3: Update Authentication Plugin

1. Connect with root (using skip-grant-tables if needed).
2. Change plugin to mysql_native_password:

   ALTER USER 'username'@'host' IDENTIFIED WITH mysql_native_password BY 'password';

3. Flush privileges.

Alternative Fixes

• Reinstall MySQL (last resort): Backup databases, purge and reinstall MySQL, then restore.
• Use mysql_secure_installation to set root password interactively.
• Check config file (my.cnf) for any skip-grant-tables or bind-address settings that may interfere.

Prevention

• Always use strong passwords and store them securely.
• Use specific hosts instead of wildcard '%' for production users.
• Keep MySQL updated to avoid plugin compatibility issues.
• Regularly audit user accounts and privileges.
• Backup mysql.user table before making changes.

By following these steps, you can resolve MySQL ERROR 1045 and restore database access. For persistent issues, check MySQL error logs for additional details.