【python】SSH隧道远程连接mysql

「爱情、让人受尽委屈。」 2022-09-10 15:25 330阅读 0赞

#### 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）][python_python_mysql_pymysql]

[python_python_mysql_pymysql]: https://blog.csdn.net/qq_39147299/article/details/108064711