通过 SSH 隧道的 MySQL 连接

MySQL connection over SSH tunnel

提问by madfriend

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

我已经在两台服务器A和B之间建立了一个 SSH 隧道。B有 MySQL 服务器，这有效：

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

While this doesn't:

虽然这不会：

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

Although my.cnf has these lines:

虽然 my.cnf 有这些行：

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

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

现在关于隧道。它连接以下内容：(A) localhost(9989) -> (B) localhost(3306)但是当（在A 上，端口转发）我做

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

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

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

And when I do

当我这样做时

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

I get ERROR 1045 (28000): Access denied for user 'user'@'localhost' (using password: YES)

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

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

可能是什么原因？我究竟做错了什么？

回答by RandomSeed

There are three issues here.

这里有三个问题。

1 - Forget about the SSH tunnel for now

1 - 暂时忘记 SSH 隧道

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

您不能将 MySQL 绑定到多个特定 IP。第一个bind-address子句被第二个子句覆盖（因此被忽略）。您的服务器只收听99.99.99.99.

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

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

Look in your my.cnffor a socketclause.

看看你my.cnf的socket条款。

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

删除一个多余的bind-address条款。您可能想要使用bind-address=0.0.0.0，它指示 MySQL 守护程序侦听所有网络接口。

2 - Let's setup your SSH tunnel

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

The reason for you error ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0is not obvious to me. I suspectSSH tunnel is actually established only when it receives a connection request (in your case, when you run the mysqlclient). 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.

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

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

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

Please post the output of

请发布输出

[edit : just added ...OR host LIKE 'localhost'below, as this might be relevant for troubleshooting purposes]

[编辑：刚刚在...OR host LIKE 'localhost'下面添加，因为这可能与故障排除目的有关]

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

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

（如有必要，用实际用户名替换子句'user'后的LIKE）

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 访问控制检查用户名/密码 ( user) 和连接的来源 ( host) 以识别用户。您可能没有创建用户'user'@'localhost'。

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

注意：此时无法从我的位置访问 mysql.com，我无法链接到相关的手册页。

回答by Mxx

I just encountered this very problem.

我刚刚遇到了这个问题。

In my case MySQL server is configured with bind-address: 192.168.4.4. I originally setup an SSH tunnel with a commonly mentioned -L 3306:localhost:3306 user@serverstring and from my computer connect with mysql -h 127.0.0.1.

在我的情况下，MySQL 服务器配置为bind-address: 192.168.4.4. 我最初设置了一个带有常用-L 3306:localhost:3306 user@server字符串的 SSH 隧道，并从我的计算机连接到mysql -h 127.0.0.1.

This does not work because MySQL no longer listens on 0.0.0.0or even "localhost"(aka 127.0.0.1), only 192.168.4.4.

这不起作用，因为 MySQL 不再侦听0.0.0.0甚至"localhost"（又名 127.0.0.1），只侦听192.168.4.4.

The correct tunnel string should be -L 3306:192.168.4.4:3306 user@server. This will tell the remote tunnel end to connect to MySQL using the IP MySQL actually listens on.

正确的隧道字符串应该是-L 3306:192.168.4.4:3306 user@server. 这将告诉远程隧道端使用 MySQL 实际侦听的 IP 连接到 MySQL。

回答by bortunac

STEP-BY-STEP SSH TUNNELING

一步一步的 SSH 隧道

--- SERVER SIDE ----

- - 服务器端 - -

in target machine (that can be addresed by IP or a domain hosted) there is config file /etc/mysql/my.cnfhaving a line

在目标机器（可以通过 IP 或托管域寻址）中有配置文件/etc/mysql/my.cnf有一行

bind-address    = 127.0.0.1

confirmed with console

与控制台确认

netstat -tapn |  grep mysql
// tcp    0    0 127.0.0.1:3306     0.0.0.0:*    LISTEN      18469/mysqld

which means mysql server will respond only to request from the localhost

这意味着 mysql 服务器将只响应来自本地主机的请求

--- CLIENT SIDE ----

- - 客户端 - -

you have an account (eventualy a ssh-key) to log using cygwin,putty or a linux_shell

您有一个帐户（最终是一个 ssh 密钥）可以使用 cygwin、putty 或 linux_shell 登录

ssh user_name@host_name

create SSH TUNNEL

创建 SSH 隧道

ssh -f -N -L 1000:127.0.0.1:3306    user_name@host_name

which means hey ssh create a permanent connection from port 1000 on the machine that I type (client) to remote host_name:3306 .... 127.0.0.1 means here the remote (host_name) and should not be replaced with localhost word because this will make the connection on unix (named) socket not by IP ... You'll get 'ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysql.sock' (2)'when trying co connect mysql

这意味着嘿 ssh 创建一个从我输入的机器上的端口 1000 到远程主机名的永久连接（客户端）：3306 .... 127.0.0.1 在这里表示远程（主机名），不应替换为 localhost 字，因为这会在 unix（命名）套接字上建立连接，而不是通过 IP ......你会得到“错误 2002（HY000）：无法通过套接字'/var/run/mysql.sock'（2）'连接到本地 MySQL 服务器”尝试共同连接mysql时

-f = go in background -N = no excution

-f = 进入后台 -N = 不执行

both -f -N kind of nohoop - you can close console and tunnels persist

-f -N 都是 nohoop - 你可以关闭控制台和隧道持续存在

--- SERVER SIDE ---

- - 服务器端 - -

netstat -tapn |  grep ssh
// tcp  0  0  server_ip:22   clint_ip:port  ESTABLISHED 24915/sshd: user_name

which means there is a permanent connection through shh protocol

这意味着通过 shh 协议存在永久连接

--- CLIENT SIDE ---

- - 客户端 - -

mysql -h 127.0.0.1 -P 1000 -u mysql_user -pmysql_pass

now your (client side) mysql client is conected to remote mysql server ... here 127.0.0.1 is client machine

现在您的（客户端）mysql 客户端已连接到远程 mysql 服务器......这里 127.0.0.1 是客户端机器

same for workbench, heidiSQL

工作台，heidiSQL 相同

---

---

how to kill ssh tunnels

如何杀死 ssh 隧道

ps fax | grep ssh
kill process_id

回答by Carlos Devia

In my case, a configuration in the SSH daemon was blocking the tunnel. AllowTcpForwarding should be enabled.

就我而言，SSH 守护程序中的配置阻止了隧道。应启用 AllowTcpForwarding。

AllowTcpForwarding yes

回答by suz

I did have the same proble ("Lost connection...") on Windows (while using ssh tunnel via Putty). I got 2 issues here:

我"Lost connection..."在 Windows 上确实有同样的问题 ( )（通过 Putty 使用 ssh 隧道时）。我在这里有两个问题：

1. Wrong port was used, double check if you are setting it correctly
2. I forgot enabling in Putty: Connection > SSH > Tunnels > Local ports accept connections from other hosts

1. 使用了错误的端口，请仔细检查是否设置正确
2. 我忘了启用 Putty: Connection > SSH > Tunnels > Local ports accept connections from other hosts

回答by SieluLintu

A simple step worked for me... I'll share this, so maybe some of you can be spared a headache.

一个简单的步骤对我有用......我会分享这个，所以也许你们中的一些人可以免于头疼。

MY SETTING

我的设置

In my particular case, I have a Percona server running on Ubuntu, connected to MySQL Workbench (in a Windows VM) through SSH; the server ran fine for several days before spitting an error 10060 while processing a query.

在我的特殊情况下，我有一个在 Ubuntu 上运行的 Percona 服务器，它通过 SSH 连接到 MySQL Workbench（在 Windows VM 中）；在处理查询时出现错误 10060 之前，服务器运行了好几天。

WHAT WORKED FOR ME

什么对我有用

I found in a forum from Acquia.com that in some cases the Workbench won't accept '127.0.0.1' as host, so you must change it to 'localhost'. I did it, and it worked (oddly, the Workbench asked for the passwords again, even if they were already stored, but worked nevertheless).

我在 Acquia.com 的论坛中发现，在某些情况下，Workbench 不会接受“127.0.0.1”作为主机，因此您必须将其更改为“localhost”。我做到了，并且有效（奇怪的是，工作台再次要求输入密码，即使它们已经存储，但仍然有效）。