Docker Compose部署下Python脚本无法连接MySQL问题排查

通过Docker Compose部署了MySQL、Adminer及Python数据导入服务，Adminer可正常连接MySQL，但Python执行数据导入脚本时，出现“无法连接到mysql_db上的MySQL服务器（连接被拒绝）”的错误。以下是相关配置文件、脚本代码及报错信息：

docker-compose.yml配置 #

version: '3.9'

services:
  mysql_db:
    image: mysql:latest
    restart: unless-stopped
    environment:
      MYSQL_DATABASE: ${MY_SQL_DATABASE}
      MYSQL_USER: ${MY_SQL_USER}
      MYSQL_PASSWORD: ${MY_SQL_PASSWORD}
      MYSQL_ROOT_PASSWORD: ${MY_SQL_ROOT_PASSWORD}
    ports:
      - '3306:3306'
    volumes:
      - ./mysql-data:/var/lib/mysql

  adminer:
    image: adminer:latest
    restart: unless-stopped
    ports:
      - 8080:8080
  
  ingestion-python:
    build:
      context: . 
      dockerfile: ingestion.dockerfile
    depends_on:
      - mysql_db

Python导入脚本 #

from dotenv import load_dotenv
import os
import pandas as pd
from sqlalchemy import create_engine


def main():
   
    load_dotenv('.env')
    
    user = os.environ.get('MY_SQL_USER')
    password = os.environ.get('MY_SQL_PASSWORD')
    host = os.environ.get('MY_SQL_HOST')
    port = os.environ.get('MY_SQL_PORT')
    db = os.environ.get('MY_SQL_DATABASE')
    table_name = os.environ.get('MY_SQL_TABLE_NAME')


    print(f'mysql+pymysql://{user}:{password}@{host}:{port}/{db}')

    engine = create_engine(f'mysql+pymysql://{user}:{password}@{host}:{port}/{db}')

    df = pd.read_csv('./data/data.parquet', encoding='ISO-8859-1', on_bad_lines='skip', engine='python')
    df.to_sql(name=table_name, con=engine, if_exists='append')


if __name__ == '__main__':
    
    main()

报错信息 #

2023-02-14 08:58:59 sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (2003, "Can't connect to MySQL server on 'mysql_db' ([Errno 111] Connection refused)")
2023-02-14 08:58:59 (Background on this error at: https://sqlalche.me/e/20/e3q8)

.env配置 #

#MYSQL CONFIG
MY_SQL_DATABASE = test_db
MY_SQL_USER = data
MY_SQL_PASSWORD = random
MY_SQL_ROOT_PASSWORD = root

#PYTHON INGESTION
MY_SQL_HOST = mysql_db
MY_SQL_PORT = 3306
MY_SQL_TABLE_NAME = test_table

出现这个问题的核心原因通常是Python服务启动时，MySQL服务还未完全就绪，或者MySQL的网络配置限制了容器间连接。以下是具体的排查和解决步骤：

1. MySQL服务未完全就绪就执行Python脚本 #

depends_on 仅能保证mysql_db容器启动，无法确保MySQL服务已经完成初始化并可以接受连接。Python脚本可能在MySQL还没准备好时就发起连接，导致连接被拒绝。

解决方法： #

方法一：为Python脚本添加连接重试逻辑

在脚本中增加循环重试机制，直到成功连接MySQL：

from dotenv import load_dotenv
import os
import pandas as pd
from sqlalchemy import create_engine
from sqlalchemy.exc import OperationalError
import time


def main():
    load_dotenv('.env')
    
    user = os.environ.get('MY_SQL_USER')
    password = os.environ.get('MY_SQL_PASSWORD')
    host = os.environ.get('MY_SQL_HOST')
    port = os.environ.get('MY_SQL_PORT')
    db = os.environ.get('MY_SQL_DATABASE')
    table_name = os.environ.get('MY_SQL_TABLE_NAME')

    db_url = f'mysql+pymysql://{user}:{password}@{host}:{port}/{db}'
    print(db_url)

    # 最多重试5次，每次间隔3秒
    max_retries = 5
    retry_count = 0
    engine = None
    while retry_count < max_retries:
        try:
            engine = create_engine(db_url)
            # 测试连接是否有效
            with engine.connect():
                print("成功连接到MySQL")
                break
        except OperationalError:
            retry_count += 1
            print(f"连接失败，重试第 {retry_count}/{max_retries} 次...")
            time.sleep(3)
    
    if not engine:
        print("多次重试仍无法连接MySQL，退出程序")
        return

    df = pd.read_csv('./data/data.parquet', encoding='ISO-8859-1', on_bad_lines='skip', engine='python')
    df.to_sql(name=table_name, con=engine, if_exists='append')
    print("数据导入完成")


if __name__ == '__main__':
    main()

方法二：在Docker Compose中配置健康检查

通过healthcheck让Docker确认MySQL服务就绪后，再启动Python服务：
修改docker-compose.yml中的mysql_db和ingestion-python服务：

version: '3.9'

services:
  mysql_db:
    image: mysql:latest
    restart: unless-stopped
    environment:
      MYSQL_DATABASE: ${MY_SQL_DATABASE}
      MYSQL_USER: ${MY_SQL_USER}
      MYSQL_PASSWORD: ${MY_SQL_PASSWORD}
      MYSQL_ROOT_PASSWORD: ${MY_SQL_ROOT_PASSWORD}
    ports:
      - '3306:3306'
    volumes:
      - ./mysql-data:/var/lib/mysql
    # 添加健康检查
    healthcheck:
      test: ["CMD", "mysqladmin", "ping", "-h", "localhost", "-u", "${MY_SQL_USER}", "-p${MY_SQL_PASSWORD}"]
      interval: 5s
      timeout: 5s
      retries: 5

  adminer:
    image: adminer:latest
    restart: unless-stopped
    ports:
      - 8080:8080
  
  ingestion-python:
    build:
      context: . 
      dockerfile: ingestion.dockerfile
    depends_on:
      # 依赖mysql_db的健康状态
      mysql_db:
        condition: service_healthy

2. MySQL仅监听localhost #

如果MySQL配置为只监听localhost，那么容器外（包括同一Docker网络内的其他容器）无法连接。

解决方法： #

在docker-compose.yml的mysql_db服务中添加command参数，强制MySQL监听所有地址：

mysql_db:
  image: mysql:latest
  restart: unless-stopped
  environment:
    MYSQL_DATABASE: ${MY_SQL_DATABASE}
    MYSQL_USER: ${MY_SQL_USER}
    MYSQL_PASSWORD: ${MY_SQL_PASSWORD}
    MYSQL_ROOT_PASSWORD: ${MY_SQL_ROOT_PASSWORD}
  ports:
    - '3306:3306'
  volumes:
    - ./mysql-data:/var/lib/mysql
  # 强制绑定所有地址
  command: --bind-address=0.0.0.0

3. 环境变量传递问题（可选排查） #

确认Python容器内是否正确加载了.env文件。可以直接在docker-compose.yml中为ingestion-python服务显式传递环境变量，避免依赖容器内的文件：

ingestion-python:
  build:
    context: . 
    dockerfile: ingestion.dockerfile
  depends_on:
    mysql_db:
      condition: service_healthy
  environment:
    MY_SQL_USER: ${MY_SQL_USER}
    MY_SQL_PASSWORD: ${MY_SQL_PASSWORD}
    MY_SQL_HOST: mysql_db
    MY_SQL_PORT: 3306
    MY_SQL_DATABASE: ${MY_SQL_DATABASE}
    MY_SQL_TABLE_NAME: ${MY_SQL_TABLE_NAME}

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