8.2.19. Configure SSL for MySQL

TLS/SSL encryption is used to secure data transmitted between the client and the server. Follow these instructions to configure encryption using your own SSL certificate with hostname validation.

Use the console on your device to run the commands. This will create certificate and key files. The server certificate files are installed on the hosting server; the client certificate files are used when configuring the client; and the CA certificate is used to verify the authenticity of the server and client certificates.

1. Create a CA (Certificate Authority) certificate that will be used to sign and verify the server and client certificates:

   openssl req -new -x509 -newkey rsa:4096 -nodes -sha256 -days 3650 \
     -keyout ca-key.pem \
     -out ca-cert.pem \
     -subj "/CN=MySQL_CA" \
     -addext "basicConstraints=critical,CA:true,pathlen:0" \
     -addext "keyUsage=critical,keyCertSign,cRLSign"

2. Create a server certificate (in the commands, replace example.mysql.network with your instance's host address):
   1. Create a key and CSR:

      openssl req -new -newkey rsa:2048 -nodes -sha256 \
        -keyout server-key.pem \
        -out server-req.pem \
        -subj "/CN=example.mysql.network"

   2. Sign the certificate:

      openssl x509 -req -sha256 -days 365 \
        -in server-req.pem \
        -CA ca-cert.pem \
        -CAkey ca-key.pem \
        -CAcreateserial \
        -out server-cert.pem \
        -extfile <(printf "basicConstraints=critical,CA:FALSE\nkeyUsage=critical,digitalSignature,keyEncipherment\nextendedKeyUsage=serverAuth\nsubjectAltName=DNS:example.mysql.network")

3. Create a client certificate:
   1. Create a key and CSR:

      openssl req -new -newkey rsa:2048 -nodes -sha256 \
        -keyout client-key.pem \
        -out client-req.pem \
        -subj "/CN=mysql-client"

   2. Sign the certificate:

      openssl x509 -req -sha256 -days 365 \
        -in client-req.pem \
        -CA ca-cert.pem \
        -CAkey ca-key.pem \
        -CAserial ca-cert.srl \
        -out client-cert.pem \
        -extfile <(printf "basicConstraints=critical,CA:FALSE\nkeyUsage=critical,digitalSignature,keyEncipherment\nextendedKeyUsage=clientAuth")

4. Install the server certificate, its key, and the CA certificate in the "MySQL" section on the "Settings → SSL" tab (leave the private key password field blank if you did not specify one when creating the certificate):
5. Check the host address in the installed certificate (use your connection credentials in the command: example.mysql.network — host, 12345 — port):

   openssl s_client -connect example.mysql.network:12345 -starttls mysql -showcerts </dev/null 2>/dev/null | openssl x509 -noout -subject -issuer -ext subjectAltName

   Without a certificate installed, the output looks like this:

   subject=CN = MySQL_Server_8.0.36-28_Auto_Generated_Server_Certificate
   issuer=CN = MySQL_Server_8.0.36-28_Auto_Generated_CA_Certificate
   No extensions in certificate

   After installing the certificate, the output will show the host address of your instance:

   subject=CN = example.mysql.network
   issuer=CN = example.mysql.network
   X509v3 Subject Alternative Name:
       DNS:example.mysql.network

6. Connect to the instance using a client certificate and make sure the connection is encrypted (use your connection credentials in the command: example.mysql.network — host, 12345 — port, user1 — user):

   mysql -h example.mysql.network -P 12345 -u user1 -p --ssl-mode=VERIFY_IDENTITY --ssl-ca=ca-cert.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem -e "SHOW STATUS LIKE 'Ssl_cipher'; SHOW STATUS LIKE 'Ssl_version';"

   If everything is configured correctly, the connection is established successfully, and the output looks like this:

   +---------------+------------------------+
   | Variable_name | Value                  |
   +---------------+------------------------+
   | Ssl_cipher    | TLS_AES_256_GCM_SHA384 |
   +---------------+------------------------+
   +---------------+---------+
   | Variable_name | Value   |
   +---------------+---------+
   | Ssl_version   | TLSv1.3 |
   +---------------+---------+