Node.js and MySQL Connection Error: In-depth Analysis and Solutions for ER_ACCESS_DENIED_ERROR

Problem Background and Error Analysis

When connecting to a MySQL database from a Node.js application, developers frequently encounter the ER_ACCESS_DENIED_ERROR: Access denied for user 'root'@'localhost' (using password: YES) error. This error indicates authentication failure, yet interestingly, the same credentials work perfectly via command-line tools. This inconsistency often stems from compatibility issues in underlying drivers.

Core Solution: MySQL Driver Version Update

Based on practical verification, MySQL Node.js driver version compatibility is a critical factor causing this error. Older driver versions may have defects in authentication protocol implementation or mismatched security policies. Below is a complete example of resolving the issue by updating the driver:

// Update mysql dependency version in package.json
{
  "dependencies": {
    "mysql": "^2.18.1"
  }
}

After installing the update, reconfigure the database connection:

const mysql = require('mysql');

const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'your_password',
  database: 'your_database'
});

connection.connect((err) => {
  if (err) {
    console.error('Connection error:', err);
    return;
  }
  console.log('Database connection successful');
});

Other Potential Causes and Supplementary Solutions

Beyond driver version issues, the following factors may also lead to authentication failure:

Special Character Handling in Passwords

When passwords contain special characters such as $, @, or #, ensure proper escaping. In JavaScript strings, some characters may require additional handling:

// Handling passwords with special characters
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'te\$t123', // Escape the $
  database: 'test_db'
});

Port Configuration Verification

Ensure the MySQL port used by the Node.js application matches the actual service port. The default is 3306, but it may differ in certain environments:

const connection = mysql.createConnection({
  host: 'localhost',
  port: 3306, // Explicitly specify the port
  user: 'root',
  password: 'password',
  database: 'your_database'
});

User Privileges and Creating New Users

If the root user continues to cause issues, consider creating a new database user:

-- Execute in MySQL
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'secure_password';
GRANT ALL PRIVILEGES ON *.* TO 'app_user'@'localhost';
FLUSH PRIVILEGES;

Then use the new user credentials in Node.js:

const connection = mysql.createConnection({
  host: 'localhost',
  user: 'app_user',
  password: 'secure_password',
  database: 'your_database'
});

Environment-Specific Considerations

In virtual machine and Docker environments, network configurations can impact connection behavior. Ensure:

• Firewall rules permit database port communication
• Container network configurations correctly map ports
• Hostname resolution is consistent inside and outside containers

Best Practices Summary

A systematic approach to resolving ER_ACCESS_DENIED_ERROR includes: keeping drivers updated to the latest stable version, verifying connection parameter accuracy, handling special characters appropriately, and testing network connectivity in complex environments. Through layered troubleshooting, developers can efficiently identify and resolve authentication issues.