Connect remote database ssh via pool

tidyverse
1

My goal is to get rid of SequelPro to access my remote live-databases and do some queries and analyse them locally.

to access my local dev-databases i use DBI/pool

library(DBI)
library(pool)

pool <- dbPool(
  drv = RMySQL::MySQL(),
  dbname = "dev_playground",
  host = "my_host.at",
  username = "my_user",
  password = "my_password"
)

what i want i something as easy as in sequel pro to remote connect our live database

pool <- dbPool(
  drv = RMySQL::MySQL(),
  dbname = "live_data",
  host = "my_host.at",
  username = "my_user",
  password = "my_password"
  ## missing and on my wishlist:
  ssh_host = "sftp.my_host.at"
  ssh_user = "mr_tunnelrat"
  ssh_password= "supasecret_pass123"
  ssh_port="1234"
)

is there an goldstandard without doing much pipe() and permanently open connections?

2 Likes
2

Can you point me to some docs on connecting to MySQL via an ssh tunnel. It's not something I've heard of before. Does MySQL not support SSL connections out of the box?

3

I believe that the default behavior for MySQL is to not allow remote connections (hence your need to ssh into the server hosting it). I'm able to use MySQL at work and connect directly to remote databases in R the same way as I do through SequelPro (using SSL and not needing ssh). You probably just need to either: A) talk with the DBA/sysadmin/devops team to consider allowing connects to the remote server from a whitelisted IP, for example your office; or B) try using ssh tunneling to forward the mysql port (typically 3306) to your localhost and using that to connect from R.

1 Like
4

Try this :slight_smile:

ssh -L 3306:localhost:3306 username@remoteserver.com -p 17022 -NnT
5

I like the idea daniel is mentioning above. Isn't it possible to add the parameters to pool package to allow for ssh tunneling to dbs that won't accept direction connections. In addition @robsalasco for a work around - i write below and what do you think?
For a shiny app running with the pool library would you recommend tunneling within the script itself before attempting to perform the db connection. Something like this :-

system("nohup ssh -v -f -o StrictHostKeyChecking=no -i ./path/to/id_rsa -N -L 3306:localhost:3306 user@hostname", wait = FALSE, ignore.stdout = TRUE, ignore.stderr = TRUE, input = character(0))

1 Like
6

@hadley for most databases these days accessed by several members over secure tcp/ip you're required to create an ssh tunnel before accessing them. For Postgres details here https://www.postgresql.org/docs/8.2/static/ssh-tunnels.html

Even third party tools for quering the data like sqltabs or popsql have additional parameters that enable you add the details to enable the tunneling as added by Daniel above in his missing part.

1 Like
7

Thanks a lot for the well articulated version of my request.
You describe exactly my problem and my wish for a solution.

1 Like
8

I'm curious to know if there's something like this as of today.