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%';