How to Securely Connect Python to a MySQL Server Using SSH Tunneling and Key Pairs on the Same Machine?

Connecting Python to MySQL via SSH Tunnelling

Introduction

Establishing secure connections between Python and MySQL servers is essential for data exchange and management. By utilizing SSH tunnelling, we can enhance the security of these connections by creating an encrypted channel between the client and server. This article explores how to establish such a tunnelling connection using Python.

Question

How can we enable Python to connect to a MySQL server through an SSH tunnel, facilitated by SSH key pairs, with the SSH tunnel host and the MySQL server on the same machine?

Answer

Utilizing SSH TunnelForwarder

The following approach utilizes the sshtunnel library to establish the tunnel and then connects to MySQL using pymysql:

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

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'

# Create the SSH tunnel
with SSHTunnelForwarder(
        (ssh_host, ssh_port),
        ssh_username=ssh_user,
        ssh_pkey=mypkey,
        remote_bind_address=(sql_hostname, sql_port)) as tunnel:

    # Connect to MySQL
    conn = pymysql.connect(host='127.0.0.1', user=sql_username,
            passwd=sql_password, db=sql_main_database,
            port=tunnel.local_bind_port)

    # Execute a query
    query = '''SELECT VERSION();'''
    data = pd.read_sql_query(query, conn)

    # Close the connection
    conn.close()

Key Components

• SSH Tunnel Forwarder: This library manages the SSH connection and establishes a tunnel between the client and the SSH server.
• Host Key Pairs: These keys are used to authenticate the SSH connection and ensure secure communication.
• SSHTunnelForwarder.remote_bind_address: Specifies where the tunnel will connect to the MySQL server.
• PyMySQL: Used to establish a MySQL connection using the tunnel's local bind port.

The above is the detailed content of How to Securely Connect Python to a MySQL Server Using SSH Tunneling and Key Pairs on the Same Machine?. For more information, please follow other related articles on the PHP Chinese website!