如何将Python MySQL SSH端口转发方案转换为R(dbplyr)实现

以下两种方案都可以实现和你提供的Python代码完全等效的效果，按需选择即可。

方案1：R会话内自动管理SSH隧道（和Python sshtunnel逻辑1:1对应） #

不需要提前操作终端，隧道的创建、销毁全在R代码里完成，和现有Python实现的流程完全一致。

依赖包安装 #

先安装需要的包：

install.packages(c("ssh", "DBI", "RMariaDB", "dplyr", "dbplyr"))

注：RMariaDB是目前维护最活跃的MySQL/MariaDB R驱动，兼容性远好于旧版RMySQL，推荐优先使用。

完整可运行代码 #

# 加载包
library(ssh)
library(DBI)
library(RMariaDB)
library(dplyr)
library(dbplyr)

# 配置参数，和Python代码里的参数一一对应
pkey_path <- "~/.ssh/id_ed25519"  # Ed25519私钥路径
ssh_config <- list(
  host = "jumphost.mycompany.com",
  port = 22,
  user = "me"
)
mysql_config <- list(
  host = "mycompany.com",
  port = 3306,
  user = "me",
  password = "***",
  dbname = "my_db"
)

# 创建SSH端口转发隧道
tunnel <- ssh_tunnel(
  host = ssh_config$host,
  port = ssh_config$port,
  user = ssh_config$user,
  keyfile = pkey_path,
  target = paste0(mysql_config$host, ":", mysql_config$port)
)
local_bind_port <- tunnel$port # 等效于Python里的tunnel.local_bind_port

# 连接本地转发端口的MySQL服务
con <- dbConnect(
  MariaDB(),
  host = "127.0.0.1",
  port = local_bind_port,
  user = mysql_config$user,
  password = mysql_config$password,
  dbname = mysql_config$dbname
)

# 测试查询，对应Python里查版本的逻辑
dbGetQuery(con, "SELECT VERSION();")

# ------------ dbplyr工作流示例 ------------
# 直接映射库中的表，用dplyr语法写查询，不用手写SQL
# my_table <- tbl(con, "你的表名")
# result <- my_table |>
#   filter(时间 >= "2024-01-01") |>
#   group_by(分类) |>
#   summarise(总金额 = sum(金额)) |>
#   collect() # collect()把查询结果拉到本地存成tibble

# 用完记得关闭连接和隧道，避免端口占用
dbDisconnect(con)
ssh_disconnect(tunnel$session)

如果不想每次手动写关闭逻辑，可以用on.exit封装成自动清理的函数，即使查询报错也会自动释放资源：

query_mysql <- function(sql) {
  # 建立隧道和连接
  tunnel <- ssh_tunnel(
    host = ssh_config$host, port = ssh_config$port, user = ssh_config$user,
    keyfile = pkey_path, target = paste0(mysql_config$host, ":", mysql_config$port)
  )
  con <- dbConnect(
    MariaDB(), host = "127.0.0.1", port = tunnel$port,
    user = mysql_config$user, password = mysql_config$password, dbname = mysql_config$dbname
  )
  # 函数退出时自动清理资源
  on.exit({
    dbDisconnect(con)
    ssh_disconnect(tunnel$session)
  }, add = TRUE)
  # 执行查询返回结果
  dbGetQuery(con, sql)
}

# 直接调用即可，不需要手动管理连接
query_mysql("SELECT VERSION();")

方案2：系统终端手动开SSH隧道（更轻量） #

如果不想在R里维护隧道进程，可以直接在系统终端执行SSH端口转发命令，长期开着隧道做分析的时候更方便：

# 命令格式：ssh -L 本地端口:MySQL服务内网地址:MySQL端口 跳板机用户名@跳板机地址 -i 私钥路径 -N
ssh -L 3307:mycompany.com:3306 me@jumphost.mycompany.com -i ~/.ssh/id_ed25519 -N

命令执行后不要关终端窗口，隧道就会一直保持运行。此时R里直接连本地的3307端口即可，不需要额外的隧道相关代码：

library(DBI)
library(RMariaDB)
library(dplyr)
library(dbplyr)

con <- dbConnect(
  MariaDB(),
  host = "127.0.0.1",
  port = 3307, # 和上面-L参数里的本地端口保持一致
  user = "me",
  password = "***",
  dbname = "my_db"
)

# 后续所有dbplyr操作和普通本地数据库连接完全一致

注意事项 #

• 如果你的私钥设置了密码短语，第一次建立连接时会弹出输入提示，输入正确即可正常连接
• 如果连接报错，可以先检查跳板机是否能正常SSH登录、私钥权限是否正确（本地私钥权限需要设为600）
• 用dbplyr操作时，记得最后加collect()才会把远端数据拉取到本地内存，否则只是生成查询SQL不会实际执行

内容的提问来源于stack exchange，提问作者user2579689