通过SSH隧道的MySQL连接 [英] MySQL connection over SSH tunnel

问题描述

我已经在两个服务器 A 和 B 之间建立了SSH隧道. B 具有MySQL服务器，并且可以运行:

I have set up a SSH tunnel between two servers A and B. B has MySQL server, and this works:

mysql -h localhost -P 3306 -u user -p

这不是:

mysql -h 127.0.0.1 -P 3306 -u user -p

尽管my.cnf具有以下几行:

Although my.cnf has these lines:

bind-address        = 127.0.0.1
# Next addr differs slightly, but anyway
bind-address        = 99.99.99.99

现在关于隧道.它连接以下内容:(A) localhost(9989) -> (B) localhost(3306) 但是，当(在 A 上，端口已转发)时，我会

Now about the tunnel. It connects the following:(A) localhost(9989) -> (B) localhost(3306) But when (on A, with ports forwarded) I do

mysql -v -h 127.0.0.1 -P 9989 -u user userdb -p

我得到ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0

当我这样做

mysql -v -h localhost -P 9989 -u user userdb -p

我得到ERROR 1045 (28000): Access denied for user 'user'@'localhost' (using password: YES)

可能是什么原因?我在做什么错了?

What might be the reason? What am I doing wrong?

推荐答案

这里有三个问题.

1-暂时忘记SSH隧道

1 - Forget about the SSH tunnel for now

您不能将MySQL绑定到多个特定IP. 第一个bind-address子句被第二个子句覆盖(因此，被忽略).您的服务器仅监听99.99.99.99.

You cannot bind MySQL to more than one specific IP. The first bind-address clause is overridden (therefore, ignored) by the second one. Your server only listens to 99.99.99.99.

之所以可以与-h localhost连接但不能与-h 127.0.0.1连接的原因是，在第一种形式中，您实际上不是通过TCP/IP连接，而是通过本地套接字连接.

The reason why you can connect with -h localhost but not with -h 127.0.0.1 is that in the first form, you do not actually connect through TCP/IP, but through a local socket.

在您的my.cnf中查找socket子句.

删除一个冗余的bind-address子句.您可能要使用bind-address=0.0.0.0，它指示MySQL守护程序监听所有网络接口.

Remove one redundant bind-address clause. You may want to use bind-address=0.0.0.0, which instructs MySQL daemon to listen to all network interfaces.

2-让我们设置您的SSH隧道

2 - Let's setup your SSH tunnel

您出错的原因ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0对我来说并不明显.我可疑 SSH隧道仅在收到连接请求时才建立(在您的情况下，当您运行mysql客户端时).由于您的服务器没有监听127.0.0.1(请参阅上一段)，因此无法建立SSH隧道，连接失败，并且客户端将其解释为网络故障.

The reason for you error ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0 is not obvious to me. I suspect SSH tunnel is actually established only when it receives a connection request (in your case, when you run the mysql client). Since your server does not listen to 127.0.0.1 (see previous paragraph), the SSH tunnel cannot be established, connection fails, and your client interprets it as a network failure.

3-为什么mysql -v -h localhost -P 9989 -u user userdb -p失败

请发布

mysql > SELECT user, host FROM mysql.user WHERE user LIKE 'user' OR host LIKE 'localhost';

(在LIKE子句之后，将'user'替换为实际的用户名)

(replace 'user', after the LIKE clause, with the actual user name if necessary)

MySQL访问控制会同时检查用户名/密码(user)和连接源(host)，以识别用户.您可能没有创建用户'user'@'localhost'.

MySQL access control checks both the username/password (user) and the origin of the connection (host) to identify a user. You probably did not create a user 'user'@'localhost'.

注意:目前无法从我的位置访问mysql.com，我无法链接到相关的手册页.

N.B.: mysql.com being unreachable from my location at this time, I cannot link to the relevant manual pages.

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