Skip to main content

Configure MySQL SSL/TLS for AIQ Environments

Configure MySQL SSL/TLS for AIQ Environments

Use MySQL SSL/TLS configuration only with environment-owner approval and release-specific AIQ database guidance.

OpenSSL

  • Confirm needs to be installed,whether the properdatabase packageis canmanaged beby foundAIQ, atcustomer https://wiki.openssl.org/index.php/Binariesinfrastructure, there,or it could be founda third partyparty.
  • Use binaryapproved distributions,certificates, forkey our purposes any for Windows product could work but the first choice in the list will offer self-install executables.

    https://slproweb.com/products/Win32OpenSSL.html

    Environment Variable setup

    Please set up the environment variables as mentioned below.

    Check if OpenSSL is installed

    Open cmdstorage, and typerotation openssl

    procedures.
  • Avoid documenting private keys, passwords, or internal hostnames.
  • Validate connectivity from AIQ components after enabling SSL/TLS.

CreatingValidation and configuring the MySQL public certificate.workflow

a) Open a Command prompt as administrator.

b) Move to the MySQL 8 Server’s bin folder.

c) Execute mysql_ssl_rsa_setup.exe as below.

mysql_ssl_rsa_setup.exe --datadir="C:\ProgramData\MySQL\MySQL Server 8.0\Data"

Folder specified for –datadir parameter is the one that points to the MySQL Data files usually under ProgramData root folder.

d) Verify

  1. Plan the certificate byand issuingdatabase asconfiguration followchange.
  2. Apply obtaining an output similar to the one. 
    C:\ProgramData\MySQL\MySQL Server 8.0\Data>openssl x509 -in ca.pem -text
Certificate:
    Data:
        Version: 3 (0x2)
        Serial Number: 1 (0x1)
        Signature Algorithm: sha256WithRSAEncryption
        Issuer: CN = MySQL_Server_8.0.22_Auto_Generated_CA_Certificate
        Validity
            Not Before: Nov  3 16:28:59 2020 GMT
            Not After : Nov  1 16:28:59 2030 GMT
        Subject: CN = MySQL_Server_8.0.22_Auto_Generated_CA_Certificate
        Subject Public Key Info:
            Public Key Algorithm: rsaEncryption
                RSA Public-Key: (2048 bit)
                Modulus:
                    00:b6:40:7d:bc:53:c3:c3:43:db:0b:2e:e5:c1:0b:
                    53:da:0a:0d:6d:f6:ce:21:0a:34:e1:49:02:10:1d:
                    dc:7c:dc:92:53:1a:02:7d:4e:e1:44:89:14:26:c8:
                    f9:4f:6c:f5:64:b5:20:c0:2b:67:00:d9:cc:7e:f2:
                    be:e4:8a:03:fd:5e:37:24:b0:96:97:0e:70:cc:b4:
                    02:d1:75:e7:eb:18:74:5b:7f:5a:3e:4a:6b:35:a8:
                    ec:3a:d9:a3:60:68:cf:c6:16:11:92:2d:e9:ae:fe:
                    04:18:fa:9c:cf:88:4e:f7:a8:da:a4:5f:29:a0:7f:
                    ed:90:af:59:b0:a1:5f:1e:85:bc:d2:74:6e:d8:52:
                    0e:ee:4c:ac:2c:7b:d9:61:a9:13:2e:35:41:20:11:
                    cf:ec:db:b8:e1:d1:47:f1:e2:16:02:31:a1:ad:2f:
                    7d:2d:43:00:65:71:cb:11:65:e5:55:f0:ad:0d:b6:
                    b3:43:4c:27:91:0b:19:52:f2:e7:d0:8a:2a:56:0a:
                    8d:c6:38:cf:bf:8e:38:59:fe:9a:f4:f1:81:3e:93:
                    f4:51:f8:cc:3f:5f:ad:51:0c:2a:30:e4:1d:c9:69:
                    8f:07:19:de:e3:a7:e5:0d:24:7f:31:be:43:34:6b:
                    15:c2:32:6d:0b:02:c0:cc:2c:fa:50:f3:42:c9:c7:
                    5d:8d
                Exponent: 65537 (0x10001)
        X509v3 extensions:
            X509v3 Basic Constraints: critical
                CA:TRUE
    Signature Algorithm: sha256WithRSAEncryption
         84:33:78:03:09:bc:ac:a3:3c:41:e1:6e:a5:db:6e:7c:09:1e:
         5c:83:59:70:f6:11:7e:43:04:b9:1e:bd:a7:eb:f7:b1:19:f2:
         41:97:ca:52:42:ce:a8:09:a8:38:94:1d:c8:39:c6:e6:7e:fd:
         39:a5:04:4b:ca:b4:5d:66:de:d7:db:0e:38:78:54:1e:3f:3a:
         91:f4:27:9b:1a:7b:af:a7:73:42:26:5e:92:e0:c7:ad:ae:04:
         38:3a:00:23:31:83:37:9d:98:8e:81:50:eb:e3:46:17:f2:32:
         c0:6b:e0:06:25:52:6d:ec:3d:67:5c:e3:9a:29:11:e8:e9:2a:
         b6:87:61:77:13:24:0a:33:4f:a7:11:67:e5:5f:77:77:db:64:
         08:c7:c3:1d:b3:e3:59:67:95:7b:b0:2b:c5:c3:e8:7c:d2:c3:
         d1:31:5f:89:11:0b:da:db:dc:73:74:4f:4b:80:43:37:eb:b5:
         4e:41:c6:1d:43:d5:74:51:ac:76:c4:7c:00:85:9e:3d:b8:9b:
         8b:45:09:df:9d:8e:1b:9f:0d:7b:f0:fc:6e:30:8b:be:b9:63:
         6b:eb:4a:7d:b5:8d:48:bf:5c:07:43:0f:b0:5f:ed:24:08:1c:
         61:b9:6b:d8:69:33:48:c5:9c:2a:57:23:87:63:c2:e8:32:6e:
         4d:f8:10:2f
-----BEGIN CERTIFICATE-----
MIIDBjCCAe6gAwIBAgIBATANBgkqhkiG9w0BAQsFADA8MTowOAYDVQQDDDFNeVNR
TF9TZXJ2ZXJfOC4wLjIyX0F1dG9fR2VuZXJhdGVkX0NBX0NlcnRpZmljYXRlMB4X
DTIwMTEwMzE2Mjg1OVoXDTMwMTEwMTE2Mjg1OVowPDE6MDgGA1UEAwwxTXlTUUxf
U2VydmVyXzguMC4yMl9BdXRvX0dlbmVyYXRlZF9DQV9DZXJ0aWZpY2F0ZTCCASIw
DQYJKoZIhvcNAQEBBQADggEPADCCAQoCggEBALZAfbxTw8ND2wsu5cELU9oKDW32
ziEKNOFJAhAd3HzcklMaAn1O4USJFCbI+U9s9WS1IMArZwDZzH7yvuSKA/1eNySw
lpcOcMy0AtF15+sYdFt/Wj5KazWo7DrZo2Boz8YWEZIt6a7+BBj6nM+ITveo2qRf
KaB/7ZCvWbChXx6FvNJ0bthSDu5MrCx72WGpEy41QSARz+zbuOHRR/HiFgIxoa0v
fS1DAGVxyxFl5VXwrQ22s0NMJ5ELGVLy59CKKlYKjcY4z7+OOFn+mvTxgT6T9FH4
zD9frVEMKjDkHclpjwcZ3uOn5Q0kfzG+QzRrFcIybQsCwMws+lDzQsnHXY0CAwEA
AaMTMBEwDwYDVR0TAQH/BAUwAwEB/zANBgkqhkiG9w0BAQsFAAOCAQEAhDN4Awm8
rKM8QeFupdtufAkeXINZcPYRfkMEuR69p+v3sRnyQZfKUkLOqAmoOJQdyDnG5n79
OaUES8q0XWbe19sOOHhUHj86kfQnmxp7r6dzQiZekuDHra4EODoAIzGDN52YjoFQ
6+NGF/IywGvgBiVSbew9Z1zjmikR6OkqtodhdxMkCjNPpxFn5V93d9tkCMfDHbPj
WWeVe7ArxcPofNLD0TFfiREL2tvcc3RPS4BDN+u1TkHGHUPVdFGsdsR8AIWePbib
i0UJ352OG58Ne/D8bjCLvrlja+tKfbWNSL9cB0MPsF/tJAgcYblr2GkzSMWcKlcj
h2PC6DJuTfgQLw==
-----END CERTIFICATE-----

    e) Check MySQL SSL support status

    Connect to the instance via mysql -uroot -p (in Windows, mysql.exe is locatedit in C:\Programa Files\MySQL\MySQLnon-production Serverenvironment 8.0\bin)first.

  3. Validate AIQ database connectivity and run:

    Connectlogs.
  4. Document torollback the instance via mysql -uroot -p (in Windows, mysql.exe is located in C:\Program Files\MySQL\MySQL Server 5.7\bin)steps and run:certificate
    renewal 
    mysql> show global variables like '%ssl%';
+-------------------------------------+-----------------+
| Variable_name                       | Value           |
+-------------------------------------+-----------------+
| admin_ssl_ca                        |                 |
| admin_ssl_capath                    |                 |
| admin_ssl_cert                      |                 |
| admin_ssl_cipher                    |                 |
| admin_ssl_crl                       |                 |
| admin_ssl_crlpath                   |                 |
| admin_ssl_key                       |                 |
| have_openssl                        | YES             |
| have_ssl                            | YES             |
| mysqlx_ssl_ca                       |                 |
| mysqlx_ssl_capath                   |                 |
| mysqlx_ssl_cert                     |                 |
| mysqlx_ssl_cipher                   |                 |
| mysqlx_ssl_crl                      |                 |
| mysqlx_ssl_crlpath                  |                 |
| mysqlx_ssl_key                      |                 |
| performance_schema_show_processlist | OFF             |
| ssl_ca                              | ca.pem          |
| ssl_capath                          |                 |
| ssl_cert                            | server-cert.pem |
| ssl_cipher                          |                 |
| ssl_crl                             |                 |
| ssl_crlpath                         |                 |
| ssl_fips_mode                       | OFF             |
| ssl_key                             | server-key.pem  |
+-------------------------------------+-----------------+
25 rows in set (0.02 sec)
    ownership.

f) Grant permissions allowing SSL connection.

ALTER USER 'aiq'@'localhost','aiq'@'%' REQUIRE SSL;
FLUSH PRIVILEGES;

g) Create the folder for our certificates:


mkdir C:\mysqlCerts

h) Create CA Certificates.

openssl genrsa 2048 > "C:\mysqlCerts\ca-key.pem"openssl req -new -x509 -nodes -days 3600 -key "C:\mysqlCerts\ca-key.pem" > "C:\mysqlCerts\ca-cert.pem"Country Name (2 letter code) [AU]:US
State or Province Name (full name) [Some-State]:CA
Locality Name (eg, city) []:SanJose
Organization Name (eg, company) [Internet Widgits Pty Ltd]:Appvance
Organizational Unit Name (eg, section) []:
Common Name (e.g. server FQDN or YOUR name) []:
Email Address []:info@appvance.com

i) Create Server certificates.

openssl req -newkey rsa:2048 -days 3600 -nodes -keyout "C:\mysqlCerts\server-key.pem" > "C:\mysqlCerts\server-req.pem"Provide the same values as defined above.
Can give blank challenge password and optional company name
openssl x509 -req -in "C:\mysqlCerts\server-req.pem" -days 3600 -CA "C:\mysqlCerts\ca-cert.pem" -CAkey "C:\mysqlCerts\ca-key.pem" -set_serial 01 > "C:\mysqlCerts\server-cert.pem"

j) Create client certificates.

openssl req -newkey rsa:2048 -days 3600 -nodes -keyout "C:\mysqlCerts\client-key.pem" > "C:\mysqlCerts\client-req.pem"Provide the same values as above.
openssl x509 -req -in "C:\mysqlCerts\client-req.pem" -days 3600 -CA "C:\mysqlCerts\ca-cert.pem" -CAkey "C:\mysqlCerts\ca-key.pem" -set_serial 01 > "C:\mysqlCerts\client-cert.pem"

k) Update MySQL's config file (in Windows, it's called my.ini, not my.cnf as in Linux, and not my-default.ini because the latter is a template), which normally lives in C:\ProgramData\MySQL\MySQL Server 8.0\ (note: ProgramData is a hidden folder), to include this under the [mysqld] section:

Note: If you have \s in your path, you will need to replace it with \\s because mysqld will substitue the \s for a whitespace character which will break the path to your key. The extra backslash escapes the original backslash, leaving your path intact. 

ssl-ca = "C:\mysqlCerts\ca-cert.pem"ssl-cert = "C:\mysqlCerts\\server-cert.pem"ssl-key = "C:\mysqlCerts\\server-key.pem"

l) and restart MYSQL service/server.

m) Now let's connect again via our normal user (not the ssluser) with mysql -uroot -p, and check our ssl variables:

mysql> show global variables like '%ssl%';
+-------------------------------------+-------------------------------+
| Variable_name                       | Value                         |
+-------------------------------------+-------------------------------+
| admin_ssl_ca                        |                               |
| admin_ssl_capath                    |                               |
| admin_ssl_cert                      |                               |
| admin_ssl_cipher                    |                               |
| admin_ssl_crl                       |                               |
| admin_ssl_crlpath                   |                               |
| admin_ssl_key                       |                               |
| have_openssl                        | YES                           |
| have_ssl                            | YES                           |
| mysqlx_ssl_ca                       |                               |
| mysqlx_ssl_capath                   |                               |
| mysqlx_ssl_cert                     |                               |
| mysqlx_ssl_cipher                   |                               |
| mysqlx_ssl_crl                      |                               |
| mysqlx_ssl_crlpath                  |                               |
| mysqlx_ssl_key                      |                               |
| performance_schema_show_processlist | OFF                           |
| ssl_ca                              | C:\mysqlCerts\ca-cert.pem     |
| ssl_capath                          |                               |
| ssl_cert                            | C:\mysqlCerts\server-cert.pem |
| ssl_cipher                          |                               |
| ssl_crl                             |                               |
| ssl_crlpath                         |                               |
| ssl_fips_mode                       | OFF                           |
| ssl_key                             | C:\mysqlCerts\server-key.pem  |
+-------------------------------------+-------------------------------+
25 rows in set (0.01 sec)

n) connect with the aiq user then, now with the --ssl-mode flag:

mysql.exe -uaiq -p --ssl-mode=REQUIRED

o) If it connects, we're done.

C:\Program Files\MySQL\MySQL Server 8.0\bin>mysql.exe -uaiq -p --ssl-mode=REQUIREDEnter password: ***Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 12Server version: 8.0.22 MySQL Community Server - GPL
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
 

Last updated: JuneSeptember 26,20, 20232026

If you have any feedback on this topic, please contact us at documentation@appvance.ai

Back to top