启用 Python 通过 SSH 隧道连接到 MySQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21903411/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Enable Python to Connect to MySQL via SSH Tunnelling
提问by Nyxynyx
I'm using MySqldbwith Python 2.7 to allow Python to make connections to another MySQL server
我正在使用MySqldbPython 2.7 来允许 Python 连接到另一个 MySQL 服务器
import MySQLdb
db = MySQLdb.connect(host="sql.domain.com",
user="dev",
passwd="*******",
db="appdb")
Instead of connecting normally like this, how can the connection be made through a SSH tunnel using SSH key pairs?
不是像这样正常连接，如何使用 SSH 密钥对通过 SSH 隧道建立连接？
The SSH tunnel should ideally be opened by Python. The SSH tunnel host and the MySQL server are the same machine.
SSH 隧道最好由 Python 打开。SSH 隧道主机和 MySQL 服务器是同一台机器。
回答by yeaske
Paramiko is the best python module to do ssh tunneling. Check out the code here: https://github.com/paramiko/paramiko/blob/master/demos/forward.py
Paramiko 是执行 ssh 隧道的最佳 Python 模块。在此处查看代码：https: //github.com/paramiko/paramiko/blob/master/demos/forward.py
As said in comments this one works perfect. SSH Tunnel for Python MySQLdb connection
正如评论中所说，这个完美无缺。 用于 Python MySQLdb 连接的 SSH 隧道
回答by Carlos D.
I'm guessing you'll need port forwarding. I recommend sshtunnel.SSHTunnelForwarder
我猜你需要端口转发。我建议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)
...
回答by Kathan Shah
Only this worked for me
只有这对我有用
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()
回答by karthik r
If your private key file is encrypted, this is what worked for me:
如果您的私钥文件已加密，这对我有用：
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()
回答by DimitriBolt
You may only write the path to the private key file: ssh_pkey='/home/userName/.ssh/id_ed25519'(documentation is here: https://sshtunnel.readthedocs.io/en/latest/).
您只能写入私钥文件的路径：（ssh_pkey='/home/userName/.ssh/id_ed25519'文档在这里：https: //sshtunnel.readthedocs.io/en/latest/）。
If you use mysql.connector from Oracle you must use a construction
cnx = mysql.connector.MySQLConnection(...
Important: a construction
cnx = mysql.connector.connect(...
does not work via an SSh! It is a bug.
(The documentation is here: https://dev.mysql.com/doc/connector-python/en/connector-python-connectargs.html).
如果您使用 Oracle 的 mysql.connector，则必须使用构造
cnx = mysql.connector.MySQLConnection(... 重要提示：构造
cnx = mysql.connector.connect(...不能通过 SSh 工作！这是一个错误。（文档在这里：https: //dev.mysql.com/doc/connector-python/en/connector-python-connectargs.html）。
Also, your SQL statement must be ideal. In case of an error on SQL server side, you do not receive an error message from SQL-server.
此外，您的 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()
回答by Hemanth Sharma
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)
