Configure Database Nodes and Router
- 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:
Thessl-cert
andssl-key
are not mandatory forVERIFY_IDENTITY
. However, if the database user requires X509 authentication, then bothssl-cert
andssl-key
must be provided withclient-cert
andclient-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%';