Stelo Technical Documents

Configuring MySQL SSL Connections

Last Update: 2024/04/09
Product: SQDR Plus and SQDR
Version: All
Article ID: SQV00PL086

Overview

Goal: To connect from Tier 2 (Stelo Capture) to a MySQL source database using SSL/TLS connections

Method: use the wrapper-local.conf mechanism to use both v8 & v5 MySQL JDBC drivers.

If no SSL parameters are passed to the v8 JDBC driver, it uses a default setting of sslMode=PREFERRED, which is equivalent to the legacy settings of "useSSL=true", "requireSSL=false", and "verifyServerCertificate=false"

This procedure was tested with AWS RDS for MySQL v8.0.35 and MySQL Connector/J v8.0.33.

Procedure

Download & Stage the v8 JDBC driver
Verify host (tier 1) settings
Create Agent User
Create Agent
Modify the SQDR Plus (Tier 2) environment to use both v5 & v8 JDBC drivers

SQDR (Tier 3)

 

Download & Stage the v8 JDBC driver

Download a recent version of the MySQL JDBC driver from:
https://dev.mysql.com/downloads/connector/j/

Select the Archives panel to download older versions (e.g. 8.0.33).

Extract mysql-connector-j-8.0.33.jar from the downloaded jar file and copy it to a known place. In our example, we used
C:\ProgramData\StarQuest\sqdrplus\conf\.

There is no need to download the v5 driver, as it is bundled with SQDR Plus.

Verify host (T1) settings

The source must allow non-SSL connections temporarily while we create the agent. For an AWS source, use the AWS console to examine the DB instance parameter group (e.g. mysql80custom) and confirm that require_secure_transport = 0. This is a dynamic setting, so it is not necessary to restart MySQL after modifying it.

You should also verify these settings which are described in the Quick Start Guide to Using SQDR Plus.

• binlog_format = ROW
  
• log_bin_trust_function_creators = 1
  
• binlog retention hours has been set to a value other than the default of NULL; execute the following SQL:
  CALL mysql.rds_show_configuration;
  CALL mysql.rds_set_configuration('binlog retention hours', 24);
  

Create Agent User

Create Agent User as described in the Quick Start Guide to Using SQDR Plus.

Create Agent

Use SQDR Control Center's Add Agent Wizard to create the agent. Do not select the Use SSL checkbox.

After creating the agent, do a grant similar to this (where SQDRUSER is the user account that tier 3 will be using to access the host database)
GRANT SELECT ON SQDR.SQ_PROPERTIES TO SQDRUSER

Modify the SQDR Plus (Tier 2) environment to use both v5 & v8 JDBC drivers

1. Create a text file C:\ProgramData\StarQuest\sqdrplus\conf\wrapper-local.conf

containing the following:
wrapper.java.classpath.8=C:\ProgramData\StarQuest\sqdrplus\conf\mysql-connector-j-8.0.33.jar
wrapper.java.classpath.20=C:\Program Files\StarQuest\sqdrplus\/capagent/mysql-connector.jar

Those values may change in the future.

2. Restart SQDR Plus Launch Agent Service from the Services control panel
   
3. Edit the following properties in the agent configuration:

sourceDbDriver - com.mysql.cj.jdbc.Driver
sourceDbUrl - remove useSSL=false from the URL

Use the Plus symbol to add the following property:
useSSL - true (this is info passed to the binlog reader)

Save the configuration; this will restart the Agent.

4. Verify that the Agent is still able to connect to the source database.
5. If the agent is occasionally failing with "cannot load JDBC driver" errors, it may be necessary to restart the system.
6. Configure the source to restrict connections to SSL only For AWS, modify the DB instance parameter group to set require_secure_transport = 1
   
7. Verify that the agent is still able to connect to the source database.

SQDR (Tier 3)

We recommend using MySQL Connector ODBC v8.0.27. The default behavior of MySQL ODBC v8.0.x is similar to that of the v8 JDBC driver, where SSL connections are preferred and no special configuration is required.