SSL Configuration on Group Replication Cluster

Learn about SSL configuration on Group Replication Cluster.

Overview of Database Cluster SSL Configuration for Group Replication

A clustered database environment contains different nodes, constituting one primary node and one or more secondary nodes. There can be only one primary node at any instant. Each node has its own distinct hostname with a MySQL database instance, which is maintained by a separate configuration for that particular node. All the nodes in the cluster collectively represent the database.

There is a Router as well, which is the first point of contact for any client trying to connect to the database.

When enabling SSL connectivity, all of the database nodes and the Router will need to have their own authorization keys and server certificates. These certificates must be authorized by a common Certificate Authority (CA).

The certificates that are commonly used for this setup are:

  • ca.pem: The certificate of the common CA (Certification Authority)

  • server-cert.pem: The certificate that is certified by the CA for identifying the database node

  • server-key.pem: The private key of the individual database node

  • router-cert.pem: The certificate that is certified by the CA for identifying the router

  • router-key.pem: The private key of the router

Configuration for the Router and database nodes is described in the following tables. For the purpose of this explanation, the following example shows one router and three database nodes.

Table 9-1 Router and Database Node Configuration

Router -

Hostname

mysqlrouter.company.com

Config Filename

mysqlrouter.conf

Port

6446

Common Name

mysqlrouter.company.com

Certificate Name

server-cert.pem

Key file name

server-key.pem

Database Node 1

-

Hostname

dbnode1.company.com

Config Filename

my.cnf

Port

3308

Common Name

dbnode1

Certificate Name

server-cert.pem

Key file name

server-key.pem

Node Rank

Primary

Database Node2

-

Hostname

dbnode2.company.com

Config Filename

my.cnf

Port

3308

Common Name

dbnode2

Certificate Name

server-cert.pem

Key file name

server-key.pem

Node Rank

Secondary

Database Node3

-

Hostname

dbnode3.company.com

Config Filename

my.cnf

Port

3308

Common Name

dbnode3

Certificate Name

server-cert.pem

Key file name

server-key.pem

Node Rank

Secondary

Create Server Certificates

Before you begin configuring the router and database nodes, you'll need to create SSL server certificates. For connecting database nodes and router using SSL, you must have the right SSL keys and certificates for secure communication. All certificates must be recognized by a common Certification Authority (CA). If the keys and certificates were auto-generated during database/router installation (or if they are self-signed) then the connection might fail. Only certificates that are authorized by a CA are allowed to proceed further.

If the authorized server key and certificates are already available, then ensure that the certificates have the correct permissions and have been placed in the correct path for the router/database node.

For steps to generate SSL certificates for server, see:

Creating SSL Certificates and Keys Using OpenSSL

Tasks for Configuring SSL Certificates

  1. Generate a separate certificate and key for each database node.

  2. Use the same ca.pem which is common to all database nodes and routers.

  3. In the server-certificate for the database nodes, specify the common name without the domain name. See the common name in the Table 9-1 in Overview of Database Cluster SSL Configuration for Group Replication for reference.

  4. Ensure that the server certificate name and key file name match the corresponding database node and router values.

  5. To verify the CN values in each generated server certificate, invoke openSSL using the following commands :
    openssl x509 -text -in ca.pem
openssl x509 -text -in server-cert.pem
openssl x509 -text -in client-cert.pem

    The issuer CN must be the same for all. The subject CN must contain only hostname without domain name.

  6. After generating the certificates, verify them against the CA file.

  7. Copy the generated certificate and key file to the MySQL data directory for each database node and router. Ensure that you provide read permission to all users and retain write permission to file owner only.

  8. Copy the common ca.pem to every node and router and provide read permissions to all users.

Configure Database Nodes and Router

Use the settings similar to the following, to configure database nodes and router for connecting over a secure SSL connection.
Router

In the Router config file, ensure that the below settings are present:

CLIENT_SSL_MODE=PREFERRED
CLIENT_SSL_CERT=absolute path of the generated router certificate
CLIENT_SSL_KEY=absolute path of the generated router key
SERVER_SSL_MODE=AS_CLIENT
SERVER_SSL_VERIFY=VERIFY_IDENTITY
SERVER_SSL_CA=absolute path of the common ca.pem placed on this server

After it is configured, provide read permissions to all users and revoke write permissions from group and others.

Database Node
In each of the MySQL database nodes, make sure the following are set under the appropriate section:
SSL_CAPATH=absolute path of the common ca.pem placed on this node
SSL_CA=ca.pem
SSL_CERT=server-cert.pem
SSL_KEY=server-key.pem
GROUP_REPLICATION_SSL_MODE=REQUIRED
REQUIRE_SECURE_TRANSPORT=ON

After configuring the database node, provide read permissions to all users and revoke write permissions from group and others.

Testing the Connection

After the configurations are in place and the appropriate permissions have been provided to the configuration files, test the settings by restarting the database nodes and router.

Test the Database Nodes Connection
Ensure that the database node does not terminate. Check the logs under log-error setting in the configuration file for any errors or warnings that indicate the SSL settings were not accepted. Try connecting to the specific node using the following command line (use the common name as specified in the certificate for this node):
mysql -u username -p password -h db_common_name -P db_port --ssl-mode=VERIFY_IDENTITY --ssl-ca=path/of/ca.pem

Make sure that the connection does not generate any errors.

Similarly, connect with different SSL-modes by providing the appropriate parameter values.

Note:

The ssl-cert and ssl-key are not mandatory for VERIFY_IDENTITY. However, if the database user requires X509 authentication, then both ssl-cert and ssl-key must be provided with client-cert and client-key.

Test all database nodes using this method and then test the router connection.

Test the Router Connection

After the database nodes are up, restart the router and monitor it ensuring it does not terminate.

Check the logs under log-error setting in the configuration file for any errors or warnings that indicate the SSL settings were not accepted. If there are no errors or warnings, try connecting to the database from the router using the following command. Make sure you use the common name as specified in the certificate for the router:
mysql -u username -p password -h router_common_name -P router_port --ssl-mode=VERIFY_IDENTITY --ssl-ca=path/of/ca.pem

Ensure that connection goes through without any errors.

Verify the Connection from the Router to the Database Node
First determine the currently active primary node, using the following command:
MySQL> SHOW VARIABLES like '%hosts%';
Now logout from the database and switchover the database to another node. Then login to the database from the router again, using the following command:
mysql -u username -p password -h router_common_name -P router_port --ssl-mode=VERIFY_IDENTITY --ssl-ca=path/of/ca.pem

Check the currently active primary node using the same command again:

MySQL> SHOW VARIABLES like '%hosts%';