让Python通过SSH隧道连接MySQL

Question

我正在使用Python 2.7和MySqldb来让Python连接到另一个MySQL服务器。

import MySQLdb
db = MySQLdb.connect(host="sql.domain.com",
     user="dev", 
      passwd="*******", 
      db="appdb")

如何使用SSH密钥对通过SSH隧道进行连接，而不是像通常情况下那样进行连接？

最好由Python打开SSH隧道。SSH隧道主机和MySQL服务器是同一台计算机。

- Nyxynyx

---

你有谷歌过吗？使用Python打开SSH隧道：https://dev59.com/ZlLTa4cB1Zd3GeqPaW6C，通过该隧道连接到MySql：https://dev59.com/HnA65IYBdhLWcg3w4C-j。 - mbatchkarov

你可能有使用SSH的充分理由，但如果这是直接连接到MySQL服务器，最好改用SSL。这样可以减少出错的可能性。 - geertjanvdk

@geertjanvdk 这很有趣，为什么SSL会是更好的选择？我正在寻找在客户端和服务器之间建立安全连接的方法，SSH是我首先想到的。 - Nyxynyx

你不会使用SSH连接到安全网站，对吧？那样只会增加复杂性。如果你的MySQL服务器可以直接访问，SSL是首选。而且，SSL可以在任何连接器或操作系统（比如Windows）上使用。使用SSH隧道时，你需要保持它的稳定性，监控等等。 - geertjanvdk

Answer 1

只有这个对我有用

import pymysql
import paramiko
import pandas as pd
from paramiko import SSHClient
from sshtunnel import SSHTunnelForwarder
from os.path import expanduser

home = expanduser('~')
mypkey = paramiko.RSAKey.from_private_key_file(home + pkeyfilepath)
# if you want to use ssh password use - ssh_password='your ssh password', bellow

sql_hostname = 'sql_hostname'
sql_username = 'sql_username'
sql_password = 'sql_password'
sql_main_database = 'db_name'
sql_port = 3306
ssh_host = 'ssh_hostname'
ssh_user = 'ssh_username'
ssh_port = 22
sql_ip = '1.1.1.1.1'

with SSHTunnelForwarder(
        (ssh_host, ssh_port),
        ssh_username=ssh_user,
        ssh_pkey=mypkey,
        remote_bind_address=(sql_hostname, sql_port)) as tunnel:
    conn = pymysql.connect(host='127.0.0.1', user=sql_username,
            passwd=sql_password, db=sql_main_database,
            port=tunnel.local_bind_port)
    query = '''SELECT VERSION();'''
    data = pd.read_sql_query(query, conn)
    conn.close()

- Kathan Shah

---

sql_ip 用于什么？ - OneAdamTwelve

@kathanshah 如果中间有跳板机，该如何使用？它是否允许默认的 .ssh/config 文件设置？ - Sollosa

@OneAdamTwelve，sql_ip 看起来是不必要且未使用的。但这个例子可以工作！（我使用了MySQLdb） - Ben Ogorek

对于任何遇到“xxx.com主机无法连接到此MySQL服务器”的人，这里有一个小的附加注释。您需要在SSHTunnelForwarder()的第一部分中添加local_bind_address = ('127.0.0.1', sql_port)，例如：with SSHTunnelForwarder( (ssh_host, ssh_port), ssh_username=ssh_user, ssh_pkey=mypkey, remote_bind_address=('127.0.0.1', sql_port), local_bind_address = ('127.0.0.1', sql_port) ) as tunnel:为什么？默认的本地地址解析为mysql无法解析的外部主机名。 - Majte

正如@DimitriBolt所提到的，如果您使用来自Oracle的mysql.connector，则必须使用构造函数cnx = mysql.connector.MySQLConnection(...)。重要提示：构造函数cnx = mysql.connector.connect(...)在通过SSH时无法工作！这是一个错误。 - Msvstl

Answer 2

我猜你会需要端口转发。我建议使用sshtunnel.SSHTunnelForwarder

import mysql.connector
import sshtunnel

with sshtunnel.SSHTunnelForwarder(
        (_host, _ssh_port),
        ssh_username=_username,
        ssh_password=_password,
        remote_bind_address=(_remote_bind_address, _remote_mysql_port),
        local_bind_address=(_local_bind_address, _local_mysql_port)
) as tunnel:
    connection = mysql.connector.connect(
        user=_db_user,
        password=_db_password,
        host=_local_bind_address,
        database=_db_name,
        port=_local_mysql_port)
    ...

- Carlos D.

---

我应该放什么在remote_bind_address中？我在哪里可以找到这些信息？ - Maciek Semik

如果你在本地，可以尝试这样做：_remote_bind_address = '127.0.0.1' _local_bind_address = '0.0.0.0' - Carlos D.

这是什么类型的转发？远程转发吗？需要进行任何服务器设置吗？ - Anupam Srivastava

它不需要任何服务器设置。实际上，它是一种端口转发设置，可以通过SSH隧道访问安全外壳（SSH）连接的服务器端上的应用程序。 - Carlos D.

@CarlosD. 有没有关于如何打包sshtunnel的提示？我通过pip安装了它，然后将其压缩并导入到AWS Lambda中。但是当我导入sshtunnel时，出现错误：没有名为'_cffi_backend'的模块。我很难解决这个问题。 - MeNeedHelp

Answer 3

from sshtunnel import SSHTunnelForwarder
import pymysql
import pandas as pd

tunnel = SSHTunnelForwarder(('SSH_HOST', 22), ssh_password=SSH_PASS, ssh_username=SSH_UNAME,
     remote_bind_address=(DB_HOST, 3306)) 
tunnel.start()
conn = pymysql.connect(host='127.0.0.1', user=DB_UNAME, passwd=DB_PASS, port=tunnel.local_bind_port)
data = pd.read_sql_query("SHOW DATABASES;", conn)

感谢https://www.reddit.com/r/learnpython/comments/53wph1/connecting_to_a_mysql_database_in_a_python_script/提供的帮助。

- Hemanth Sharma

---

使用起始通道的方法对我来说比使用'with'语句的方式要好得多。 它以某种方式解决了我的问题： https://stackoverflow.com/questions/63774543/why-can-i-connect-to-mysql-through-shell-but-cant-do-it-through-python - Manaslu

我不知道为什么，但是只有这种方式才能正常工作。使用with语句似乎对我无效。 - igorkf

使用 {with} 引入了 tunnel.close()，但由于某些原因出现了挂起现象。使用 tunnel.stop() 然后再使用 tunnel.close() 可以规避这个问题。 - Negative Correlation

Answer 4

如果您的私钥文件已加密，这是我的解决方法：

    mypkey = paramiko.RSAKey.from_private_key_file(<<file location>>, password='password')
    sql_hostname = 'sql_hostname'
    sql_username = 'sql_username'
    sql_password = 'sql_password'
    sql_main_database = 'sql_main_database'
    sql_port = 3306
    ssh_host = 'ssh_host'
    ssh_user = 'ssh_user'
    ssh_port = 22


    with SSHTunnelForwarder(
            (ssh_host, ssh_port),
            ssh_username=ssh_user,
            ssh_pkey=mypkey,
            ssh_password='ssh_password',
            remote_bind_address=(sql_hostname, sql_port)) as tunnel:
        conn = pymysql.connect(host='localhost', user=sql_username,
                               passwd=sql_password, db=sql_main_database,
                               port=tunnel.local_bind_port)
        query = '''SELECT VERSION();'''
        data = pd.read_sql_query(query, conn)
        print(data)
        conn.close()

- karthik r

Answer 5

您只需编写私钥文件的路径：ssh_pkey='/home/userName/.ssh/id_ed25519'（有关文档，请参见：https://sshtunnel.readthedocs.io/en/latest/）。

如果您使用来自 Oracle 的 mysql.connector，则必须使用以下结构：cnx = mysql.connector.MySQLConnection(... 重要提示：以下结构 cnx = mysql.connector.connect(... 无法通过SSH工作！这是一个bug。 （有关文档，请参见：https://dev.mysql.com/doc/connector-python/en/connector-python-connectargs.html）。

此外，您的SQL语句必须是理想的。如果在SQL服务器端发生错误，则不会收到来自SQL服务器的错误消息。

import sshtunnel
import numpy as np

with sshtunnel.SSHTunnelForwarder(ssh_address_or_host='ssh_host',
                                  ssh_username="ssh_username",
                                  ssh_pkey='/home/userName/.ssh/id_ed25519',
                                  remote_bind_address=('localhost', 3306),
                                  ) as tunnel:
    cnx = mysql.connector.MySQLConnection(user='sql_username',
                                          password='sql_password',
                                          host='127.0.0.1',
                                          database='db_name',
                                          port=tunnel.local_bind_port)
    cursor = cnx.cursor()
    cursor.execute('SELECT * FROM db_name.tableName;')
    arr = np.array(cursor.fetchall())
    cursor.close()
    cnx.close()

- DimitriBolt

---

你能提供关于这个 bug 的支持参考吗？我在早期版本中使用了 .connect()，但现在不再行，所以这是一个有用的线索。 - Mark Andersen

Answer 6

这对我很有效：

import mysql.connector
import sshtunnel

with sshtunnel.SSHTunnelForwarder(
    ('ip-of-ssh-server', 'port-in-number-format'),
    ssh_username = 'ssh-username',
    ssh_password = 'ssh-password',
    remote_bind_address = ('127.0.0.1', 3306)
) as tunnel:
    connection = mysql.connector.connect(
        user = 'database-username',
        password = 'database-password',
        host = '127.0.0.1',
        port = tunnel.local_bind_port,
        database = 'databasename',
    )
    mycursor = connection.cursor()
    query = "SELECT * FROM datos"
    mycursor.execute(query)

- Carlos Vallejo

Answer 7

如果您正在使用Python，并且所有的用户名、密码、主机和端口都是正确的，那么只剩下一件事情了，就是使用参数(use_pure=True)。这个参数使用Python来解析细节和密码。您可以查看mysql.connector.connect()参数的文档。

with sshtunnel.SSHTunnelForwarder(
    (ssh_host,ssh_port),
    ssh_username=ssh_username,
    ssh_pkey=ssh_pkey,
    remote_bind_address=(sql_host, sql_port)) as tunnel:
connection = mysql.connector.MySQLConnection(
    host='127.0.0.1',
    port=tunnel.local_bind_port,
    user=sql_username,
    password=sql_password,
    use_pure='True')
query = 'select version();'
data = pd.read_sql_query(query, connection)
print(data)
connection. Close()

- Aditya Dutta

Answer 8

有人在另一条评论中说过这句话。如果你使用 Oracle 的 python mysql.connector，那么你必须使用一个构造函数 cnx = mysql.connector.MySQLConnection(...。

重要提示：一个构造函数 cnx = mysql.connector.connect(... 无法通过 SSH 工作！这个 bug 让我浪费了一整天的时间去理解为什么连接会被远程服务器断开：

with sshtunnel.SSHTunnelForwarder(
        (ssh_host,ssh_port),
        ssh_username=ssh_username,
        ssh_pkey=ssh_pkey,
        remote_bind_address=(sql_host, sql_port)) as tunnel:
    connection = mysql.connector.MySQLConnection(
        host='127.0.0.1',
        port=tunnel.local_bind_port,
        user=sql_username,
        password=sql_password)
    query = 'select version();'
    data = pd.read_sql_query(query, connection)
    print(data)
    connection.close()

- Martin Karanja

Answer 9

Paramiko是最好的Python模块，用于进行SSH隧道。在此处查看代码： https://github.com/paramiko/paramiko/blob/master/demos/forward.py

正如评论中所说，这个工具非常完美。 Python MySQLdb连接的SSH隧道

- yeaske

Answer 10

最佳实践是将连接变量参数化。这是我的实现方式。运行得非常好！

import mysql.connector
import sshtunnel
import pandas as pd
import configparser

config = configparser.ConfigParser()
config.read('c:/work/tmf/data_model/tools/config.ini')

ssh_host = config['db_qa01']['SSH_HOST']
ssh_port = int(config['db_qa01']['SSH_PORT'])
ssh_username = config['db_qa01']['SSH_USER']
ssh_pkey = config['db_qa01']['SSH_PKEY']
sql_host = config['db_qa01']['HOST']
sql_port = int(config['db_qa01']['PORT'])
sql_username = config['db_qa01']['USER']
sql_password = config['db_qa01']['PASSWORD']

with sshtunnel.SSHTunnelForwarder(
        (ssh_host,ssh_port),
        ssh_username=ssh_username,
        ssh_pkey=ssh_pkey,
        remote_bind_address=(sql_host, sql_port)) as tunnel:
    connection = mysql.connector.connect(
        host='127.0.0.1',
        port=tunnel.local_bind_port,
        user=sql_username,
        password=sql_password)
    query = 'select version();'
    data = pd.read_sql_query(query, connection)
    print(data)
    connection.close()

- Sushanta Meher