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.
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()
sql_ip appears to be unnecessary and unused. But this example works! (I used MySQLdb)
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)
...
_remote_bind_address = '127.0.0.1' _local_bind_address = '0.0.0.0'
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)
with statement seems not to work for me.
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()
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()
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()
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()
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)
For everyone having issues with using SSHTunnelForwarder as a context manager, it's very likely you're letting that context end before using the database connection. This is how you can encapsulate both SSHTunnelForwarder and pymysql.connect in a new context manager:
(likewise for MySQLdb.connect, mysql.connector.connect or any other package following the Python Database API Specification)
from contextlib import contextmanager
import pymysql
from sshtunnel import SSHTunnelForwarder
@contextmanager
def connect():
tunnel = SSHTunnelForwarder(
("ssh_host", 9922),
ssh_password="ssh_password",
ssh_username="ssh_username",
remote_bind_address=("127.0.0.1", 3306),
)
tunnel.start()
try:
conn = pymysql.connect(
host="127.0.0.1",
user="user",
password="password",
database="database",
port=tunnel.local_bind_port,
)
try:
yield conn
finally:
conn.close()
finally:
tunnel.stop()
# ... then elsewhere you can do ...
with connect() as conn, conn.cursor() as cur:
cur.execute("SELECT VERSION()")
print(cur.fetchone())
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
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()
