带跳转主机和远程数据库的SSH隧道转发

Question

我有一个远程MySQL数据库托管在Amazon RDS（“D”）上。出于安全目的，它只能通过远程服务器（“C”）访问。C可以通过一个跳转主机“B”通过ssh访问。我需要一个双ssh隧道来访问远程SQL主机。

[A: local host] -> [B: jump host] -> [C: target host] => [D: RDS MySQL host]

我想通过Python访问D，使用paramiko和/或sshtunnel。我能找到的所有信息包括：

• 单个ssh隧道和远程SQL主机（例如A->C=>D，无跳转主机）
  • 在Python中首先使用mysqldb ssh
  • Python mysql通过SSH连接
  null

  import paramiko
  
  ssh = paramiko.SSHClient()
  ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
  proxy = paramiko.ProxyCommand("ssh -A B_username@B_host -W C_host:12345")
  ssh.connect("C_host", username="C_username", sock=proxy)
  
  stdin, stdout, stderr = ssh.exec_command("mysql -u D_username -p D_password -h D_host_rds")
  print("STDOUT:\n{}\n\nSTDERR:\n{}\n".format(stdout.read(), stderr.read()))
  # successfully prints out MySQL welcome screen
  

  我正在寻找这样的东西（从sshtunnel文档中的示例2修改）：

  import paramiko
  from sshtunnel import SSHTunnelForwarder
  
  with SSHTunnelForwarder(
      intermediate = {
          ("B_host", 22),
          ssh_username = "B_username",
          ssh_password = "B_password")},
      remote = {
          ("C_host", 12345),
          ssh_username = "C_username",
          ssh_password = "C_password")},
      remote_bind_address=("D_host_rds", 3306),
      local_bind_address=("0.0.0.0", 3307)) as server:
  
      conn = MySQLdb.connect(
          user = "D_username",
          passwd = "D_password",
          db = "my_database",
          host = "127.0.0.1",
          port = 3307)
  

  TL；DR：在Python中，我如何通过两个ssh跳转来转发端口？

Answer 1

我想通了。它结合使用ssh配置设置和sshtunnel库中的SSHTunnelForwarder上下文管理器。

使用以下模型和命名约定：

[A:本地主机]->[B:跳转主机]->[C:目标主机]=>[D:RDS MySQL主机]

Host C_ssh_shortcut
    HostName C_host
    User C_user
    Port 22
    ForwardAgent yes
    ProxyCommand ssh B_user@B_host -W %h:%p

ssh-add

import sqlalchemy
from sshtunnel import SSHTunnelForwarder

with SSHTunnelForwarder(
    "C_ssh_shortcut",                     # The SSHTunnelForwarder "ssh_address_or_host" argument, which takes care of bypassing B through the ProxyCommand set up in ~/.ssh/config
    remote_bind_address=(D_host, 3306),   # Points to your desired destination, ie. database host on 3306, which is the MySQL port
    local_bind_address=('', 1111)         # Gives a local way to access this host and port on your machine. '' is localhost / 127.0.0.1, 1111 is an unused port
) as server:
    connection_string = "mysql+pymysql://D_user:D_password@localhost:1111/D_dbname"  # note that D_host and D_port were replaced by the host and port defined in "local_bind_address"
    engine = sqlalchemy.create_engine(connection_string)
    # do your thing