Accessing MySQL database through a SSH Tunnel

A SSH tunnel links a local port to a port on a remote host. Communication between local and remote host is passed through SSH Tunnel to the remote port. The added benefit of this setup is that the communications between your local machine and the remote host is encrypted by the SSH connection.

In this article, we will use Python script to access data from a remote host.

Prerequisites

• pymysql
• paramiko
• sshtunnel
• private_key_file – for logging into the remote database
• credentials – ssh_username, ssh_hostname, ssh_port , sql_username, sql_password, sql_database_name

Create a Python module for getting data from remote database

You may store all your connection and credentials details as variables inside the module. Write a method to access the database over the SSH tunnel and execute the SQL query.

"""
This module contains various helper methods for interacting with database.
"""
import logging
import logging.config
import pymysql
import paramiko
from paramiko import SSHClient
from sshtunnel import SSHTunnelForwarder

# Load the private key for SSH
mypkey = paramiko.RSAKey.from_private_key_file('remote-database-private-key-file')
# ssh variables
ssh_host = 'remote-ip-acddress'
ssh_port = 22
localhost = '127.0.0.1'
localport = 9990
ssh_user = 'remote-user'

# database variables
sql_username='database-user'
sql_password='database-password'
sql_main_database='database'

def dbQuery(q):
    """ access the database over the SSH tunnel and execute the query """
    logger = get_logger()
    logger.debug("Connecting to reporting server...", end="")
    with SSHTunnelForwarder(
        (ssh_host, ssh_port),
        ssh_username=ssh_user,
        ssh_pkey=mypkey,
        remote_bind_address=(localhost, 3306)) as tunnel:
        logger.debug("Connected")
        logger.debug("Connecting to database...")
        try:
            conn = pymysql.connect(
                host='127.0.0.1', 
                user=sql_username,
                passwd=sql_password,
                db=sql_main_database,
                port=tunnel.local_bind_port,
                cursorclass=pymysql.cursors.DictCursor)
            logger.debug("Connected")
            logger.debug("Executing query...")
            cursor = conn.cursor()
            cursor.execute(q)
            data = cursor.fetchall()
            logger.debug("Done")
        finally:
            conn.close()
    return data

Call the module in your script

A script below shows how to call the module to get current date from the remote database:

"""
Get current date from a remote database (MariaDB) 
""
from <folder-where-module-is-located> import <moduled-name>

# these are the queries to get data for

TodayDateQuery = """
select CURDATE() 
"""
def main():
    """ Main entry point of the app """
    print("get_current date")
    dates = <module-folder-name>.dbQuery(TodayDateQuery)
    print(dates)