6.3.1 Configuring MySQL to Use Encrypted Connections

On the server side, the --ssl option specifies that the server permits but does not require encrypted connections. This option is enabled by default, so it need not be specified explicitly.

To require that clients connect using encrypted connections, enable the require_secure_transport system variable. See Configuring Encrypted Connections as Mandatory.

These options on the server side specify the certificate and key files the server uses when permitting clients to establish encrypted connections:

For example, to enable the server for encrypted connections, start it with these lines in the my.cnf file, changing the file names as necessary:

[mysqld]
ssl-ca=ca.pem
ssl-cert=server-cert.pem
ssl-key=server-key.pem

To specify in addition that clients are required to use encrypted connections, enable the require_secure_transport system variable:

[mysqld]
ssl-ca=ca.pem
ssl-cert=server-cert.pem
ssl-key=server-key.pem
require_secure_transport=ON

Each certificate and key option names a file in PEM format. Should you need to create the required certificate and key files, see Section 6.3.3, “Creating SSL and RSA Certificates and Keys”. MySQL servers compiled using OpenSSL can generate missing certificate and key files automatically at startup. See Section 6.3.3.1, “Creating SSL and RSA Certificates and Keys using MySQL”. Alternatively, if you have a MySQL source distribution, you can test your setup using the demonstration certificate and key files in its mysql-test/std_data directory.

The server performs certificate and key file autodiscovery. If --ssl is enabled (possibly along with --ssl-cipher) and other --ssl-xxx options are not given to configure encrypted connections explicitly, the server attempts to enable encrypted connection support automatically at startup:

If the server automatically enables encrypted connection support, it writes a note to the error log. If the server discovers that the CA certificate is self-signed, it writes a warning to the error log. (The certificate is self-signed if created automatically by the server or manually using mysql_ssl_rsa_setup.)

MySQL also provides these options for server-side SSL control:

The values of the --ssl-xxx options set the values of the corresponding system variables (ssl_ca, ssl_cert, ssl_key, and so forth).

To explicitly specify which encryption protocols and ciphersuites the server permits for encrypted connections, use the tls_version and tls_ciphersuites system variables; see Section 6.3.2, “Encrypted Connection Protocols and Ciphers”. For example, you can set tls_version to prevent clients from using less-secure protocols.

Certain encryption-related system variables can be set at runtime: require_secure_transport, and (as of MySQL 8.0.16) tls_version and tls_ciphersuites. If changed with SET GLOBAL, the new values apply to connections established subsequent to the change, and apply only until server restart. If changed with SET PERSIST, the new values also apply to subsequent server restarts. See Section 13.7.5.1, “SET Syntax for Variable Assignment”.

In addition, as of MySQL 8.0.16, the SSL context the server uses for new connections is reconfigurable at runtime. This capability may be useful, for example, to avoid restarting a MySQL server that has been running so long that its SSL certificate has expired.

The server creates the initial SSL context from the values that the context-related system variables have at startup. It also initializes a set of context-related status variables to indicate the values used in the context. The following table shows the system variables that define the SSL context and the corresponding status variables that indicate the currently active context values.

To reconfigure the SSL context at runtime, use this procedure:

The members of each pair of system and status variables may have different values temporarily due to the way the reconfiguration procedure works:

In some cases, ALTER INSTANCE RELOAD TLS by itself may suffice to reconfigure the SSL context, without changing any system variables. Suppose that the certificate in the file named by ssl_cert has expired. It is sufficient to replace the existing file contents with a nonexpired certificate and execute ALTER INSTANCE RELOAD TLS to cause the new file contents to be read and used for new connections.

By default, the RELOAD TLS action rolls back with an error and has no effect if the configuration values do not permit creation of a new SSL context. The previous context values continue to be used for new connections.

If the optional NO ROLLBACK ON ERROR clause is given and a new context cannot be created, rollback does not occur. Instead, a warning is generated and SSL is disabled for new connections.

The server-side --ssl option has an effect only at server startup on whether the server accepts SSL connections. It is ignored by, and has no effect on the operation of, ALTER INSTANCE RELOAD TLS. For example, you can use --ssl=0 to start the server with SSL connections disabled, then reconfigure SSL and execute ALTER INSTANCE RELOAD TLS to enable SSL connections at runtime.

ALTER INSTANCE RELOAD TLS changes only the SSL context the server itself uses for new connections. It does not affect the SSL context used by other enabled server plugins or components such as X Plugin or Group Replication. If you want to apply the reconfiguration to Group Replication's group communication connections, which take their settings from the server's SSL context-related system variables, you must issue STOP GROUP_REPLICATION followed by START GROUP_REPLICATION to stop and restart Group Replication.

By default, MySQL client programs attempt to establish an encrypted connection if the server supports encrypted connections, with further control available through the --ssl-mode option:

Attempts to establish an unencrypted connection fail if the require_secure_transport system variable is enabled on the server side to cause the server to require encrypted connections. See Configuring Encrypted Connections as Mandatory.

The following options on the client side identify the certificate and key files clients use when establishing encrypted connections to the server. They are similar to the options used on the server side, but --ssl-cert and --ssl-key identify the client public and private key:

For additional security relative to that provided by the default encryption, clients can supply a CA certificate matching the one used by the server and enable host name identity verification. In this way, the server and client place their trust in the same CA certificate and the client verifies that the host to which it connected is the one intended:

MySQL also provides these options for client-side SSL control:

Depending on the encryption requirements of the MySQL account used by a client, the client may be required to specify certain options to connect using encryption to the MySQL server.

Suppose that you want to connect using an account that has no special encryption requirements or that was created using a CREATE USER statement that included the REQUIRE SSL clause. Assuming that the server supports encrypted connections, a client can connect using encryption with no --ssl-mode option or with an explicit --ssl-mode=PREFFERED option:

mysql

Or:

mysql --ssl-mode=PREFERRED

For an account created with a REQUIRE SSL clause, the connection attempt fails if an encrypted connection cannot be established. For an account with no special encryption requirements, the attempt falls back to an unencrypted connection if an encrypted connection cannot be established. To prevent fallback and fail if an encrypted connection cannot be obtained, connect like this:

mysql --ssl-mode=REQUIRED

If the account has more stringent security requirements, other options must be specified to establish an encrypted connection:

For additional information about the REQUIRE clause, see Section 13.7.1.3, “CREATE USER Syntax”.

To prevent use of encryption and override other --ssl-xxx options, invoke the client program with --ssl-mode=DISABLED:

mysql --ssl-mode=DISABLED

To determine whether the current connection with the server uses encryption, check the session value of the Ssl_cipher status variable. If the value is empty, the connection is not encrypted. Otherwise, the connection is encrypted and the value indicates the encryption cipher. For example:

For the mysql client, an alternative is to use the STATUS or \s command and check the SSL line:

Or:

For some MySQL deployments it may be not only desirable but mandatory to use encrypted connections (for example, to satisfy regulatory requirements). This section discusses configuration settings that enable you to do this. These levels of control are available:

To require that clients connect using encrypted connections, enable the require_secure_transport system variable. For example, put these lines in the server my.cnf file:

[mysqld]
require_secure_transport=ON

Alternatively, to set and persist the value at runtime, use this statement:

SET PERSIST sets the value for the running MySQL instance. It also saves the value, causing it to be used for subsequent server restarts. See Section 13.7.5.1, “SET Syntax for Variable Assignment”.

With require_secure_transport enabled, client connections to the server are required to use some form of secure transport, and the server permits only TCP/IP connections that use SSL, or connections that use a socket file (on Unix) or shared memory (on Windows). The server rejects nonsecure connection attempts, which fail with an ER_SECURE_TRANSPORT_REQUIRED error.

To invoke a client program such that it requires an encrypted connection whether or not the server requires encryption, use an --ssl-mode option value of REQUIRED, VERIFY_CA, or VERIFY_IDENTITY. For example:

mysql --ssl-mode=REQUIRED
mysqldump --ssl-mode=VERIFY_CA
mysqladmin --ssl-mode=VERIFY_IDENTITY

To configure a MySQL account to be usable only over encrypted connections, include a REQUIRE clause in the CREATE USER statement that creates the account, specifying in that clause the encryption characteristics you require. For example, to require an encrypted connection and the use of a valid X.509 certificate, use REQUIRE X509:

For additional information about the REQUIRE clause, see Section 13.7.1.3, “CREATE USER Syntax”.

To modify existing accounts that have no encryption requirements, use the ALTER USER statement.