python使用mysqldb模块通过ssh隧道连接mysql(密码|密钥)

主要出于安全考虑，数据库服务器只允许堡垒机通过ssh访问，这对日常的使用带来了麻烦。问题是这样的，MySQL数据库放在了服务器A上，只允许服务器B来访问，而我在机器C上，可以通过ssh连接服务器B。为了解决在机器C上连接mysql这个问题

pip install sshtunnel
pip install mysqlclient

ssh通过密码连接

import MySQLdb
from sshtunnel import SSHTunnelForwarder
 
with SSHTunnelForwarder(
         ('sshhost.domain.com', 22),    # B机器的配置--跳板机
         ssh_password="sshpasswd",     # B机器的配置--跳板机账号
         ssh_username="sshusername",  # B机器的配置--跳板机账户密码
         remote_bind_address=('mysqlhost.domain.com', 3306)) as server:  # A机器的配置-MySQL服务器
 
    conn = MySQLdb.connect(host='127.0.0.1',             # 此处必须是必须是127.0.0.1，代表C机器
                           port=server.local_bind_port,
                           user='user',                 # A机器的配置-MySQL服务器账户
                           passwd='password',   # A机器的配置-MySQL服务器密码c
                           db='dbname', # 可以限定，只访问特定的数据库,否则需要在mysql的查询或者操作语句中，指定好表名
                          charset='utf8'      # 和数据库字符编码集合，保持一致，这样能够解决读出数据的中文乱码问题
)

ssh通过密钥连接

# -*- coding:utf-8 -*-
import pymysql
from sshtunnel import SSHTunnelForwarder


with SSHTunnelForwarder(
        ('*.*.*.*', 32),  # 指定ssh登录的跳转机的address，端口号
        ssh_username="log",  # 跳转机的用户名
        ssh_pkey="C:/Users/WYXCz/.ssh/id_rsa",  # 私钥路径
        ssh_private_key_password="m5!nf",  # 跳转机的用户密码
        remote_bind_address=('*.*.*.*', 3306)) as server:  # mysql服务器的address，端口号
    conn = pymysql.connect(host='127.0.0.1',  # 此处必须是是127.0.0.1
                           port=server.local_bind_port,
                           user='crawl_data',  # 数据库用户名
                           passwd='aJn',  # 数据库密码
                           db='crawl_data' # 数据库名称
                           )

    cursor = conn.cursor()
    # 使用 execute()  方法执行 SQL 查询
    cursor.execute("SELECT VERSION()；")

    # 使用 fetchone() 方法获取单条数据.
    data = cursor.fetchone()

    print("Database version : %s " % data)
    # 关闭数据库连接
    cursor.close()

然后接下来的查询什么的，直接写在with那里边，与conn对齐就可以了。

我们要注意的一个问题，我们对于数据库连接这一部分，往往是在一个单独的函数里，与其他数据库的查询插入删除更新操作往往不在一起，这样的话，with as 有个特点就是，离开这块作用域，对象就被销毁掉了，别的函数里是没法用的，也就会出现一种情况是，连接上了，但是对象又给销毁掉了，结果查询的时候直接显示这个错误：OperationalError: (2006, ‘MySQL server has gone away’)， 而网上查询这个错误，多半说的是因为你查询的 sql操作的时间过长，或者是传送的数据太大 ，但是我这个地方实际上就是因为出了with as 的作用域，导致连接又给关闭掉了，所以出现这样的结果。

解决方法：将SSHTunnelForwarder出来的对象赋值给server，然后启动server，然后进行一系列操作之后，再stop掉。

这样连接数据库我们就写成了一个单独的函数，改了之后，直接也还放在这个函数里就好了，替代原来的connect语句。

def connect(self):
    '''
    self.client = MySQLdb.connect(host=self.server, port=self.port, user=self.user,
                                  passwd=self.password, db=self.database,
                                  charset=self.charset)
    # log.info('Connect to MySQL Server: ' + self.server)
    '''

    server = SSHTunnelForwarder(
            ('sshhost.domain.com', 22),  # B机器的配置
            ssh_password='ssh_password',
            ssh_username='ssh_username',
            remote_bind_address=('mysqlhost.domain.com', mysql.port)
    )
    server.start()

    self.client = MySQLdb.connect(host='127.0.0.1',  # 此处必须是是127.0.0.1
                                  port=server.local_bind_port,
                                  user='username',
                                  passwd='password',
                                  db='dbname')

    server.close()#最后一定要server.close()，不然程序不会结束