Docker Compose部署下NodeJS无法连接MySQL的问题排查

问题现象 #

执行sudo docker-compose up启动服务时，NodeJS容器抛出数据库权限错误：

Access denied for user 'Pedro'@'172.19.0.4' (using password: YES)

完整错误栈：

backend_container |     at Packet.asError (/app/node_modules/mysql2/lib/packets/packet.js:728:17)
backend_container |     at ClientHandshake.execute (/app/node_modules/mysql2/lib/commands/command.js:29:26)
backend_container |     at Connection.handlePacket (/app/node_modules/mysql2/lib/connection.js:478:34)
backend_container |     at PacketParser.onPacket (/app/node_modules/mysql2/lib/connection.js:97:12)
backend_container |     at PacketParser.executeStart (/app/node_modules/mysql2/lib/packet_parser.js:75:16)
backend_container |     at Socket.<anonymous> (/app/node_modules/mysql2/lib/connection.js:104:25)
backend_container |     at Socket.emit (node:events:517:28)
backend_container |     at addChunk (node:internal/streams/readable:335:12)
backend_container |     at readableAddChunk (node:internal/streams/readable:308:9)
backend_container |     at Readable.push (node:internal/streams/readable:245:10) {
backend_container |   code: 'ER_ACCESS_DENIED_ERROR',
backend_container |   errno: 1045,
backend_container |   sqlState: '28000',
backend_container |   sqlMessage: "Access denied for user 'Pedro'@'172.19.0.4' (using password: YES)",
backend_container |   sql: undefined
backend_container | }

相关配置代码 #

NodeJS数据库连接代码 #

const db = mysql.createConnection({
    host: process.env.DB_HOST, 
    user: process.env.DB_USER, 
    password: process.env.DB_PASSWORD,
    database: process.env.DB_DATABASE, 
    timezone: 'Z'
});

db.connect(err => {
    if (err) {
        console.error("Error al conectar a la base de datos:", err);
    } else {
        console.log("Conexión exitosa a la base de datos!");
    }
});

NodeJS Dockerfile #

FROM node:18

WORKDIR /app

COPY package*.json ./

RUN npm install

COPY . .

EXPOSE 3000

CMD ["node", "index.js"]

docker-compose.yml #

version: '3.2'

services:
    database:
      image: "mysql"
      environment:
        MYSQL_ROOT_PASSWORD: dbpasswd
        MYSQL_DATABASE: Monitoreo
      ports:
        - "3306:3306"
      volumes:
        - ./BD/Monitoreo_BD.sql:/docker-entrypoint-initdb.d/Monitoreo_BD.sql
        - mysql-data:/var/lib/mysql

    backend:
        image: 'pedrocastro2001/backend-so1'
        container_name: "backend_container"
        environment:
          DB_HOST: database
          DB_USER: Pedro
          DB_PASSWORD: Pedro22
          DB_DATABASE: Monitoreo
        ports:
          - "3000:3000"
        depends_on:
          - database

    frontend:
        image: 'pedrocastro2001/frontend-so1'
        container_name: "frontend_container"
        ports:
          - "5173:5173"

volumes:
  mysql-data:

问题原因与解决方案 #

核心问题 #

你的docker-compose.yml中未创建Pedro数据库用户，MySQL容器仅初始化了root用户和Monitoreo数据库，但Pedro用户不存在，导致连接被拒绝。

具体修复步骤 #

1. 在MySQL服务中添加用户初始化配置
   修改docker-compose.yml的database服务环境变量，添加MYSQL_USER和MYSQL_PASSWORD，让MySQL官方镜像自动创建指定用户并授予目标数据库访问权限：

   database:
     image: "mysql"
     environment:
       MYSQL_ROOT_PASSWORD: dbpasswd
       MYSQL_DATABASE: Monitoreo
       MYSQL_USER: Pedro  # 新增：创建Pedro用户
       MYSQL_PASSWORD: Pedro22  # 新增：设置用户密码
     ports:
       - "3306:3306"
     volumes:
       - ./BD/Monitoreo_BD.sql:/docker-entrypoint-initdb.d/Monitoreo_BD.sql
       - mysql-data:/var/lib/mysql
   

   镜像会自动为Pedro用户授予Monitoreo数据库的全部权限，允许从任意主机（包括Docker内部网段）访问。

2. 清理旧数据卷（必须操作）
   之前的mysql-data卷已保存无Pedro用户的数据库状态，需删除旧卷才能让新配置生效：

   # 停止并删除容器
   sudo docker-compose down
   # 查看项目对应的mysql-data卷全名
   sudo docker volume ls
   # 删除目标卷（替换为实际卷名）
   sudo docker volume rm your_project_name_mysql-data
   # 重新启动服务
   sudo docker-compose up
   

3. 验证初始化脚本（可选）
   若Monitoreo_BD.sql脚本中有自定义用户/权限配置，需确保脚本内的用户名、密码与docker-compose.yml一致，避免权限冲突。

额外优化：处理容器启动顺序问题 #

depends_on仅保证数据库容器先启动，不保证MySQL服务完全就绪。可在NodeJS代码中添加重试逻辑，避免因数据库未初始化完成导致的连接失败：

const connectWithRetry = () => {
    db.connect(err => {
        if (err) {
            console.error("Error al conectar a la base de datos, reintentando en 2 segundos...:", err);
            setTimeout(connectWithRetry, 2000);
        } else {
            console.log("Conexión exitosa a la base de datos!");
        }
    });
};

connectWithRetry();

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