Enable Python to Connect to MySQL via SSH Tunnelling

Question

I'm using MySqldb with Python 2.7 to allow Python to make connections to another MySQL server

import MySQLdb db = MySQLdb.connect(host="sql.domain.com",      user="dev",        passwd="*******",        db="appdb") 

Instead of connecting normally like this, how can the connection be made through a SSH tunnel using SSH key pairs?

The SSH tunnel should ideally be opened by Python. The SSH tunnel host and the MySQL server are the same machine.

Answer 1

Only this worked for me

import pymysql import paramiko import pandas as pd from paramiko import SSHClient from sshtunnel import SSHTunnelForwarder from os.path import expanduser  home = expanduser('~') mypkey = paramiko.RSAKey.from_private_key_file(home + pkeyfilepath) # if you want to use ssh password use - ssh_password='your ssh password', bellow  sql_hostname = 'sql_hostname' sql_username = 'sql_username' sql_password = 'sql_password' sql_main_database = 'db_name' sql_port = 3306 ssh_host = 'ssh_hostname' ssh_user = 'ssh_username' ssh_port = 22 sql_ip = '1.1.1.1.1'  with SSHTunnelForwarder(         (ssh_host, ssh_port),         ssh_username=ssh_user,         ssh_pkey=mypkey,         remote_bind_address=(sql_hostname, sql_port)) as tunnel:     conn = pymysql.connect(host='127.0.0.1', user=sql_username,             passwd=sql_password, db=sql_main_database,             port=tunnel.local_bind_port)     query = '''SELECT VERSION();'''     data = pd.read_sql_query(query, conn)     conn.close() 

Answer 2

I'm guessing you'll need port forwarding. I recommend sshtunnel.SSHTunnelForwarder

import mysql.connector import sshtunnel  with sshtunnel.SSHTunnelForwarder(         (_host, _ssh_port),         ssh_username=_username,         ssh_password=_password,         remote_bind_address=(_remote_bind_address, _remote_mysql_port),         local_bind_address=(_local_bind_address, _local_mysql_port) ) as tunnel:     connection = mysql.connector.connect(         user=_db_user,         password=_db_password,         host=_local_bind_address,         database=_db_name,         port=_local_mysql_port)     ...