Python连接MySQL报Unknown server host(11001)：求解析与解决方案

问题代码 #

import mysql.connector
from mysql.connector
import connection
conn = connection.MySQLConnection(
           host='*****', username='****', password='*****',
           database='****', port=3306)

错误栈信息 #

PS C:\Users\InfoASB-McMConstruct\OneDrive - ASB-McM UK Construction\Desktop\Anjali\Scripts>  & 'C:\Users\InfoASB-McMConstruct\AppData\Local\Programs\Python\Python311\python.exe' 'c:\Users\InfoASB-McMConstruct\.vscode\extensions\ms-python.python-2023.6.1\pythonFiles\lib\python\debugpy\adapter/../..\debugpy\launcher' '53639' '--' 'C:\Users\InfoASB-McMConstruct\OneDrive - ASB-McM UK Construction\Desktop\Anjali\Scripts\fetchData.py'
Traceback (most recent call last):
  File "C:\Users\InfoASB-McMConstruct\AppData\Local\Programs\Python\Python311\Lib\site-packages\mysql\connector\connection_cext.py", line 291, in _open_connection
    self._cmysql.connect(**cnx_kwargs)
_mysql_connector.MySQLInterfaceError: Unknown MySQL server host 'db5012717913.hosting-data.io' (11001)

后续引发的异常：

Traceback (most recent call last):
  File "C:\Users\InfoASB-McMConstruct\AppData\Local\Programs\Python\Python311\Lib\runpy.py", line 198, in _run_module_as_main
    return _run_code(code, main_globals, None,
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\InfoASB-McMConstruct\AppData\Local\Programs\Python\Python311\Lib\runpy.py", line 88, in _run_code
    exec(code, run_globals)
  File "c:\Users\InfoASB-McMConstruct\.vscode\extensions\ms-python.python-2023.6.1\pythonFiles\lib\python\debugpy\adapter/../..\debugpy\launcher/../..\debugpy\__main__.py", line 39, in <module>
    cli.main()
  File "c:\Users\InfoASB-McMConstruct\.vscode\extensions\ms-python.python-2023.6.1\pythonFiles\lib\python\debugpy\adapter/../..\debugpy\launcher/../..\debugpy/..\debugpy\server\cli.py", line 430, in main     
    run()
  File "c:\Users\InfoASB-McMConstruct\.vscode\extensions\ms-python.python-2023.6.1\pythonFiles\lib\python\debugpy\adapter/../..\debugpy\launcher/../..\debugpy/..\debugpy\server\cli.py", line 284, in run_file 
    runpy.run_path(target, run_name="__main__")
  File "c:\Users\InfoASB-McMConstruct\.vscode\extensions\ms-python.python-2023.6.1\pythonFiles\lib\python\debugpy\_vendored\pydevd\_pydevd_bundle\pydevd_runpy.py", line 321, in run_path
    return _run_module_code(code, init_globals, run_name,
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "c:\Users\InfoASB-McMConstruct\.vscode\extensions\ms-python.python-2023.6.1\pythonFiles\lib\python\debugpy\_vendored\pydevd\_pydevd_bundle\pydevd_runpy.py", line 135, in _run_module_code
    _run_code(code, mod_globals, init_globals,
  File "c:\Users\InfoASB-McMConstruct\.vscode\extensions\ms-python.python-2023.6.1\pythonFiles\lib\python\debugpy\_vendored\pydevd\_pydevd_bundle\pydevd_runpy.py", line 124, in _run_code
    exec(code, run_globals)
  File "C:\Users\InfoASB-McMConstruct\OneDrive - ASB-McM UK Construction\Desktop\Anjali\Scripts\fetchData.py", line 9, in <module>
    conn = mysql.connector.connect(host='db5012717913.hosting-data.io',
    self.connect(**kwargs)
  File "C:\Users\InfoASB-McMConstruct\AppData\Local\Programs\Python\Python311\Lib\site-packages\mysql\connector\abstracts.py", line 1181, in connectb\site-packages\mysql\connector\abstracts.py", line 1181, in connect
    self._open_connection()                                                     b\site-packages\mysql\connector\connection_cext.py", line 296, in _open_connection
  File "C:\Users\InfoASB-McMConstruct\AppData\Local\Programs\Python\Python311\Lion
    raise get_mysql_exception(
mysql.connector.errors.DatabaseError: 2005 (HY000): Unknown MySQL server host 'db5012717913.hosting-data.io' (11001)

错误解析 #

核心错误是_mysql_connector.MySQLInterfaceError: Unknown MySQL server host 'db5012717913.hosting-data.io' (11001)，后续的DatabaseError是该错误引发的上层异常。这个错误表示系统无法通过DNS解析找到指定的MySQL服务器主机地址，本质是网络层面的主机不可达问题，而非数据库账号权限或代码语法问题。

解决方案 #

• 校验主机地址拼写：仔细核对数据库服务商提供的主机地址db5012717913.hosting-data.io，确认没有拼写错误（比如漏写字符、标点错误）。
• 测试DNS解析能力：在本地命令行执行以下命令，检查是否能解析主机地址：

  nslookup db5012717913.hosting-data.io
  

  或者

  ping db5012717913.hosting-data.io
  

  如果命令返回“无法找到主机”或类似提示，说明本地DNS服务器无法解析该域名，可尝试更换公共DNS服务器（比如8.8.8.8或1.1.1.1）。
• 检查网络访问权限：
  • 确认本地网络可以访问该MySQL服务器所在的网络，比如是否需要连接公司VPN才能访问内网数据库，或者服务器是否设置了IP白名单，需将本地公网IP添加到服务器的访问白名单中。
  • 测试服务器端口是否可达，使用Windows命令：

    Test-NetConnection db5012717913.hosting-data.io -Port 3306
    

• 简化代码避免导入错误：原代码的导入语句存在换行问题，可简化为更简洁的写法，减少潜在的语法问题：

  import mysql.connector
  
  conn = mysql.connector.connect(
      host='db5012717913.hosting-data.io',
      username='你的用户名',
      password='你的密码',
      database='你的数据库名',
      port=3306
  )
  

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