# MySQL Access Denied: Authentication Failed

You attempt to log into MySQL and encounter this error:

bash
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

This authentication failure is one of the most common MySQL errors. It can happen when setting up a new server, after upgrades, or simply after forgetting a password. Let me show you how to recover access.

Understanding the Error

The access denied error means MySQL rejected your authentication attempt. Common causes include:

  1. 1.Incorrect password
  2. 2.User doesn't exist
  3. 3.User exists but not for the host you're connecting from
  4. 4.Password is correct but plugin authentication differs
  5. 5.User exists but has no privileges

Step 1: Verify the User and Host

MySQL users are defined by both username AND host. The user 'root'@'localhost' is different from 'root'@'127.0.0.1' or 'root'@'%'.

If you can still access MySQL with another user, check existing users:

sql
SELECT user, host, authentication_string, plugin
FROM mysql.user
WHERE user = 'root';

You might see multiple entries:

bash
+------+-----------+-----------------------+-----------------------+
| user | host      | authentication_string | plugin                |
+------+-----------+-----------------------+-----------------------+
| root | localhost | *hash...              | mysql_native_password |
| root | %         |                       | auth_socket           |
+------+-----------+-----------------------+-----------------------+

The empty password with auth_socket plugin is common on newer Ubuntu systems.

Step 2: Reset Root Password Using Skip-Grant-Tables

If you've lost the root password, reset it by starting MySQL in safe mode.

Stop MySQL:

bash
sudo systemctl stop mysql

Start MySQL with skip-grant-tables:

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

The --skip-networking flag prevents remote connections during recovery for security.

Connect without password:

bash
mysql -u root

Reset the password:

For MySQL 5.7.6 and later:

sql
FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewStrongPassword123!';

For MySQL 5.7.5 and earlier:

sql
FLUSH PRIVILEGES;
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('NewStrongPassword123!');

Stop MySQL and restart normally:

bash
sudo killall mysqld
sudo systemctl start mysql

Step 3: Fix Authentication Plugin Issues

Modern MySQL installations often use auth_socket or caching_sha2_password plugins. This can cause issues with older clients.

Check current plugin:

sql
SELECT user, host, plugin FROM mysql.user WHERE user = 'root';

Change to native password authentication:

sql
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'YourPassword123!';
FLUSH PRIVILEGES;

Or create a new user with native password:

sql
CREATE USER 'admin'@'localhost' IDENTIFIED WITH mysql_native_password BY 'YourPassword123!';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Step 4: Resolve Host Mismatch

If you're connecting from a different host than the one defined in MySQL, you'll get access denied.

Check allowed hosts for your user:

sql
SELECT user, host FROM mysql.user;

To connect from any host (use with caution):

sql
CREATE USER 'myuser'@'%' IDENTIFIED BY 'SecurePassword123!';
GRANT ALL PRIVILEGES ON mydatabase.* TO 'myuser'@'%';
FLUSH PRIVILEGES;

For specific hosts:

sql
CREATE USER 'myuser'@'192.168.1.%' IDENTIFIED BY 'SecurePassword123!';
GRANT ALL PRIVILEGES ON mydatabase.* TO 'myuser'@'192.168.1.%';
FLUSH PRIVILEGES;

Step 5: Check for Anonymous Users

Anonymous users can interfere with authentication. Check for them:

sql
SELECT user, host FROM mysql.user WHERE user = '';

Remove anonymous users:

sql
DROP USER ''@'localhost';
DROP USER ''@'your-hostname';
FLUSH PRIVILEGES;

Step 6: Verify Connection Parameters

When connecting, specify parameters explicitly to avoid confusion:

```bash # Connect with all parameters mysql -h localhost -P 3306 -u root -p

# For TCP connection (uses 127.0.0.1, not localhost) mysql -h 127.0.0.1 -P 3306 -u root -p

# With protocol specified mysql --protocol=TCP -h localhost -P 3306 -u root -p ```

Step 7: Using MySQL Configuration File

Store credentials securely in a configuration file:

**Create ~/.my.cnf:**

ini
[client]
user = root
password = YourPassword123!
host = localhost

Set proper permissions:

bash
chmod 600 ~/.my.cnf

Now you can connect without entering credentials:

bash
mysql

Step 8: Docker and MySQL Access

For MySQL running in Docker:

```bash # Get into container docker exec -it mysql-container mysql -u root -p

# If you need to reset password in Docker docker exec -it mysql-container mysql --skip-grant-tables ```

Or set the root password when starting:

bash
docker run --name mysql -e MYSQL_ROOT_PASSWORD=YourPassword123! -d mysql:latest

Verification

Test your new credentials:

bash
mysql -u root -p -e "SELECT USER(), CURRENT_USER();"

Expected output:

bash
+----------------+----------------+
| USER()         | CURRENT_USER()|
+----------------+----------------+
| root@localhost | root@localhost |
+----------------+----------------+

Check your privileges:

sql
SHOW GRANTS FOR 'root'@'localhost';

Common Error Variations

Using password: NO

bash
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

You're not providing a password. Use -p flag:

bash
mysql -u root -p

Using password: YES

You're providing a password, but it's wrong. Reset it following the steps above.

Unknown database

bash
ERROR 1044 (42000): Access denied for user 'user'@'localhost' to database 'mydb'

The user lacks privileges on this specific database:

sql
GRANT ALL PRIVILEGES ON mydb.* TO 'user'@'localhost';
FLUSH PRIVILEGES;

Security Best Practices

After recovering access, secure your installation:

```sql -- Remove anonymous users DELETE FROM mysql.user WHERE User='';

-- Disallow root remote login (optional) DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');

-- Remove test database DROP DATABASE IF EXISTS test;

-- Apply changes FLUSH PRIVILEGES; ```

Run the secure installation script:

bash
mysql_secure_installation

With these methods, you can recover from any MySQL access denied situation. Always remember to store credentials securely and use strong passwords.