通过 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) 本地主机(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'(使用密码: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 子句.

Look in your my.cnf for a socket clause.

删除一个多余的 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 失败

3 - Why mysql -v -h localhost -P 9989 -u user userdb -p fails

请贴出

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

(替换 'user'，在 LIKE 子句之后，如有必要，使用实际用户名)

(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屋！