使用SSH软件时,无法通过套接字错误连接到本地MySQL服务器 [英] Can't connect to local MySQL server through socket error when using SSH tunel
问题描述
我正在尝试使用dplyr连接到远程数据库,我通常通过ssh隧道进行查询。
I am trying to use dplyr to connect to a remote database, that I usually query through a ssh tunnel.
我首先设置了一个ssh隧道,如下所示:
I first set up a ssh tunnel like the following:
alias tunnel_ncg='ssh -fNg -L 3307:127.0.0.1:3306 mysqluser@myhost mysql5 -h 127.0.0.1:3306 -P 3307 -u mysqluser -p mypassword'
此时我可以访问数据库连接到本地主机:3307。例如:
At this point I can access the database by connecting to localhost:3307. For example:
mysql -h '127.0.0.1' -P 3307 -u mysqluser
如果我尝试通过dplyr访问同一个数据库,我收到一个错误,抱怨说它无法连接到本地的MySQL套接字: p>
If I try to access the same database through dplyr, I get an error complaining that it can't connect to the local MySQL socket:
> conDplyr = src_mysql(dbname = "mydb", user = "mysqluser", password = "mypassword", host = "localhost", port=3307)
Error in .local(drv, ...) :
Failed to connect to database: Error: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
我的理解是,RMySQL / dplyr正在尝试在本地计算机中查找套接字文件,但是它们应该真正在远程服务器中查找。有没有办法解决这个问题,还是解决问题?
My understanding is that RMySQL/dplyr are trying to looking for a socket file in the local computer, however they should really be looking for it in the remote server. Is there a way to fix this, or a work-around?
更新:
如果我尝试通过dbConnect / RMySQL连接,连接工作正常:
If I try to connect through dbConnect/RMySQL, the connection works fine:
> dbConnect(dbDriver("MySQL"), user="mysqluser", password="mypassword", dbname="mydb", host="127.0.0.1", port=3307)
<MySQLConnection:0,1>
推荐答案
像愚蠢的代码 localhost
与IP地址( 127.0.0.1
)解决问题。
As silly as it sounds replacing localhost
with an IP address (127.0.0.1
) solves the problem.
src_mysql(
dbname = "mydb", user = "mysqluser", password = "mypassword",
host = "127.0.0.1", port=3307)
有关说明,请查看 MySql文档:
在Unix上,MySQL程序将主机名localhost以某种方式与其他基于网络的程序相比有所不同。
On Unix, MySQL programs treat the host name localhost specially, in a way that is likely different from what you expect compared to other network-based programs.
对于与本地主机的连接,MySQL程序尝试连接到本地服务器通过使用Unix套接字文件。 即使提供了--port或-P选项来指定端口号,也会发生这种情况。
For connections to localhost, MySQL programs attempt to connect to the local server by using a Unix socket file. This occurs even if a --port or -P option is given to specify a port number.
确保客户端进行TCP / IP连接到本地服务器,请使用--host或-h来指定主机名值127.0.0.1,或本地服务器的IP地址或名称。
To ensure that the client makes a TCP/IP connection to the local server, use --host or -h to specify a host name value of 127.0.0.1, or the IP address or name of the local server.
这篇关于使用SSH软件时,无法通过套接字错误连接到本地MySQL服务器的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!