15 Using Oracle GoldenGate with MySQL Group Replication
This topic describes the requirements and configuration steps for setting up Oracle GoldenGate to support MySQL Group Replication.
Topics:
- Oracle GoldenGate Features to Support MySQL Group Replication
- Requirements for Supporting Group Replication
- SSL Configuration on Group Replication Cluster
Parent topic: Using Oracle GoldenGate for MySQL
Oracle GoldenGate Features to Support MySQL Group Replication
The following are Oracle GoldenGate features required to support capture from a MySQL database Group Replication instance.
- CSN Format
-
The Extract for MySQL Group Replication uses a new CSN format that is based on the Group Replication Global Transaction ID. This CSN format should be used with
ATCSN
andAFTERCSN
when manually positioning a MySQL Group Replication Extract or Replicat whose source trail was generated by a MySQL Group Replication Extract.An example of the sequence used in group replication capture is:00000000000000000001:f77024f9-f4e3-11eb-a052-0021f6e03f10:0000000000000010654
In this sequence, the Oracle GoldenGate sequence number is
00000000000000000001
and the GTID isf77024f9-f4e3-11eb-a052-0021f6e03f10:0000000000000010654
.
- Extended Checkpoint Support
-
The Extract for MySQL Group Replication includes an extended checkpoint file in addition to the core Extract checkpoint file. The extended checkpoint file is created in the same checkpoint directory where the core checkpoint and has a cpex extension after the name of the capture group for example,
extmysql.cpex
.This file is created when Extract starts and is deleted when Extract is deleted and should not be edited.
- Using GTID-based Extract
-
If
gtid_mode
is enabled in MySQL database, then Oracle GoldenGate Extract for MySQL automatically starts using the GTID-based recovery mechanism and extended checkpoint, which enables it to support failover and recovery. There is no extra parameter required for the Extract.Note:
If not using Group Replication, it is recommended to disablegtid_mode
on the source MySQL database. This will return the Extract’s capture behavior to using the log number and offset method.
Parent topic: Using Oracle GoldenGate with MySQL Group Replication
Requirements for Supporting Group Replication
-
Oracle GoldenGate for MySQL Group Replication supports MySQL version 8.0 and higher and requires Oracle GoldenGate version 21.7 or higher.
-
Only Group Replication configured in Single-Primary Mode is supported for Extract.
-
The MySQL database setting
gtid_mode
must be enabled.
Topic:
Parent topic: Using Oracle GoldenGate with MySQL Group Replication
Limitations of Group Replication with Oracle GoldenGate for MySQL
The following limitations of support apply when using MySQL Group Replication with Oracle GoldenGate for MySQL:
-
Group Replication configured for multi-primary mode is not supported.
-
Extract does not support remote trails nor multiple local trails when configured against a MySQL Group Replication instance. If you need to use remote trails, then you can use data Pump to send the trail in Classic Architecture. In Microservices Architecture, use the DISTPATH to transport the trail. See Manage Distribution Paths.
-
When Extract is running against a database configured for Group Replication, positioning by log number and offset is not supported.
Parent topic: Requirements for Supporting Group Replication
SSL Configuration on Group Replication Cluster
Learn about SSL configuration on Group Replication Cluster.
Topics:
- Overview of Database Cluster SSL Configuration for Group Replication
- Create Server Certificates
- Configure Database Nodes and Router
Parent topic: Using Oracle GoldenGate with MySQL Group Replication
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).
-
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 15-1 Router and Database Node Configuration
Router | - |
---|---|
Hostname |
|
Config Filename |
|
Port |
|
Common Name |
|
Certificate Name |
|
Key file name |
|
Database Node 1 |
- |
Hostname |
|
Config Filename |
|
Port |
|
Common Name |
|
Certificate Name |
|
Key file name |
|
Node Rank |
Primary |
Database Node2 |
- |
Hostname |
|
Config Filename |
|
Port |
|
Common Name |
|
Certificate Name |
|
Key file name |
|
Node Rank |
Secondary |
Database Node3 |
- |
Hostname |
|
Config Filename |
|
Port |
|
Common Name |
|
Certificate Name |
|
Key file name |
|
Node Rank |
Secondary |
Parent topic: SSL Configuration on Group Replication Cluster
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
-
Generate a separate certificate and key for each database node.
-
Use the same
ca.pem
which is common to all database nodes and routers. -
In the server-certificate for the database nodes, specify the common name without the domain name. See the common name in the Table 15-1 in Overview of Database Cluster SSL Configuration for Group Replication for reference.
-
Ensure that the server certificate name and key file name match the corresponding database node and router values.
-
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.
-
After generating the certificates, verify them against the CA file.
-
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.
-
Copy the common
ca.pem
to every node and router and provide read permissions to all users.
Parent topic: SSL Configuration on Group Replication Cluster
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%';
Parent topic: SSL Configuration on Group Replication Cluster