Docker Compose部署Node+Express+Sequelize+MySQL连接错误求助

问题描述 #

尝试Docker化部署集成MySQL与Sequelize的Node Express服务器时，MySQL容器启动失败，同时存在数据库连接配置错误。

原始docker-compose.yml配置 #

version: '3.9'

services:
  mysql_db:
    platform: linux/x86_64
    image: mysql:5.7
    restart: always
    environment:
      MYSQL_DATABASE: budget
      MYSQL_ROOT_PASSWORD: 12345678
    ports:
      - 3309:3306
    networks:
      - budget_app_network
    volumes:
      - mysql_db_volume:/var/lib/mysql

  api: 
    build:
      context: ./server
      dockerfile: Dockerfile
    ports:
      - ${API_PORT}:${API_PORT}
    networks:
      - budget_app_network
    environment:
      PORT: ${API_PORT}
      DATABASE_URL: mysql://root:12345678@localhost:3309/budget
      ACCESS_TOKEN_SECRET: ${ACCESS_TOKEN_SECRET}
      REFRESH_TOKEN_SECRET: ${REFRESH_TOKEN_SECRET}
      MAIL_TOKEN_SECRET: ${MAIL_TOKEN_SECRET}
    depends_on:
      - mysql_db

networks:
  budget_app_network:

volumes:
  mysql_db_volume: {}

错误日志 #

✔ Network budget_budget_app_network Created 0.1s 
✔ Container budget-mysql_db-1 Created 0.0s Attaching to budget-mysql_db-1 budget-mysql_db-1 | 2023-06-29 12:16:17+00:00 
[Note] [Entrypoint]: Entrypoint script for MySQL Server 5.7.42-1.el7 started. budget-mysql_db-1 | 2023-06-29 12:16:17+00:00 
[Note] [Entrypoint]: Switching to dedicated user 'mysql' budget-mysql_db-1 | 2023-06-29 12:16:17+00:00 
[Note] [Entrypoint]: Entrypoint script for MySQL Server 5.7.42-1.el7 started. budget-mysql_db-1 | '/var/lib/mysql/mysql.sock' -> '/var/run/mysqld/mysqld.sock' budget-mysql_db-1 | 2023-06-29T12:16:17.503579Z 0 
[Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). budget-mysql_db-1 | 2023-06-29T12:16:17.504445Z 0 
[Note] mysqld (mysqld 5.7.42) starting as process 1 ... budget-mysql_db-1 | 2023-06-29T12:16:17.506351Z 0 
[Note] InnoDB: PUNCH HOLE support available budget-mysql_db-1 | 2023-06-29T12:16:17.506362Z 0 
[Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins budget-mysql_db-1 | 2023-06-29T12:16:17.506364Z 0 
[Note] InnoDB: Uses event mutexes budget-mysql_db-1 | 2023-06-29T12:16:17.506366Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier budget-mysql_db-1 | 2023-06-29T12:16:17.506368Z 0 
[Note] InnoDB: Compressed tables use zlib 1.2.13 budget-mysql_db-1 | 2023-06-29T12:16:17.506369Z 0 [Note] InnoDB: Using Linux native AIO budget-mysql_db-1 | 2023-06-29T12:16:17.506519Z 0 
[Note] InnoDB: Number of pools: 1 budget-mysql_db-1 | 2023-06-29T12:16:17.506596Z 0 
[Note] InnoDB: Using CPU crc32 instructions budget-mysql_db-1 | 2023-06-29T12:16:17.507485Z 0 
[Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M budget-mysql_db-1 | 2023-06-29T12:16:17.511283Z 0 
[Note] InnoDB: Completed initialization of buffer pool budget-mysql_db-1 | 2023-06-29T12:16:17.512403Z 0 
[Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority(). budget-mysql_db-1 | 2023-06-29T12:16:17.524033Z 0 

[ERROR] [FATAL] InnoDB: Table flags are 0 in the data dictionary but the flags in file ./ibdata1 are 0x4800! budget-mysql_db-1 | 2023-06-29 12:16:17 0x7fa4adb1e880 InnoDB: Assertion failure in thread 140345265481856 in file ut0ut.cc line 921 budget-mysql_db-1 | InnoDB: We intentionally generate a memory trap. budget-mysql_db-1 | InnoDB: Submit a detailed bug report to http://bugs.mysql.com. budget-mysql_db-1 | InnoDB: If you get repeated assertion failures or crashes, even budget-mysql_db-1 | InnoDB: immediately after the mysqld startup, there may be budget-mysql_db-1 | InnoDB: corruption in the InnoDB tablespace. Please refer to budget-mysql_db-1 | InnoDB: http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html budget-mysql_db-1 | InnoDB: about forcing recovery. budget-mysql_db-1 | 12:16:17 UTC - mysqld got signal 6 ; budget-mysql_db-1 | This could be because you hit a bug. It is also possible that this binary budget-mysql_db-1 | or one of the libraries it was linked against is corrupt, improperly built, budget-mysql_db-1 | or misconfigured. This error can also be caused by malfunctioning hardware. budget-mysql_db-1 | Attempting to collect some information that could help diagnose the problem. budget-mysql_db-1 | As this is a crash and something is definitely wrong, the information budget-mysql_db-1 | collection process might fail. budget-mysql_db-1 | budget-mysql_db-1 | key_buffer_size=8388608 budget-mysql_db-1 | read_buffer_size=131072 budget-mysql_db-1 | max_used_connections=0 budget-mysql_db-1 | max_threads=151 budget-mysql_db-1 | thread_count=0 budget-mysql_db-1 | connection_count=0

错误原因分析 #

1. InnoDB表空间损坏：日志中InnoDB: Table flags are 0 in the data dictionary but the flags in file ./ibdata1 are 0x4800!表明持久化卷中的ibdata1文件损坏，或与当前MySQL 5.7版本不兼容（比如之前用过更高版本MySQL，残留了不兼容的数据文件）。
2. 数据库连接配置错误：DATABASE_URL中使用localhost:3309，但在Docker容器内部，localhost指向容器自身，而非MySQL容器。应该使用Docker网络中的服务名mysql_db，并连接容器内部的3306端口。

修复步骤 #

1. 清理损坏的MySQL数据卷 #

执行以下命令彻底删除现有容器和关联数据卷，清除损坏的ibdata1文件：

docker-compose down -v

2. 修改数据库连接配置 #

更新docker-compose.yml中api服务的DATABASE_URL环境变量：

DATABASE_URL: mysql://root:12345678@mysql_db:3306/budget

3. 添加MySQL服务就绪等待（可选但推荐） #

depends_on仅保证容器启动顺序，不确保MySQL服务完全就绪。可以在Node项目中添加连接重试逻辑：

const sequelize = new Sequelize(process.env.DATABASE_URL);

async function connectDB() {
  try {
    await sequelize.authenticate();
    console.log('数据库连接成功');
  } catch (error) {
    console.log('数据库连接失败，5秒后重试...');
    setTimeout(connectDB, 5000);
  }
}

connectDB();

4. 重新启动服务 #

docker-compose up --build

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