python SSH连接mysql、redis、cassandra、mongo

安装 sshtunnel

pipenv install sshtunnel --skip-lock

准备ssh 配置文件

vi ~/.ssh/config

ssh连接配置

Host jump_name
    Hostname     #跳板机ip地址
    Port         #跳板机端口号
    User  #用户名
    ProxyCommand #代理名称
    IdentityFile #私钥文件
Host * #通用配置
    ServerAliveInterval 30
    ServerAliveCountMax 10
    TCPKeepAlive yes

ssh配置文件写到项目/config/ssh/config目录下

import os
BASE_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
# 用于适配不同平台找到配置文件路径
def jump_machine_config_file():
    path = f'{BASE_DIR}/config/ssh/config'
    with open(path, 'r+') as f:
        s = f.read()
        s_new = s.replace('{BASE_DIR}', BASE_DIR)
        new_path = f'{BASE_DIR}/config/ssh/config_tmp'
        new_fo = open(new_path, 'w+', encoding='UTF-8')
        new_fo.write(s_new)
        new_fo.close()
    return new_path

连接mysql数据库示例

import traceback
import pymysql
from sshtunnel import SSHTunnelForwarder

class MysqlManager(object):
    def __init__(self, **kwargs):
        try:
            if 'jump_machine' in kwargs.keys():
                jump_machine_name = kwargs.pop('jump_machine')
                new_path = jump_machine_config_file()
                host, port = kwargs['host'], kwargs['port']
                self.server = SSHTunnelForwarder(jump_machine_name, ssh_config_file=new_path, remote_bind_address=(host, port))
                self.server.start()
                kwargs['host'], kwargs['port'] = '127.0.0.1', self.server.local_bind_port
            else:
                self.server = ''
            self.connection = pymysql.connect(**kwargs)
            self.cursor = self.connection.cursor(cursor=pymysql.cursors.DictCursor)
        except pymysql.Error as e:
            logger.error(traceback.format_exc())

jump_machine 数据库跳板机名称，即配置文件中的jump_name

连接redis示例

import redis
import json

from sshtunnel import SSHTunnelForwarder
from rediscluster import StrictRedisCluster
from utils.logger import logger
from config.settings import jump_machine_config_file


class RedisManager(object):
    def __init__(self, host=None, port=None, cluster_nodes: list = None, password=None, jump=None):
        """
        连接redis
        """
        try:
            if jump:
                ssh_config_file = jump_machine_config_file()
                if cluster_nodes:
                    # 首先打开一个tunnel用于startup_nodes寻找redis所有集群的ip
                    tunnels = [(i['host'], i['port']) for i in cluster_nodes]
                    ssh_tunnel = SSHTunnelForwarder(jump, ssh_config_file=ssh_config_file, remote_bind_addresses=tunnels)
                    ssh_tunnel.start()
                    cluster_nodes = [{'host': '127.0.0.1', 'port': i} for i in ssh_tunnel.local_bind_ports]
                    self.client = StrictRedisCluster(startup_nodes=cluster_nodes, skip_full_coverage_check=True,
                                                     nodemanager_follow_cluster=True, password=password)
                    # 取出收集到的集群node信息，保存在nodes和slots两个变量里，nodes是ip和port的对应信息，slots是key和nodes的对应信息
                    real_nodes = self.client.connection_pool.nodes.nodes
                    real_slots = self.client.connection_pool.nodes.slots
                    # 最后用取出的node信息再次开通tunnel用于取得对应key的value
                    tunnels = [(i['host'], i['port']) for i in real_nodes.values()]
                    ssh_tunnel2 = SSHTunnelForwarder(jump, ssh_config_file=ssh_config_file, remote_bind_addresses=tunnels)
                    ssh_tunnel2.start()
                    tunnel_bindings = {k[0]: v[1] for k, v in ssh_tunnel2.tunnel_bindings.items()}
                    # 使用binding的tunnel地址替换掉nodes和slots中的地址
                    cluster_nodes = {}
                    for port in ssh_tunnel2.local_bind_ports:
                        host = '127.0.0.1'
                        name = f'{host}:{port}'
                        cluster_nodes[name] = {'host': host, 'port': port, 'name': name, 'server_type': 'master'}
                    print(cluster_nodes)
                    self.client.connection_pool.nodes.nodes = cluster_nodes
                    for slots in real_slots.values():
                        for slot in slots:
                            real_host = slot['host']
                            if real_host in tunnel_bindings:
                                slot['host'] = '127.0.0.1'
                                slot['port'] = tunnel_bindings[real_host]
                                slot['name'] = f'127.0.0.1:{tunnel_bindings[real_host]}'
                else:
                    ssh_tunnel = SSHTunnelForwarder(jump, ssh_config_file=ssh_config_file, remote_bind_address=(host, port))
                    ssh_tunnel.start()
                    pool = redis.ConnectionPool(host='127.0.0.1', port=ssh_tunnel.local_bind_port)
                    self.client = redis.Redis(connection_pool=pool)
            else:
                if cluster_nodes:
                    self.client = StrictRedisCluster(startup_nodes=cluster_nodes, skip_full_coverage_check=True,
                                                     nodemanager_follow_cluster=True, password=password)
                else:
                    pool = redis.ConnectionPool(host=host, port=port)
                    self.client = redis.Redis(connection_pool=pool)

        except Exception as e:
            logger.debug("Redis Error: %s" % e)

ssh连接cassandra

import json
import traceback
from cassandra.cluster import Cluster
from cassandra.auth import PlainTextAuthProvider
from sshtunnel import SSHTunnelForwarder
from config.settings import jump_machine_config_file


class CassandraManager(object):
    def __init__(self, nodes, keyspace, port, user, password, jump_machine=None):
        try:
            if jump_machine:
                tunnel_nodes = [(i, port) for i in nodes]
                ssh_config_file = jump_machine_config_file()
                ssh_tunnel = SSHTunnelForwarder(jump_machine, ssh_config_file=ssh_config_file, remote_bind_addresses=tunnel_nodes)
                ssh_tunnel.start()
                connect_nodes = ssh_tunnel.local_bind_addresses
            else:
                connect_nodes = nodes
            auth_provider = PlainTextAuthProvider(username=user, password=password)
            cluster = Cluster(connect_nodes, port=port, auth_provider=auth_provider)
            self.session = cluster.connect(keyspace=keyspace)
        except Exception as e:
            print("Cassandra Error: %s" % (e,))

ssh连接mongo

import sys
import traceback

import pymongo
from sshtunnel import SSHTunnelForwarder
from config.settings import jump_machine_config_file


class MongodbManager(object):
    def __init__(self, **kwargs):
        try:
            jump = kwargs.get('jump_machine', None)
            host = kwargs.get('host', None)
            port = kwargs.get('port', None)
            username = kwargs.get('username', None)
            password = kwargs.get('password', None)
            database = kwargs.get('database', None)
            if jump:
                ssh_config_file = jump_machine_config_file()
                ssh_tunnel = SSHTunnelForwarder(jump, ssh_config_file=ssh_config_file, remote_bind_address=(host, port))
                ssh_tunnel.start()
                self.conn = pymongo.MongoClient('127.0.0.1', ssh_tunnel.local_bind_port)
            else:
                self.conn = pymongo.MongoClient(kwargs['host'], kwargs['port'])
            self.db = self.conn[database]  # connect db
            if username and password:
                self.connected = self.db.authenticate(username, password)
            else:
                self.connected = True
        except ConnectionError:
            print(traceback.format_exc())
            print('Connect Statics Database Fail.')
            sys.exit(1)