☰

OpenSSL 可以用来调试与 MySQL 服务器的 SSL 连接吗？

tag-icon • tag-icon mysql ssl
OpenSSL 可以用来调试与 MySQL 服务器的 SSL 连接吗？

我希望我的 Web 服务器通过 SSL 连接与 MySQL 数据库服务器通信。Web 服务器运行 CentOS5，数据库服务器运行 FreeBSD。证书由中间 CA DigiCert 提供。

MySQL 应该使用 ssl，根据my.cnf：

# The MySQL server
[mysqld]
port            = 3306
socket          = /tmp/mysql.sock
ssl
ssl-capath = /opt/mysql/pki/CA
ssl-cert = /opt/mysql/pki/server-cert.pem
ssl-key = /opt/mysql/pki/server-key.pem

当我启动 MySQL 时，守护进程启动时没有错误。这表明证书文件都是可读的。

但是当我尝试从 Web 服务器连接到数据库服务器时，出现错误：

[root@webserver ~]# mysql -h mysql.example.org -u user -p
ERROR 2026 (HY000): SSL connection error

如果我尝试使用 openssl 进一步调试：

[root@webserver ~]# openssl s_client -connect mysql.example.org:3306 0>/dev/null
CONNECTED(00000003)
15706:error:140770FC:SSL routines:SSL23_GET_SERVER_HELLO:unknown protocol:s23_clnt.c:588:

这是测试与 MySQL 数据库服务器的 SSL 连接的有效方法吗？这个SSL23_GET_SERVER_HELLO:unknown protocol消息很奇怪，因为如果您在用于非 SSL 流量的端口上使用 SSL，通常会看到这样的消息。

相同的 openssl 命令似乎可以很好地与 LDAP 和 HTTP 服务器配合使用：

$ openssl s_client -connect ldap.example.org:636  0>/dev/null
CONNECTED(00000003)
depth=2 /C=US/O=The Go Daddy Group, Inc./OU=Go Daddy Class 2 Certification Authority
...
$ openssl s_client -connect www.example.org:443  0>/dev/null
CONNECTED(00000003)
depth=0 /DC=org/DC=example/OU=Services/CN=www.example.org

答案1

OpenSSL 版本 1.1.1（发布于 2019-09-11）添加了-starttls mysql对提交 a2d9cfbac5d87b03496d62079aef01c601193b58。不幸的是，我无法在 OpenSSL 更新日志中找到对此新功能的引用。

我通过创建 SSL 证书成功测试了此功能，具体内容如下https://dev.mysql.com/doc/refman/5.7/en/creating-ssl-files-using-openssl.html：

$ echo | openssl s_client -starttls mysql -connect spx-bionic.censored.com:3306 -CAfile /tmp/ca.pem
CONNECTED(00000003)
depth=1 C = AU, ST = Some-State, O = Internet Widgits Pty Ltd, CN = mysql test CA
verify return:1
depth=0 C = AU, ST = Some-State, O = Internet Widgits Pty Ltd, CN = spx-bionic.censored.com
verify return:1
---
Certificate chain
 0 s:/C=AU/ST=Some-State/O=Internet Widgits Pty Ltd/CN=spx-bionic.censored.com
   i:/C=AU/ST=Some-State/O=Internet Widgits Pty Ltd/CN=mysql test CA
 1 s:/C=AU/ST=Some-State/O=Internet Widgits Pty Ltd/CN=mysql test CA
   i:/C=AU/ST=Some-State/O=Internet Widgits Pty Ltd/CN=mysql test CA
---
Server certificate
-----BEGIN CERTIFICATE-----
CENSORED
-----END CERTIFICATE-----
subject=/C=AU/ST=Some-State/O=Internet Widgits Pty Ltd/CN=spx-bionic.censored.com
issuer=/C=AU/ST=Some-State/O=Internet Widgits Pty Ltd/CN=mysql test CA
---
No client certificate CA names sent
Client Certificate Types: RSA sign, DSA sign, ECDSA sign
Requested Signature Algorithms: RSA+SHA512:DSA+SHA512:ECDSA+SHA512:RSA+SHA384:DSA+SHA384:ECDSA+SHA384:RSA+SHA256:DSA+SHA256:ECDSA+SHA256:RSA+SHA224:DSA+SHA224:ECDSA+SHA224:RSA+SHA1:DSA+SHA1:ECDSA+SHA1
Shared Requested Signature Algorithms: RSA+SHA512:DSA+SHA512:ECDSA+SHA512:RSA+SHA384:DSA+SHA384:ECDSA+SHA384:RSA+SHA256:DSA+SHA256:ECDSA+SHA256:RSA+SHA224:DSA+SHA224:ECDSA+SHA224:RSA+SHA1:DSA+SHA1:ECDSA+SHA1
Peer signing digest: SHA512
Server Temp Key: ECDH, P-521, 521 bits
---
SSL handshake has read 2599 bytes and written 632 bytes
---
New, TLSv1/SSLv3, Cipher is ECDHE-RSA-AES256-GCM-SHA384
Server public key is 2048 bit
Secure Renegotiation IS supported
Compression: NONE
Expansion: NONE
No ALPN negotiated
SSL-Session:
    Protocol  : TLSv1.2
    Cipher    : ECDHE-RSA-AES256-GCM-SHA384
    Session-ID: AD25B7C3018E4715F262188D982AAE141A232712316E0A3292B0C14178E0F505
    Session-ID-ctx: 
    Master-Key: C121967E8FAEC4D0E0157419000660434D415251B0281CCBFC6D7A2AE8B0CC63AEFE22B332E91D31424C1BF03E5AF319
    Key-Arg   : None
    PSK identity: None
    PSK identity hint: None
    SRP username: None
    TLS session ticket lifetime hint: 7200 (seconds)
    TLS session ticket:
    0000 - 82 db 03 0f c0 ce f2 26-62 bd 1b 18 71 03 88 db   .......&b...q...
    0010 - a6 66 7c 71 94 0c d5 ec-96 30 46 53 4a e6 cd 76   .f|q.....0FSJ..v
    0020 - 66 b3 22 86 7d 9f 7e 2c-14 1d 66 f2 46 8f d2 d3   f.".}.~,..f.F...
    0030 - f7 0a 0b f5 9e 05 97 e1-2b b3 ba 79 78 16 b8 59   ........+..yx..Y
    0040 - dc c5 0d a8 de 0b 3a df-4b ec f9 73 3f 4c c3 f1   ......:.K..s?L..
    0050 - 86 b6 f7 aa a7 92 84 77-9f 09 b2 cc 5d dd 35 41   .......w....].5A
    0060 - 23 5d 77 74 e1 96 91 ac-28 81 aa 83 fe fc d2 3c   #]wt....(......<
    0070 - f9 23 09 6d 00 e0 da ef-48 69 92 48 54 61 69 e8   .#.m....Hi.HTai.
    0080 - 30 0e 1f 49 7d 08 63 9e-91 70 fc 00 9f cd fe 51   0..I}.c..p.....Q
    0090 - 66 33 61 24 42 8f c2 16-57 54 48 ec 6a 87 dc 50   f3a$B...WTH.j..P

    Start Time: 1537350458
    Timeout   : 300 (sec)
    Verify return code: 0 (ok)
---
DONE

除 之外-starttls mysql，OpenSSL 1.1.1 版还支持postgres和ldap。支持的协议完整列表可在此处找到https://github.com/openssl/openssl/blob/OpenSSL_1_1_1-stable/apps/s_client.c#L815-L831

答案2

回答我自己的问题。如果您有更好的答案，并且有好的、权威的来源，请发表答案。

简短回答：不可以，OpenSSL 不能用于调试 MySQL SSL 连接。这是因为 MySQL 使用纯文本启动会话，然后切换到 SSL。

在阅读中https://dev.mysql.com/doc/dev/mysql-server/latest/page_protocol_connection_phase.html，MySQL 从纯文本连接开始，然后启动实际的 SSL。这解释了 MySQL 如何能够在一个端口（端口 3306）上监听纯文本和加密连接。将其与 HTTP 或 LDAP 服务器进行比较，其中一个端口用于纯文本连接，另一个端口用于加密连接。

它从客户端连接到服务器开始，服务器可能会发送 ERR 数据包并完成握手，或者发送初始握手数据包，客户端会用握手响应数据包进行应答。在此阶段，客户端可以请求 SSL 连接，在这种情况下，在客户端发送其身份验证响应之前会建立 SSL 通信通道

答案3

我找到了另一种方法来实现这一点，方法是借助sslscan --starttls-mysql（而不是openssl s_client -starttls mysql）：

$ sslscan --starttls-mysql mysql.example.com:3306
Version: 2.0.7
OpenSSL 1.1.1j  16 Feb 2021

Connected to 10.20.30.40

Testing SSL server mysql.example.com on port 3306 using SNI name mysql.example.com

  SSL/TLS Protocols:
SSLv2     disabled
SSLv3     disabled
TLSv1.0   enabled
TLSv1.1   enabled
TLSv1.2   enabled
TLSv1.3   disabled

  TLS Fallback SCSV:
Server supports TLS Fallback SCSV

  TLS renegotiation:
Secure session renegotiation supported

  TLS Compression:
OpenSSL version does not support compression
Rebuild with zlib1g-dev package for zlib support

  Heartbleed:
TLSv1.2 not vulnerable to heartbleed
TLSv1.1 not vulnerable to heartbleed
TLSv1.0 not vulnerable to heartbleed

  Supported Server Cipher(s):
Preferred TLSv1.2  256 bits  ECDHE-RSA-AES256-GCM-SHA384   Curve P-256 DHE 256
Accepted  TLSv1.2  256 bits  DHE-RSA-AES256-GCM-SHA384     DHE 2048 bits
Accepted  TLSv1.2  128 bits  ECDHE-RSA-AES128-GCM-SHA256   Curve P-256 DHE 256
Accepted  TLSv1.2  128 bits  DHE-RSA-AES128-GCM-SHA256     DHE 2048 bits
Accepted  TLSv1.2  256 bits  ECDHE-RSA-AES256-SHA384       Curve P-256 DHE 256
Accepted  TLSv1.2  256 bits  DHE-RSA-AES256-SHA256         DHE 2048 bits
Accepted  TLSv1.2  128 bits  ECDHE-RSA-AES128-SHA256       Curve P-256 DHE 256
Accepted  TLSv1.2  128 bits  DHE-RSA-AES128-SHA256         DHE 2048 bits
Accepted  TLSv1.2  256 bits  ECDHE-RSA-AES256-SHA          Curve P-256 DHE 256
Accepted  TLSv1.2  256 bits  DHE-RSA-AES256-SHA            DHE 2048 bits
Accepted  TLSv1.2  128 bits  ECDHE-RSA-AES128-SHA          Curve P-256 DHE 256
Accepted  TLSv1.2  128 bits  DHE-RSA-AES128-SHA            DHE 2048 bits
Accepted  TLSv1.2  256 bits  AES256-GCM-SHA384            
Accepted  TLSv1.2  128 bits  AES128-GCM-SHA256            
Accepted  TLSv1.2  256 bits  AES256-SHA256                
Accepted  TLSv1.2  128 bits  AES128-SHA256                
Accepted  TLSv1.2  256 bits  AES256-SHA                   
Accepted  TLSv1.2  128 bits  AES128-SHA                   
Accepted  TLSv1.2  112 bits  TLS_RSA_WITH_3DES_EDE_CBC_SHA
Preferred TLSv1.1  256 bits  ECDHE-RSA-AES256-SHA          Curve P-256 DHE 256
Accepted  TLSv1.1  256 bits  DHE-RSA-AES256-SHA            DHE 2048 bits
Accepted  TLSv1.1  128 bits  ECDHE-RSA-AES128-SHA          Curve P-256 DHE 256
Accepted  TLSv1.1  128 bits  DHE-RSA-AES128-SHA            DHE 2048 bits
Accepted  TLSv1.1  256 bits  AES256-SHA                   
Accepted  TLSv1.1  128 bits  AES128-SHA                   
Accepted  TLSv1.1  112 bits  TLS_RSA_WITH_3DES_EDE_CBC_SHA
Preferred TLSv1.0  256 bits  ECDHE-RSA-AES256-SHA          Curve P-256 DHE 256
Accepted  TLSv1.0  256 bits  DHE-RSA-AES256-SHA            DHE 2048 bits
Accepted  TLSv1.0  128 bits  ECDHE-RSA-AES128-SHA          Curve P-256 DHE 256
Accepted  TLSv1.0  128 bits  DHE-RSA-AES128-SHA            DHE 2048 bits
Accepted  TLSv1.0  256 bits  AES256-SHA                   
Accepted  TLSv1.0  128 bits  AES128-SHA                   
Accepted  TLSv1.0  112 bits  TLS_RSA_WITH_3DES_EDE_CBC_SHA

  Server Key Exchange Group(s):
TLSv1.2  141 bits  sect283k1
TLSv1.2  141 bits  sect283r1
TLSv1.2  204 bits  sect409k1
TLSv1.2  204 bits  sect409r1
TLSv1.2  285 bits  sect571k1
TLSv1.2  285 bits  sect571r1
TLSv1.2  128 bits  secp256k1
TLSv1.2  128 bits  secp256r1 (NIST P-256)
TLSv1.2  192 bits  secp384r1 (NIST P-384)
TLSv1.2  260 bits  secp521r1 (NIST P-521)
TLSv1.2  128 bits  brainpoolP256r1
TLSv1.2  192 bits  brainpoolP384r1
TLSv1.2  256 bits  brainpoolP512r1

  SSL Certificate:
Signature Algorithm: sha256WithRSAEncryption
RSA Key Strength:    2048

Subject:  mysql.example.com
Altnames: DNS:mysql.example.com, DNS:mysql-masters.example.com
Issuer:   R3

Not valid before: Oct  2 13:37:45 2021 GMT
Not valid after:  Dec 31 13:37:44 2021 GMT

答案4

我在 MacOS X 客户端与 Ubuntu 服务器通信时遇到了类似的问题。

如果您省略客户端证书和客户端密钥，只使用服务器证书的 CA，您能否检查连接是否正常工作？您能够建立加密连接吗？这通常需要将关联用户的 ssl_type 列设置为 ANY。

相关内容