通过 SSH 连接 MySQL

Question

我在尝试从本地 MySQL 客户端连接到远程 MySQL 服务器时遇到错误。(服务器运行 Debian，客户端运行 OSX)

我可以设法通过 ssh 连接到服务器...

local$ ssh john@serverip

(注意:我使用的是 ssh 公钥，因此我不输入任何密码)...并从那里连接到 MySQL ...

distant$ mysql -u sqluser -p myDatabase

(好的，我已连接，我可以执行任何我想要的 MySQL 命令)

下面是我的客户端配置:

下面是我的客户端尝试通过 ssh 连接时的调试跟踪:

Used command:  /usr/bin/ssh -v -N -o ControlMaster=no -o ExitOnForwardFailure=yes -o ConnectTimeout=10 -o NumberOfPasswordPrompts=3 -o TCPKeepAlive=no -o ServerAliveInterval=60 -o ServerAliveCountMax=1 john@serverip -L 52004/127.0.0.1/3306

OpenSSH_6.2p2, OSSLShim 0.9.8r 8 Dec 2011
debug1: Reading configuration data /etc/ssh_config
debug1: /etc/ssh_config line 20: Applying options for *
debug1: /etc/ssh_config line 102: Applying options for *
debug1: Connecting to serverip [serverip] port 22.
debug1: fd 3 clearing O_NONBLOCK
debug1: Connection established.
debug1: identity file /Users/john/.ssh/id_rsa type 1
debug1: identity file /Users/john/.ssh/id_rsa-cert type -1
debug1: identity file /Users/john/.ssh/id_dsa type -1
debug1: identity file /Users/john/.ssh/id_dsa-cert type -1
debug1: Enabling compatibility mode for protocol 2.0
debug1: Local version string SSH-2.0-OpenSSH_6.2
debug1: Remote protocol version 2.0, remote software version OpenSSH_6.0p1 Debian-4+deb7u2
debug1: match: OpenSSH_6.0p1 Debian-4+deb7u2 pat OpenSSH*
debug1: SSH2_MSG_KEXINIT sent
debug1: SSH2_MSG_KEXINIT received
debug1: kex: server->client aes128-ctr hmac-md5 none
debug1: kex: client->server aes128-ctr hmac-md5 none
debug1: SSH2_MSG_KEX_DH_GEX_REQUEST(1024<1024<8192) sent
debug1: expecting SSH2_MSG_KEX_DH_GEX_GROUP
debug1: SSH2_MSG_KEX_DH_GEX_INIT sent
debug1: expecting SSH2_MSG_KEX_DH_GEX_REPLY
debug1: Server host key: RSA 4b:56:4e:f2:71:50:ed:fd:25:aa:bf:0d:1a:a0:89:40
debug1: Host 'serverip' is known and matches the RSA host key.
debug1: Found key in /Users/john/.ssh/known_hosts:1
debug1: ssh_rsa_verify: signature correct
debug1: SSH2_MSG_NEWKEYS sent
debug1: expecting SSH2_MSG_NEWKEYS
debug1: SSH2_MSG_NEWKEYS received
debug1: Roaming not allowed by server
debug1: SSH2_MSG_SERVICE_REQUEST sent
debug1: SSH2_MSG_SERVICE_ACCEPT received
debug1: Authentications that can continue: publickey
debug1: Next authentication method: publickey
debug1: Offering RSA public key: /Users/john/.ssh/id_rsa
debug1: Server accepts key: pkalg ssh-rsa blen 279
debug1: read PEM private key done: type RSA
debug1: Authentication succeeded (publickey).
Authenticated to serverip ([serverip]:22).
debug1: Local connections to LOCALHOST:52004 forwarded to remote address 127.0.0.1:3306
debug1: Local forwarding listening on ::1 port 52004.
debug1: channel 0: new [port listener]
debug1: Local forwarding listening on 127.0.0.1 port 52004.
debug1: channel 1: new [port listener]
debug1: Requesting no-more-sessions@openssh.com
debug1: Entering interactive session.
debug1: Connection to port 52004 forwarding to 127.0.0.1 port 3306 requested.
debug1: channel 2: new [direct-tcpip]
channel 2: open failed: connect failed: Connection refused
debug1: channel 2: free: direct-tcpip: listening port 52004 for 127.0.0.1 port 3306, connect from 127.0.0.1 port 52006, nchannels 3

知道出了什么问题吗？

(注意:我用 serverip 替换了我的真实服务器 ip，用 sqluser 替换了我真实的 sql 用户名，用 john 替换了我真实的 ssh 用户名)

编辑:在服务器上输入:

netstat -an | grep 3306

给予:

tcp        0      0 serverip:3306       0.0.0.0:*               LISTEN     

编辑:我的问题的解决方案是在客户端配置选项中将“127.0.0.1”更改为 serverip。我一开始以为serverip只会用于SSH服务器，然后MySQL服务器将是127.0.0.1(localhost)，但我错了。非常感谢@Honore Doktorr 和@Kenster 为我指出了正确的解决方案

Answer 1

debug1: Connection to port 52004 forwarding to 127.0.0.1 port 3306 requested.
debug1: channel 2: new [direct-tcpip]
channel 2: open failed: connect failed: Connection refused

远程 ssh 服务器尝试连接到 127.0.0.1 端口 3306 以服务转发请求，但出现“连接被拒绝”错误。 “连接被拒绝”通常意味着没有任何东西在请求的地址和端口处监听连接。

最简单的解释是，应该在远程服务器上运行的 mysql 服务器实际上并未运行，或者它正在监听与您期望的不同的地址和/或端口。

您应该检查远程服务器以确保 mysql 确实正在运行。如果是，则找到它实际监听的地址和端口，并更正您的端口转发设置以转发到正确的地址和端口。

编辑:

tcp        0      0 serverip:3306       0.0.0.0:*               LISTEN     

这表明 mysql 进程专门监听 serverip 接口(interface)，而不是 localhost 接口(interface)。它只会接收针对 serverip 地址的连接尝试，而不是 127.0.0.1。

将 Hôte MySql 字段的值更改为 serverip 地址(即 netstate 输出中出现的任何内容)可能会解决该问题。或者，可以将 mysql 服务器重新配置为监听 127.0.0.1 地址，或监听 0.0.0.0(接受任何接口(interface)上的连接的“通配符”地址)。