Starting with MySQL Shell 8.0.33, it's possible to fully enable SSL/TLS certificate-based authentication seamlessly across an InnoDB Cluster, ClusterSet or, ReplicaSet setup.

Let's dive into the details of the new features with complete examples of how to set up the strongest encryption configuration possible.

Certificate Based Authentication For Intra-node Communication

To secure and validate users for the internally managed replication accounts, it's now possible to specify certificate-based (and optionally passwordless) authentication. This makes it possible to enforce that the connections using the managed replication accounts must supply valid credentials instead of just a password (both can be used simultaneously). In other words, when Cluster members enter distributed recovery, the client used to connect to the source must have a valid certificate for authentication and identity verification.

On the other hand, it's now also possible to require TLS encryption with additional certificate-based validation for the Cluster's Group Replication internal channels and for the asynchronous replication channels in InnoDB ReplicaSet and InnoDB ClusterSet. This means that all the connections used to transfer data between members are secure.

The next two diagrams illustrate how all communication channels in a ClusterSet (including its Clusters) and ReplicaSet can be completely secure.

InnoDB ClusterSet

InnoDB ReplicaSet 

Once each server is assigned a certificate , and by using the new options available in MySQL Shell 8.0.33, we can see that:

• All communications between members of a Cluster are protected and validated.
• The managed asynchronous replication channel between the Primary Cluster of a ClusterSet and any of the Replica Clusters is protected and validated.
• The asynchronous replication channels between the members of a ReplicaSet are protected and validated.

Certificate-based Authentication and Identity Verification

Verifying the identity of the source is done by using a new option, memberAuthType, exposed for both InnoDB Cluster and InnoDB ReplicaSet. This option controls the authentication type to use for the internally managed replication accounts, or in other words, it enforces any client wanting to connect using the managed replication account to supply valid credentials. The option supports the following values:

• PASSWORD - account authenticates with password only
• CERT_ISSUER - account authenticates with client certificate, which must match the expected issuer (equivalent to VERIFY_CA)
• CERT_SUBJECT - account authenticates with client certificate, which must match the expected issuer and subject (equivalent to VERIFY_IDENTITY)
• CERT_ISSUER_PASSWORD - combines both "CERT_ISSUER" and "PASSWORD" values
• CERT_SUBJECT_PASSWORD - combines both "CERT_SUBJECT" and "PASSWORD" values

This option is set when creating a Cluster or a ReplicaSet and ClusterSets inherit the option from the Primary Cluster, which means that all Replica Clusters will use the memberAuthType configuration of the Primary Cluster. When the option value contains "ISSUER", another option, certIssuer, must be supplied with a value to match with the certificate issuer of the client trying to connect. Similarly, when memberAuthType contains "SUBJECT" the option certSubject must be supplied and match with the client certificate subject.

NOTE: The certIssuer is a Cluster or ReplicaSet wide option, while certSubject is a per-instance option.

SSL Mode

To control the SSL mode of a Cluster's Group Replication internal channels and of a ClusterSet asynchronous replication channels, we have the options memberSslMode and clusterSetReplicationSslMode respectively. MySQL Shell 8.0.33 introduces the equivalent option, replicationSslMode, for the SSL mode of asynchronous replication channels of ReplicaSets. All three options accept the following values:

• DISABLED: TLS encryption is disabled for the replication channel.
• REQUIRED: TLS encryption is enabled for the replication channel.
• VERIFY_CA: like "REQUIRED", but additionally verify the peer server TLS certificate against the configured Certificate Authority (CA) certificates.
• VERIFY_IDENTITY: like "VERIFY_CA", and additionally verify that the peer server certificate matches the host to which the connection is attempted.
• AUTO: TLS encryption will be enabled (like "REQUIRED") if supported by the instance, otherwise disabled.

How to secure a InnoDB ClusterSet setup

Using MySQL Shell, we will setup a two cluster InnoDB ClusterSet where all Clusters require that the replication accounts supply a password and a valid certificate issuer and subject. Additionally, GR and any other managed replication channels must use "VERIFY_IDENTITY" mode. This set-up will ensure the strongest encryption configuration currently supported.

Certificates

The first thing we need to do is to create a valid Certificate Authority. In this example, and for the sake of simplicity, a self-signed CA:

$ mkdir ssl-ca
$ echo "basicConstraints=CA:TRUE\n" > ssl-ca/cav3.ext

$ openssl req -newkey rsa:2048 -days 3650 -nodes -keyout ssl-ca/ca-key.pem -subj '/CN=MyName' -out ssl-ca/ca-req.pem && openssl rsa -in ssl-ca/ca-key.pem -out ssl-ca/ca-key.pem
$ openssl x509 -sha256 -days 3650 -extfile ssl-ca/cav3.ext -CAcreateserial -req -in ssl-ca/ca-req.pem -signkey ssl-ca/ca-key.pem -out ssl-ca/ca.pem

$ rm ssl-ca/cav3.ext
$ rm ssl-ca/ca-req.pem

Replace MyName with the appropriate value. Notice that, in the following examples, this will be used as "/CN=MyName" in the certIssuer argument.

With the generated CA, we can create a certificate for each server in our ClusterSet. Since we want to use "CERT_SUBJECT_PASSWORD", we need to have a valid CommonName, which must match the server hostname.

$ mkdir ssl-servers
$ echo "basicConstraints=CA:TRUE\n" > ssl-servers/cert3.ext

$ openssl req -newkey rsa:2048 -days 3650 -nodes -keyout ssl-servers/s1-key.pem -subj '/CN=s1-hostname' -out ssl-servers/s1-req.pem && openssl rsa -in ssl-servers/s1-key.pem -out ssl-servers/s1-key.pem
$ openssl x509 -sha256 -days 3650 -extfile ssl-servers/cert3.ext -req -in ssl-servers/s1-req.pem -CA ssl-ca/ca.pem -CAkey ssl-ca/ca-key.pem -CAcreateserial -out ssl-servers/s1-cert.pem

$ rm ssl-servers/cert3.ext
$ rm ssl-servers/s1-req.pem

This will generate the certificates for one server: "s1". You must do this for each server individually, taking special notice to replace s1-hostname with the correct hostname for the corresponding server. In the following examples, this will be used as "/CN=s1-hostname" in the certSubject argument.

Quick check to see if the certificates are correct:

$ openssl verify -CAfile ssl-ca/ca.pem ssl-servers/s1-cert.pem

We now need to configure each server with these certificates. This must be made manually by copying the respective files to the corresponding server and editing its configuration file and adding these lines:

[mysqld]
ssl_ca = ssl-ca/ca.pem
ssl_cert = ssl-servers/s1-cert.pem
ssl_key = ssl-servers/s1-key.pem

This is for a single server "s1": it should be repeated for each server using the correct generated certificates. Note that if you start the server with these options, it will automatically require all users to use an SSL connection. To avoid this, you can add require_secure_transport = OFF to the configuration file.

NOTE: these SSL options are just an example and should not be used directly in a production environment.

Create the Primary Cluster

We can know proceed and create the Primary Cluster. Because we want to use "CERT_SUBJECT_PASSWORD", we need to specify both certIssuer and certSubject.

Connect to "s1" and create the primary Cluster with the following options:

mysqlsh-js> primary = dba.createCluster("primary", {memberSslMode: "VERIFY_IDENTITY", memberAuthType: "CERT_SUBJECT_PASSWORD", certIssuer: "/CN=MyName", certSubject: "/CN=s1-hostname"});

We can now add a secondary member to the Cluster. Notice that we need to supply certSubject with the correct CommonName of the target server, because this option is per-instance and the cluster's memberAuthType requires it.

mysqlsh-js> primary.addInstance("s2-address", {certSubject: "/CN=s2-hostname"});

Before adding the instance, MySQL Shell performs a connectivity check using the supplied configuration. If any problem with either the SSL certificates or network is found, the command is canceled and any change reverted. This behavior can be controlled with the global Shell option dba.connectivityChecks, and is enabled by default.

Let's check if the cluster options are correctly set:

mysqlsh-js> primary.options();

Create the ClusterSet

We can now create the ClusterSet. Remember that the ClusterSet inherits the memberAuthType of the Primary Cluster, so that option can't be specified. We need to specify, however, the clusterSetReplicationSslMode option so that new replicas require encrypted connections for their ClusterSet asynchronous replication channels.

mysqlsh-js> cluster_set = primary.createClusterSet("cluster_set", {clusterSetReplicationSslMode: "VERIFY_IDENTITY"});

And check that the options are correct:

mysqlsh-js> cluster_set.options();

Create the Replica Cluster

With the ClusterSet created and configured, we can now create a Replica Cluster. Since the inherited memberAuthType option is "CERT_SUBJECT_PASSWORD", we need to supply certSubject when creating the new replica:

mysqlsh-js> replica = cluster_set.createReplicaCluster("s3-address", "replica", {certSubject: "/CN=s3-hostname"});

Add an instance to the replica:

mysqlsh-js> replica.addInstance("s4-address", {certSubject: "/CN=s4-hostname"});

And check if the Replica Cluster options are correct:

mysqlsh-js> replica.options();

And that's it: all connections between members of the ClusterSet are encrypted and verified, including Group Replication, Asynchronous Managed Channels and Distributed Recovery channels.

More Information

For more information please consult the documentation on the topic:

• Securing InnoDB Cluster/ClusterSet
• ReplicaSet Encryption and Authentication

As always, we are eager to listen to the community feedback. You can reach us at #shell and #mysql_innodb_cluster in Slack: https://mysqlcommunity.slack.com/

If you find a problem or a missing feature, please reach out to us via a bug report, or a support ticket.

Resources:

• Download our MySQL Community Shell & Server packages at https://dev.mysql.com/downloads/.
• MySQL Enterprise binaries are available on My Oracle Support: https://support.oracle.com (Choose the “Patches & Updates” tab, and then choose the “Product or Family (Advanced Search)” side tab in the “Patch Search” portlet.)
• or use Oracle Software Delivery Cloud on http://edelivery.oracle.com/ to download MySQL Enterprise binaries.

Enjoy, and Thank you for using MySQL!