Comprehensive Guide to Resolving MySQL Remote Connection ERROR 1045 (28000): Access Denied

Problem Background and Phenomenon Analysis

In database management practice, MySQL remote connection failures represent common operational challenges. This paper is based on a typical case: MySQL 5.1.31 running on Windows XP system, where local connections work normally but remote connections encounter ERROR 1045(28000). Specifically, when connecting remotely from IP address 192.168.233.163 to MySQL server at 192.168.233.142, although port 3306 communication is normal, user authentication fails.

Core Problem Diagnosis

Initial investigation showed that mysql.user table contained root user records for both localhost and % hosts, suggesting complete permission settings. However, deeper analysis revealed the root cause lies in special character handling within password strings. In Linux bash environment, the dollar sign ($) is interpreted as an environment variable expansion symbol, leading to password verification failure.

The verification process can be simulated through the following code to reproduce the issue:

# Error example: $ symbol in password not escaped
mysql --host=192.168.233.142 --user=root --password=pas$word

# Correct example: using backslash to escape special characters
mysql --host=192.168.233.142 --user=root --password=pas\$word

Systematic Solution Approach

For MySQL remote connection access denied issues, a layered troubleshooting strategy is recommended:

1. Password Special Character Handling

In command-line environments, special characters in passwords require proper escaping. Beyond dollar signs, other shell special characters like exclamation marks (!), quotes, etc., need similar treatment.

# Examples of escaping various special characters
mysql --host=server_ip --user=username --password=pass\!word
mysql --host=server_ip --user=username --password=\'complex&password\'
mysql --host=server_ip --user=username --password="mix@ed#pass"

2. User Privilege Configuration Optimization

Although the % wildcard was configured in the case study, best practices recommend using specific IP addresses or subnet ranges for enhanced security.

-- Create remote access privileges for specific IP
GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.233.163' 
IDENTIFIED BY 'password' WITH GRANT OPTION;

-- Refresh privileges to make changes effective
FLUSH PRIVILEGES;

3. Server Configuration Verification

Check the bind-address setting in MySQL configuration files to ensure the server listens on all network interfaces rather than only local loopback.

# Confirm the following configuration in my.cnf or my.ini
# bind-address = 0.0.0.0  # Allow connections from all IPs
# or
# bind-address = 192.168.233.142  # Bind to specific IP

Extended Troubleshooting

Referring to similar errors in the auxiliary Plesk installation failure case, ERROR 1045 can originate from multiple factors:

Authentication Plugin Compatibility

Different MySQL versions may have variations in authentication mechanisms, particularly compatibility issues between mysql_native_password and caching_sha2_password.

-- Check user authentication plugins
SELECT user, host, plugin FROM mysql.user WHERE user='root';

-- Modify authentication method when necessary
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'password';

Network Layer Validation

After testing port connectivity with telnet, further verify MySQL protocol-level communication:

# Test MySQL protocol handshake
echo "" | telnet 192.168.233.142 3306
# Observe if output contains MySQL version information

Preventive Measures and Best Practices

To avoid similar connection issues, establishing standardized MySQL remote access configuration procedures is recommended:

First, use password management tools or configuration files to store authentication information in development environments, avoiding direct input of passwords containing special characters in command lines. Second, implement the principle of least privilege by creating dedicated users for remote access rather than directly using root accounts. Finally, establish comprehensive monitoring and log auditing mechanisms to detect connection anomalies in real-time.

Through the systematic analysis and solutions provided in this paper, developers can effectively diagnose and repair various access denied issues in MySQL remote connections, ensuring the reliability and security of database services.