6

I want to use the RMySQL package to query a database. I usually type ssh website.com in my mac terminal before using dbConnect() and dbGetQuery() in a R script.

My database is behind a firewall and only accessible locally and to the best of my knowledge needs to be ssh'd into.

Is it possible to do the entire process in R?

I've tried system('ssh website.com')) without success

Thanks

Joseph Noirre
  • 387
  • 4
  • 20
  • @Joesph Was my answer helpful to you? – Santosh M. Nov 15 '17 at 21:07
  • 1
    @Santosh No, I needed to establish an SSH connection. Usually I would do it from terminal, but I'm wondering if there's a way to do it within R. – Joseph Noirre Nov 15 '17 at 21:25
  • You can do it in R. Look at my answer. It tells you how to do it in R. – Santosh M. Nov 15 '17 at 21:26
  • @Santosh Sorry I must be missing something here, but without establishing a SSH tunnel first I can't even establish a connection I'm getting `Error in .local(drv, ...) : Failed to connect to database: Error: Can't connect to MySQL server on '127.0.0.1' (57)` – Joseph Noirre Nov 15 '17 at 21:31
  • you don't need connection through ssh. You missed entering server address on host. localhost is for database on local machine – Santosh M. Nov 15 '17 at 21:35
  • For example, assume that your db is hosted on server `140.44.55.66`. So, you would have `host = "140.44.55.66"` in con. You would also need to enter `port`. Usually default `port` for MySQL is 3306 but you should check. – Santosh M. Nov 15 '17 at 21:41
  • I editted my answer. I highly recommend you to go through the documentation. Better document would be on CRAN. Here is a link: https://cran.r-project.org/web/packages/RMySQL/RMySQL.pdf – Santosh M. Nov 15 '17 at 21:46
  • I've also tried the MySQL host without success. – Joseph Noirre Nov 16 '17 at 14:26
  • your host must be server address as I told you. And port need to be MySQL port. – Santosh M. Nov 16 '17 at 15:38
  • 1
    The following link might help https://stackoverflow.com/questions/35731200/accessing-a-mysql-database-in-r-rstudio-after-tunneling-via-ssh – caot Nov 24 '17 at 15:00

1 Answers1

2

You need to establish a port forward between your system and the ssh server.

Add the following to your ~/.ssh/config file:

Host mysql-tunnel-website.com
  LocalForward 3306 localhost:3306

I highly recommend SSH key usage for this. Github's SSH key guide is pretty good. I'm not an R coder, but R might dislike the need to enter a password interactively in a system() call and SSH keys (when passwordless or when added to an ssh-agent) remove that need.

Now you should be able to start up the tunnel in R with:

system('ssh -f mysql-tunnel-website.com')

This will map website.com's localhost port 3306 to your ssh client's localhost on port 3306, allowing you to run the same code on your ssh client system as you would have on the remote website.com system.

Your R code needs to point to host=localhost and port=3306 (which should be the default).

If the remote SQL server isn't served by website.com's localhost with port 3306, simply change the localhost:3306 to the appropriate server:port combination relative to what is accessible from website.com. If you're unable to use port 3306 on your ssh client system (perhaps you're running MySQL locally?), you can forward to a different port by changing that first 3306 to any other port (I tend to prefix a digit like 13306) and then be sure to specify that alternate port (e.g. port=13306) in your R code.

Adam Katz
  • 14,455
  • 5
  • 68
  • 83