R SSH 隧道 MySQL [英] R SSH Tunnel MySQL

问题描述

我正在寻找一种方法，允许我使用 SSH 隧道连接到 R 中的 MySQL 服务器(而不是文件)；我假设它需要 RCurl 和 RODBC 的组合，但我似乎无法让它正常工作.

I'm looking for a way that will allow me to use an SSH Tunnel to connect to a MySQL Server (as opposed to a file) within R; I'm assuming it'll require a combination of RCurl and RODBC, but I can't seem to get it to work properly.

我看到了这篇文章和 这篇文章 谈论使用 SSH 连接到特定文件或表，但我希望将其用作 Shiny 应用程序的一部分，该应用程序将根据用户的输入执行不同的 SQL 查询，这需要连接到服务器与特定文件相反.

I came across this post and this post that talk about utilizing SSH to connect to specific files or tables, but I'm hoping to use it as part of a Shiny app that will execute different SQL queries based on input from the user, which would require connecting into the server as opposed to specific files.

我假设代码看起来像这些行 x = scp("remote.ssh.host.com", "/home/dir/file.txt", "My.SCP.Passphrase", user="username")，但是我会用 odbcConnect() 语句替换 "/home/dir/file.txt" 部分还是替换它带有我要访问的特定数据库的端口号吗?

I'm assuming the code would look something along these lines x = scp("remote.ssh.host.com", "/home/dir/file.txt", "My.SCP.Passphrase", user="username"), but would I replace the "/home/dir/file.txt" piece with an odbcConnect() statement or replace it with the port number for the specific database I want to access?

我用于常规 odbcConnect() 的行是 odbcConnect(dsn, uid = "userid", pwd = "password").部分问题是，我正在 Windows 上开发它，但它将部署到 Linux 服务器(由其他人处理)，所以我正在努力弄清楚我的 server.R 代码中到底需要使用什么连接到数据库.

The line I use for a regular odbcConnect() is odbcConnect(dsn, uid = "userid", pwd = "password"). Part of the problem is, I am developing it on Windows, but it will be deployed to a Linux server (handled be someone else) so I'm struggling to figure out what exactly will need to be used in my server.R code for connecting to the database.

推荐答案

好的，所以要在 Windows 上进行测试，要么使用 Cygwin，要么安装 OpenSSH 这样您就可以在 Windows 中从命令行运行 ssh，就像在 Linux 中一样.

Okay, so to test this on Windows, either grab Cygwin, or install OpenSSH so you can run ssh from the command line in Windows, like you would do in Linux.

在 Windows 机器上运行 ssh 后，首先尝试通过 SSH 建立隧道.从命令行运行:

Once you have ssh running on your Windows box, then try first making a tunnel through SSH. Run this from the command line:

ssh -f <server_user>@<server_ip>  -L <unused_local_port>:localhost:<database_remote_port> -N 

显然，用适当的信息替换<>"中的所有内容.它会询问密码，并记住这不是数据库密码，而是服务器本身的密码.值得注意的是，server_ip 不必是带有数据库的服务器，只要是在适当子网内并运行 SSH 服务器的任何服务器，这几乎是所有 Linux 机器.

Obviously, replace everything in '<>' with the appropriate information. It will ask for the password, and remember that this isn't the database password, but the password to the server itself. Notably, the server_ip doesn't have to be the server with the database on it, just any server that is inside the proper subnet and that runs an SSH server, which is pretty much all Linux machines.

现在，设置 ODBC 连接，除了使 IP localhost 和端口 unused_local_port.现在，尝试在 R 中连接到新的 ODBC 连接.如果成功，您就成功了.

Now, setup an ODBC connection, except make the IP localhost, and the port unused_local_port. Now, try connecting to your new ODBC connection in R. If this works you're halfway there.

下一个问题是密码，因为您必须输入密码才能通过 SSH 连接，但在 R 中，您将无法在简单的 system 命令后输入密码.所以你必须设置一些公共/私人 rsa 密钥对.值得注意的是，这将使任何有权访问您的用户/密码的人现在都可以自动访问您的服务器，所以要小心.首先，生成一个 SSH 密钥:

The next problem is the password, because you will have to enter a password to connect via SSH, but in R you won't be able to input it after a simple system command. So you have to setup some a public/private rsa key pair. Notably, this will make it so that anyone with access to your user/pass on your Windows box will now have automatic access to your server, so be careful. First, generate a SSH key:

ssh-keygen -t rsa

不要设置密码，并将其保存在默认位置.现在，在远程主机上为您的公钥创建目录，并将您的公钥放在那里.

Don't make a passphrase, and save it in the default location. Now, create the directory for your public key on the remote host, and drop your public key in there.

# This creates a directory on the other machine if it wasn't already there. (Type in your password on the remote machine)
ssh <server_user>@<server_ip> mkdir -p .ssh
# This adds your public key to the list of accepted ones:
cat ~/.ssh/id_rsa.pub | ssh <server_user>@<server_ip> 'cat >> .ssh/authorized_keys'

现在尝试从命令行再次创建隧道:

Now try creating your tunnel again from the command line:

 ssh -f <server_user>@<server_ip>  -L <unused_local_port>:localhost:<database_remote_port> -N 

如果它没有要求您输入密码，则您已成功创建了密钥对.现在您已准备好从命令行运行 ssh 命令.但在此之前，请尝试终止您的 ssh 命令，这样您就可以确保 R 确实在创建隧道，而不仅仅是重用旧隧道.您可以通过 Windows 任务管理器 (Ctrl+Alt+Esc) 来完成，只需右键单击并结束处理 ssh.exe.

If it doesn't ask you for the password, you have succeeded in creating your keypair. Now you are ready to run your ssh command from the command line. But before you do that, try and kill your ssh command, so you can make sure that R is actually creating the tunnel, and you aren't just reusing an old one. You can do it through Windows Task Manager (Ctrl+Alt+Esc), and just right click and End Process the ssh.exe.

所以，只需运行:

system('ssh -f <server_user>@<server_ip>  -L <unused_local_port>:localhost:<database_remote_port> -N')

然后连接到新的隧道 ODBC 连接.

And then connect to your new tunneled ODBC connection.

这篇关于R SSH 隧道 MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！