本地Node.js API连接Docker容器内MySQL失败的问题求助

问题背景 #

尝试连接本地Docker容器内的MySQL数据库，可通过phpMyAdmin正常登录root和test账号，但Node.js服务连接时出现权限拒绝或超时错误。

容器配置（docker-compose.yaml） #

version: '3.0'

services:
  database:
    image: mysql:latest
    container_name: pm-mysql
    restart: always
    command: --default-authentication-plugin=mysql_native_password
    #ports: '3306:3307'
    environment:
      MYSQL_ROOT_PASSWORD: test
      MYSQL_DATABASE: test
      MYSQL_USER: test
      MYSQL_PASSWORD: test
    networks:
      - wpsite
  # phpmyadmin
  database_phpmyadmin:
    container_name: phpmyadmin-pm-mysql
    depends_on:
      - database
    image: phpmyadmin/phpmyadmin:latest
    restart: always
    ports:
      - '8080:80'
    environment:
      PMA_HOST: database
      MYSQL_ROOT_PASSWORD: wackySee)71
    networks:
      - wpsite
networks:
  wpsite:

Node.js连接代码 #

// initialize parameters
var express = require('express');
var app = express();
const { body, validationResult } = require('express-validator');
require('dotenv').config();
// Mysql parameters
const mysql = require('mysql2');


app.get('/', function (req, res) {
  res.send('testing get api');
});

// API link: http://localhost/database/
app.get('/database/', function (req, res) {
    console.log("database")

    // Connect to MySQL
    var con = mysql.createConnection({
      host: 'localhost',
      user: "test",
      password: "test",
      database : 'test',
      //port: "3306"
    });
    
    con.connect(function(err) {
      if (err) throw err;
      console.log("Connected!");
    });

    // Read parameter
    tags = req.query.in;

    // Return JSON
    res.end(JSON.stringify(req.query.in));
  });

// Listen port 80
app.listen(80, function () {
  console.log('server: Listening on port 80!');
});

错误情况 #

1. 使用localhost连接时，抛出权限错误：

PM web server: Listening on port 80!
database
F:\PM_web_system\server\index.js:27
      if (err) throw err;
               ^

Error: Access denied for user 'test'@'localhost' (using password: YES)
    at Packet.asError (F:\PM_web_system\server\node_modules\mysql2\lib\packets\packet.js:728:17)
    at ClientHandshake.execute (F:\PM_web_system\server\node_modules\mysql2\lib\commands\command.js:29:26)
    at Connection.handlePacket (F:\PM_web_system\server\node_modules\mysql2\lib\connection.js:456:32)     
    at PacketParser.onPacket (F:\PM_web_system\server\node_modules\mysql2\lib\connection.js:85:12)        
    at PacketParser.executeStart (F:\PM_web_system\server\node_modules\mysql2\lib\packet_parser.js:75:16) 
    at Socket.<anonymous> (F:\PM_web_system\server\node_modules\mysql2\lib\connection.js:92:25)
    at Socket.emit (node:events:513:28)
    at addChunk (node:internal/streams/readable:315:12)
    at readableAddChunk (node:internal/streams/readable:289:9)
    at Socket.Readable.push (node:internal/streams/readable:228:10) {
  code: 'ER_ACCESS_DENIED_ERROR',
  errno: 1045,
  sqlState: '28000',
  sqlMessage: "Access denied for user 'test'@'localhost' (using password: YES)",
  sql: undefined
}

2. 使用容器IP172.30.0.2连接时，抛出超时错误：

server: Listening on port 80!
database
F:\PM_web_system\server\index.js:27
      if (err) throw err;
               ^

Error: connect ETIMEDOUT
    at Connection._handleTimeoutError (F:\PM_web_system\server\node_modules\mysql2\lib\connection.js:189:17)
    at listOnTimeout (node:internal/timers:559:17)
    at processTimers (node:internal/timers:502:7) {
  errorno: 'ETIMEDOUT',
  code: 'ETIMEDOUT',
  syscall: 'connect',
  fatal: true
}

账号信息 #

数据库中test账号的主机配置为%（允许从任意地址连接），root账号的主机包含localhost、127.0.0.1等本地地址。

问题根源分析 #

1. 端口未映射：docker-compose中MySQL的端口映射被注释，导致宿主机无法直接通过localhost或容器IP访问容器内的3306端口。
2. 网络隔离：Node.js服务运行在宿主机，而MySQL容器处于自定义wpsite网络中，宿主机默认无法直接访问容器IP（除非配置端口映射或网络连通）。
3. 地址混淆：使用localhost连接时，实际指向的是宿主机本地而非容器内的MySQL，若宿主机未安装MySQL则连接失败；若安装了，则会尝试连接本地MySQL，导致账号权限不匹配。

修复步骤 #

方案1：开启端口映射（宿主机直接连接） #

1. 修改docker-compose.yaml，取消MySQL端口映射的注释并配置端口：

services:
  database:
    # ...其他配置
    ports:
      - '3307:3306'  # 宿主机3307端口映射到容器3306端口，避免与宿主机MySQL冲突

2. 重启容器：

docker-compose down && docker-compose up -d

3. 修改Node.js连接配置，使用宿主机localhost+映射端口：

var con = mysql.createConnection({
  host: 'localhost',
  user: "test",
  password: "test",
  database : 'test',
  port: "3307"
});

方案2：将Node.js服务加入Docker网络（容器间内部连接） #

如果不想暴露MySQL端口给宿主机，可将Node.js服务加入同个Docker网络：

1. 修改docker-compose.yaml，添加Node.js服务配置：

services:
  # ...database和phpmyadmin配置
  node-api:
    build: ./path/to/node/server  # 替换为你的Node服务目录路径
    container_name: pm-node-api
    ports:
      - '80:80'
    depends_on:
      - database
    networks:
      - wpsite

2. 修改Node.js连接配置，使用MySQL容器名作为host：

var con = mysql.createConnection({
  host: 'database',  // Docker网络内可直接用容器名访问
  user: "test",
  password: "test",
  database : 'test',
  port: "3306"
});

3. 重启所有容器：

docker-compose down && docker-compose up -d

验证连接 #

重启服务后，访问http://localhost/database/，查看控制台是否输出Connected!，确认连接成功。

内容的提问来源于stack exchange，提问作者Alex Chow TK