python MySQL 连接泄漏排查 & 数据库迁移实践

背景

项目有两个 Python 服务（Backend :8000、Agent Service :8001），技术栈：

在频繁开发过程中，遇到数据库报错：

sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (1040, 'Too many connections')

Part 1：连接泄漏排查

现象

频繁 hot reload 后，MySQL 连接数不断增加，最终达到 max_connections 上限，所有服务无法连接数据库。

通过 SHOW PROCESSLIST 可以看到大量 Sleep 状态的连接：

+-----+------+-------------------+--------+---------+------+
| Id  | User | Host              | db     | Command | Time |
+-----+------+-------------------+--------+---------+------+
| 150 | root | 172.17.0.1:53270  | mydb   | Sleep   | 2612 |
| 151 | root | 172.17.0.1:53428  | mydb   | Sleep   | 2312 |
| 152 | root | 172.17.0.1:48910  | mydb   | Sleep   | 2012 |
| ...                                                       |
+-----+------+-------------------+--------+---------+------+

Sleep 时间从几百秒到几千秒不等，说明旧连接一直没有被关闭。

排查过程

第一步：检查连接池配置

两个服务的连接池配置：

合计最大 45 连接/次启动。MySQL 默认 max_connections 约 151，3-4 次 reload 即达上限。

第二步：尝试 signal handler 释放连接池（失败）

尝试在 SIGTERM 信号处理器中调用 engine.dispose()：

# 方案 1：asyncio.run_until_complete（失败）
loop = asyncio.new_event_loop()
loop.run_until_complete(engine.dispose())  # RuntimeWarning: coroutine never awaited

# 方案 2：sync_engine.dispose()（无效）
engine.sync_engine.dispose()  # 调用了但 TCP 连接没有真正关闭

# 方案 3：signal handler 中 sys.exit()（导致 uvicorn 异常退出）
# 方案 4：signal handler 中不退出（reload 失效，进程不退出）
# 方案 5：恢复默认 handler 后 os.kill re-raise（Ctrl+C 失效）

结论：signal handler 方案行不通。aiomysql 的异步连接无法在同步的信号处理器中正确关闭。

第三步：检查 lifespan shutdown

FastAPI 的 lifespan 中已有 await engine.dispose() 调用。但通过添加 print() 调试日志发现 lifespan shutdown 根本没有执行到 close_db()。

原因：启动命令中 --timeout-graceful-shutdown 3 只给 3 秒，而 lifespan shutdown 中有多个清理步骤（Redis 断开、TaskPool 停止、Scheduler 停止等），3 秒内来不及执行到 close_db()，uvicorn 就强制杀掉了进程。

第四步：增加超时后确认 dispose 无效

将 --timeout-graceful-shutdown 改为 300 后，确认 await engine.dispose() 被调用：

🛑 LIFESPAN SHUTDOWN TRIGGERED
🔄 Closing database connection pool...
✅ Database connection pool closed

但 SHOW PROCESSLIST 显示连接数仍在增加。

根本原因：aiomysql 的架构缺陷

aiomysql 的两阶段关闭设计

aiomysql 借鉴了 aiohttp 的设计理念，将连接关闭拆成两个方法：

这个设计的初衷是：在无法 await 的场景（如 __del__、atexit）中至少能做最基本的清理（close()），而在正常的异步上下文中使用 ensure_closed() 做完整的优雅关闭。

问题：close() 只关闭了客户端的 socket，没有通知 MySQL 服务器。MySQL 服务器并不知道连接已断开，仍然保持这条连接在 Sleep 状态，直到 wait_timeout 超时。

SQLAlchemy 的同步连接池 vs 异步驱动

调用链：

await engine.dispose()
  → greenlet_spawn(sync_engine.dispose)
    → QueuePool.dispose()          # 同步方法
      → for conn in pool:          # 同步循环
          conn.close()             # 期望这是一个完整的同步关闭操作

SQLAlchemy 的 QueuePool.dispose() 是纯同步代码。它遍历连接池中的所有连接，调用每个连接的 close() 方法，并期望 close() 是一个完整的、阻塞的关闭操作。

但对 aiomysql 而言，close() 只是一个”半关闭”：客户端标记了关闭，TCP 层面可能也断了，但 MySQL QUIT 包没有发送。服务器端对此一无所知。

虽然外层 await engine.dispose() 通过 greenlet 桥接了异步→同步调用，但 greenlet 桥接的是外层的一次调用，内部循环中的每个 conn.close() 仍然是同步执行的。即使 close() 返回一个协程，也不会被 await。

greenlet 桥接
│
├── 桥接了：await engine.dispose() → sync_engine.dispose()  ✓
│
└── 没有桥接：内部循环中每个 conn.close() 的返回值  ✗
    （如果 close() 返回协程，该协程直接被丢弃）

为什么连接没有被服务器感知到断开？

即使客户端 socket 被 close() 关闭了，在某些网络环境下（特别是容器网络、NAT、云环境），TCP FIN 包可能不会立即到达服务器：

• 容器网络可能有额外的网络层（bridge、overlay）
• TCP keepalive 默认间隔很长（Linux 默认 7200 秒）
• MySQL 只在收到数据或 QUIT 包时才检查连接状态，不主动探测

所以 MySQL 服务器会一直维持这些”幽灵连接”在 Sleep 状态，直到 wait_timeout 到期。

对比：asyncmy 为什么没这个问题

asyncmy 是 aiomysql 的替代品，它的 close() 实现不同：

切换驱动只需改连接字符串和依赖：

# aiomysql（当前）
mysql+aiomysql://user:pass@host/db

# asyncmy（推荐）
mysql+asyncmy://user:pass@host/db

相关 issue： - aiomysql #942 — RuntimeWarning: coroutine ‘Connection.close’ was never awaited - aiomysql #641 — No way to close a connection outside of a coroutine - SQLAlchemy #10457 — Async engine doesn’t fully close connections with aiomysql

最终解决方案

既然 aiomysql 的 close() 不发送 QUIT 包，服务器无法感知连接断开，就让 MySQL 服务器通过 wait_timeout 主动断开空闲连接。长期建议切换到 asyncmy 驱动。

1. MySQL 端：设置 wait_timeout（治本）

SET GLOBAL wait_timeout = 60;
SET GLOBAL interactive_timeout = 60;

注意：这条命令的坑详见 Part 2。

2. Python 端：pool_recycle < wait_timeout

确保 Python 端在连接被服务器断开前主动回收，避免使用已被断开的”死连接”：

engine = create_async_engine(
    database_url,
    pool_recycle=50,     # 小于 MySQL wait_timeout(60)
    pool_pre_ping=True,  # checkout 时检测连接是否有效（双保险）
    ...
)

3. 启动命令：给足 graceful shutdown 时间

# 错误：3 秒太短，lifespan 来不及执行清理
uvicorn app.main:app --reload --timeout-graceful-shutdown 3

# 正确：给足时间让 lifespan shutdown 完成
uvicorn app.main:app --reload --timeout-graceful-shutdown 30

关键教训

Part 2：正确配置 wait_timeout + 数据不丢失的完整操作

为什么 SET GLOBAL wait_timeout = 60 没有生效？

执行后查询发现值还是 28800：

SET GLOBAL wait_timeout = 60;
SET GLOBAL interactive_timeout = 60;
SHOW VARIABLES LIKE 'wait_timeout';   -- 还是 28800 ！

原因有三个：

原因一：查的是 SESSION 级别，不是 GLOBAL

MySQL 的 timeout 是“出生决定”——连接建立时拷贝 GLOBAL 值成为 SESSION 值，此后永远不再变化。

-- 正确的验证方式：分别查 GLOBAL 和 SESSION
SELECT @@global.wait_timeout, @@session.wait_timeout;
-- 结果可能是：60（GLOBAL 改了）、28800（SESSION 还是旧值）

SET GLOBAL 只影响改完之后新建的连接，不影响已有连接。而你当前查询所用的连接（mycli / mysql CLI）是在 SET GLOBAL 之前建立的，所以 SHOW VARIABLES 看到的仍然是 28800。

原因二：容器重启后配置文件覆盖

SET GLOBAL 是运行时修改，不持久化。容器重启后从配置文件加载，wait_timeout 恢复为默认的 28800。

原因三：权限问题（容器环境）

即使是 root，在容器环境中也可能缺少 SUPER 或 SYSTEM_VARIABLES_ADMIN 权限，导致 SET GLOBAL 静默失败。验证：

SHOW GRANTS FOR CURRENT_USER;

为什么 SET SESSION wait_timeout = 60 也没用？

SET SESSION 只改当前这一条连接。应用程序（uvicorn）的连接是另外的连接，完全不受影响。没有办法批量修改所有已有连接的 SESSION 值。

两个 timeout 的区别

两个都要改，否则只改其中一个对另一类连接无效。

正确方案：通过配置文件持久化 wait_timeout

由于数据库运行在容器中（Podman/Docker），SET GLOBAL 不持久化，必须通过配置文件。但当前容器没有挂载数据卷，直接删容器会丢失数据。完整操作分为四步：

第一步：导出数据（保护现场）

# 从宿主机通过网络导出
mysqldump -u root -p -h <db-host> \
  --single-transaction \
  --quick \
  --routines \
  --events \
  --triggers \
  --set-gtid-purged=OFF \
  --all-databases > alldb.sql

# 或者通过容器内的 mysqldump 导出
podman exec -it <container-name> mysqldump -uroot -p<password> \
  --single-transaction \
  --quick \
  --all-databases > alldb.sql

参数说明：

注意：macOS 上 mysqldump 不是独立软件，需要 brew install mysql-client 安装，并手动加 PATH：

echo 'export PATH="/opt/homebrew/opt/mysql-client/bin:$PATH"' >> ~/.zshrc
source ~/.zshrc

第二步：准备配置文件（宿主机）

mkdir -p ~/mariadb-conf ~/mariadb-data

创建 ~/mariadb-conf/custom.cnf：

[mysqld]
wait_timeout = 60
interactive_timeout = 60
max_connections = 200

使用 custom.cnf 放在 conf.d/ 目录下，而不是直接覆盖 /etc/my.cnf。这样不会覆盖数据库的默认配置，更安全。MariaDB/MySQL 启动时会自动加载 /etc/mysql/conf.d/*.cnf 下的所有文件，后加载的覆盖先加载的。

第三步：重建容器（挂载数据卷 + 配置）

# 停止并删除旧容器（数据已在第一步导出）
podman stop <container-name>
podman rm <container-name>

# 重建：挂载数据持久化 + 自定义配置
podman run -d --name <container-name> -p 3306:3306 \
  -e MYSQL_ROOT_PASSWORD=<your-password> \
  -v ~/mariadb-data:/var/lib/mysql \
  -v ~/mariadb-conf:/etc/mysql/conf.d \
  mariadb:<version>

# 等待数据库就绪
sleep 10

关键： - -v ~/mariadb-data:/var/lib/mysql 让数据持久化到宿主机，以后重建容器不再丢数据 - -v ~/mariadb-conf:/etc/mysql/conf.d 让自定义配置持久化，容器重建后 wait_timeout 仍然生效

第四步：导入数据

如果是 MySQL 8.0 → MariaDB 迁移，需要先修复兼容性（MariaDB 10.3 对标 MySQL 5.7，属于降级迁移）：

# 替换 MySQL 8.0 特有的 collation（MariaDB 10.3 不支持）
sed -i '' 's/utf8mb4_0900_ai_ci/utf8mb4_unicode_ci/g' alldb.sql
sed -i '' 's/utf8mb4_0900_bin/utf8mb4_bin/g' alldb.sql

导入：

# 必须用 mysql 客户端，不能用 mycli
mysql -u root -h <db-host> -p --default-character-set=utf8mb4 < alldb.sql

踩坑：不要用 mycli < dump.sql。mycli 是 Python 写的交互客户端，会用 UTF-8 解码器读取整个文件，遇到 binary 数据（如 BLOB）会报 UnicodeDecodeError。

验证配置生效

-- 1. 确认 wait_timeout 已生效（必须用新连接查）
SELECT @@global.wait_timeout, @@session.wait_timeout;
-- 应该都是 60

-- 2. 确认所有 timeout 相关配置
SHOW VARIABLES LIKE '%timeout';

-- 3. 确认当前连接数
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Max_used_connections';

-- 4. 查看活跃连接
SHOW PROCESSLIST;

验证容器中配置文件读取路径：

podman exec <container-name> mariadbd --verbose --help | grep -A 10 "Default options"

清理残留的僵尸连接：

-- 生成 KILL 语句（杀掉 Sleep 超过 60 秒的连接）
SELECT CONCAT('KILL ', id, ';')
FROM information_schema.processlist
WHERE COMMAND = 'Sleep' AND TIME > 60;

MySQL 8.0 → MariaDB 兼容性速查

MariaDB 10.3 对标 MySQL 5.7，从 MySQL 8.0 迁移属于降级迁移：

建议：如果可能，升级 MariaDB 到 10.6+ / 10.11+，兼容性大幅提升。

最终的容器启动命令模板

podman run -d --name <container-name> -p 3306:3306 \
  -e MYSQL_ROOT_PASSWORD=<your-password> \
  -v ~/mariadb-data:/var/lib/mysql \
  -v ~/mariadb-conf:/etc/mysql/conf.d \
  mariadb:<version>

~/mariadb-conf/custom.cnf：

[mysqld]
wait_timeout = 60
interactive_timeout = 60
max_connections = 200

以后重建容器只需 podman rm + podman run，数据和配置都在宿主机，不会丢失。