ssh port forward to mysql from R

Question

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

ssh -L 3306:localhost:3306 me@jumphost.mycompany.com

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)

https://rmariadb.r-dbi.org/ Why do you connect over ssh to the DB? Is port 3306 on jumphost.mycompany.com blocked by the firewall? If yes, do the ssh tunnel and then use localhost on the DBI client. — danlooo, Jun 12 '22 at 15:00
hi @danlooo - correct, the MySQL server is behind firewall. I need to go through the ssh jumphost server to get to it. If i try to connect using DBI client, I get this error: Error: Failed to connect: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) — user2579689, Jun 12 '22 at 15:16
Please show how you are *connecting to ssh in Terminal*. Why don't you use same host `mysql.mycompany.com`? — Parfait, Jun 12 '22 at 16:25
@Parfait - I connect to ssh in Mac Terminal by typing: ssh -L 3306:localhost:3306 me@jumphost.mycompany.com. I can then connect to with mysql mysql -h mysql.mycompany.com -ume -pmy_password . i don't know how to get this to work in a R session/script. — user2579689, Jun 12 '22 at 17:59
How are you running your R script? Inside same Mac terminal or elsewhere like RStudio? — Parfait, Jun 12 '22 at 21:32

score 2 · Answer 1 · answered Jun 12 '22 at 16:14

2

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.

answered Jun 12 '22 at 16:14

Bill Karwin

538,548
86
673
828

Thanks for your suggestion. When i change localhost to 127.0.0.1, I get a new error: Error: Failed to connect: Lost connection to MySQL server at 'handshake: reading initial communication packet', system error: 60 – user2579689 Jun 12 '22 at 17:56
Well, at least now it's not trying to connect to localhost. Try using the same hostname argument you gave to your mysql client command. In your example it's `mysql.mycompany.com`, but I suppose that is not the real hostname, it's just an example you used for this post. – Bill Karwin Jun 12 '22 at 18:05
correct, i've obscured my company's server name. Here's what I get adding the actual hostname: Error: Failed to connect: Can't connect to MySQL server on 'mysql.mycompany.com' (60) – user2579689 Jun 12 '22 at 18:10
Wait -- did you change localhost in your ssh command or in the dbConnect() call? I assume MySQL is running on your jump host, so you should not change the ssh tunnel command. Only change localhost to 127.0.0.1 or mysql.mycompany.com in the dbConnect() call. – Bill Karwin Jun 12 '22 at 18:15
i only changed this part (from localhost and 127..0.0.1) DBI::dbConnect(RMariaDB::MariaDB(), host = "mysql.mycompany.com" . We have separate servers for the jumphost and mysql. – user2579689 Jun 12 '22 at 18:22
i asked about a variation on this same underlying issue in a separate post, where i received good information, but still get similar errors to what i've mentioned above. I thought this example in this post would be simpler and easier to explain, but i'm questioning that now. Previous post: https://stackoverflow.com/questions/72511654/translate-python-mysql-ssh-port-forwarding-solution-to-r-dbplyr – user2579689 Jun 12 '22 at 18:26
If mysql is not running on the jump host, then why do you use localhost in the ssh tunnel? It should be like `ssh -L 3306:mysql.mycompany.com:3306 me@jumphost.mycompany.com`. If the tunnel tries to map the terminus of the tunnel to port 3306 on the jump host, it won't work. You want the terminus of the tunnel to open port 3306 on another host within your remote network. So it must be a hostname that can be resolved on your jump host. – Bill Karwin Jun 12 '22 at 18:35
re: "why do you use localhost in the ssh tunnel?" - I'm only using this because it works (in terminal) :-) - really just pasted it in from my company's documentation. Sorry to not be able to give you better information about our configuration. – user2579689 Jun 12 '22 at 18:56
Some part of your description doesn't add up. If your ssh tunnel goes to to `3306:localhost:3306`, the only way that could work is if something (e.g. mysqld) is running on the jump host, listening on port 3306. – Bill Karwin Jun 12 '22 at 19:52

score 1 · Answer 2 · answered Jun 12 '22 at 21:53

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 me@jumphost.mycompany.com

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 me@jumphost.mycompany.com

R script (using system)

library(DBI)
library(RMariaDB)

# COMMAND LINE INTERFACE CALL
system("ssh -L 3306:localhost:3306 me@jumphost.mycompany.com")

# 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 me@jumphost.mycompany.com")

thank you @parfait. I think it's close to working, but i'm stuck after running: system("ssh -L 3306:localhost:3306 me@jumphost.mycompany.com"). I get this message "Pseudo-terminal will not be allocated because stdin is not a terminal." and am not able to run subsequent lines (i.e. mysql connection) in the script. I tried adding "wait = FALSE" param to the system call, but then i get the same error: Error: Failed to connect: Can't connect to MySQL server on '"mysql.mycompany.com' (60) — user2579689, Jun 12 '22 at 23:21
See this [post](https://stackoverflow.com/q/7114990/1422451) to try the `-tt` or `-T` arguments. How did the command line version work where you set up R script without any `system` calls? — Parfait, Jun 13 '22 at 15:52

ssh port forward to mysql from R

2 Answers2