[paramiko] SSH连接数据库

原创已于 2023-08-20 13:44:42 修改 · 431 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #ssh #运维

于 2023-08-20 13:43:51 首次发布

[paramiko] SSH连接服务器专栏收录该内容

4 篇文章

订阅专栏

博客介绍了通过SSH连接数据库的两种不同写法，重点围绕SSH与数据库连接的方式展开，属于信息技术中数据库和运维相关内容。

SSH连接数据库，两种不同的写法：
第二种记得server.start()
方式一：

#!/usr/bin/env python3
import os
import pymysql as pymysql
from sshtunnel import SSHTunnelForwarder


class connect_ssh:

    def connect(self, cmd):
        jump_host_ip = "**"  # 跳板机
        jump_host_user = "user"
        jump_host_key = os.getenv('HOME') + "/.ssh/id_rsa"
        target_bind_address = ('**', 22)

        print("connecting to the jump host")

        with SSHTunnelForwarder(
                ssh_address_or_host=(jump_host_ip, 22),  # 跳板机
                ssh_username=jump_host_user,
                ssh_pkey=jump_host_key,
                remote_bind_address=target_bind_address,
                local_bind_address=('127.0.0.1', 13306)
        ) as tunnel:
            print(tunnel.local_bind_address)
            #  连接数据库
            conn = pymysql.connect(
                host='master.****',
                port=6606,
                database='test_db',
                user='user',
                password='pw'
            )
            cursor = conn.cursor()
            query = cmd
            cursor.execute(query)
            data = cursor.fetchall()
            print(data)
        tunnel.close()


if __name__ == '__main__':
    server = connect_ssh()
    server.connect(cmd="select * from task_tab order by id desc limit 1;")
    print("done")

方式二：

#!/usr/bin/env python3
import os
import pymysql as pymysql
from sshtunnel import SSHTunnelForwarder


class connect_ssh:

    def connect(self, cmd):
        jump_host_ip = "**"  # 跳板机
        jump_host_user = "user"
        jump_host_key = os.getenv('HOME') + "/.ssh/id_rsa"
        target_bind_address = ('**', 22)

        print("connecting to the jump host")

        server = SSHTunnelForwarder(
                ssh_address_or_host=(jump_host_ip, 22),  # 跳板机
                ssh_username=jump_host_user,
                ssh_pkey=jump_host_key,
                remote_bind_address=target_bind_address,
                local_bind_address=('127.0.0.1', 13306)
        )
        server.start()
        print(server.local_bind_address)
        #  连接数据库
        conn = pymysql.connect(
            host='master.****',
                port=6606,
                database='test_db',
                user='user',
                password='pw'
        )
        cursor = conn.cursor()
        query = cmd
        cursor.execute(query)
        data = cursor.fetchall()
        print(data)
        server.close()


if __name__ == '__main__':
    server = connect_ssh()
    server.connect(cmd="select * from task_tab order by id desc limit 1;")
    print("done")