Replicate data from MySQL Heatwave to Amazon Kinesis

Learn how to use OCI GoldenGate to replicate data from MySQL Heatwave to Amazon Kinesis.

Overview

This quickstart example demonstrates how to set up and run a replication between MySQL Heatwave to Amazon Kinesis using OCI GoldenGate.

Before you begin

To successfully complete this quicktart, you must have the following:

Task 0: Set up the environment

To set up the environment for this Quickstart:
  1. Download the sample data script, and then run the script on the MySQL Heatwave database to create the database and load the data.
  2. Create a ggadmin user using the following script. Remember to replace <ggadmin-password> with a valid password:
    CREATE USER 'ggadmin' IDENTIFIED BY '<ggadmin-password>';
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT, CREATE,CREATE VIEW, EVENT, INSERT, UPDATE, DROP,EXECUTE, DELETE ON *.* TO 'ggadmin';
  3. Open port 3306, through which OCI GoldenGate can connect.
    1. In the Oracle Cloud console, locate the subnet that the MySQL Heatwave database uses.
    2. In the security list of the subnet, create an Ingress rule for port TCP/3306.

Task 1: Create the OCI GoldenGate resources

  1. Create a MySQL deployment for the source MySQL Heatwave database.
  2. Create a Big Data deployment for Amazon Kinesis target.
  3. Create a connection to the source MySQL Heatwave Database.
  4. Connect to Amazon Kinesis.
  5. Create a connection to GoldenGate, and then assign this connection to the source MySQL deployment.
  6. Assign the source connection to the source MySQL deployment.
  7. Assign the target connection to the target Big Data deployment.

Task 2: Create the Extracts

  1. On the Deployments page, select the MySQL deployment created in Task 1.
  2. On the deployment details page, click Launch Console.
  3. Sign in to the source MySQL deployment console using the Administrator credentials specified when you created the deployment in Task 1.
  4. Open the navigation menu, and then select Configuration.
  5. Review the credentials for the MySQL Heatwave connection. Take note of the Domain and Alias.
  6. Return to the Administration Service Overview page.
  7. On the Administration Service Overview page, click Add Extract (plus icon).
  8. On the Extract Type page, select Change Data Capture Extract, and then click Next.
  9. Complete the Extract Options as follows, and then click Next:
    1. For Process Name, enter a name for the Extract, such as CDCEXT.
    2. For Credential Domain, select the domain from Step 5.
    3. For Credential Alias, select the alias from Step 5.
    4. For Trail Name, enter a two-character trail name, such as C1.
  10. Enable Remote if capturing from a MySQL Database that is not using global transaction identifiers (GTIDs).
  11. On the Extract Parameters page, add the following:
    TABLE SRC_OCIGGLL.*;
  12. Click Create and Run.

    You're returned to the Administration Service Overview page, where you can observe the Extract starting.

Task 3: Create the Distribution Path to target Big Data deployment

  1. On the Deployments page, select the target Big Data deployment.
  2. On the deployment details page, click Launch Console.
  3. Create a user for the Distribution Path:
    1. Open the navigation menu, and then click Administrator.
    2. Click Add New User (plus icon), complete the fields as follows, and then click Submit:
      • For Username, enter ggsnet.
      • For Role, select Operator.
      • Enter the password twice for verification.
  4. In the source MySQL deployment console, create a credential for the user created in the previous step.
    1. Open the navigaton menu, and then select Configuration.
    2. Click Add Credential (plus icon), complete the fields as follows, and then click Submit:
      • For Credential Domain, enter GGSNetwork.
      • For Credential Alias, enter dpuser.
      • For Database Name, you can enter any name and leave the Database Server and Port fields blank, or use the default values.
      • For User ID, enter ggsnet.
      • For Password, enter the same password used in the previous step.
  5. Create a Distribution Path:
    1. In the source MySQL deployment console, click Distribution Service, and then click Add Path (plus icon).
    2. Complete the following fields, and click Create and Run:
    • For Path Name, enter a name for this path.
    • For Source Extract, select the Change Data Capture Extract (CDCEXT).
    • For Trail Name, select the Change Data Capture Extract trail file (C1).
    • For Target Authentication Method, select UserID Alias.
    • For Target, select wss.
    • For Target Host, enter the target OCI GoldenGate deployment console URL, without the https:// or any trailing slashes.
    • For Port Number, enter 443.
    • For Trail Name, enter C1.
    • For Domain, enter the domain name created in the previous step.
    • For Alias, enter the alias created in the previous step.

    You're returned to the Distribution Service Overview page where you can review the path created.

  6. In the target OCI GoldenGate Big Data deployment console, review the Receiver path created as a result of the Distribution path:
    1. Click Receiver Service.
    2. Review the Receiver path details.

Task 4: Add a Replicat for Amazon Kinesis

  1. Add a Replicat:
    1. In Big Data deployment click Administrator Service, and then click Add Replicat (plus icon).
    2. Select Classic Replicat and then click Next.
  2. On the Replicat Options page, complete the following fields, and then click Next:
    1. For Process Name, enter a name.
    2. For Trail Name, enter the name of the Trail from Task 2.
    3. For Target, select Amazon Kinesis.
    4. For Alias, select the Amazon Kinesis connection created in Task 1.
  3. On the Replicat Parameters page, leave the default, and then click Next.
  4. On the Properties page, edit the fields marked #TODO.
    1. gg.handler.kinesis.region: provide the Amazon Web Services (AWS) region for the target Kinesis stream.
    2. gg.handler.kinesis.streamMappingTemplate: by default, it is set to ${tableName} which will map the streams based on source table name. If you want to map to an existing data stream, you can provide static stream names or you can use Template Keywords to assign stream names dynamically.
  5. Click Create and Run.

Task 5: Verify Data Replication from MySQL Heatwave to Amazon Kinesis

Perform updates to the source MySQL Heatwave database to verify replication to Amazon Kinesis.
  1. In OCI Bastion, create an SSH port forwarding session using MySQL IP and port 3306. Add your public SSH key.
  2. Connect to MySQL in Cloud Shell using your private key and port 3306.
  3. After connecting successfully, run the following command:
    mysqlsh admin@localhost:3306 --sql
  4. Run the following script to perform inserts into the MySQL Heatwave database:
    use SRC_OCIGGLL;
Insert into SRC_OCIGGLL.SRC_CITY (CITY_ID,CITY,REGION_ID,POPULATION) values (1000,'Houston',20,743113);
Insert into SRC_OCIGGLL.SRC_CITY (CITY_ID,CITY,REGION_ID,POPULATION) values (1001,'Dallas',20,822416);
Insert into SRC_OCIGGLL.SRC_CITY (CITY_ID,CITY,REGION_ID,POPULATION) values (1002,'San Francisco',21,157574);
Insert into SRC_OCIGGLL.SRC_CITY (CITY_ID,CITY,REGION_ID,POPULATION) values (1003,'Los Angeles',21,743878);
Insert into SRC_OCIGGLL.SRC_CITY (CITY_ID,CITY,REGION_ID,POPULATION) values (1004,'San Diego',21,840689);
Insert into SRC_OCIGGLL.SRC_CITY (CITY_ID,CITY,REGION_ID,POPULATION) values (1005,'Chicago',23,616472);
Insert into SRC_OCIGGLL.SRC_CITY (CITY_ID,CITY,REGION_ID,POPULATION) values (1006,'Memphis',23,580075);
Insert into SRC_OCIGGLL.SRC_CITY (CITY_ID,CITY,REGION_ID,POPULATION) values (1007,'New York City',22,124434);
Insert into SRC_OCIGGLL.SRC_CITY (CITY_ID,CITY,REGION_ID,POPULATION) values (1008,'Boston',22,275581);
Insert into SRC_OCIGGLL.SRC_CITY (CITY_ID,CITY,REGION_ID,POPULATION) values (1009,'Washington D.C.',22,688002);
commit;
  5. In the source MySQL deployment console, select the Change Data Capture Extract name (CDCEXT), and then click Statistics. Verify that SRC_OCIGGLL.SRC_CITY has 10 inserts.
  6. In target Big Data deployment console, select the Replicat Name and then click Statistics. Verify that SRC_OCIGGLL.SRC_CITY has 10 inserts.
  7. In Amazon Kinesis, you can check the messages in target data stream/data viewer.