从python连接到远程服务器中的MySQL

Question

我在MacOS X 10.12上使用Python3.5，pymysql 0.7.6。

我试图使用python访问远程服务器中的MySQL数据库。使用以下命令行访问我没有问题：

ssh root@XXX.XXX.XXX.XXX
root@XXX.XXX.XXX.XXX's password: my_server_password

然后在服务器上：

mysql my_database -p
Enter password: my_database_password

它起作用了，我可以用我的数据库做各种事情。现在，我尝试在python中做同样的工作，下面是我在这里的其他文章中找到的文档或众多示例：

import pymysql

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

它不起作用，因为它是错误的：

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

凭据是正确的凭据，我检查了端口是否是正确的端口，正如其他帖子中所建议的那样。我怀疑这可能与数据库也有一个密码有关，而不仅仅是服务器，但我还没有找到包含这两个密码的任何方法。实际上，我不确定在connect命令中应该包含哪个密码，是服务器密码还是数据库密码。这对他们都不起作用。

那么，你有什么建议，可能是什么问题，或如果我错过了一个重要的一点？

Answer 1

当您运行mysql命令时，您将在shell中执行此操作。也就是说，您正在通过localhost连接连接到远程计算机上运行的服务器。该远程服务器似乎并不是为了允许远程连接而设置的，它只允许机器本身的连接。

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

我喜欢用于此目的的模块是：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()

Answer 2

根据所提供的资料，我建议你尝试以下办法：

1. 确保MySQL绑定地址为"0.0.0.0“。您可以通过编辑/etc/mysql/my.cnf并检查bind-address选项设置为"0.0.0.0“来做到这一点。
2. 确保从您的远程计算机打开了端口。安装"nmap“，然后使用nmap -sS -O -p3306 <public-server-ip>。如果看到像3306/mysql open这样的东西一切都很好。如果没有，您需要确保防火墙没有阻塞该端口。

正如Rocket所建议的那样，您可能希望使用SSH隧道来降低直接暴露MySQL端口的风险，因为数据可能是以清晰的方式发送的。如果您只需要在部署到服务器之前在本地测试您的代码，您可以在运行Python代码之前使用以下命令：ssh -f user@serverip -L 3306:127.0.0.1:3306 -N。在这种情况下，您不需要完成步骤一和步骤二。

Answer 3

工作代码

#!/usr/local/bin/python3
# -*- coding: utf-8 -*-

import pymysql
import sshtunnel
from sshtunnel import SSHTunnelForwarder

server = SSHTunnelForwarder(
    ('xx.xx.xx.xx', 22),
    ssh_username='deploy',
    ssh_pkey='~/.ssh/id_rsa',
    remote_bind_address=('127.0.0.1', 3306)
)
server.start()

con = pymysql.connect(host='127.0.0.1', user='root', passwd='mysql', db='backoffice_demo', port=server.local_bind_port)

with con:

    cur = con.cursor()
    cur.execute("SELECT VERSION()")

    version = cur.fetchone()

    print("Database version: {}".format(version[0]))