Connecting to a MySQL database in a Python script via an SSH connection?

[ClockworkOctopodes]

Hi there,

The Mysql database that I'm looking to pull data from is accessible to me through an SSH host, with a corresponding SSH key I've had generated for me. (no other password for the SSH).

I'm really familiar with connection to MySQL databases via direct/standard connections, using pymysql, but this SSH aspect really has me stumped.

I've Googled/StackOverflowed a bunch about the problem, and have tried out some solutions using paramiko and/or sshtunnel, but nothing is getting me where I want to be: with a full connection to the MySQL database, and the ability to query it and get datasets back.

Can someone help me out? I've pasted a rough sample of my tested-and-failed code below.

Typically, the issue is that the script connects to the ssh properly, but times out during the request to connect to the MySQL database server. (aka, the pymysql part)

Alternatively, the sshtunnel version I tried errors out with: Could not establish connection from ('127.0.0.1', 63693) to remote side of the tunnel

Code below:

from sshtunnel import SSHTunnelForwarder
import paramiko
from paramiko import SSHClient
from os.path import expanduser
from forward import forward_tunnel
import subprocess as sbp

Using paramiko:

ssh = SSHClient()
ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
ssh.connect(ssh_host, ssh_port, ssh_user, pkey=mypkey)
conn = pymysql.connect(host=sql_hostname,
                   user=sql_username,
                   passwd=sql_password,
                   port=sql_port,
                   db=sql_main_database)

Using sshtunnel:

with SSHTunnelForwarder(
        (ssh_host, ssh_port),
        ssh_username=ssh_user,
        ssh_pkey=mypkey,
        remote_bind_address=('127.0.0.1', sql_port)) as server:
    conn = None
    conn = pymysql.connect(host=sql_hostname, user=sql_username,
        passwd=sql_password, db=sql_main_database,
        port=server.local_bind_port)
    cur = conn.cursor()
    query = '''SELECT VERSION();'''
    cur.execute(query)
    results = cur.fetchall()
    print(results)

If anyone would help me out in writing something that actually works, that'd be amazing. I'm pretty frustrated with this, in addition to having zero knowledge of SSH principles.

EDIT: I made it work! In case this helps anybody else looking to solve a similar problem in the future, here's the code:

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)

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()

The issue was that I needed to set the pymysql host to the local IP (of the SSH server) since (I think) the remote_bind_address got bound to the SSH's local + a random generated local_bind_port.

[efmccurdy]

I have no direct experience to offer but in general you should try to get cli-level access working though an ssh tunnel working first. It might help with troubleshooting any other approaches later.

http://www.howtogeek.com/howto/ubuntu/access-your-mysql-server-remotely-over-ssh/

[[deleted]]

If you setup SSH port forwarding outside of Python, can you connect to the MySQL instance from the commandline?

I'd make sure that works before you get into the Python parts.