Connecting to MySQL on a remote server via SSH (paramiko+pymysql)

Question

Unable to connect to mysql on remote server. Error:

mysql.connector.errors.DatabaseError: 2003 (HY000): Can't connect to MySQL server on 'xxx.hoct.com:3306' (10060)

Connection to the server via ssh occurs. For the SSH tunnel, I chose paramiko, because you can connect only if you disable the algorithms - ['rsa-sha2-512', 'rsa-sha2-256']. I saw many examples of how an ssh tunnel is made through the sshtunnel library, but I did not understand how to disable the algorithms in it.

Most likely, mysql.connector needs to be passed somehow so that the connection occurs through the ssh tunnel. Please help me fix the code.

import mysql.connector  # pip install mysql-connector-python
from paramiko import RSAKey, SSHClient, Transport

ssh_passprase = 'passprase'
ssh_key_path = 'C:\\Users\\dell\\.ssh\\deploy_pk2'
private_key = RSAKey.from_private_key_file(ssh_key_path, password=ssh_passprase)
ssh_host = 'xxx.hoct.com'
ssh_username = 'user'
ssh_port = 22

sql_hostname = 'localhost'
sql_port = 3306
sql_username = 'user'
sql_password = 'password'
sql_main_database = 'db'

transport = Transport((ssh_host, int(ssh_port)))
transport.disabled_algorithms = {'pubkeys': ['rsa-sha2-512', 'rsa-sha2-256']}
transport.connect(username=ssh_username, pkey=private_key)
transport.local_bind_address = (sql_port, sql_port)
transport.open_channel("session", (ssh_host, sql_port), (sql_hostname, sql_port))

print(mysql.connector.__version__)
cnx = mysql.connector.connect(user=sql_username, password=sql_password,
                              host=sql_hostname, port=sql_port,
                              database=sql_main_database,
                              )
cursor = cnx.cursor()
query = "SELECT * FROM price_source"
cursor.execute(query)
for row in cursor:
    print(row)

cnx.close()
transport.close()

Answer 1

As a result, it turned out to solve the problem with code.

import paramiko
import pymysql

ssh_passprase = 'passprase'
ssh_key_path = 'C:\\Users\\dell\\.ssh\\deploy_pk2'
private_key = RSAKey.from_private_key_file(ssh_key_path, password=ssh_passprase)
ssh_host = 'xxx.hoct.com'
ssh_username = 'user'
ssh_port = 22

sql_hostname = 'localhost'
sql_port = 3306
sql_username = 'user'
sql_password = 'password'
sql_main_database = 'db'

client = paramiko.SSHClient()
client.set_missing_host_key_policy(paramiko.AutoAddPolicy())
client.connect(hostname=ssh_host, username=ssh_username, port=ssh_port, look_for_keys=False, allow_agent=False, pkey=private_key,
               password=None, disabled_algorithms=dict(pubkeys=["rsa-sha2-512", "rsa-sha2-256"]))
transport = client.get_transport()
channel = transport.open_channel("direct-tcpip", ('127.0.0.1', sql_port), (sql_hostname, sql_port))
c=pymysql.connect(database=sql_main_database, user=sql_username, password=sql_password, defer_connect=True)
c.connect(channel)

with c.cursor() as cursor:
    cursor.execute('SELECT VERSION()')
    print(cursor.fetchone())