MySQL连接SSH隧道-如何指定其他MySQL服务器?

I've got ~50 databases all set up at different host names, with the requirement that I connect to them through an SSH tunnel.

我有大约50个数据库都设置在不同的主机名上，需要通过SSH通道连接它们。

For example:

例如:

SSH host at ssh.example.com

SSH主机ssh.example.com
MySQL host at mysql1.example.com

MySQL主机mysql1.example.com

I have managed to create the tunnel using autossh (web server running Debian), but I can't seem to figure out how to connect to a specific MySQL hostname "beyond" the SSH tunnel.

我已经成功地使用autossh(运行Debian的web服务器)创建了隧道，但是我似乎不知道如何连接到一个特定的MySQL主机名“超越”SSH隧道。

Typing lsof -i -n | egrep '\<ssh\>' confirms that the tunnel is working (sends port 3307 to ssh.example.com port 3306)

输入lsof -i -n |白鹭\ '确认隧道正在工作(发送端口3307到ssh.example.com端口3306) \>

So when I try mysql -h 127.0.0.1 -P 3307 I get Connection refused. Not too weird since it's not an MySQL server.

所以当我尝试mysql -h 127.0.0.1 - p3307时，我的连接被拒绝了。不是很奇怪，因为它不是MySQL服务器。

My question to you guys:

我的问题是:

How do I specify the mysql1.example.com host AFTER creating the SSH tunnel? I've tried searching everywhere but can't seem to figure it out.

如何在创建SSH隧道后指定mysql1.example.com主机?我到处都找过了，但似乎找不出来。

4 个解决方案

#1

Solved it! The thing was to connect to the correct server when creating the tunnel itself - should've seen that one coming.

解决它!事情是在创建隧道本身时连接到正确的服务器——应该已经看到了这一点。

ssh -f user@ssh.example.com -L 3307:mysql1.example.com:3306 -N

ssh -f user@ssh.example.com - l3307:mysql1.example.com:3306 -N。

Then mysql -h 127.0.0.1 -P 3307 worked as intended. :)

然后mysql -h 127.0.0.1 - p3307按预期工作。:)

#2

I got a nice blog about how to connect to MySQL using SSH tunnel. It is very well explained here.

我有一个关于如何使用SSH隧道连接到MySQL的博客。这里有很好的解释。

The command to connect to SSH tunnel:

连接到SSH隧道的命令:

ssh -L [listening port]:[REMOTE_MYSQL_HOST]:[REMOTE_MYSQL_PORT] [SSH_USER]@[SSH_HOST]

The command to connect to MySQL:

连接到MySQL的命令:

localhost:[listening port]

#3

When you don't have direct access to mysql-server, you use jump-server.

当您没有对mysql-server的直接访问权限时，您可以使用跳转服务器。

From your machine, you connect(ssh) to jump-server and from there you connect to your mysql-server.

从您的机器上，您连接(ssh)到跳转服务器，然后从那里您连接到您的mysql-server。

This can be avoided by using ssh- tunneling.

通过使用ssh- tunneling可以避免这种情况。

Suppose your

假设你的

       jump server is `jump-ip`
       mysql server is `mysql-ip`
       your machine is `machine-ip`

Just open ssh client(Putty in windows or terminal in linux/ios).

只要打开ssh客户机(在windows或linux/ios中的终端)。

Type:

类型:

    ssh -L [local-port]:[mysql-ip]:[mysql-port] [jump-server-user]@[jump-ip]

After this, you can use your localhost and local-port to access mysql-server on the remote machine directly.

之后，可以使用本地主机和本地端口直接访问远程机器上的mysql-server。

Eg. Your Jdbc url to access mysql database, in that case, will be

如。在这种情况下，访问mysql数据库的Jdbc url将是

jdbc:mysql://localhost:[local-port]/[database-name]

#4

I created a tunnelto the DB using this command

我使用这个命令为DB创建了一个隧道

ssh  -L 10000:localhost:3306  user@<ip addess to connect DB> -N -f

-L is local host port it is user defined u can provide any port number

-L是本地主机端口，用户定义u可以提供任意端口号

between 0 to 65535. 0 to 1023 are reserved.

在0到65535之间。保留0到1023。

whether you are using key based authentication to the server you should

无论您是使用基于密钥的身份验证到服务器，您应该。

mention the key like this.

像这样提到钥匙。

ssh -i <path of the private key>  -L 10000:localhost:3306  user@<ip addess to connect DB> -N -f

#1