[Python] SSH tunnel remote connection to MySQL

1. explain

mysql Deployed on a server , But you can't connect to the server directly （ For the sake of safety ）, You need a springboard machine to access , The springboard machine can pass SSH Secret key access . We can use paramiko It's in this bag SSHTunnelForwarder Connect

2. Code

import paramiko
import pymysql
from sshtunnel import SSHTunnelForwarder
def get_tunnel_ssh(config_dict):
private_key = paramiko.RSAKey.from_private_key_file(filename=config_dict.get("ssh_key_path"),
password=config_dict.get("ssh_password"))
ssh_tunnel = SSHTunnelForwarder(ssh_address_or_host=(config_dict.get("ssh_ip"), config_dict.get("ssh_port")),
ssh_username=config_dict.get("ssh_user"),
ssh_pkey=private_key,
remote_bind_address=(config_dict.get("ip"), config_dict.get("port")))
ssh_tunnel.start()
# ssh_tunnel.close()
return ssh_tunnel
def get_mysql_conn():
ssh_config = {

'ip': '47.241.XX.XX',
'port': 3306,
'ssh_ip': '23.248.XX.XX',
'ssh_port': 6666,
'ssh_user': 'pan',
'ssh_password': 'Tcjo5XXXXX',
'ssh_key_path': r"E:\pan\dingding\key",
}
mysql_config = {

"host": "127.0.0.1",
"port": None,
"user": "pan",
"passwd": "&B2dpzXXXXX",
"db": "walletdb3",
"charset": "utf8mb4",
}
tunnel = get_tunnel_ssh(ssh_config)
mysql_config.update({
"port": tunnel.local_bind_port}) # Be careful mysql The port uses the port of the tunnel 
print(mysql_config)
mysql_conn = pymysql.connect(**mysql_config)
return mysql_conn

3. Use

from utils.ssh_mysql import get_mysql_conn
mysql_conn = get_mysql_conn()
cursor = mysql_conn.cursor()
cursor.execute("select * from eth_balance_guarder order by id desc limit 1;")
data = cursor.fetchall()
print(data)
cursor.close()
mysql_conn.close()

About mysql You can refer to
【python】python Connect mysql database （pymysql）