企业网络环境下Python连接MySQL 8.0失败问题求助

环境与尝试过程 #

我在企业网络的Windows环境下尝试访问全新的MySQL 8.0服务器，使用Python 3.9.13，网络仅开放1600端口（非MySQL默认端口）。

尝试1：使用pymysql连接 #

代码：

import pymysql

def test_mysql_connection(host, port, username, password):
    try:
        # 建立MySQL连接
        connection = pymysql.connect(
            host=host,
            port=port,
            user=username,
            password=password
        )

        print("成功连接到MySQL数据库！")
        connection.close()
    except pymysql.Error as err:
        print(f"错误: {err}")
    except Exception as e:
        print(f"发生未知错误: {e}")

# 参数配置
ip = "ip"
port = 1600
username = "admin"
password = "pass"

# 测试连接
test_mysql_connection(ip, port, username, password)

执行结果：

Error: (2013, 'Lost connection to MySQL server during query ([WinError 10054] An existing connection was forcibly closed by the remote host)')

尝试2：使用sqlalchemy连接 #

代码：

from sqlalchemy import create_engine
from sqlalchemy.exc import OperationalError

db_username = "admin"
db_password = "pass"
db_server = "ip,1600"  # 加入端口1600
db_name = "db"
driver = "ODBC Driver 18 for SQL Server"

# 连接字符串
connection_string = f"mssql+pyodbc://{db_username}:{db_password}@{db_server}/{db_name}?driver={driver}"

try:
    engine = create_engine(connection_string)
    engine.connect()
    print("连接成功！")
except OperationalError as e:
    print("数据库连接错误:", e)

执行结果：

Error connecting to the database: (pyodbc.OperationalError) ('08001', '[08001] [Microsoft][ODBC Driver 18 for SQL Server]TCP Provider: Timeout error [258].  (258) (SQLDriverConnect); [08001] [Microsoft][ODBC Driver 18 for SQL Server]Login timeout expired (0); [08001] [Microsoft][ODBC Driver 18 for SQL Server]Unable to complete login process due to delay in prelogin response (258)')

已完成的排查操作 #

• MySQL服务器已配置为监听1600端口
• 客户端和服务器防火墙（含硬件防火墙）均已开放1600端口
• 已安装ODBC Driver 18.0
• 延长了超时和保活时间
• 扩大了查询行数限制，但以上操作均无效

特殊情况 #

使用MySQL Workbench远程访问时，无论在客户端还是服务器PC都能正常连接，可创建表、写入数据、执行查询等。

更新记录 #

更新1 #

根据建议将sqlalchemy代码中的驱动改为MySQL ODBC 8.1 Unicode Driver，出现新错误：

sqlalchemy.exc.DBAPIError: (pyodbc.Error) ('HY000', '[HY000] [MySQL][ODBC 8.1(w) Driver]Missing option MULTI_HOST=1 (0) (SQLDriverConnect); [HY000] [MySQL][ODBC 8.1(w) Driver]Missing option MULTI_HOST=1 (0)')

更新2 #

在连接串中添加MULTI_HOST=1参数后，出现新错误：
连接串代码：

connection_string = f"mssql+pyodbc://{db_username}:{db_password}@{db_server}/{db_name}?driver={driver}&MULTI_HOST=1"

执行结果：

pyodbc.Error: ('HY000', "[HY000] [MySQL][ODBC 8.1(w) Driver]Unknown MySQL server host '1600' (11001) (2005) (SQLDriverConnect); [HY000] [MySQL][ODBC 8.1(w) Driver]Unknown MySQL server host '1600' (11001) (2005)")

The above exception was the direct cause of the following exception:

sqlalchemy.exc.DBAPIError: (pyodbc.Error) ('HY000', "[HY000] [MySQL][ODBC 8.1(w) Driver]Unknown MySQL server host '1600' (11001) (2005) (SQLDriverConnect); [HY000] [MySQL][ODBC 8.1(w) Driver]Unknown MySQL server host '1600' (11001) (2005)")

更新3 #

将连接串中的mssql+pyodbc://改为mysql+pymysql://后，出现新错误：

Traceback (most recent call last):
  File "c:\Users\gjykz5\Documents\main.py", line 16, in <module>
    engine.connect()
  File "C:\Users\gjykz5\AppData\Local\Programs\Python\Python39\lib\site-packages\sqlalchemy\engine\base.py", line 3315, in connect
    return self._connection_cls(self, close_with_result=close_with_result)
  File "C:\Users\gjykz5\AppData\Local\Programs\Python\Python39\lib\site-packages\sqlalchemy\engine\base.py", line 96, in __init__
    else engine.raw_connection()
  File "C:\Users\gjykz5\AppData\Local\Programs\Python\Python39\lib\site-packages\sqlalchemy\engine\base.py", line 3394, in raw_connection
    return self._wrap_pool_connect(self.pool.connect, _connection)
  File "C:\Users\gjykz5\AppData\Local\Programs\Python\Python39\lib\site-packages\sqlalchemy\engine\base.py", line 3361, in _wrap_pool_connect
    return fn()
  File "C:\Users\gjykz5\AppData\Local\Programs\Python\Python39\lib\site-packages\sqlalchemy\pool\base.py", line 327, in connect
    return _ConnectionFairy._checkout(self)
  File "C:\Users\gjykz5\AppData\Local\Programs\Python\Python39\lib\site-packages\sqlalchemy\pool\base.py", line 894, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "C:\Users\gjykz5\AppData\Local\Programs\Python\Python39\lib\site-packages\sqlalchemy\pool\base.py", line 493, in checkout
    rec = pool._do_get()
  File "C:\Users\gjykz5\AppData\Local\Programs\Python\Python39\lib\site-packages\sqlalchemy\pool\impl.py", line 146, in _do_get
    self._dec_overflow()
  File "C:\Users\gjykz5\AppData\Local\Programs\Python\Python39\lib\site-packages\sqlalchemy\util\langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "C:\Users\gjykz5\AppData\Local\Programs\Python\Python39\lib\site-packages\sqlalchemy\util\compat.py", line 211, in raise_
    raise exception
  File "C:\Users\gjykz5\AppData\Local\Programs\Python\Python39\lib\site-packages\sqlalchemy\pool\impl.py", line 143, in _do_get
    return self._create_connection()
  File "C:\Users\gjykz5\AppData\Local\Programs\Python\Python39\lib\site-packages\sqlalchemy\pool\base.py", line 273, in _create_connection
    return _ConnectionRecord(self)
  File "C:\Users\gjykz5\AppData\Local\Programs\Python\Python39\lib\site-packages\sqlalchemy\pool\base.py", line 388, in __init__
    self.__connect()
  File "C:\Users\gjykz5\AppData\Local\Programs\Python\Python39\lib\site-packages\sqlalchemy\pool\base.py", line 691, in __connect
    pool.logger.debug("Error on connect(): %s", e)
  File "C:\Users\gjykz5\AppData\Local\Programs\Python\Python39\lib\site-packages\sqlalchemy\util\langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "C:\Users\gjykz5\AppData\Local\Programs\Python\Python39\lib\site-packages\sqlalchemy\util\compat.py", line 211, in raise_
    raise exception
  File "C:\Users\gjykz5\AppData\Local\Programs\Python\Python39\lib\site-packages\sqlalchemy\pool\base.py", line 686, in __connect
    self.dbapi_connection = connection = pool._invoke_creator(self)
  File "C:\Users\gjykz5\AppData\Local\Programs\Python\Python39\lib\site-packages\sqlalchemy\engine\create.py", line 574, in connect
    return dialect.connect(*cargs, **cparams)
  File "C:\Users\gjykz5\AppData\Local\Programs\Python\Python39\lib\site-packages\sqlalchemy\engine\default.py", line 598, in connect
    return self.dbapi.connect(*cargs, **cparams)
TypeError: __init__() got an unexpected keyword argument 'driver'

解决建议 #

1. 修正sqlalchemy连接串的基础错误

   • 连接串开头不能用mssql+pyodbc://（这是SQL Server的协议），MySQL的正确协议格式：
     • 用pymysql时：mysql+pymysql://用户名:密码@主机IP:端口/数据库名，不要加driver参数（pymysql无需ODBC驱动）
     • 用ODBC时：mysql+pyodbc://用户名:密码@主机IP:端口/数据库名?driver=MySQL+ODBC+8.1+Unicode+Driver，注意端口用冒号跟在IP后，不是逗号
       示例（pymysql版）：

   connection_string = f"mysql+pymysql://{db_username}:{db_password}@{ip}:{port}/{db_name}"
   

2. 解决pymysql连接失败的核心问题
   Workbench能连接但pymysql不行，大概率是MySQL 8.0的认证协议兼容性问题：

   • 升级pymysql到最新兼容版本：pip install --upgrade pymysql
   • 在pymysql连接时显式指定认证插件：

     connection = pymysql.connect(
         host=host,
         port=port,
         user=username,
         password=password,
         auth_plugin='mysql_native_password'
     )
     

   • 或在MySQL服务器端修改用户认证方式：

     ALTER USER 'admin'@'%' IDENTIFIED WITH mysql_native_password BY 'pass';
     FLUSH PRIVILEGES;
     

3. 企业网络额外排查点

   • 检查是否有代理或深度包检测（DPI）设备拦截了Python发起的连接请求（Workbench流量可能被特殊放行）
   • 用telnet测试端口连通性，确认网络层可达：

     telnet ip 1600
     

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