R: MySQL connection through bastion directly from R

Question

I want to connect to MySQL database through bastion host.

I followed some previous StackOverflow answers and I am able to establish connection through the terminal (in RStudio) :

ssh -i <path_to_key_file> <username>@<bastion_host> -L 3306:<db_host>:3306

Then from R:

library(RMySQL)
m <- dbDriver("MySQL")

con <- dbConnect(
  m,
  user='<db_username>',
  password='<db_password>',
  host='127.0.0.1',
  dbname='<db_name>',
  port=3306
)

dbGetQuery(con, "SHOW TABLES;")
dbDisconnect(con)

But now, how can I establish connection directly from the R code, so I don't have to ssh in the terminal? I know library(ssh) might be helpful here but I can't figure out how to make it working.

Thanks!

score 0 · Accepted Answer · answered May 24 '22 at 07:01

Ok, solution adapted from similar post here (that I did not find initially).

library(RMySQL)
library(ssh)
library(sys)

target <- "<db_host>:3306"
ProxySev <- "<username>@<bastion_host>"
keyfile <- "<path_to_key_file>"
port <- 3307

cmd <- paste0('ssh::ssh_tunnel(ssh::ssh_connect(host = "', ProxySev, '", keyfile ="', keyfile, '"), port = ', port, ', target = "', target, '")')
pid <- sys::r_background(
  std_out = FALSE,
  std_err = FALSE,
  args = c("-e", cmd)
)

conn <- dbConnect(
  dbDriver("MySQL"),
  user='<db_username>',
  password='<db_password>',
  host='127.0.0.1',
  dbname='<db_name>',
  port=3307
)  

dbGetQuery(conn, "SHOW TABLES")
dbDisconnect(conn)

R: MySQL connection through bastion directly from R

1 Answers1