Docker化Node.js项目连接MySQL遇SequelizeAccessDeniedError求助

问题背景 #

将Node.js+MySQL项目Docker化后，两个容器均正常启动，但Node.js容器持续抛出SequelizeAccessDeniedError，MySQL容器日志也显示Access denied for user 'scrumboard'@'172.23.0.3' (using password: YES)，容器间通信正常但权限验证失败。

Node.js容器日志 #

> scrumboard-app@1.0.0 start
> node app.js

Use no environment variables
{
  username: 'scrumboard',
  password: 'scrumboard',
  database: 'scrumboard',
  host: 'mysql',
  dialect: 'mysql',
  port: 3306,
  operatorsAliases: false
}
(node:20) [SEQUELIZE0004] DeprecationWarning: A boolean value was passed to options.operatorsAliases. This is a no-op with v5 and should be removed.
(Use `node --trace-deprecation ...` to show where the warning was created)
server is running on port 3000
Unhandled rejection SequelizeAccessDeniedError: Access denied for user 'scrumboard'@'172.23.0.3' (using password: YES)
    at /scrumboard-app/node_modules/sequelize/lib/dialects/mysql/connection-manager.js:125:19
    at tryCatcher (/scrumboard-app/node_modules/bluebird/js/release/util.js:16:23)
    at Promise._settlePromiseFromHandler (/scrumboard-app/node_modules/bluebird/js/release/promise.js:547:31)
    at Promise._settlePromise (/scrumboard-app/node_modules/bluebird/js/release/promise.js:604:18)
    at Promise._settlePromise0 (/scrumboard-app/node_modules/bluebird/js/release/promise.js:649:10)
    at Promise._settlePromises (/scrumboard-app/node_modules/bluebird/js/release/promise.js:725:18)
    at _drainQueueStep (/scrumboard-app/node_modules/bluebird/js/release/async.js:93:12)
    at _drainQueue (/scrumboard-app/node_modules/bluebird/js/release/async.js:86:9)
    at Async._drainQueues (/scrumboard-app/node_modules/bluebird/js/release/async.js:102:5)
    at Async.drainQueues [as _onImmediate] (/scrumboard-app/node_modules/bluebird/js/release/async.js:15:14)
    at process.processImmediate (node:internal/timers:471:21)

a user is connected

MySQL容器日志 #

2022-11-29T15:11:59.491328Z 0 [Note] Server hostname (bind-address): '*'; port: 3306
2022-11-29T15:11:59.491368Z 0 [Note] IPv6 is available.
2022-11-29T15:11:59.491377Z 0 [Note]   - '::' resolves to '::';
2022-11-29T15:11:59.491389Z 0 [Note] Server socket created on IP: '::'.
2022-11-29T15:11:59.495079Z 0 [Warning] Insecure configuration for --pid-file: Location '/var/run/mysqld' in the path is accessible to all OS users. Consider choosing a different directory.
2022-11-29T15:11:59.499174Z 0 [Note] Event Scheduler: Loaded 0 events
2022-11-29T15:11:59.499449Z 0 [Note] mysqld: ready for connections.
Version: '5.7.40'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  MySQL Community Server (GPL)
2022-11-29T15:12:00.101178Z 2 [Note] Access denied for user 'scrumboard'@'172.23.0.3' (using password: YES)

相关配置文件 #

docker-compose.yml

version: '3.8'

services: 
  mysql:
    image: mysql:5.7
    restart: unless-stopped
    env_file: ./.env
    environment:
      - MYSQL_ROOT_PASSWORD=scrumboard
      - MYSQL_PASSWORD=scrumboard
      - MYSQL_DATABASE=scrumboard
      - MYSQL_USER=scrumboard
    ports:
      - 3306:3306
    volumes:
      - db:/var/lib/mysql

  app:
    depends_on:
      - mysql
    build: ./scrumboard-app
    restart: unless-stopped
    env_file: ./.env
    ports:
     - 3000:3000
    stdin_open: true
    tty: true

volumes:
  db:

Sequelize配置文件（config.json）

"development": {
    "username": "scrumboard",
    "password": "scrumboard",
    "database": "scrumboard",
    "host": "mysql",
    "dialect": "mysql",
    "port": 3306,
    "operatorsAliases": false
  }

数据库连接代码

const config = require(__dirname + '/../config/config.json')[env];

let sequelize;

 console.info('Use no environment variables');
 console.info(config);
 sequelize = new Sequelize(config.database, config.username, config.password, config);

可能的原因及解决方法 #

• 残留的MySQL数据卷导致用户配置不生效
  Docker的命名卷db会持久化MySQL数据，如果之前创建过容器，卷中可能留存旧的用户权限数据，新的环境变量配置不会覆盖已有数据。解决方法：

  docker-compose down -v
  docker-compose up -d
  

  此命令会删除所有关联的卷和容器，重新初始化MySQL。

• MySQL用户的访问权限范围受限
  MySQL默认创建的用户可能仅允许localhost访问，而Node容器从Docker内网IP（如172.23.0.3）发起连接，需要赋予用户跨主机访问权限：

  # 进入MySQL容器
  docker exec -it $(docker-compose ps -q mysql) mysql -u root -p
  # 输入root密码scrumboard后执行以下SQL
  GRANT ALL PRIVILEGES ON scrumboard.* TO 'scrumboard'@'%' IDENTIFIED BY 'scrumboard';
  FLUSH PRIVILEGES;
  

• Node应用提前发起连接，MySQL未完成初始化
  depends_on仅保证容器启动顺序，不确保MySQL服务完全就绪。Node应用可能在MySQL初始化用户/数据库前尝试连接，导致权限验证失败。可以添加连接重试逻辑：

  async function connectDB() {
    try {
      await sequelize.authenticate();
      console.log('数据库连接成功');
    } catch (error) {
      console.log('数据库连接失败，2秒后重试...');
      setTimeout(connectDB, 2000);
    }
  }
  // 启动时执行连接逻辑
  connectDB();
  

• 环境变量冲突
  docker-compose中同时使用env_file和environment字段，如果.env文件中存在同名变量（如MYSQL_USER、MYSQL_PASSWORD），会覆盖environment中的配置，导致实际创建的用户信息与预期不符。可以注释掉env_file: ./.env，或确保.env中的变量值与environment一致。

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