使用Python over SSH隧道端口转发访问MySQL数据库

Question

我在远程服务器上有MySQL数据库，在访问它之前，我需要使用ssh隧道进行端口转发。如果没有python，我通常会执行下面的命令，然后在shell中输入我的密码。一旦通过身份验证，我就可以通过连接到localhost:3306来访问mysql工作台中的数据库。我在我的Mac OS X El Capitan上做所有的事情

ssh -L 3306:remote.database.host:3306 xxxx@xxxx.com

现在我想从Python访问数据库，下面是我使用的脚本：

import pexpect
import time
import sqlalchemy as sql
from sqlalchemy.engine.url import URL

myDB = URL(drivername='mysql+pymysql', host='localhost',
database='test_db',
username='roott',
password='xxxxxx',
port=3306)

child = pexpect.spawnu('ssh -L 3306:remote.database.host:3306 xxxx@xxxx.com')
child.expect (u'password:')
child.sendline ('xxxxx')

while child.isalive():
    try:
    engine = sql.create_engine(name_or_url=myDB)
    connection = engine.connect()
    connection.close()
    break
finally:
    child.close()

上面的脚本给了我以下错误：

sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (2003, "Can't connect to MySQL server on 'localhost' ([Errno 61] Connection refused)")

我还通过参考jsjc在Python - SSH Tunnel Setup and MySQL DB Access上的解决方案来尝试使用sshtunnelforwarder，它也不起作用。

from sshtunnel import SSHTunnelForwarder
from sqlalchemy import create_engine
from sqlalchemy.engine.url import URL

with SSHTunnelForwarder(('ssh_host', 22),
                    ssh_username='xxxxx',
                    ssh_pkey='/path/to/key/file',
                    local_bind_address=('127.0.0.1', 3306),
                    remote_bind_address=('127.0.0.1', 3306)) as server:


myDB = URL(drivername='mysql+pymysql', host='127.0.0.1',
           database='test_db',
           username='roott',
           password='xxxxx',
           port=3306
           )

engine = create_engine(name_or_url=myDB)
connection = engine.connect()    
connection.close()

我得到以下信息登录到控制台，但没有任何反应，它只是挂在那里，我必须手动杀死程序。

DEBUG:paramiko.transport:starting thread (client mode): 0xbe25940
DEBUG:paramiko.transport:Local version/idstring: SSH-2.0-paramiko_1.16.0
DEBUG:paramiko.transport:Remote version/idstring: SSH-2.0-OpenSSH_6.2
INFO:paramiko.transport:Connected (version 2.0, client OpenSSH_6.2)
DEBUG:paramiko.transport:........
DEBUG:paramiko.transport:Kex agreed: diffie-hellman-group1-sha1
DEBUG:paramiko.transport:Cipher agreed: aes128-ctr
DEBUG:paramiko.transport:MAC agreed: hmac-sha2-256
DEBUG:paramiko.transport:Compression agreed: none
DEBUG:paramiko.transport:kex engine KexGroup1 specified hash_algo <built-in function openssl_sha1>
DEBUG:paramiko.transport:Switch to new keys ...
DEBUG:paramiko.transport:Attempting public-key auth...
DEBUG:paramiko.transport:userauth is OK
INFO:paramiko.transport:Auth banner: b'Amazon Linux AMI release 2014.09\nKernel \\r on an \\m\n'
INFO:paramiko.transport:Authentication continues...
DEBUG:paramiko.transport:Methods: ['password']
DEBUG:paramiko.transport:[chan 0] Max packet in: 32768 bytes
WARNING:paramiko.transport:Oops, unhandled type 3

Answer 1

这是我以前遇到过的问题。问题是，当使用localhost作为数据库服务器时，驱动程序坚持连接到本地unix套接字，而不是使用指定的端口通过环回接口连接。

解决方法是将数据库服务器更改为除localhost之外的其他服务器。这将导致驱动程序使用您指定的服务器/端口，而不是尝试连接到本地套接字。

myDB = URL(drivername='mysql+pymysql', host='127.0.0.1', ...)
myDB = URL(drivername='mysql+pymysql', host='your.ip.add.ress', ...)
myDB = URL(drivername='mysql+pymysql', host='your.hostname', ...)

Answer 2

Idk如果OP已经解决了这个问题(已经3年了，所以我希望是这样)，但我的解决方案非常简单。我只需要在"with“子句中缩进myDB、engine和connection定义。否则，ssh隧道将在您有机会连接到数据库之前关闭：

with SSHTunnelForwarder(('ipforsshing', sshport), ssh_username='sshuser', ssh_pkey='privatekeypath', local_bind_address=('127.0.0.1', 3305), remote_bind_address=('127.0.0.1', 3306)) as server:
    adcrawl_db = URL(drivername='mysql+pymysql', host='127.0.0.1', database='dbname', username='dbusername',password='dbpassword', port=3305)

    engine = create_engine(name_or_url=adcrawl_db)
    connection = engine.connect()
    print("CONNECTED!")
    connection.close()

希望这对将来的任何人都有帮助。