ssh port forward to mysql from R-CodePudding

I'm able to connect to my company's MySQL server via SSH in Terminal

ssh -L 3306:localhost:3306 [email protected]

mysql -h mysql.mycompany.com -ume -pmy_password

I'm struggling to find a way to do this in an R Script. Any suggestions appreciated.

If I try to connect using DBI (after connecting to ssh in Terminal):

con <- DBI::dbConnect(RMariaDB::MariaDB(), 
                      host = "localhost",
                      user = "me",
                      password = "my_password")

I get this error: Error: Failed to connect: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

CodePudding user response：

Use 127.0.0.1 instead of localhost.

If you use localhost, the client does not connect to port 3306 using TCP. It tries to connect to a UNIX socket, which only reaches an instance running on your client host.

I answered the same question about MySQL in the past (Accessing SQL through SSH Tunnel). MariaDB is not MySQL, but in this case they should work the same.

CodePudding user response：

Since you can successfully run the mysql CLI in same session as ssh port forwarding, consider running R at command line as well using its CLI, Rscript, or the shell, R. Alternatively, try running the SSH port forwarding directly inside R code using the command line caller, system.

The challenge you are encountering is the SSH tunneling occurs in a different session to your R session. Your R environment must run in the same session as SSH port forwarding.

Bash Command Line (using Rscript)

ssh -L 3306:localhost:3306 [email protected]

Rscript my_database_connect_script.R    
# OR /path/to/R/installation/bin/Rscript my_database_connect_script.R

ssh -O cancel -L 3306:localhost:3306 [email protected]

R script (using system)

library(DBI)
library(RMariaDB)

# COMMAND LINE INTERFACE CALL
system("ssh -L 3306:localhost:3306 [email protected]")

# OPEN DB CONNECTION
con <- DBI::dbConnect(
    RMariaDB::MariaDB(), 
    host = "mysql.mycompany.com",   # SAME HOST AS SUCCESSFUL mysql CLI
    user = "me",
    password = "my_password"
)

dbGetQuery(con, "SHOW DATABSES")

# CLOSE DB CONNECTION
dbDisconnect(con)

# CANCEL PORT FORWARDING
system("ssh -O cancel -L 3306:localhost:3306 [email protected]")