2.4. Setting up TLS encryption on a MySQL server

Procedure

1. Store the CA and server certificates in the /etc/pki/tls/certs/ directory:

   # mv <path>/server.example.com.crt.pem /etc/pki/tls/certs/
   # mv <path>/ca.crt.pem /etc/pki/tls/certs/

2. Set permissions on the CA and server certificate that enable the MySQL server to read the files:

   # chmod 644 /etc/pki/tls/certs/server.example.com.crt.pem /etc/pki/tls/certs/ca.crt.pem

   Because certificates are part of the communication before a secure connection is established, any client can retrieve them without authentication. Therefore, you do not need to set strict permissions on the CA and server certificate files.

3. Store the server’s private key in the /etc/pki/tls/private/ directory:

   # mv <path>/server.example.com.key.pem /etc/pki/tls/private/

4. Set secure permissions on the server’s private key:

   # chmod 640 /etc/pki/tls/private/server.example.com.key.pem
   # chgrp mysql /etc/pki/tls/private/server.example.com.key.pem

   If unauthorized users have access to the private key, connections to the MySQL server are no longer secure.

5. Restore the SELinux context:

   # restorecon -Rv /etc/pki/tls/

Procedure

1. Create the /etc/my.cnf.d/mysql-server-tls.cnf file:

   1. Add the following content to configure the paths to the private key, server and CA certificate:

      [mysqld]
      ssl_key = /etc/pki/tls/private/server.example.com.key.pem
      ssl_cert = /etc/pki/tls/certs/server.example.com.crt.pem
      ssl_ca = /etc/pki/tls/certs/ca.crt.pem

   2. If you have a Certificate Revocation List (CRL), configure the MySQL server to use it:

      ssl_crl = /etc/pki/tls/certs/example.crl.pem

   3. Optional: Reject connection attempts without encryption. To enable this feature, append:

      require_secure_transport = on

   4. Optional: Set the TLS versions the server should support. For example, to support only TLS 1.3, append:

      tls_version = TLSv1.3

      By default, the server supports TLS 1.2 and TLS 1.3.

2. Restart the mysqld service:

   # systemctl restart mysqld

1. Verify that MySQL now has TLS encryption enabled:

   # mysql -u root -p -h <MySQL_server_hostname> -e "SHOW session status LIKE 'Ssl_cipher';"
   +---------------+------------------------+
   | Variable_name | Value                  |
   +---------------+------------------------+
   | Ssl_cipher    | TLS_AES_256_GCM_SHA384 |
   +---------------+------------------------+

2. If you configured the MySQL server to only support specific TLS versions, display the tls_version variable:

   # mysql -u root -p -e "SHOW GLOBAL VARIABLES LIKE 'tls_version';"
   +---------------+---------+
   | Variable_name | Value   |
   +---------------+---------+
   | tls_version   | TLSv1.3 |
   +---------------+---------+

3. Verify that the server uses the correct CA certificate, server certificate, and private key files:

   # mysql -u root -e "SHOW GLOBAL VARIABLES WHERE Variable_name REGEXP '{caret}ssl_ca|{caret}ssl_cert|{caret}ssl_key';"
   +-----------------+-------------------------------------------------+
   | Variable_name   | Value                                           |
   +-----------------+-------------------------------------------------+
   | ssl_ca          | /etc/pki/tls/certs/ca.crt.pem                   |
   | ssl_capath      |                                                 |
   | ssl_cert        | /etc/pki/tls/certs/server.example.com.crt.pem   |
   | ssl_key         | /etc/pki/tls/private/server.example.com.key.pem |
   +-----------------+-------------------------------------------------+

Procedure

1. Connect as an administrative user to the MySQL server:

   # mysql -u root -p -h server.example.com

   If your administrative user has no permissions to access the server remotely, perform the command on the MySQL server and connect to localhost.

2. Use the REQUIRE SSL clause to enforce that a user must connect by using a TLS-encrypted connection:

   MySQL [(none)]> ALTER USER 'example'@'%' REQUIRE SSL;

Verification

1. Connect to the server as the example user by using TLS encryption:

   # mysql -u example -p -h server.example.com
   ...
   MySQL [(none)]>

   If no error is shown and you have access to the interactive MySQL console, the connection with TLS succeeds.

   By default, the client automatically uses TLS encryption if the server provides it. Therefore, the --ssl-ca=ca.crt.pem and --ssl-mode=VERIFY_IDENTITY options are not required, but improve the security because, with these options, the client verifies the identity of the server.

2. Attempt to connect as the example user with TLS disabled:

   # mysql -u example -p -h server.example.com --ssl-mode=DISABLED
   ERROR 1045 (28000): Access denied for user 'example'@'server.example.com' (using password: YES)

   The server rejected the login attempt because TLS is required for this user but disabled (--ssl-mode=DISABLED).