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
  

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 and AFTERCSN 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 is f77024f9-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 disable gtid_mode on the source MySQL database. This will return the Extract’s capture behavior to using the log number and offset method.

Requirements for Supporting Group Replication

This topic describes the requirements for using Oracle GoldenGate with MySQL Group Replication database clusters.

• 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:

• Limitations of Group Replication with Oracle GoldenGate for MySQL
  

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.

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
  

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