r - 使用 SSH 隧道时无法通过套接字错误连接到本地 MySQL 服务器

Question

我正在尝试使用 dplyr 连接到远程数据库，我通常通过 ssh 隧道进行查询。

我首先设置了一个 ssh 隧道，如下所示:

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 来访问数据库。例如:

mysql -h '127.0.0.1' -P 3307 -u mysqluser

如果我尝试通过 dplyr 访问同一个数据库，我会收到一个错误，提示它无法连接到本地 MySQL 套接字:

> 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 正在尝试在本地计算机中查找套接字文件，但是他们实际上应该在远程服务器中查找它。有没有办法解决这个问题，或解决方法？

更新:

如果我尝试通过 dbConnect/RMySQL 进行连接，则连接工作正常:

> dbConnect(dbDriver("MySQL"), user="mysqluser", password="mypassword", dbname="mydb", host="127.0.0.1", port=3307)
<MySQLConnection:0,1>

Answer 1

听起来很愚蠢，取代 localhost使用 IP 地址 ( 127.0.0.1 ) 解决了问题。

src_mysql(
    dbname = "mydb", user = "mysqluser", password = "mypassword",
    host = "127.0.0.1", port=3307)

有关解释，请查看 MySQL documentation :

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.

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.

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.