使用mysql-connector-python连接MySQL遇2003等连接错误求助

场景1：Windows凭据连接DDB_A出现10061拒绝连接错误 #

问题复现 #

执行以下代码时触发错误：

mydb = mysql.connector.connect(
    user="user_windows",
    password="password_windows",
    database="DDB_A"
)

错误信息：

ConnectionRefusedError: [WinError 10061] No connection could be made because the target machine actively refused it
InterfaceError: 2003: Can't connect to MySQL server on '127.0.0.1:3306' (10061 No connection could be made because the target machine actively refused it)

排查解决步骤 #

• 补全正确的主机地址：Excel能正常连接说明目标数据库不在本地127.0.0.1，打开Excel的数据库连接属性，找到实际的服务器IP/主机名，添加到代码的host参数中：

  mydb = mysql.connector.connect(
      host="实际服务器IP或主机名",
      user="user_windows",
      password="password_windows",
      database="DDB_A"
  )
  

• 启用Windows集成身份验证支持：mysql-connector-python默认不支持Windows域凭据验证，需安装扩展版并指定认证插件：
  1. 安装支持插件的连接器：pip install mysql-connector-python-cext
  2. 修改连接代码：

     mydb = mysql.connector.connect(
         host="实际服务器IP或主机名",
         user="user_windows",
         password="password_windows",
         database="DDB_A",
         auth_plugin='authentication_windows_client'
     )
     

• 检查防火墙权限：确认Python.exe或你的开发IDE（如VS Code、PyCharm）被允许通过Windows防火墙访问网络，在「允许应用通过防火墙」列表中勾选对应程序的网络权限。

场景2：专用凭据连接DDB_B出现10060超时错误 #

问题复现 #

执行以下代码时触发错误：

mydb = mysql.connector.connect(
    host="X.X.X.X",
    user="user_database",
    password="password_database",
    database="DDB_B"
)

错误信息：

TimeoutError: [WinError 10060] A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond
InterfaceError: 2003: Can't connect to MySQL server on 'X.X.X.X:3306' (10060 A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond)

排查解决步骤 #

• 核对数据库端口：Excel可能使用了非默认的3306端口，查看Excel连接配置中的端口号，添加到代码的port参数：

  mydb = mysql.connector.connect(
      host="X.X.X.X",
      port="实际端口号",
      user="user_database",
      password="password_database",
      database="DDB_B"
  )
  

• 配置代理：若Excel依赖系统代理连接，需为Python脚本配置代理：
  1. 代码中直接指定代理：

     mydb = mysql.connector.connect(
         host="X.X.X.X",
         user="user_database",
         password="password_database",
         database="DDB_B",
         proxy_host="代理服务器IP",
         proxy_port=代理端口号
     )
     

  2. 或在运行脚本前设置环境变量（Windows CMD）：

     set HTTP_PROXY=http://代理IP:端口
     set HTTPS_PROXY=http://代理IP:端口
     

• 添加SSL配置：如果Excel连接启用了SSL加密，Python代码需补充SSL参数：

  mydb = mysql.connector.connect(
      host="X.X.X.X",
      user="user_database",
      password="password_database",
      database="DDB_B",
      ssl_ca="ca证书文件路径.pem",
      ssl_disabled=False
  )
  

• 验证防火墙权限：确认Python进程被允许访问外部网络，在Windows防火墙设置中开启对应程序的网络访问权限。

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