★ FEATURED ARTICLE 1. 问题现象与根因定位1.1 那个让人抓狂的报错到底长什么样先还原一下现场。你写了一个常驻的Python服务用PyMySQL连MySQL可能是定时任务、可能是Flask/Django的后台worker、也可能是个消息队列的消费者。服务刚启动时一切正常查询飞快日志干净。然后你去吃了个午饭或者把它挂在服务器上跑了一夜第二天回来一看日志满屏都是这个pymysql.err.OperationalError: (2006, MySQL server has gone away)或者偶尔是另一个变种pymysql.err.OperationalError: (2013, Lost connection to MySQL server during query)这两个错误码是PyMySQL用户最常撞上的两堵墙。2006的意思是“服务器已经断开”2013的意思是“查询过程中连接丢了”。它们指向的是同一类问题你的连接对象还在Python进程里活着但它对应的那条MySQL服务端连接已经死了。新手遇到这个问题的第一反应通常是“MySQL是不是不稳定”然后去重启数据库、去调大内存、去怀疑网络。折腾一圈发现没用因为根因根本不在MySQL服务端而在于连接的生命周期管理。1.2 为什么连接会“莫名其妙”断掉要理解这件事得先搞清楚一条MySQL连接从建立到消亡中间会经历哪些“杀手”。我把常见的几类原因列出来你可以对照自己的场景排查断连原因触发条件典型错误码服务端空闲超时连接空闲超过wait_timeout默认28800秒即8小时2006服务端主动KILLDBA或监控脚本清理长时间空闲连接2006 / 2013网络中间设备回收防火墙、负载均衡器、NAT网关的空闲连接回收2006 / 2013查询包过大单次发送超过max_allowed_packet2006服务端重启或崩溃数据库维护、OOM被杀2006 / 2013TCP连接半开客户端以为连着服务端早已关闭2013其中最常见的就是第一条空闲超时。MySQL服务端有个参数叫wait_timeout默认值通常是28800秒。一条连接如果在这段时间内没有任何交互服务端就会单方面把它关掉。注意是“单方面”——服务端关连接的时候不会通知客户端它只是默默地把这条连接从自己的连接池里摘掉。而你的Python进程对此一无所知。PyMySQL的Connection对象依然持有那个socket文件描述符conn.open属性可能还是True。直到你下一次执行cursor.execute()数据包发出去才发现对面早就没人了于是抛出2006。这里有个很多人忽略的细节wait_timeout是服务端参数但客户端也有一个对应的interactive_timeout。对于交互式连接比如你在MySQL命令行里敲用的是interactive_timeout对于非交互式连接比如PyMySQL这种程序连接用的是wait_timeout。两个参数默认都是28800但它们是独立的改一个不一定影响另一个。1.3 为什么“过段时间”这个描述如此精准你注意到没有几乎所有遇到这个问题的人描述都是“过段时间就断”。这个“过段时间”不是随机的它高度吻合wait_timeout的数值。如果你的服务是低频访问的——比如每10分钟跑一次定时任务或者用户量很小、请求间隔很长——那么连接在两次使用之间的空闲时间很容易超过阈值。反过来如果你的服务QPS很高连接一直在被复用反而不容易撞上这个问题。这就解释了为什么很多人在开发环境测不出来一上生产、一挂夜就出问题。还有一种情况是“过段时间”但时间不固定。这通常意味着断连不是由wait_timeout引起的而是网络中间设备在作祟。比如某些云环境的负载均衡器会把空闲超过60秒的连接回收掉这种就更隐蔽因为60秒在开发时很容易被忽略。排查第一步先确认你的wait_timeout到底是多少。执行SHOW VARIABLES LIKE %timeout%;重点看wait_timeout和interactive_timeout。如果这个值是28800而你的服务空闲间隔确实可能超过这个数那基本可以锁定原因了。2. 五种解决方案的取舍与实操知道了根因解决思路就清晰了要么让连接别空闲那么久要么在连接失效时能自动恢复要么干脆别复用长连接。下面我把实践中验证过的几种方案逐一拆开讲每种都给出适用场景和具体代码。2.1 方案一调大wait_timeout治标不治本最直接的办法是去MySQL服务端把wait_timeout调大。修改方式有两种-- 临时生效重启后失效 SET GLOBAL wait_timeout 604800; SET GLOBAL interactive_timeout 604800;或者改配置文件my.cnf[mysqld] wait_timeout 604800 interactive_timeout 604800604800秒是7天。这样调完之后连接空闲一周才会被回收大部分场景下就不会再撞上2006了。但我必须说清楚这个方案的局限它只是把问题推迟了没有解决。只要你的服务运行时间足够长、空闲间隔足够大总有一天还是会断。而且调大wait_timeout有副作用——服务端会积累大量空闲连接占用连接数配额。MySQL的max_connections默认才151如果每个客户端都挂着几条不释放的长连接很容易把连接数打满导致新连接被拒绝。所以这个方案我只推荐一种场景你的服务生命周期短、连接数少、且不方便改代码。比如一个跑几分钟就结束的脚本把timeout调大确实能省事。但如果是常驻服务请往下看。2.2 方案二使用连接池并配置健康检查这是生产环境最推荐的方案。连接池的核心思想是不直接持有连接而是从池子里借用完还回去池子负责检测连接是否还活着死的就丢掉重建。Python里常用的MySQL连接池有DBUtils的PooledDB以及SQLAlchemy自带的连接池。先看DBUtils的用法from dbutils.pooled_db import PooledDB import pymysql POOL PooledDB( creatorpymysql, maxconnections10, mincached2, maxcached5, blockingTrue, ping1, host127.0.0.1, port3306, userapp_user, passwordyour_password, databaseapp_db, charsetutf8mb4, ) def query_user(user_id): conn POOL.connection() try: with conn.cursor() as cursor: cursor.execute(SELECT id, name FROM users WHERE id %s, (user_id,)) return cursor.fetchone() finally: conn.close()这里的关键参数是ping1。它的含义是每次从池子里取连接时先ping一下服务端如果连接已死就自动重连。ping可以取0、1、2、4、7数字是位掩码组合ping1表示“取连接时检查”ping2表示“归还连接时检查”ping4表示“创建游标时检查”。一般设ping1就够了。DBUtils的ping底层调用的就是PyMySQL的Connection.ping()方法它会尝试发送一个轻量级的COM_PING包。如果服务端已经关闭了连接ping会失败池子就会把这个连接销毁并新建一条。再看SQLAlchemy的方案如果你用的是ORM这个更自然from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker engine create_engine( mysqlpymysql://app_user:your_password127.0.0.1:3306/app_db?charsetutf8mb4, pool_size10, max_overflow5, pool_recycle3600, pool_pre_pingTrue, ) Session sessionmaker(bindengine)两个参数是重点pool_pre_pingTrue每次从池子取连接前先ping等价于DBUtils的ping1。pool_recycle3600连接在池子里存活超过3600秒就强制回收重建。这个参数是主动预防比ping更彻底——ping只能发现已经死的连接而recycle能在连接变死之前就把它换掉。pool_recycle的取值有个经验公式设成比wait_timeout小一些。比如wait_timeout是28800那pool_recycle设成7200或3600都很安全。这样连接永远不会活到被服务端回收的年龄。实操心得pool_pre_ping和pool_recycle建议同时开。pre_ping是兜底recycle是预防。只开pre_ping的话每次取连接都要多一次网络往返高频场景下开销不小只开recycle的话如果连接因为网络抖动提前死了recycle还没到时间照样会报错。两个一起用既安全又高效。2.3 方案三手动重试装饰器轻量兜底如果你的项目规模小不想引入连接池那可以写一个重试装饰器在捕获到2006/2013时自动重连并重试。这个方案的好处是零依赖、代码量小坏处是每个查询都要包一层且重试逻辑要自己处理幂等性。import pymysql import functools import time def with_retry(max_retries3, delay1): def decorator(func): functools.wraps(func) def wrapper(self, *args, **kwargs): last_exc None for attempt in range(max_retries): try: return func(self, *args, **kwargs) except pymysql.err.OperationalError as e: code e.args[0] if code in (2006, 2013): last_exc e self._reconnect() time.sleep(delay * (attempt 1)) continue raise raise last_exc return wrapper return decorator class MySQLClient: def __init__(self, **config): self.config config self.conn None self._connect() def _connect(self): self.conn pymysql.connect(**self.config) def _reconnect(self): try: self.conn.close() except Exception: pass self._connect() with_retry(max_retries3, delay1) def execute(self, sql, paramsNone): with self.conn.cursor() as cursor: cursor.execute(sql, params) return cursor.fetchall()这个装饰器的逻辑是捕获OperationalError如果错误码是2006或2013就重连、等待、重试最多重试3次。等待时间用delay * (attempt 1)做退避避免瞬间重试又撞上同样的网络问题。但这里有个必须注意的坑重试只对幂等操作安全。SELECT、UPDATE基于主键、DELETE基于主键这些重试没问题。但如果是INSERT重试可能导致重复插入。所以要么给INSERT加唯一约束要么在重试前判断一下操作类型。2.4 方案四每次操作新建连接最笨但最稳如果你的服务QPS极低——比如一天就跑几次的定时任务——那最简单的办法就是每次操作都新建连接用完就关。这样根本不存在连接空闲的问题因为连接的生命周期就是一次操作的时间。import pymysql from contextlib import contextmanager contextmanager def get_conn(): conn pymysql.connect( host127.0.0.1, port3306, userapp_user, passwordyour_password, databaseapp_db, charsetutf8mb4, connect_timeout5, ) try: yield conn finally: conn.close() def do_something(): with get_conn() as conn: with conn.cursor() as cursor: cursor.execute(SELECT COUNT(*) FROM orders) return cursor.fetchone()这个方案的代价是每次操作都要经历TCP三次握手、MySQL认证握手开销大概在几毫秒到几十毫秒。对于低频场景完全可以接受对于高频场景就是灾难。所以只推荐给QPS低于1的服务。2.5 方案五改用异步驱动或ORM框架如果你用的是aiomysql、asyncmy这类异步驱动它们通常自带连接池和健康检查。以aiomysql为例import aiomysql import asyncio async def main(): pool await aiomysql.create_pool( host127.0.0.1, port3306, userapp_user, passwordyour_password, dbapp_db, minsize2, maxsize10, pool_recycle3600, autocommitTrue, ) async with pool.acquire() as conn: async with conn.cursor() as cursor: await cursor.execute(SELECT 1) result await cursor.fetchone() print(result) pool.close() await pool.wait_closed() asyncio.run(main())aiomysql的pool_recycle参数和SQLAlchemy的语义一致都是主动回收老连接。异步驱动在连接管理上通常比同步驱动更完善因为异步场景下连接空闲更常见社区对这个问题处理得更成熟。3. 参数计算与配置细节3.1 wait_timeout与pool_recycle的配合计算这两个参数的配合是解决断连问题的核心。我给出一个具体的计算过程你可以套用到自己的环境。假设你的MySQL服务端wait_timeout是默认的28800秒8小时。你的服务是常驻worker平均每5分钟处理一次任务但夜间可能几小时没任务。最坏情况下连接空闲时间可能达到夜间无任务的时长比如6小时21600秒。这个值小于28800理论上不会触发服务端超时。但考虑到网络中间设备可能更早回收连接以及服务端可能因为其他原因KILL连接我们不能只依赖wait_timeout。pool_recycle的取值原则是小于wait_timeout且小于网络设备的最短空闲回收时间。如果不知道网络设备的回收时间保守取一个较小的值比如1800秒30分钟。pool_recycle min(wait_timeout, network_idle_timeout) * 0.5按这个公式28800 * 0.5 14400秒4小时。但考虑到网络设备可能60秒就回收我通常直接设成1800或3600牺牲一点连接复用率换取稳定性。3.2 max_allowed_packet导致的断连除了空闲超时还有一个容易被忽略的断连原因单次发送的数据包超过max_allowed_packet。这个参数默认在MySQL 5.7是4MBMySQL 8.0是64MB。如果你一次性插入大量数据或者查询返回超大字段就可能触发2006。排查方法SHOW VARIABLES LIKE max_allowed_packet;如果发现你的数据包接近这个值有两个选择调大服务端参数或者在客户端分批处理。# 分批插入每批1000条 def batch_insert(conn, table, rows, batch_size1000): with conn.cursor() as cursor: for i in range(0, len(rows), batch_size): batch rows[i:i batch_size] placeholders ,.join([%s] * len(batch[0])) sql fINSERT INTO {table} VALUES ({placeholders}) cursor.executemany(sql, batch) conn.commit()3.3 连接池大小的估算连接池不是越大越好。池子太大服务端连接数吃紧池子太小高并发时请求排队。一个经验公式是pool_size 平均QPS * 平均查询耗时(秒) * 冗余系数假设你的服务平均QPS是50单次查询平均耗时20ms0.02秒冗余系数取2pool_size 50 * 0.02 * 2 2也就是说理论上2个连接就够了。但实际中要考虑峰值和慢查询通常会设成max(理论值, 5)。我一般起步设10然后根据监控调整。注意MySQL服务端的max_connections是所有客户端共享的。如果你的应用有多个实例每个实例的池子大小要乘以实例数总和不能超过max_connections的80%留20%给管理连接和其他服务。4. 常见问题排查速查表4.1 错误码对照与快速定位错误码含义首要排查方向2006MySQL server has gone away空闲超时、包过大、服务端KILL2013Lost connection during query网络中断、查询超时、服务端崩溃2003Cant connect to MySQL server网络不通、端口错误、服务未启动1045Access denied用户名密码错误、权限不足1040Too many connections连接数打满检查池子大小和泄漏1205Lock wait timeout锁竞争与断连无关但常被混淆4.2 排查步骤清单遇到断连问题按这个顺序排查基本能覆盖90%的情况确认错误码2006还是2013前者偏空闲超时后者偏网络问题。查wait_timeoutSHOW VARIABLES LIKE wait_timeout;看是否小于你的空闲间隔。查连接空闲时间在代码里记录每次取连接和归还连接的时间戳算出实际空闲时长。查网络设备如果服务部署在云环境确认负载均衡、NAT的超时配置。查max_allowed_packet如果断连发生在写入大数据的瞬间重点查这个。查服务端日志MySQL的error log里可能有KILL连接的记录。加监控在重连逻辑里打日志统计断连频率和时段找出规律。4.3 几个反直觉的坑坑一conn.open不可靠。PyMySQL的Connection.open属性只反映客户端状态服务端关了连接它可能还是True。判断连接是否可用要用conn.ping(reconnectFalse)它会实际发一个包失败就抛异常。坑二autocommitFalse时长连接会积累事务。如果你开了事务但忘了commit连接会一直持有锁服务端可能因为锁等待超时把它KILL掉。长连接场景下建议开autocommitTrue需要事务时显式begin。坑三重连后session变量丢失。如果你在连接建立后设置了SET time_zone、SET names之类的session变量重连后这些设置会丢失。解决办法是在_reconnect之后重新执行这些初始化语句或者用连接池的init_command参数。POOL PooledDB( creatorpymysql, ping1, init_commandSET time_zone08:00, # ... 其他参数 )坑四多线程共享连接。PyMySQL的Connection对象不是线程安全的。如果你在多线程环境里共享一个连接会出现各种诡异问题包括断连。正确做法是每个线程从池子里取自己的连接或者用threading.local()做线程隔离。5. 生产环境的完整落地方案5.1 一个可直接抄的配置模板把前面所有要点整合起来这是一个我用了很久的配置模板适配大部分中小规模的生产场景import pymysql from dbutils.pooled_db import PooledDB DB_CONFIG { host: 127.0.0.1, port: 3306, user: app_user, password: your_password, database: app_db, charset: utf8mb4, connect_timeout: 5, read_timeout: 30, write_timeout: 30, autocommit: True, } POOL PooledDB( creatorpymysql, maxconnections20, mincached3, maxcached10, maxshared0, blockingTrue, maxusageNone, setsession[SET time_zone08:00], ping1, **DB_CONFIG ) def get_connection(): return POOL.connection()几个参数的解释maxconnections20池子上限根据你的QPS和实例数调整。mincached3启动时预建3条连接避免首次请求时的建连延迟。maxcached10空闲连接最多保留10条超出的会被关闭释放。blockingTrue池子满了之后取连接的请求会阻塞等待而不是直接报错。setsession每次新建连接时执行的初始化语句解决重连后session变量丢失的问题。ping1取连接时检查健康状态。5.2 监控与告警的埋点光解决问题还不够你得知道问题有没有再发生。在重连逻辑里加埋点import logging import time logger logging.getLogger(__name__) class MonitoredPool: def __init__(self, pool): self.pool pool self.reconnect_count 0 self.last_reconnect_time None def connection(self): conn self.pool.connection() # 包装一层记录重连事件 original_ping conn.ping def monitored_ping(reconnectTrue): try: return original_ping(reconnectreconnect) except Exception: self.reconnect_count 1 self.last_reconnect_time time.time() logger.warning(Connection lost, reconnecting. Total: %d, self.reconnect_count) raise conn.ping monitored_ping return conn把reconnect_count暴露给监控系统Prometheus、StatsD都行设个告警阈值。如果一天内重连次数超过某个值说明你的pool_recycle设得太大或者网络确实不稳定需要进一步排查。5.3 不同场景的方案选择建议最后给一个决策表帮你快速选方案场景推荐方案理由常驻服务QPS 10连接池 pool_recycle pre_ping兼顾性能和稳定常驻服务QPS 1连接池 大pool_recycle低频场景重连开销可忽略定时脚本跑完即退每次新建连接无空闲问题代码简单异步服务aiomysql/asyncmy自带池异步生态的标准做法临时排查调大wait_timeout快速止血但不推荐长期用我个人在实际操作中的体会是连接池 pool_recycle pre_ping 这个组合能解决95%以上的断连问题。剩下的5%通常是网络设备或服务端配置的极端情况需要结合监控和日志具体分析。另外别迷信“调大wait_timeout”这个方案它只是把炸弹的引信拉长该炸还是会炸。真正靠谱的做法是让客户端具备自愈能力连接死了能自动重建而不是祈祷它别死。
阅读完成 · 觉得有帮助？
