python 使用ssh隧道连接mysql

环境

• 数据库只允许内网访问
• 服务器公网SSH访问

模块安装

pip install sshtunnel -i https://pypi.doubanio.com/simple/  
pip install MySQL-python==1.2.5 -i https://pypi.doubanio.com/simple/  

方法

#!/usr/bin/python
#_*_coding:utf-8_*_
# code.by leoiceo

import MySQLdb  
from sshtunnel import SSHTunnelForwarder  
import MySQLdb.cursors  #以dict形式输出

def dbconnect_ssh(ssh_host,ssh_port,keyfile,ssh_user,db_host,db_name,sql,db_port,db_user,db_passwd):  
    with SSHTunnelForwarder(
            (ssh_host, ssh_port),
            #ssh_password="sshpasswd",
            ssh_pkey=keyfile,
            ssh_username=ssh_user,
            remote_bind_address=(db_host, db_port)
    ) as server:

        db = MySQLdb.connect(
            host='127.0.0.1',
            port=server.local_bind_port,
            user=db_user,
            passwd=db_passwd,
            db=db_name,
            charset="utf8",
            cursorclass=MySQLdb.cursors.DictCursor)

        cursor = db.cursor()

        try:
            cursor.execute(sql)
            data = cursor.fetchall()
            db.commit()
        except:
            db.rollback()

        collect = []
        for result in data:
            collect.append(result)

        db.close()
        cursor.close()

        return collect

if __name__ == "__main__":  
    ssh_host = "1.1.1.1"           #SSH服务器地址
    ssh_port = 22                  #SSH端口
    keyfile = "/root/.ssh/id_rsa"  #SSH密钥
    ssh_user = "leoiceo"           #SSH用户名
    db_host = "192.168.1.1"        #数据库地址
    db_name = 'mysql'              #数据库名
    sql = 'show databases;'        #SQL
    db_port = 3306                 #数据库端口
    db_user = 'root'               #数据库用户名
    db_passwd = '123456'           #数据库密码
    result = dbconnect_ssh(ssh_host,ssh_port,keyfile,ssh_user,db_host,db_name,sql,db_port,db_user,db_passwd)
    print result