无法使用 PyMySQL 建立到远程 MySQL 服务器的 TLS TCP 连接，其他工具可以工作 [英] Unable to make TLS TCP connection to remote MySQL server with PyMySQL, other tools work

问题描述

设置一个我想与中央 MySQL 数据库通信的新服务器，使用 TLS 连接以确保安全

Setting up a new server that I want to communicate to a central MySQL database, using a TLS connection for security

以下步骤，例如 this 我已经能够为我的 MySQL 服务器设置 TLS，我已经创建了几个能够从任何主机登录的用户 (%)，并且需要 SSL 连接

Following steps like this I have been able to set up TLS for my MySQL server, I have made several users that are able to login from any host (%), and require SSL connections

+------------+-----------+----------+
| user       | host      | ssl_type |
+------------+-----------+----------+
| testuser   | %         | ANY      |
+------------+-----------+----------+

我可以在任何主机上通过使用 HeidiSQL 或 MySQL CLI 工具等工具进行连接来确认这一点例如:mysql -u testuser -p -h mysql_server_IP 这将启动 TLS 连接，如 \s 所确认这排除了我在这个论坛和其他论坛上看到的大多数问题，这是由主机设置为 localhost 引起的.

I can confirm this on any host by connecting using tools like HeidiSQL or the MySQL CLI tool Ex: mysql -u testuser -p -h mysql_server_IP this will initiate a TLS connection, as confirmed by \s This rules out the majority of issues I have seen on this and other forums, which is caused by the host being set to localhost.

访问本地数据库时，以下工作正常.连接到非 TLS 远程数据库服务器时，它也能正常工作.

When accessing local databases, the following works fine. When connecting to non-TLS remote database servers, it also works fine.

import pymysql.cursors

connection = pymysql.connect(host=host,
                             user=user,
                             password=password,
                             db=db,
                             cursorclass=pymysql.cursors.DictCursor)

当尝试使用 require-tls 访问我的服务器时，我收到以下错误pymysql.err.OperationalError: (1045, "Access denied for user 'testuser'@'desktop.example.com' (using password: YES)")

When attempting to access my server with require-tls I receive the following error pymysql.err.OperationalError: (1045, "Access denied for user 'testuser'@'desktop.example.com' (using password: YES)")

我提出的其他发现表明该错误是由以下原因引起的:无效的用户名/密码组合，或该主机禁止连接.但是我知道我可以从该主机建立连接，如 CLI 所示.

The other findings I have suggest that that error is caused by: Invalid username / password combinations, or, connection prohibited by that host. However I know I can make connections from this host, as demonstrated by the CLI.

推荐答案

当连接到 my.cnf 中只有 require_secure_transport = ON 的服务器时，PyMySQL 给出了一个更明显的错误，即无法启动 TLS 连接.pymysql.err.InternalError: (3159, 'Connections using insecure transport is disabled while --require_secure_transport=ON.' 但是如果 MySQL 用户本身需要 SSL，您会从上面的问题.

When connecting to a server that only has the require_secure_transport = ON in my.cnf, PyMySQL gives a more obvious error around being unable to start a TLS connection. pymysql.err.InternalError: (3159, 'Connections using insecure transport are prohibited while --require_secure_transport=ON.' But if the MySQL user itself requires SSL, you get the more generic permission denied error from the question above.

在 github 问题跟踪器 上提到了提供 CA .pem 文件.如果您无权访问这些文件并希望隐式信任自签名证书.docs 提到了 -ssl 标志，它允许您传入各种证书文件的路径.

On the github issue tracker there is mention of supplying the CA .pem file. If you don't have access to these files and want to trust the self signed cert implicitly. The docs mention the -ssl flag, which allows you to pass in paths for various cert files.

但是，通过传入有效字典，无需任何有效密钥，您可以有效地覆盖信任自签名证书.示例:

However, by passing in a valid dictionary, without any of the valid keys, you can effectively blanket trust self-signed certs. Example:

connection = pymysql.connect(host=host,
                             user=user,
                             password=password,
                             db=db,
                             cursorclass=pymysql.cursors.DictCursor,
                             ssl={"fake_flag_to_enable_tls":True})

这篇关于无法使用 PyMySQL 建立到远程 MySQL 服务器的 TLS TCP 连接，其他工具可以工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！