GitHub Actions中Node.js连接MySQL时root用户访问被拒求助

问题背景 #

首次使用GitHub Actions测试Node.js API连接MySQL，遇到数据库访问瓶颈，即使root用户也无法正常连接。以下是相关配置及错误信息：

GitHub Actions 配置 #

on: [push]

jobs:
  build:

    runs-on: ubuntu-latest

    strategy:
      matrix:
        node-version: [12.x]

    services:
      mysql:
        image: mysql:5.7
        env:
          DB_HOST: localhost
          MYSQL_ROOT_PASSWORD: password
         
          DB_NAME: heroku_b5b309c959adca7
          MYSQL_Grant: SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO 'b734bbe733ac95'@'localhost' WITH GRANT OPTION

        ports:
          - 3306
        options: --health-cmd="mysqladmin ping" --health-interval=10s --health-timeout=5s --health-retries=3

    steps:
      - uses: actions/checkout@v1
      - name: Use Node.js ${{ matrix.node-version }}
        uses: actions/setup-node@v1
        with:
          node-version: ${{ matrix.node-version }}
      - name: npm install, build, and test
        run: |
            npm install
            sudo service mysql start
            #mysql --host 127.0.0.1 --port ${{ job.services.mysql.ports['3306'] }} -uroot -ppassword  -e "CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'passthatword';"
            mysql --host 127.0.0.1 --port ${{ job.services.mysql.ports['3306'] }} -uroot -ppassword  -e "SELECT host,user,authentication_string FROM mysql.user"
            mysql --host 127.0.0.1 --port ${{ job.services.mysql.ports['3306'] }} -uroot -ppassword  -e "CREATE DATABASE IF NOT EXISTS db"
            #mysql --host 127.0.0.1 --port ${{ job.services.mysql.ports['3306'] }} -uroot -ppassword  -e "GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'localhost' IDENTIFIED BY 'passthatword' WITH GRANT OPTION;"
            mysql --host 127.0.0.1 --port ${{ job.services.mysql.ports['3306'] }} -uroot -ppassword  -e "SHOW DATABASES"
            mysql --host 127.0.0.1 --port ${{ job.services.mysql.ports['3306'] }} -uroot -ppassword  -e "SHOW GRANTS"
            mysql --host 127.0.0.1 --port ${{ job.services.mysql.ports['3306'] }} -uroot -ppassword -Ddb < database.sql
            mysql --host 127.0.0.1 --port ${{ job.services.mysql.ports['3306'] }} -uroot -ppassword -Ddb < testing_with_all_tables.txt
            npm test

错误信息 #

测试过程中出现数据库连接失败，具体错误为Access denied for user 'root'@'localhost' (using password: YES)（或类似的权限/连接拒绝类错误）。

数据库连接代码（db_connection.js） #

const mysql = require("mysql2");
const dbConfig = require("../config/db.general.config.js");
// 创建数据库连接池
const connection = mysql.createPool({
  connectionLimit: 100,
  host: dbConfig.HOST,
  user: dbConfig.USER,
  password: dbConfig.PASSWORD,
  database: dbConfig.DB,
  port: dbConfig.port,
  debug: false,
});
// 尝试建立连接
connection.getConnection(function (err) {
  if (err) throw err;
  console.log("Connected!");
});
module.exports = connection;

数据库配置文件（db.general.config.js） #

module.exports = {
  HOST: process.env.DB_HOST || 'localhost',
  USER: process.env.DB_USER || 'root',
  PASSWORD: process.env.DB_PASSWORD || 'passthatword',
  DB:   process.env.DB_NAME || 'database',
  port: process.env.DB_PORT || '3306',
};

问题排查与解决步骤 #

1. 移除本地MySQL启动命令 #

GitHub Actions的services已经启动了一个独立的MySQL容器，你在steps中执行sudo service mysql start会启动主机本地的MySQL服务，和容器服务产生端口冲突。直接删除这行命令。

2. 修正密码不匹配问题 #

这是核心错误：你在MySQL容器中设置的root密码是password，但dbConfig的默认密码是passthatword，两者完全不匹配。必须保证Node.js使用的密码和容器的root密码一致：

• 要么修改dbConfig的默认密码为password
• 要么在steps的环境变量中显式设置DB_PASSWORD=password

3. 正确传递环境变量 #

容器内的环境变量不会自动同步到job的steps中，需要手动在steps里设置需要的数据库参数，同时注意端口是容器映射的随机端口，必须用${{ job.services.mysql.ports['3306'] }}获取：

- name: npm install, build, and test
  env:
    DB_HOST: 127.0.0.1
    DB_USER: root
    DB_PASSWORD: password
    DB_NAME: db
    DB_PORT: ${{ job.services.mysql.ports['3306'] }}
  run: |
      npm install
      mysql --host ${{ env.DB_HOST }} --port ${{ env.DB_PORT }} -uroot -p${{ env.DB_PASSWORD }} -e "SELECT host,user FROM mysql.user"
      mysql --host ${{ env.DB_HOST }} --port ${{ env.DB_PORT }} -uroot -p${{ env.DB_PASSWORD }} -D${{ env.DB_NAME }} < database.sql
      mysql --host ${{ env.DB_HOST }} --port ${{ env.DB_PORT }} -uroot -p${{ env.DB_PASSWORD }} -D${{ env.DB_NAME }} < testing_with_all_tables.txt
      npm test

4. 移除无效配置 #

MYSQL_Grant不是MySQL官方镜像支持的环境变量，这行配置完全无效。如果需要创建自定义用户并授权，直接在steps中用mysql命令执行：

mysql --host ${{ env.DB_HOST }} --port ${{ env.DB_PORT }} -uroot -p${{ env.DB_PASSWORD }} -e "CREATE USER IF NOT EXISTS 'b734bbe733ac95'@'%' IDENTIFIED BY 'your-custom-password';"
mysql --host ${{ env.DB_HOST }} --port ${{ env.DB_PORT }} -uroot -p${{ env.DB_PASSWORD }} -e "GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO 'b734bbe733ac95'@'%' WITH GRANT OPTION;"
mysql --host ${{ env.DB_HOST }} --port ${{ env.DB_PORT }} -uroot -p${{ env.DB_PASSWORD }} -e "FLUSH PRIVILEGES;"

最终修正后的GitHub Actions配置示例 #

on: [push]

jobs:
  build:
    runs-on: ubuntu-latest
    strategy:
      matrix:
        node-version: [12.x]
    services:
      mysql:
        image: mysql:5.7
        env:
          MYSQL_ROOT_PASSWORD: password
          MYSQL_DATABASE: db
        ports:
          - 3306
        options: --health-cmd="mysqladmin ping" --health-interval=10s --health-timeout=5s --health-retries=3
    steps:
      - uses: actions/checkout@v1
      - name: Use Node.js ${{ matrix.node-version }}
        uses: actions/setup-node@v1
        with:
          node-version: ${{ matrix.node-version }}
      - name: npm install, build, and test
        env:
          DB_HOST: 127.0.0.1
          DB_USER: root
          DB_PASSWORD: password
          DB_NAME: db
          DB_PORT: ${{ job.services.mysql.ports['3306'] }}
        run: |
          npm install
          # 验证MySQL容器连通性
          mysqladmin --host ${{ env.DB_HOST }} --port ${{ env.DB_PORT }} -uroot -p${{ env.DB_PASSWORD }} ping
          # 导入数据库文件
          mysql --host ${{ env.DB_HOST }} --port ${{ env.DB_PORT }} -uroot -p${{ env.DB_PASSWORD }} -D${{ env.DB_NAME }} < database.sql
          mysql --host ${{ env.DB_HOST }} --port ${{ env.DB_PORT }} -uroot -p${{ env.DB_PASSWORD }} -D${{ env.DB_NAME }} < testing_with_all_tables.txt
          # 执行测试
          npm test

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