使用 SSH tunel 时无法通过套接字错误连接到本地 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'

此时我可以通过连接到 localhost: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 套接字:

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>

推荐答案

用 IP 地址 (127.0.0.1) 替换 localhost 听起来很傻，但解决了这个问题.

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 tunel 时无法通过套接字错误连接到本地 MySQL 服务器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！