通过SSH建立隧道后，在R(RStudio)中访问MySQL数据库 [英] Accessing a MySQL database in R (RStudio) after tunneling via SSH

问题描述

1. 我想连接到MySQL数据库并将表下载到R中.
2. 只能通过SSH隧道访问另一台服务器来访问数据库(MySQL提示符).
3. 我正在MacBook Pro(2015年)上使用Mac OSX El Capitan.

问题:

从终端"，我可以轻松地通过SSH到服务器，然后输入MySQL提示符并运行所需的查询.

Problem:

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

但是，我无法在RStudio中执行后一个任务.

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

我尝试的代码是:

> 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)

虽然我从ps -A | grep ssh可以看到ssh进程正在运行(我希望它运行20秒钟)，并且在R中该命令正常执行(不等待更多输入)，但是我无法连接到相关的数据库.该错误消息是无法连接"的结果，RStudio挂起至少一分钟，然后出现错误.

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.

一个怎么样 (1)结合使用SSH和 (2)将MySQL放在远程服务器上 (3)将表直接加载到R数据帧中吗?

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?

谢谢.

推荐答案

您应该使用称为端口转发的端口.一些详细信息在这里( https://help.ubuntu.com/community/SSH/OpenSSH/PortForwarding ) 例如，假设您想使用SSH隧道从笔记本电脑连接到 http://www.ubuntuforums.org .您将使用源端口号8080(备用http端口)，目标端口80(http端口)和目标服务器www.ubuntuforums.org. :

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>

<host>应替换为笔记本电脑名称的位置.

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

这是为整个计算机完成的，因此您不需要从r studio进行此操作.

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

Offcourse，您需要将端口转发到3036.但是您需要服务器上的特殊特权.因为在大多数主机上，您只能从localhost连接(例如，从php连接)

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)

这篇关于通过SSH建立隧道后，在R(RStudio)中访问MySQL数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！