Node.js API部署至ispmanager后出现MySQL连接拒绝错误（ECONNREFUSED 3306）

问题描述 #

用ISPmanager搭建个人Node.js API，本地运行正常，但部署到线上后，在ISPmanager的bash中执行npm start时返回Error: connect ECONNREFUSED 127.0.0.1:3306错误。通过bash可正常连接MySQL并获取数据，MySQL服务处于运行状态。已尝试切换localhost与127.0.0.1、配置3306端口、检查登录信息、设置socketPath等方案，问题仍未解决。

错误日志 #

bash-4.2$ npm start                                                                                                               

> cosmisexodus@1.0.0 start                                                                                                        
> node server.js                                                                                                                  

Servidor ativo na porta 3000.                                                                                                     
Database connection error: Error: connect ECONNREFUSED 127.0.0.1:3306                                                               
    at TCPConnectWrap.afterConnect [as oncomplete] (node:net:1278:16)                                                               
    --------------------                                                                                                            
    at Protocol._enqueue (/var/www/cosmicexodus/data/www/api.cosmicexodus.xyz/node_modules/mysql/lib/protocol/Protocol.js:144:48)
    at Protocol.handshake (/var/www/cosmicexodus/data/www/api.cosmicexodus.xyz/node_modules/mysql/lib/protocol/Protocol.js:51:23)
    at Connection.connect (/var/www/cosmicexodus/data/www/api.cosmicexodus.xyz/node_modules/mysql/lib/Connection.js:116:18)
    at Object.<anonymous> (/var/www/cosmicexodus/data/www/api.cosmicexodus.xyz/app/models/db.js:13:12)                            
    at Module._compile (node:internal/modules/cjs/loader:1198:14)                                                                 
    at Object.Module._extensions..js (node:internal/modules/cjs/loader:1252:10)                                                   
    at Module.load (node:internal/modules/cjs/loader:1076:32)                                                                     
    at Function.Module._load (node:internal/modules/cjs/loader:911:12)                                                              
    at Module.require (node:internal/modules/cjs/loader:1100:19)                                                                  
    at require (node:internal/modules/cjs/helpers:119:18) {                                                                       
  errno: -111,
  code: 'ECONNREFUSED',
  syscall: 'connect',
  address: '127.0.0.1',
  port: 3306,
  fatal: true
}                                                                                                                                  
/var/www/cosmicexodus/data/www/api.cosmicexodus.xyz/app/models/db.js:16                                                           
    throw error; // You can choose to throw the error or handle it as needed                                                        
    ^                                                                                                                             

Error: connect ECONNREFUSED 127.0.0.1:3306                                                                                        
    at TCPConnectWrap.afterConnect [as oncomplete] (node:net:1278:16)                                                               
    --------------------                                                                                                            
    at Protocol._enqueue (/var/www/cosmicexodus/data/www/api.cosmicexodus.xyz/node_modules/mysql/lib/protocol/Protocol.js:144:48)
    at Protocol.handshake (/var/www/cosmicexodus/data/www/api.cosmicexodus.xyz/node_modules/mysql/lib/protocol/Protocol.js:51:23)
    at Connection.connect (/var/www/cosmicexodus/data/www/api.cosmicexodus.xyz/node_modules/mysql/lib/Connection.js:116:18)
    at Object.<anonymous> (/var/www/cosmicexodus/data/www/api.cosmicexodus.xyz/app/models/db.js:13:12)                            
    at Module._compile (node:internal/modules/cjs/loader:1198:14)                                                                 
    at Object.Module._extensions..js (node:internal/modules/cjs/loader:1252:10)                                                   
    at Module.load (node:internal/modules/cjs/loader:1076:32)                                                                     
    at Function.Module._load (node:internal/modules/cjs/loader:911:12)                                                              
    at Module.require (node:internal/modules/cjs/loader:1100:19)                                                                  
    at require (node:internal/modules/cjs/helpers:119:18) {                                                                       
  errno: -111,
  code: 'ECONNREFUSED',
  syscall: 'connect',
  address: '127.0.0.1',
  port: 3306,
  fatal: true
}

相关代码片段 #

db.config.js #

module.exports = {
    DBSERVER: 'localhost', // ISPmanager推荐用localhost，但我也试过127.0.0.1
    DBUSER: 'admin', 
    DBPASS: 'password', 
    DBNAME: 'test',
    PORT: 3306,
  };

db.js #

const mysql = require('mysql');
const DBConfig = require('../config/db.config.js');

// 创建数据库连接
const connection = mysql.createConnection({
  host: DBConfig.DBSERVER,
  user: DBConfig.DBUSER,
  password: DBConfig.DBPASS,
  database: DBConfig.DBNAME
});

// 连接数据库
connection.connect(error => {
  if (error) {
    console.error('Database connection error:', error); // 记录错误详情
    throw error; // 可以选择抛出错误或自行处理
  } else {
    console.log('Connection to the database established');
  }
});

module.exports = connection;

server.js #

const express = require('express');
const cors = require('cors');
const bodyParser = require('body-parser')

const app = express();
const PORT = process.env.PORT || 3000;
const corsOptions = {
   origin: 'http://localhost'
 };
 //app.use(bodyParser.json());

app.use(cors(corsOptions));

// 处理content-type为application/json的请求
app.use(express.json());

// 处理content-type为application/x-www-form-urlencoded的请求
app.use(express.urlencoded({ extended: true }));

// 测试用的首页路由
app.get("/", (req, res) => {
  res.json({ message: "CosmicExodus . mind" });
});

// 导入路由并初始化
require('./app/routes/exodus.routes.js')(app);

// 启动服务器监听指定端口
app.listen(PORT, () => {
  console.log(`Servidor ativo na porta ${PORT}.`);
});

解决方案 #

1. 确认MySQL是否监听TCP/IP端口 #

ISPmanager环境中，MySQL可能仅配置为通过socket文件连接，而非TCP/IP端口。执行以下命令检查：

netstat -tulpn | grep mysql

如果未看到0.0.0.0:3306或127.0.0.1:3306的监听记录，说明MySQL未开启TCP监听。修改MySQL配置文件（通常为/etc/my.cnf或/etc/mysql/my.cnf）：

• 注释掉skip-networking行
• 若bind-address设置为127.0.0.1可保留（仅允许本地TCP连接），需远程连接则改为0.0.0.0
  修改后重启MySQL服务：

systemctl restart mysql

2. 正确配置socketPath连接 #

若MySQL仅允许socket连接，先获取socket路径：

mysql -u admin -p -e "show variables like 'socket'"

得到路径（如/var/lib/mysql/mysql.sock）后，修改db.js的连接配置：

const connection = mysql.createConnection({
  host: DBConfig.DBSERVER,
  user: DBConfig.DBUSER,
  password: DBConfig.DBPASS,
  database: DBConfig.DBNAME,
  socketPath: '/var/lib/mysql/mysql.sock' // 替换为实际socket路径
});

3. 检查数据库用户的连接权限 #

确保admin用户允许从localhost或127.0.0.1连接。登录MySQL后执行：

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

若host列不是localhost或127.0.0.1，修改权限：

GRANT ALL PRIVILEGES ON test.* TO 'admin'@'localhost';
FLUSH PRIVILEGES;

4. 确认端口未被防火墙拦截 #

执行以下命令测试本地端口访问：

telnet 127.0.0.1 3306

若连接失败，添加防火墙规则允许本地访问3306端口：

iptables -A INPUT -i lo -p tcp --dport 3306 -j ACCEPT

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