Accessing a MySQL database in R (RStudio) after tunneling via SSH

Question

I have the following problem.

Background:

I want to connect to a MySQL database and download tables into R.
The database (the MySQL prompt) can only be accessed via SSH tunnel to another server.
I am using Mac OSX El Capitan on a MacBook Pro (2015).

Problem:

From the 'terminal', I can easily SSH to the server, and thereon enter the MySQL prompt and run the queries I need.

I cannot, however, perform the latter tasks from within RStudio.

The code I tried is:

> system('ssh -f <server_user>@<server_ip> -N sleep 20')       
> db <- dbConnect(MySQL(), host="hostname", user="username", pwd="password", dbname="databasename", port=3306)
> sql1 <- paste("SELECT * FROM databasename.tablename", sep="")
> results <- dbGetQuery(con, sql1)
> dbDisconnect(con)

While I can see from ps -A | grep ssh that the ssh process is running (for the 20 seconds I want it to), and in R the command executed normally (not waiting for more input), I cannot connect to the relevant database. The error message was something to effect of "could not connect", and RStudio hangs for at least a minute before the error appears.

The questions:

How does one (1) Use SSH in conjunction with (2) MySQL on the remote server to (3) load tables directly into an R data frame?

Thanks.

Jim Garrison · Accepted Answer · 2016-03-01 21:12:15Z

3

You should use something which is called port forwarding. Some details are here (https://help.ubuntu.com/community/SSH/OpenSSH/PortForwarding) For example, say you wanted to connect from your laptop to http://www.ubuntuforums.org using an SSH tunnel. You would use source port number 8080 (the alternate http port), destination port 80 (the http port), and destination server www.ubuntuforums.org. :

ssh -L 8080:www.ubuntuforums.org:80 <host>

Where <host> should be replaced by the name of your laptop.

This is done for whole computer so you dont need to do this from r studio.

Offcourse you need to forward your port to 3036. But you need special privilige on the server. Because on most hosting you can only connect from localhost (for example from php)

edited Mar 1, 2016 at 21:12

Jim Garrison

87k20 gold badges162 silver badges197 bronze badges

answered Mar 1, 2016 at 20:16

dabal

4203 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Eben du Toit Over a year ago

Thanks! Does this mean that all MySql commands in R (even the connection strings) would pass through the tunnel?

dabal Over a year ago

Yes, because pory forwarding is done in system layer, so everything what you will send to your local port would be send to remote sever

Eben du Toit Over a year ago

And sorry for the question, but how do I know the correct local port?

dabal Over a year ago

You specify the number of local port. The only rule should be that it should be number over 1024 (those are used by system) and should not be used for other app. If you only have onr db on mysql you can choose 3036 ad well

Eben du Toit Over a year ago

Thanks so much! Such a great answer.

Collectives™ on Stack Overflow

Accessing a MySQL database in R (RStudio) after tunneling via SSH

I have the following problem.

Background:

Problem:

The questions:

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

I have the following problem.

Background:

Problem:

The questions:

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related