启用 Python 通过 SSH 隧道连接到 MySQL

2021-11-20 00:00:00 python python-2.7 ssh mysql

我在 Python 2.7 中使用 MySqldb 以允许 Python 连接到另一个 MySQL 服务器

I'm using MySqldb with Python 2.7 to allow Python to make connections to another MySQL server

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

不是像这样正常连接，如何使用SSH密钥对通过SSH隧道建立连接?

Instead of connecting normally like this, how can the connection be made through a SSH tunnel using SSH key pairs?

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

The SSH tunnel should ideally be opened by Python. The SSH tunnel host and the MySQL server are the same machine.

推荐答案

只有这个对我有用

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()

相关文章