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.

Did you google? Opening an ssh tunnel with python: http://stackoverflow.com/questions/4364355/how-to-open-an-ssh-tunnel-using-python , connecting to MySql over said tunnel: http://stackoverflow.com/questions/3577555/ssh-tunnel-for-python-mysqldb-connection — mbatchkarov, Feb 20 '14 at 09:32
You probably have good reason to use SSH, but if this is a direct connection to a MySQL server, start using SSL instead. Less things that can go wrong. — geertjanvdk, Feb 20 '14 at 09:42
@geertjanvdk Thats interesting, why would SSL be the better choice? I'm looking to make secure connections between client and server and SSH was the first to come to mind — Nyxynyx, Feb 20 '14 at 09:44
You don't use SSH to connect to a secure web site, do you? It would just complicate things. SSL is the way to go if your MySQL server is directly accessible. Also, SSL works from any connector or operating system like Windows. When using an SSH tunnel, you would need to keep it up, monitor it, etc.. — geertjanvdk, Feb 20 '14 at 09:49

score 48 · Answer 1 · answered Jun 30 '18 at 15:36

48

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

answered Jun 30 '18 at 15:36

Kathan Shah

1,655
17
25

1

What is the sql_ip used for? – OneAdamTwelve Mar 05 '21 at 23:07
@kathanshah how to use this if there'a jump host in between? & does it allow default .ssh/config file settings in place? – Sollosa Jul 26 '21 at 16:44
@OneAdamTwelve, `sql_ip` appears to be unnecessary and unused. But this example works! (I used MySQLdb) – Ben Ogorek Nov 19 '21 at 12:27
Just a tiny add-on comment for anyone who experiences "xxx.com Host is not allowed to connect to this MySQL server" with this solution. You then need to add local_bind_address = ('127.0.0.1', sql_port) in the first part of the SSHTunnelForwarder(), e.g.: with SSHTunnelForwarder( (ssh_host, ssh_port), ssh_username=ssh_user, ssh_pkey=mypkey, remote_bind_address=('127.0.0.1', sql_port), local_bind_address = ('127.0.0.1', sql_port) ) as tunnel: Why? The default local address resolves to the external hostname that mysql can't parse. – Majte Jan 16 '22 at 00:44
As mentioned by @DimitriBolt If you use mysql.connector from Oracle you must use a construction cnx = mysql.connector.MySQLConnection(... Important: a construction cnx = mysql.connector.connect(... does not work via an SSh! It is a bug – Msvstl May 01 '22 at 04:42
Just one typo in the above code in conn parameter it's "password" instead of "passwd". Moreover, the same can be done for MS SQL Server using pymssql. – Ali Mohsan Dec 29 '22 at 23:58
@Kathan Any tips on how you packaged sshtunnel. I installed via pip, zipped, and imported to AWS lambda. I receive error when I import sshtunnel. No module named '_cffi_backend'. Having a hard time resolving this. – MeNeedHelp Dec 30 '22 at 04:01

score 29 · Answer 2 · edited Nov 03 '16 at 23:02

29

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

edited Nov 03 '16 at 23:02

Eric

2,539
18
23

answered Nov 03 '16 at 20:53

Carlos D.

415
5
8

1

What am I suppose to put in remote_bind_address? Where can I find this information? – Maciek Semik Jul 26 '17 at 02:09
1

If you're in your local you can try something like this: `_remote_bind_address = '127.0.0.1' _local_bind_address = '0.0.0.0'` – Carlos D. Aug 03 '17 at 18:01
What type of forwarding is this? Remote forwarding? Does need any server setup? – Anupam Srivastava Jan 22 '18 at 14:02
1

It doesn't need any server setup. Indeed it is a setup for port forwarding that enables applications on the server side of a Secure Shell (SSH) connection to be accessed through a SSH's tunnel. – Carlos D. Jan 23 '18 at 00:42
@CarlosD. Any tips on how to package sshtunnel. I installed via pip, zipped, and imported to AWS lambda. I receive error when I import sshtunnel. No module named '_cffi_backend'. Having a hard time resolving this. – MeNeedHelp Dec 30 '22 at 04:04

Hemanth Sharma · Answer 3 · 2020-02-26T13:46:04.343

9

from sshtunnel import SSHTunnelForwarder
import pymysql
import pandas as pd

tunnel = SSHTunnelForwarder(('SSH_HOST', 22), ssh_password=SSH_PASS, ssh_username=SSH_UNAME,
     remote_bind_address=(DB_HOST, 3306)) 
tunnel.start()
conn = pymysql.connect(host='127.0.0.1', user=DB_UNAME, passwd=DB_PASS, port=tunnel.local_bind_port)
data = pd.read_sql_query("SHOW DATABASES;", conn)

credits to https://www.reddit.com/r/learnpython/comments/53wph1/connecting_to_a_mysql_database_in_a_python_script/

edited Feb 26 '20 at 13:46

answered Feb 26 '20 at 12:45

Hemanth Sharma

309
3
6

Approach with starting tunnel worked way better for me than these using 'with' statement. It somehow solved my problem: https://stackoverflow.com/questions/63774543/why-can-i-connect-to-mysql-through-shell-but-cant-do-it-through-python – Manaslu Sep 07 '20 at 11:19
I don't know why, but here only worked using this way too. Using `with` statement seems not to work for me. – igorkf Oct 05 '20 at 14:07
Using {with} initiates tunnel.close(), which for some reason hangs. Using tunnel.stop() and then tunnel.close() seems to circumvent the issue. – Negative Correlation Oct 23 '20 at 00:44

score 6 · Answer 4 · answered Mar 22 '19 at 19:59

If your private key file is encrypted, this is what worked for me:

    mypkey = paramiko.RSAKey.from_private_key_file(<<file location>>, password='password')
    sql_hostname = 'sql_hostname'
    sql_username = 'sql_username'
    sql_password = 'sql_password'
    sql_main_database = 'sql_main_database'
    sql_port = 3306
    ssh_host = 'ssh_host'
    ssh_user = 'ssh_user'
    ssh_port = 22


    with SSHTunnelForwarder(
            (ssh_host, ssh_port),
            ssh_username=ssh_user,
            ssh_pkey=mypkey,
            ssh_password='ssh_password',
            remote_bind_address=(sql_hostname, sql_port)) as tunnel:
        conn = pymysql.connect(host='localhost', 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)
        print(data)
        conn.close()

score 5 · Answer 5 · answered Jan 01 '20 at 02:00

You may only write the path to the private key file: ssh_pkey='/home/userName/.ssh/id_ed25519' (documentation is here: https://sshtunnel.readthedocs.io/en/latest/).

If you use mysql.connector from Oracle you must use a construction cnx = mysql.connector.MySQLConnection(... Important: a construction cnx = mysql.connector.connect(... does not work via an SSh! It is a bug. (The documentation is here: https://dev.mysql.com/doc/connector-python/en/connector-python-connectargs.html).

Also, your SQL statement must be ideal. In case of an error on SQL server side, you do not receive an error message from SQL-server.

import sshtunnel
import numpy as np

with sshtunnel.SSHTunnelForwarder(ssh_address_or_host='ssh_host',
                                  ssh_username="ssh_username",
                                  ssh_pkey='/home/userName/.ssh/id_ed25519',
                                  remote_bind_address=('localhost', 3306),
                                  ) as tunnel:
    cnx = mysql.connector.MySQLConnection(user='sql_username',
                                          password='sql_password',
                                          host='127.0.0.1',
                                          database='db_name',
                                          port=tunnel.local_bind_port)
    cursor = cnx.cursor()
    cursor.execute('SELECT * FROM db_name.tableName;')
    arr = np.array(cursor.fetchall())
    cursor.close()
    cnx.close()

Could you provide supporting reference on the bug? I had .connect() work in an earlier version but no longer, so this is a helpful clue. — Mark Andersen, Jun 06 '20 at 14:58

score 1 · Answer 6 · answered Jun 29 '20 at 15:17

This works for me:

import mysql.connector
import sshtunnel

with sshtunnel.SSHTunnelForwarder(
    ('ip-of-ssh-server', 'port-in-number-format'),
    ssh_username = 'ssh-username',
    ssh_password = 'ssh-password',
    remote_bind_address = ('127.0.0.1', 3306)
) as tunnel:
    connection = mysql.connector.connect(
        user = 'database-username',
        password = 'database-password',
        host = '127.0.0.1',
        port = tunnel.local_bind_port,
        database = 'databasename',
    )
    mycursor = connection.cursor()
    query = "SELECT * FROM datos"
    mycursor.execute(query)

score 0 · Answer 7 · edited Dec 02 '22 at 14:11

Someone said this in another comment. If you use the python mysql.connector from Oracle then you must use a construction cnx = mysql.connector.MySQLConnection(....

Important: a construction cnx = mysql.connector.connect(... does not work via an SSH! This bug cost me a whole day trying to understand why connections were being dropped by the remote server:

with sshtunnel.SSHTunnelForwarder(
        (ssh_host,ssh_port),
        ssh_username=ssh_username,
        ssh_pkey=ssh_pkey,
        remote_bind_address=(sql_host, sql_port)) as tunnel:
    connection = mysql.connector.MySQLConnection(
        host='127.0.0.1',
        port=tunnel.local_bind_port,
        user=sql_username,
        password=sql_password)
    query = 'select version();'
    data = pd.read_sql_query(query, connection)
    print(data)
    connection.close()

score 0 · Answer 8 · answered Jan 21 '23 at 06:22

If you are using python, and all the username, password, host and port are correct then there is just one thing left, that is using the argument (use_pure=True). This argument uses python to parse the details and password. You can see the doc of mysql.connector.connect() arguments.

with sshtunnel.SSHTunnelForwarder(
    (ssh_host,ssh_port),
    ssh_username=ssh_username,
    ssh_pkey=ssh_pkey,
    remote_bind_address=(sql_host, sql_port)) as tunnel:
connection = mysql.connector.MySQLConnection(
    host='127.0.0.1',
    port=tunnel.local_bind_port,
    user=sql_username,
    password=sql_password,
    use_pure='True')
query = 'select version();'
data = pd.read_sql_query(query, connection)
print(data)
connection. Close()

score -1 · Answer 9 · edited May 23 '17 at 11:54

-1

Paramiko is the best python module to do ssh tunneling. Check out the code here: https://github.com/paramiko/paramiko/blob/master/demos/forward.py

As said in comments this one works perfect. SSH Tunnel for Python MySQLdb connection

edited May 23 '17 at 11:54

Community

1
1

answered May 30 '14 at 17:02

yeaske

1,352
14
21

score -1 · Answer 10 · answered Jun 24 '20 at 07:11

Best practice is to parameterize the connection variables. Here is how I have implemented. Works like charm!

import mysql.connector
import sshtunnel
import pandas as pd
import configparser

config = configparser.ConfigParser()
config.read('c:/work/tmf/data_model/tools/config.ini')

ssh_host = config['db_qa01']['SSH_HOST']
ssh_port = int(config['db_qa01']['SSH_PORT'])
ssh_username = config['db_qa01']['SSH_USER']
ssh_pkey = config['db_qa01']['SSH_PKEY']
sql_host = config['db_qa01']['HOST']
sql_port = int(config['db_qa01']['PORT'])
sql_username = config['db_qa01']['USER']
sql_password = config['db_qa01']['PASSWORD']

with sshtunnel.SSHTunnelForwarder(
        (ssh_host,ssh_port),
        ssh_username=ssh_username,
        ssh_pkey=ssh_pkey,
        remote_bind_address=(sql_host, sql_port)) as tunnel:
    connection = mysql.connector.connect(
        host='127.0.0.1',
        port=tunnel.local_bind_port,
        user=sql_username,
        password=sql_password)
    query = 'select version();'
    data = pd.read_sql_query(query, connection)
    print(data)
    connection.close()

Enable Python to Connect to MySQL via SSH Tunnelling

10 Answers10

Linked

Related