[Python知识库]【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）