Node.js 无法连接 MySQL 技术问题求助

Hey there, let's break down the most common reasons your Node.js app can't connect to MySQL and how to fix them:

• Double-check your configuration values
  First, make sure the credentials in db-config.json are 100% correct—typos are the #1 culprit here! Verify:

  • host: For local MySQL, try both localhost and 127.0.0.1 (sometimes localhost uses a Unix socket instead of TCP, which can cause issues)
  • port: Default is 3306—did you change MySQL's default port?
  • user/password: Confirm the user exists in MySQL and the password matches exactly
  • database: Ensure the database name is spelled correctly and actually exists on your MySQL server
    To rule out config file loading issues, temporarily hardcode the values directly in your connection setup:

  var mysql = require('mysql');
  var connection = mysql.createConnection({
    host: 'localhost',
    user: 'your-actual-username',
    port: 3306,
    password: 'your-actual-password',
    database: 'your-db-name'
  });
  

• Verify MySQL is running
  If the MySQL service isn't active, your app can't connect at all:

  • On Windows: Open the Services app (search "Services" in Start), find MySQL, and confirm it's set to "Running"
  • On Linux/macOS: Run systemctl status mysql in your terminal. If it's stopped, start it with systemctl start mysql

• Check MySQL user permissions
  Your MySQL user might not be allowed to connect from your Node.js app's location:

  • Log into your MySQL console and run SELECT user, host FROM mysql.user; to see which hosts your user is permitted to connect from
  • For local connections, ensure your user has localhost or 127.0.0.1 listed in the host column
  • For remote connections, grant access with this SQL command (replace placeholders with your details):

    GRANT ALL PRIVILEGES ON your-db-name.* TO 'your-user'@'your-node-app-ip' IDENTIFIED BY 'your-password';
    FLUSH PRIVILEGES;
    

• Inspect firewall/security group settings
  Firewalls or cloud security groups often block port 3306:

  • Local machine: Temporarily disable your firewall to test if that resolves the issue (don't forget to re-enable it afterward!)
  • Remote MySQL server: Make sure your cloud provider's security group allows incoming traffic on port 3306 from your Node.js app's IP address

• Switch to the mysql2 library
  The older mysql npm package is deprecated—mysql2 offers better compatibility, performance, and support:

  npm uninstall mysql
  npm install mysql2
  

  Update your require statement to var mysql = require('mysql2');—the rest of your code will work exactly the same.

• Get detailed error information
  Your current error log only shows a partial message. Modify the error handler to log the full err object—this will give you specific error codes that pinpoint the root issue:

  connection.connect(function(err){
    if (!err) {
      console.log("Database is connected... \n\n");
    } else {
      console.log("Error connecting database:", err); // Logs full error object with code/context
    }
  });
  

  Common error codes to watch for:

  • ECONNREFUSED: MySQL isn't running, or the host/port is incorrect
  • ER_ACCESS_DENIED_ERROR: Invalid username/password or missing connection permissions
  • ER_BAD_DB_ERROR: The specified database doesn't exist on the server

• Test network connectivity
  If connecting to a remote MySQL server, verify basic network access:

  • Run ping your-mysql-host to check if the server is reachable
  • Use telnet your-mysql-host 3306 to test if the port is open (if this fails, it's definitely a network/firewall issue)

内容的提问来源于stack exchange，提问作者user9633331