In-depth Analysis and Resolution of MySQL ERROR 1045 (28000): Access Denied for User

Problem Background and Error Analysis

MySQL ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) is a common authentication error in MySQL database management. This error indicates that the system rejected the connection attempt when a user tried to access the database as root. In MySQL 5.7 and later versions, this error is often closely related to changes in the authentication mechanism.

Major Changes in MySQL 5.7 Authentication Mechanism

MySQL version 5.7 introduced significant improvements in user authentication, with the most notable change being the update to password storage mechanism. In earlier versions, password hashes were stored in a column named password, while starting from MySQL 5.7, this column was removed and password information is now stored in the authentication_string column.

The following code example demonstrates how to check the current MySQL version and user authentication configuration:

SELECT VERSION();
SELECT user, host, plugin, authentication_string FROM mysql.user WHERE user = 'root';

Critical Role of Password Verification Plugin

During MySQL authentication, the value in the plugin column determines the password verification method used by the system. For traditional password authentication, this value should be "mysql_native_password". If the plugin configuration is incorrect, the system cannot complete authentication even if the password is set correctly.

The following example demonstrates how to verify and update password plugin configuration:

-- Check current plugin configuration
SELECT user, plugin FROM mysql.user WHERE user = 'root';

-- Update to native password plugin if needed
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'new_password';

-- Refresh privileges
FLUSH PRIVILEGES;

Correct Password Setting Methods

Compared to DML statements that directly manipulate the mysql.user table, using MySQL's dedicated commands is more secure and reliable. The SET PASSWORD and ALTER USER statements can properly handle password hashing and plugin configuration.

Recommended secure password setting methods:

-- Method 1: Using SET PASSWORD
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('secure_password');

-- Method 2: Using ALTER USER (recommended for MySQL 5.7+)
ALTER USER 'root'@'localhost' IDENTIFIED BY 'secure_password';

-- Refresh privileges to make changes effective
FLUSH PRIVILEGES;

Complete Troubleshooting Procedure

When encountering ERROR 1045, it is recommended to follow this systematic troubleshooting process:

1. Confirm MySQL version: Use SELECT VERSION() command to verify database version
2. Check user privilege configuration: Query relevant fields in the mysql.user table
3. Verify password plugin settings: Ensure the plugin column is configured correctly
4. Use correct connection command: mysql -u root -p and enter password
5. If the problem persists, consider safe mode reset: Use --skip-grant-tables option

Recovery Operations in Safe Mode

In extreme cases, it may be necessary to enter safe mode for user configuration repair:

-- Stop MySQL service
sudo systemctl stop mysql

-- Start in skip-grant-tables mode
sudo mysqld_safe --skip-grant-tables &

-- Connect to MySQL (no password required)
mysql -u root

-- Perform repair operations
USE mysql;
UPDATE user SET plugin='mysql_native_password', authentication_string=PASSWORD('new_password') WHERE user='root';
FLUSH PRIVILEGES;
EXIT;

-- Restart MySQL service
sudo systemctl restart mysql

Best Practices and Security Recommendations

To avoid similar authentication issues, it is recommended to follow these best practices:

• Regularly update MySQL to stable versions
• Use ALTER USER instead of directly manipulating system tables
• Configure strong password policies
• Regularly backup user privilege configurations
• Avoid using root user for routine operations in production environments

By deeply understanding the core principles of MySQL authentication mechanism and adopting systematic troubleshooting methods, ERROR 1045 can be effectively resolved, ensuring secure and stable database operation.