MySQL SSL Configuration Fails: Troubleshooting “have_ssl=DISABLED” After Setup


2 views

First, let's verify the SSL certificate chain. The common mistake is improper CA certificate bundling. Instead of combining server and client certs into ca.pem, we should use the original CA certificate:

[mysqld]
ssl-ca=/root/abc/ssl_certs/ca-cert.pem  # Use original CA cert
ssl-cert=/root/abc/ssl_certs/server-cert.pem
ssl-key=/root/abc/ssl_certs/server-key.pem

Ensure your MySQL version supports SSL. For modern MySQL installations (5.7+), check with:

mysql> SHOW VARIABLES LIKE '%ssl%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| have_openssl  | YES             |
| have_ssl      | YES             |
| ssl_ca        | /path/to/ca.pem |
+---------------+-----------------+

Your certificate generation script needs adjustment. Here's the corrected sequence:

# Generate proper CA
openssl genrsa -out ca-key.pem 4096
openssl req -new -x509 -days 3650 -key ca-key.pem -out ca-cert.pem

# Server certificate with SAN
openssl req -newkey rsa:4096 -days 3650 \
  -nodes -keyout server-key.pem -out server-req.pem \
  -subj "/CN=mysql-server" \
  -addext "subjectAltName = DNS:localhost,DNS:mysql-server"

# Sign server cert
openssl x509 -req -in server-req.pem -days 3650 \
  -CA ca-cert.pem -CAkey ca-key.pem -CAcreateserial \
  -out server-cert.pem -extfile <(printf "subjectAltName=DNS:localhost,DNS:mysql-server")

Your GRANT statement should specify the exact host or use wildcards carefully:

CREATE USER 'ssluser'@'%' IDENTIFIED BY 'password' REQUIRE SSL;
GRANT ALL PRIVILEGES ON *.* TO 'ssluser'@'%';
FLUSH PRIVILEGES;

Force SSL connection from client side for testing:

mysql --ssl-mode=REQUIRED \
  --ssl-ca=/root/abc/ssl_certs/ca-cert.pem \
  --ssl-cert=/root/abc/ssl_certs/client-cert.pem \
  --ssl-key=/root/abc/ssl_certs/client-key.pem \
  -u ssluser -p

1. Check MySQL error log for SSL initialization errors
2. Verify file permissions (mysql user must read cert files)
3. Ensure OpenSSL libraries are properly installed

If issues persist, try this diagnostic query after connecting:

mysql> \s
...
SSL: Cipher in use is DHE-RSA-AES256-SHA
  • Certificate expiration (use 3650 days in test environments)
  • Mismatched Common Names between server and certificates
  • Missing subjectAltName extensions in certificates
  • Incorrect file permissions (chmod 400 for key files)

For production environments, consider using Let's Encrypt or proper CA-signed certificates instead of self-signed certs.

When I recently configured SSL for MySQL following what seemed like standard procedures, I encountered a frustrating situation where SSL remained disabled despite all configuration steps appearing correct. The server showed:

mysql> show variables like '%ssl%';
+---------------+---------------------------------------------+
| Variable_name | Value                                       |
+---------------+---------------------------------------------+
| have_openssl  | DISABLED                                    |
| have_ssl      | DISABLED                                    |
| ssl_ca        | /root/abc/ssl_certs/ca.pem          |
| ssl_capath    |                                             |
| ssl_cert      | /root/abc/ssl_certs/server-cert.pem |
| ssl_cipher    |                                             |
| ssl_key       | /root/abc/ssl_certs/server-key.pem  |
+---------------+---------------------------------------------+

Through troubleshooting, I discovered several critical points often overlooked:

# Verify MySQL was compiled with SSL support
mysql> SHOW VARIABLES LIKE 'have_ssl';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_ssl      | YES   |  # Must show YES
+---------------+-------+

Here's the full working configuration that finally enabled SSL:

[mysqld]
ssl-ca=/etc/mysql/ssl/ca.pem
ssl-cert=/etc/mysql/ssl/server-cert.pem
ssl-key=/etc/mysql/ssl/server-key.pem
ssl-cipher=DHE-RSA-AES256-SHA

# Important: Set proper permissions
chmod 600 /etc/mysql/ssl/*
chown mysql:mysql /etc/mysql/ssl/*

To confirm SSL is working, use these commands:

mysql> \s
--------------
SSL:                    Cipher in use is DHE-RSA-AES256-SHA

# Or check connection status
mysql> SELECT ssl_cipher FROM performance_schema.status_by_thread 
       WHERE thread_id = PS_CURRENT_THREAD_ID();
+---------------------+
| ssl_cipher          |
+---------------------+
| DHE-RSA-AES256-SHA  |
+---------------------+

For production environments, consider adding these settings:

[mysqld]
require_secure_transport=ON
ssl-crl=/etc/mysql/ssl/crl.pem
ssl-crlpath=/etc/mysql/ssl/crl/
tls_version=TLSv1.2,TLSv1.3
  • Verify OpenSSL version matches MySQL requirements
  • Check error logs for SSL initialization errors
  • Confirm certificate files exist and are readable by mysqld
  • Test connectivity with --ssl-mode=REQUIRED parameter