【python】SSH隧道遠程連接mysql

1.說明

mysql部署在一台服務器上，但不能直接連接該服務器（為了安全），需要通過一台跳板機才能訪問，該跳板機是可以通過SSH秘鑰訪問的。我們可以借助paramiko這個包裡面的SSHTunnelForwarder進行連接

2.代碼

import paramiko
import pymysql
from sshtunnel import SSHTunnelForwarder
def get_tunnel_ssh(config_dict):
private_key = paramiko.RSAKey.from_private_key_file(filename=config_dict.get("ssh_key_path"),
password=config_dict.get("ssh_password"))
ssh_tunnel = SSHTunnelForwarder(ssh_address_or_host=(config_dict.get("ssh_ip"), config_dict.get("ssh_port")),
ssh_username=config_dict.get("ssh_user"),
ssh_pkey=private_key,
remote_bind_address=(config_dict.get("ip"), config_dict.get("port")))
ssh_tunnel.start()
# ssh_tunnel.close()
return ssh_tunnel
def get_mysql_conn():
ssh_config = {

'ip': '47.241.XX.XX',
'port': 3306,
'ssh_ip': '23.248.XX.XX',
'ssh_port': 6666,
'ssh_user': 'pan',
'ssh_password': 'Tcjo5XXXXX',
'ssh_key_path': r"E:\pan\dingding\key",
}
mysql_config = {

"host": "127.0.0.1",
"port": None,
"user": "pan",
"passwd": "&B2dpzXXXXX",
"db": "walletdb3",
"charset": "utf8mb4",
}
tunnel = get_tunnel_ssh(ssh_config)
mysql_config.update({
"port": tunnel.local_bind_port}) # 注意mysql端口使用的是隧道的端口
print(mysql_config)
mysql_conn = pymysql.connect(**mysql_config)
return mysql_conn

3.使用

from utils.ssh_mysql import get_mysql_conn
mysql_conn = get_mysql_conn()
cursor = mysql_conn.cursor()
cursor.execute("select * from eth_balance_guarder order by id desc limit 1;")
data = cursor.fetchall()
print(data)
cursor.close()
mysql_conn.close()

關於mysql的使用可以參考
【python】python連接mysql數據庫（pymysql）