Docker Compose下Python服务无法连接MySQL的问题求助

错误信息 #

_mysql_connector.MySQLInterfaceError: Can't connect to MySQL server on 'localhost:3306' (99)

文件结构 #

docker-compose.yml
.env
mysql/
├── database.sql
└── Dockerfile
python/
├── server.py
└── Dockerfile

相关配置内容 #

docker-compose.yml #

version: '3.8'

services:

  mysql:
    build: 
      context: ./mysql
      dockerfile: Dockerfile
    restart: always
    environment:
      MYSQL_DATABASE: ${MYSQL_DATABASE}
      MYSQL_ROOT_PASSWORD: ${MYSQL_ROOT_PASSWORD}
    ports:
      - '3306:3306'

  pythonapp:
    build:
      context: ./python
      dockerfile: Dockerfile
    depends_on:
      - mysql
    ports:
      - '8080:8080'

python/Dockerfile #

FROM python:3.11-slim

RUN pip install mysql-connector-python && \
    pip install python-dotenv

COPY . /app

WORKDIR /app

CMD ["python", "-u", "server.py"]

EXPOSE 8080

mysql/Dockerfile #

FROM mysql:latest

COPY ./database.sql /docker-entrypoint-initdb.d/

python/server.py #

from http.server import HTTPServer, BaseHTTPRequestHandler
from dotenv import load_dotenv
import mysql.connector
import json
import os 

load_dotenv('../.env')

# Establish connection to MySQL
db_connection = mysql.connector.connect(
    user=os.getenv('MYSQL_USER'), 
    password=os.getenv('MYSQL_ROOT_PASSWORD'), 
    host=os.getenv('_HOST'),
    port=3306,
    database=os.getenv('MYSQL_DATABASE'))
print("DB connected")
...

.env #

_HOST=localhost
MYSQL_DATABASE=db
MYSQL_USER=root
MYSQL_ROOT_PASSWORD=password

排查与解决步骤 #

1. 修复.env文件加载路径问题 #

容器内Python服务的工作目录是/app，load_dotenv('../.env')会尝试在容器根目录找.env文件，但你并未将.env复制到容器中，导致环境变量未加载成功。

解决方法：

• 修改python/Dockerfile，添加复制.env的命令：

FROM python:3.11-slim

RUN pip install mysql-connector-python && \
    pip install python-dotenv

COPY . /app
COPY ../.env /app/.env  # 新增：将.env复制到app目录

WORKDIR /app

CMD ["python", "-u", "server.py"]

EXPOSE 8080

• 同步修改server.py的加载路径：

load_dotenv('.env')  # 改为加载当前目录的.env

2. 修正容器间网络连接配置 #

Docker Compose会自动为服务创建默认网络，服务名就是容器的主机名，Python服务要连接MySQL，需用服务名mysql作为host。

修改.env文件：

_HOST=mysql  # 替换localhost为mysql服务名
MYSQL_DATABASE=db
MYSQL_USER=root
MYSQL_ROOT_PASSWORD=password

3. 处理MySQL初始化延迟问题 #

depends_on仅保证MySQL容器启动，但不保证数据库服务完全就绪（比如执行database.sql初始化需要时间），此时Python直接连接会失败。

在server.py中添加连接重试逻辑：

from http.server import HTTPServer, BaseHTTPRequestHandler
from dotenv import load_dotenv
import mysql.connector
from mysql.connector.errors import InterfaceError
import json
import os 
import time

load_dotenv('.env')

# 重试连接数据库
db_connection = None
retries = 5
while retries > 0:
    try:
        db_connection = mysql.connector.connect(
            user=os.getenv('MYSQL_USER'), 
            password=os.getenv('MYSQL_ROOT_PASSWORD'), 
            host=os.getenv('_HOST'),
            port=3306,
            database=os.getenv('MYSQL_DATABASE'))
        print("DB connected")
        break
    except InterfaceError:
        retries -= 1
        print(f"Failed to connect, retries left: {retries}")
        time.sleep(2)

if not db_connection:
    raise Exception("Could not connect to MySQL after multiple retries")
...

4. 验证MySQL服务状态 #

执行以下命令进入MySQL容器，确认服务正常运行：

docker-compose exec mysql mysql -u root -p

输入密码后，检查目标数据库是否存在：

SHOW DATABASES;

确保db数据库已创建并可正常访问。

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