Python MySQL Connection with SSH

Question

Hi I have a shared hosting i bought and it allows for remote MySQL connection only with SSH.

So far I know that it doesn't have any Public or Private Keys..

And here's my connection setup on the MySQL Workbench which works when I try to connect:

I have looked at another stackoverflow question: Here but none of the answers seems to work for me.. :/ I'm really at a dead end and I need to get this to work. Can someone help me out please?

So the top answer on that thread looks like the accepted way to do this. I've only personally done it once. What exactly is happening when you try to implement that? Also note, that thread is in python 2.7 so there are a few adjustments to get it to work in 3.x — Rashid 'Lee' Ibrahim, Aug 07 '20 at 14:04
@Rashid'Lee'Ibrahim Honestly.. Some of the answers throw me errors like lost connection. Some of the answers i try would just not output anything as if it was stuck in a while loop. — SunAwtCanvas, Aug 07 '20 at 14:05
So with that top answer on there, you should get the effect of an "infinite loop" but really the connection is open. With ssh tunnel you aren't going to have an open sql connection you can pass around into other object. You will connect, do your queries, and then you have to close the connection for the program to actually continue running. — Rashid 'Lee' Ibrahim, Aug 07 '20 at 14:19

score 0 · Answer 1 · answered Aug 08 '20 at 01:27

So I figured it out with like a million trial and error:

import pymysql
import paramiko
import pandas as pd
from paramiko import SSHClient
from sshtunnel import SSHTunnelForwarder

ssh_host = '198.54.xx.xx'
ssh_host_port = 21098 #Ur SSH port
ssh_username = "sshuser123" #Change this
ssh_password = "sshpassword123" #Change this

db_user = 'db user' #change this
db_password = 'password123' #change this
db = 'main_db' #The db that the user is linked to


with SSHTunnelForwarder(
        (ssh_host, ssh_host_port),
        ssh_username=ssh_username,
        ssh_password=ssh_password,
        remote_bind_address=('127.0.0.1', 3306)) as tunnel:
    conn = pymysql.connect(host='127.0.0.1', user=db_user,
            passwd=db_password, db=db,
            port=tunnel.local_bind_port)
    query = '''SELECT * from tablename;'''
    data = pd.read_sql_query(query, conn)
    print(data)
    conn.close()

This is the code you should use if your SSH on MySql doesn't have any Public / Private Key.

Hope this helps anyone facing the same issue!!

shabelski89 · Answer 2 · 2020-08-07T14:23:29.073

-1

Connect to server 198.54.x.240:21098 via ssh with port-forwarding like ssh -t -gL 33069:localhost:3306 198.54.x.240 in windows use PuTTY, i like KiTTy (fork putty ) add connection and SSH Tunnel look at the pictures
Connect to MySQL via localhost:33069 (answer you know)WorkBench do the same, but 3306 on 3306, if you need more than 1 remote connection best practice forward different porst.

edited Aug 07 '20 at 14:23

answered Aug 07 '20 at 14:14

shabelski89

19
6

Python MySQL Connection with SSH

2 Answers2