Debugging MySQL SSL Connections: Using OpenSSL to Troubleshoot SSL/TLS Protocol Errors in Database Connectivity


1 views

When working with MySQL's SSL implementation, you'll encounter different behavior than standard HTTPS or LDAPS connections. The error SSL23_GET_SERVER_HELLO:unknown protocol occurs because MySQL's SSL implementation has its own protocol negotiation phase before TLS begins.

The standard openssl s_client command fails because:

  • MySQL doesn't initiate SSL immediately like HTTPS (port 443)
  • The protocol requires initial plaintext handshake before SSL negotiation
  • MySQL uses a custom protocol layer before TLS

Use these methods to verify MySQL SSL connectivity:

1. Using mysql_ssl_rsa_setup (For Testing)

# Generate test SSL files for MySQL
mysql_ssl_rsa_setup --datadir=/var/lib/mysql

2. Verify MySQL Server SSL Configuration

Check if SSL is properly enabled in MySQL:

SHOW VARIABLES LIKE '%ssl%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| have_openssl  | YES             |
| have_ssl      | YES             |
| ssl_ca        | /path/to/ca.pem |
| ssl_capath    | /path/to/ca/    |
| ssl_cert      | /path/to/cert.pem|
| ssl_key       | /path/to/key.pem |
+---------------+-----------------+

3. Client-Side SSL Verification

Force SSL connection from client:

mysql --ssl-mode=REQUIRED \
      --ssl-ca=/path/to/ca.pem \
      --ssl-cert=/path/to/client-cert.pem \
      --ssl-key=/path/to/client-key.pem \
      -h hostname -u user -p

Instead of raw OpenSSL, consider:

1. Using socat for Protocol Inspection

socat -v TCP-LISTEN:3307,reuseaddr,fork TCP:mysql.example.org:3306

2. Wireshark Packet Capture

Filter for MySQL protocol traffic on port 3306 to observe the handshake sequence.

For DigiCert intermediate certificates:

# Verify certificate chain
openssl verify -CAfile /path/to/DigiCertCA.pem /opt/mysql/pki/server-cert.pem

# Check certificate details
openssl x509 -in /opt/mysql/pki/server-cert.pem -text -noout
  • File permissions (key files should be 600)
  • Certificate chain incompleteness
  • CN/SAN mismatch with MySQL server hostname
  • Incorrect key usage in certificates

Newer versions support more TLS options:

[mysqld]
tls_version = TLSv1.2,TLSv1.3
admin_ssl = ON

When configuring SSL between MySQL servers and clients, the error ERROR 2026 (HY000): SSL connection error is particularly frustrating because it's a generic message that doesn't reveal the underlying cause. The fact that OpenSSL's s_client returns unknown protocol suggests we're dealing with a protocol negotiation issue rather than certificate problems.

MySQL doesn't use raw SSL/TLS on its port 3306 - it uses a custom protocol that wraps TLS within the MySQL protocol. This explains why openssl s_client fails with:

CONNECTED(00000003)
15706:error:140770FC:SSL routines:SSL23_GET_SERVER_HELLO:unknown protocol:s23_clnt.c:588:

Here are three reliable ways to test MySQL SSL connections:

Method 1: Using MySQL Client with SSL Verification

mysql --ssl-mode=VERIFY_IDENTITY \
      --ssl-ca=/path/to/ca.pem \
      --ssl-cert=/path/to/client-cert.pem \
      --ssl-key=/path/to/client-key.pem \
      -h mysql.example.org -u user -p

Method 2: Checking MySQL Server SSL Configuration

First verify the server actually has SSL enabled:

mysql -h localhost -e "SHOW VARIABLES LIKE '%ssl%';"
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| have_openssl  | YES             |
| have_ssl      | YES             |
| ssl_ca        | /opt/mysql/pki/CA |
| ssl_capath    |                 |
| ssl_cert      | /opt/mysql/pki/server-cert.pem |
| ssl_key       | /opt/mysql/pki/server-key.pem |
+---------------+-----------------+

Method 3: Packet Capture Analysis

Use tcpdump to observe the handshake:

tcpdump -i eth0 -s 65535 -w mysql_ssl.pcap port 3306

Then analyze with Wireshark, filtering for mysql protocol.

  • Certificate permissions (MySQL needs read access)
  • Mismatched CA between client and server
  • Incorrect file paths in my.cnf
  • Missing intermediate certificates

While s_client won't work directly, you can extract and verify certificates:

openssl x509 -in /opt/mysql/pki/server-cert.pem -text -noout
openssl verify -CAfile /opt/mysql/pki/CA server-cert.pem

Newer MySQL versions enforce stricter TLS requirements. Check TLS version compatibility:

SHOW VARIABLES LIKE 'tls_version';
SHOW VARIABLES LIKE 'ssl_cipher';