python 使用sqlalchemy,mysqldb模块通过ssh隧道连接mysql

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

1:use  sqlalchemy

from sshtunnel import SSHTunnelForwarder #Run pip install sshtunnel
from sqlalchemy.orm import sessionmaker #Run pip install sqlalchemy

with SSHTunnelForwarder(
    ('10.160.1.24', 22), #Remote server IP and SSH port
    ssh_username = "<usr>",
    ssh_password = "<pwd>",
    remote_bind_address=('127.0.0.1', 5432)
    ) as server:

    server.start() #start ssh sever
    print 'Server connected via SSH'

    #connect to PostgreSQL
    local_port = str(server.local_bind_port)
    engine = create_engine('postgresql://<db_user>:<db_pwd>@127.0.0.1:' + local_port +'/<db_name>')

    Session = sessionmaker(bind=engine)
    session = Session()

    print 'Database session created'

    #test data retrieval
    test = session.execute("SELECT * FROM <table_name>")

2: use mysqldb

import MySQLdb  
from sshtunnel import SSHTunnelForwarder  
  
with SSHTunnelForwarder(  
         ('sshhost.domain.com', 22),    #B机器的配置  
         ssh_password="sshpasswd",  
         ssh_username="sshusername",  
         remote_bind_address=('mysqlhost.domain.com', 3306)) as server:  #A机器的配置  
  
    conn = MySQLdb.connect(host='127.0.0.1',              #此处必须是是127.0.0.1  
                           port=server.local_bind_port,  
                           user='user',  
                           passwd='password',  
                           db='dbname')

 

发表评论

电子邮件地址不会被公开。 必填项已用*标注

This blog is kept spam free by WP-SpamFree.