使用 SSHTunnelForwarder 通过 SSH 连接到 MySQL 数据库 [英] Using SSHTunnelForwarder to connect to a MySQL db via SSH

问题描述

我正在尝试通过 python 中的 ssh 连接到 mysql 数据库，但出现错误.我将以下内容保存在 python 文件forward.py"中.我的代码如下:

I am trying to connect to a mysql db over ssh in python, but am getting an error. I have the following saved in a python file 'forward.py'. My code is as follows:

forward.py

from sshtunnel import SSHTunnelForwarder
import MySQLdb

with SSHTunnelForwarder(
      ('ssh_host', 22),
      ssh_password="ssh_password",
      ssh_username="ssh_username",
      remote_bind_address=('mysql_host', 3306)) as server:

     con = MySQLdb.connect(user='mysql_username',passwd='mysql_password',db='mysql_db_name',host='mysql_host',port=server.local_bind_port)

当我在终端中运行 forward.py 时，我收到以下错误:

When I run forward.py in terminal, I receive the following error:

_mysql_exceptions.OperationalError: (2003, "Can't connect to MySQL server on     
'mysql_host' (60)")

值得注意的是，这些值是为了安全起见的硬编码值，即 'mysql_host' 是一个实际的主机，我可以在运行时通过 ssh 正常运行

It is worth noting that the values are hardcoded values for security, i.e. 'mysql_host' is an actual host that I can ssh to just fine when I run

ssh mysql_host

通过终端.我也可以使用具有相同值的 Sequel Pro 进行正常连接，如下所示:http://screencast.com/t/0niuWlMDb

via terminal. I can also connect just fine using Sequel Pro with the same values, as per: http://screencast.com/t/0niuWlMDb

谁能指出我正确的方向?提前致谢

Can anyone point me in the right direction? Thanks in advance

推荐答案

我认为您的问题在于您正在创建本地 SSH 隧道，但尝试直接连接到远程主机，这违背了使用的目的SSH 隧道.

I think your issue lies in the fact that you're creating a local SSH tunnel but attempting to connect directly to a remote host, which defeats the purpose of using a SSH tunnel.

我相信，如果您将连接字符串从 host='mysql_host' 更改为 host='127.0.0.1'，您将解决您的问题.

I believe if you change your connection string from host='mysql_host' to host='127.0.0.1' you will have solved your issue.

如果您查看 Sequel Pro 实例的调试/连接设置，您将看到正在使用的实际连接命令，并希望确认是这种情况.

If you look at the debugging / connection settings of your Sequel Pro instance you will see the actual connection commands being used and should hopefully confirm that this is the case.

有关 SSH 转发的更多参考资料:http://blog.trackets.com/2014/05/17/ssh-tunnel-local-and-remote-port-forwarding-explained-with-examples.html

Further reference material on SSH forwarding : http://blog.trackets.com/2014/05/17/ssh-tunnel-local-and-remote-port-forwarding-explained-with-examples.html

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