What's Actually Happening

When MySQL returns "Access denied for user", the authentication or authorization failed. The user either doesn't exist, has wrong password, or isn't allowed to connect from the client host.

The Error You'll See

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

# Or in application: SQLSTATE[HY000] [1045] Access denied for user 'myuser'@'192.168.1.100' (using password: YES) ```

Why This Happens

  1. 1.Wrong password - Incorrect password entered
  2. 2.User doesn't exist - No such user in MySQL
  3. 3.Wrong host - User not allowed from client IP
  4. 4.Anonymous user conflict - Anonymous user takes precedence
  5. 5.Plugin authentication - Auth plugin mismatch (mysql_native_password vs caching_sha2_password)
  6. 6.Password expired - User password has expired
  7. 7.GRANT missing - User exists but has no privileges

Step 1: Connect as Root

bash
# Connect as root locally
sudo mysql -u root
# Or:
mysql -u root -p

Step 2: Check User Exists

sql
SELECT User, Host, plugin FROM mysql.user WHERE User = 'myuser';

Output shows user and allowed hosts:

bash
+--------+-----------+-----------------------+
| User   | Host      | plugin                |
+--------+-----------+-----------------------+
| myuser | localhost | mysql_native_password |
| myuser | %         | caching_sha2_password |
+--------+-----------+-----------------------+

% means any host. If your host not listed, you can't connect.

Step 3: Create or Update User

Create user:

```sql -- Create user for localhost CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypassword';

-- Create user for any host CREATE USER 'myuser'@'%' IDENTIFIED BY 'mypassword'; ```

Update password:

sql
ALTER USER 'myuser'@'localhost' IDENTIFIED BY 'newpassword';

Step 4: Grant Privileges

```sql -- Grant all privileges on specific database GRANT ALL PRIVILEGES ON mydb.* TO 'myuser'@'localhost';

-- Grant all privileges on all databases GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'localhost';

-- Apply changes FLUSH PRIVILEGES; ```

Step 5: Check Authentication Plugin

MySQL 8.0+ uses caching_sha2_password by default. Older clients may not support it:

```sql -- Check plugin SELECT User, Host, plugin FROM mysql.user;

-- Change to native password ALTER USER 'myuser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'mypassword'; ```

Step 6: Fix Host Restriction

If connecting from different IP:

```sql -- Check what hosts are allowed SELECT User, Host FROM mysql.user WHERE User = 'myuser';

-- Add permission for specific IP CREATE USER 'myuser'@'192.168.1.100' IDENTIFIED BY 'mypassword'; GRANT ALL PRIVILEGES ON mydb.* TO 'myuser'@'192.168.1.100';

-- Or allow any host CREATE USER 'myuser'@'%' IDENTIFIED BY 'mypassword'; GRANT ALL PRIVILEGES ON mydb.* TO 'myuser'@'%'; ```

Step 7: Check for Anonymous Users

Anonymous users ('' as username) can interfere:

sql
SELECT User, Host FROM mysql.user WHERE User = '';

If exists:

sql
-- Drop anonymous user
DROP USER ''@'localhost';
FLUSH PRIVILEGES;

Step 8: Fix Password Expiration

If password expired:

```sql -- Check expiration SELECT User, Host, password_expired FROM mysql.user;

-- Reset password ALTER USER 'myuser'@'localhost' IDENTIFIED BY 'newpassword';

-- Or extend expiration ALTER USER 'myuser'@'localhost' PASSWORD EXPIRE NEVER; ```

Step 9. Test Connection

```bash # Test with mysql client mysql -u myuser -p -h localhost mydb

# Test from remote host mysql -u myuser -p -h mysql-server-ip mydb ```

Step 10: Check MySQL Error Log

If issues persist:

bash
tail -f /var/log/mysql/error.log

Look for:

bash
Access denied for user 'myuser'@'192.168.1.100' (using password: YES)

Verify the Fix

```bash # Should connect successfully mysql -u myuser -p -e "SELECT USER(), CURRENT_USER();"

# Output: # +-------------------+-------------------+ # | USER() | CURRENT_USER() | # +-------------------+-------------------+ # | myuser@localhost | myuser@localhost | # +-------------------+-------------------+ ```

Prevention Tips

```sql -- Always specify host when creating users CREATE USER 'myuser'@'specific-host' IDENTIFIED BY 'password';

-- Use strong passwords -- Grant minimum required privileges GRANT SELECT, INSERT, UPDATE ON mydb.* TO 'myuser'@'localhost';

-- Avoid % for production unless necessary -- Use specific IPs or hostnames ```