How to Securely Connect Python to a Remote MySQL Server via SSH Tunneling?

Connecting Python to MySQL via SSH Tunnelling

Establishing a connection between Python and a remote MySQL server through an SSH tunnel is essential for secure data retrieval and manipulation. While basic MySQL connections are straightforward, creating an encrypted SSH tunnel adds an extra layer of security.

Here's how to enable Python to connect to MySQL via an SSH tunnel:

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)

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

This code establishes an SSH tunnel between the local machine and the remote MySQL server using SSHTunnelForwarder. The SSH connection utilizes a private key for authentication, ensuring enhanced security.

Once the tunnel is established, a Python MySQL connection is made to the local address of the tunnel, allowing for secure access to the MySQL database. Queries can then be executed to retrieve or manipulate data from the server.

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