从 python 连接到远程服务器中的 MySQL [英] Conecting to MySQL in a remote server from python

问题描述

我在 MacOS X 10.12 上使用 Python 3.5，pymysql 0.7.6.

I'm using Python 3.5, pymysql 0.7.6 on MacOS X 10.12.

我正在尝试使用 python 访问远程服务器中的 MySQL 数据库.我使用以下命令从命令行访问没有问题:

I'm trying to use python to access a MySQL database in a remote server. I have no problems to access from the command line using:

ssh [e-mail]
[e-mail]'s password: my_server_password

然后在服务器中:

mysql my_database -p
Enter password: my_database_password

它有效，我可以用我的数据库做各种各样的事情.现在，我尝试在 python 中做同样的事情，遵循文档或我在其他帖子中找到的众多示例:

And it works and I can do all sort of things with my database. Now, I try to do the same within python, following the documentation or the numerous examples I've found in other posts here:

import pymysql

cnx = pymysql.connect(host='XXX.XXX.XXX.XXX', port='3306', user='root', password='my_server_password', db='my_database')

它不起作用，出现错误:

And it does not work, getting as error:

pymysql.err.OperationalError: (2003, "Can't connect to MySQL server on 'XXX.XXX.XXX.XXX' ([Errno 61] Connection refused)")

凭据是正确的凭据，我已检查端口是否正确，正如其他帖子中所建议的那样.我怀疑它可能与具有密码的数据库有关，而不仅仅是服务器，但我还没有找到任何包含这两个密码的方法.事实上，我不确定应该在连接命令中包含哪个密码，如果是服务器密码还是数据库密码.它不适用于它们中的任何一个.

The credentials are the correct credentials and I've checked that the port is the correct port, as it is suggested in other posts. I suspect it might be related with the database having also a password, not just the server, but I haven't found any way of including both passwords. Indeed, I'm not sure which password should be included in the connect command, if the server password or the database password. It does not work with neither of them.

那么，对于这里可能存在的问题，或者我是否遗漏了重要的一点，您有什么建议吗?

So, do you have any suggestion about what might be the issue here or if I'm missing an important bit?

推荐答案

当您运行 mysql 命令时，您是在 SSH shell 中执行此操作.也就是说，您正在通过 localhost 连接连接到在远程机器上运行的服务器.该远程服务器似乎没有设置为允许远程连接到它，只能连接到机器本身.

When you run the mysql command, you are doing this in an SSH shell. That is you are connecting to the server running on the remote machine via a localhost connection. That remote server doesn't seem to be set up to allow remote connections to it, only connections from the machine itself.

您需要让您的 python 脚本通过 SSH 以与您相同的方式连接到 MySQL 服务器.您可以在远程服务器上打开到端口 3306 的 SSH 隧道.

You'll need to have your python script connect to the MySQL server the same way you are, via SSH. You can open an SSH tunnel to port 3306 on the remote server.

我喜欢用于此目的的模块是:https://pypi.python.org/pypi/sshtunnel

The module I like to use for this purpose is: https://pypi.python.org/pypi/sshtunnel

from sshtunnel import SSHTunnelForwarder
import pymysql

server = SSHTunnelForwarder(
    'XXX.XXX.XXX.XXX',
    ssh_username='root',
    ssh_password='my_server_password',
    remote_bind_address=('127.0.0.1', 3306)
)
server.start()

cnx = pymysql.connect(
    host='127.0.0.1',
    port=server.local_bind_port,
    user='root',
    password='my_database_password',
    db='my_database'
)

# Make sure to call server.stop() when you want to disconnect
# after calling cnx.close()

这篇关于从 python 连接到远程服务器中的 MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！