使用Paramiko连接MySQL失败，sshtunnel可正常连接

原本使用sshtunnel可正常连接PythonAnywhere的MySQL数据库，但由于grequests与sshtunnel存在冲突，改用兼容gevent monkey补丁的Paramiko库后，SSH隧道可正常建立，但MySQL连接报错：
An error occurred: 1045 (28000): Access denied for user 'PA_username'@'localhost' (using password: YES)

环境变量未发生变化，且无grequests时的sshtunnel版本代码可正常连接，Paramiko版本却无法成功。以下是两段代码及排查修复方案：

Paramiko版本问题代码 #

from gevent import monkey

monkey.patch_all()

import grequests
import mysql.connector
import paramiko
from pprint import pprint
import os

ssh_password = os.environ.get("PA_SSH_PASS")
db_password = os.environ.get("PA_DB_PASS")
database = os.environ.get("PA_DB_NAME")

def get_current_listing_urls():
    try:
        ssh_client = paramiko.SSHClient()
        ssh_client.load_system_host_keys()
        ssh_client.set_missing_host_key_policy(paramiko.WarningPolicy)

        ssh_client.connect(
            "ssh.eu.pythonanywhere.com",
            username=PA_username,
            password=ssh_password,
        )

        transport = ssh_client.get_transport()

        channel = transport.open_channel(
            "direct-tcpip",
            dest_addr=("PA_username.mysql.eu.pythonanywhere-services.com", 3306),
            src_addr=("127.0.0.1", 3306),
        )

        db = mysql.connector.connect(
            user="PA_username",
            password=db_password,
            host="127.0.0.1",
            port=3306,
            database=database,
            use_pure=True,
        )

        table_name = "listings"

        cursor = db.cursor(dictionary=True)

        query = f"SELECT COUNT(*) FROM {table_name};"

        cursor.execute(query)

        results = cursor.fetchall()

        return results

    except Exception as e:
        print(f"An error occurred: {str(e)}")
    finally:
        if "cursor" in locals() and cursor:
            cursor.close()
        if "db" in locals() and db:
            db.close()
        if "ssh_client" in locals() and ssh_client:
            ssh_client.close()


old_listing_urls = get_current_listing_urls()

pprint(old_listing_urls)

原sshtunnel工作代码 #

import mysql.connector
import sshtunnel
from pprint import pprint
import time
import os
import json

sshtunnel.SSH_TIMEOUT = 5.0
sshtunnel.TUNNEL_TIMEOUT = 5.0

ssh_password = os.environ.get("PA_SSH_PASS")
db_password = os.environ.get("PA_DB_PASS")
database = os.environ.get("PA_DB_NAME")


def get_current_listing_urls():
    try:
        with sshtunnel.SSHTunnelForwarder(
            ("ssh.eu.pythonanywhere.com"),
            ssh_username=PA_username,
            ssh_password=ssh_password,
            remote_bind_address=(
                "PA_username.mysql.eu.pythonanywhere-services.com",
                3306,
            ),
            threaded=False,
        ) as tunnel:
            db = mysql.connector.connect(
                user=PA_username,
                password=db_password,
                host="127.0.0.1",
                port=tunnel.local_bind_port,
                database=database,
                use_pure=True,
            )

            table_name = "listings"

            cursor = db.cursor(dictionary=True)

            query = f"SELECT COUNT(*)  FROM {table_name};"  

            cursor.execute(query)

            results = cursor.fetchall()

            return results

    except Exception as e:
        print(f"An error occurred: {str(e)}")
    finally:
        if "cursor" in locals() and cursor:
            cursor.close()
        if "db" in locals() and db:
            db.close()
        if "tunnel" in locals() and tunnel:
            tunnel.close()


old_listing_urls = get_current_listing_urls()

pprint(old_listing_urls)

问题根源 #

你用Paramiko手动创建了SSH隧道的channel，但没有让MySQL连接器实际使用这个channel进行通信，而是直接尝试连接本地3306端口（如果本地没有MySQL服务，可能根本没通；即使通了，PythonAnywhere的MySQL也不允许直接从localhost的非转发端口访问，导致权限报错）。而sshtunnel是自动将本地某个端口转发到远程MySQL，所以连接器只需连接这个本地转发端口即可正常通信。

修复方案 #

1. 让MySQL连接器使用Paramiko的channel作为通信套接字 #

mysql.connector的connect方法支持sock参数，可接收类套接字对象（比如Paramiko的Channel），直接通过这个channel和远程MySQL通信，无需绑定本地端口。

2. 修正Paramiko隧道的src_addr设置 #

不需要指定src_addr=("127.0.0.1", 3306)，改为("0.0.0.0", 0)让系统自动分配本地端口（或者直接省略，Paramiko会自动处理），因为我们不需要通过本地端口访问，而是直接用channel通信。

修正后的Paramiko代码 #

from gevent import monkey
monkey.patch_all()

import grequests
import mysql.connector
import paramiko
from pprint import pprint
import os

# 建议从环境变量读取用户名，避免硬编码
PA_username = os.environ.get("PA_USERNAME")
ssh_password = os.environ.get("PA_SSH_PASS")
db_password = os.environ.get("PA_DB_PASS")
database = os.environ.get("PA_DB_NAME")

def get_current_listing_urls():
    try:
        ssh_client = paramiko.SSHClient()
        ssh_client.load_system_host_keys()
        ssh_client.set_missing_host_key_policy(paramiko.WarningPolicy)

        ssh_client.connect(
            "ssh.eu.pythonanywhere.com",
            username=PA_username,
            password=ssh_password,
        )

        transport = ssh_client.get_transport()
        # 创建direct-tcpip通道，src_addr使用自动分配的端口
        channel = transport.open_channel(
            "direct-tcpip",
            dest_addr=(f"{PA_username}.mysql.eu.pythonanywhere-services.com", 3306),
            src_addr=("0.0.0.0", 0),
        )

        # 关键：将Paramiko的channel传入mysql.connector作为通信套接字
        db = mysql.connector.connect(
            user=PA_username,
            password=db_password,
            host="127.0.0.1",  # 此处host不生效，仅为兼容性保留
            database=database,
            use_pure=True,
            sock=channel,
        )

        table_name = "listings"
        cursor = db.cursor(dictionary=True)
        query = f"SELECT COUNT(*) FROM {table_name};"
        cursor.execute(query)
        results = cursor.fetchall()
        return results

    except Exception as e:
        print(f"An error occurred: {str(e)}")
    finally:
        # 新增关闭channel的逻辑，避免资源泄漏
        if "cursor" in locals() and cursor:
            cursor.close()
        if "db" in locals() and db:
            db.close()
        if "channel" in locals() and channel:
            channel.close()
        if "ssh_client" in locals() and ssh_client:
            ssh_client.close()


old_listing_urls = get_current_listing_urls()
pprint(old_listing_urls)

额外注意事项 #

• 确保PA_username变量正确赋值，建议从环境变量读取，避免硬编码
• 关闭资源时需新增channel的关闭步骤，防止资源泄漏
• 由于使用了sock参数，host和port参数不会实际生效，可保留默认值或留空

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