Replicate data from OCI MySQL Database to Autonomous Data Warehouse

Learn how to use OCI GoldenGate to replicate data from OCI MySQL Database to Autonomous Data Warehouse.

Related Topics

Overview

This quickstart example demonstrates how to set up and run a replication between OCI MySQL Database and Autonomous Data Warehouse using OCI GoldenGate.

Before you begin

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

OCI Bastion, to connect to OCI MySQL Database, load the sample database, and perform inserts
An OCI MySQL Database service instance to serve as the source database
An Autonomous Data Warehouse instance to serve as the target database

Task 0: Set up the environment

To set up the environment for this Quickstart:

Download the sample data script, and then run the script on the OCI MySQL database to create the database and load the data.

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

Open port 3306, through which OCI GoldenGate can connect.
1. In the Oracle Cloud console, locate the subnet that the OCI MySQL database uses.
2. In the security list of the subnet, create an Ingress rule for port TCP/3306.
Download the target schema scripts, and then run the scripts in the Autonomous Data Warehouse instance to create the schema and tables. You can use the Autonomous Data Warehouse Database Actions SQL tool to run the scripts:
1. In the Oracle Cloud console, open the Autonomous Data Warehouse database details page, and then click Database Actions.
2. In Database Actions, under Development, click SQL.
3. In the SQL tool, copy and paste the script from OCIGGLL_OCIGGS_SETUP_USERS_ADW.sql into the SQL worksheet, and then click Run Script. If successful, the Script Output tab displays confirmation messages.
4. Clear the SQL worksheet, and then copy and paste only the Create Table scripts from OCIGGLL_OCIGGS_SRC_MIRROR_USER_SEED_DATA.sql. The data will be loaded in a later task.
  
  Tip:
  You may need to run each Create Table statement separately for the SQL tool to execute the scripts successfully.
5. To verify the tables were created successfully, close the SQL window and reopen it again. In the Navigator tab, look for the SRC_OCIGGLL schema, and then select the tables from their respective dropdowns.

Task 1: Create the OCI GoldenGate resources

This quickstart example requires deployments and connections for both the source and target.

Create a deployment for the source OCI MySQL database.
Create a deployment for the target Autonomous Data Warehouse.
Create a connection to the source OCI MySQL database.
Create connection for the target Autonomous Data Warehouse.
(Optional) If your Oracle deployment doesn't have a public endpoint, then create a connection to GoldenGate, and then assign this connection to the source MySQL deployment.
Assign the source connection to the source MySQL deployment.
Assign the target connection to the target Oracle deployment.

Task 2: Create the Extracts

On the Deployments page, select the MySQL deployment created in Task 1.
On the deployment details page, click Launch Console.
Sign in to the source MySQL deployment console using the Administrator credentials specified when you created the deployment in Task 1.
Open the navigation menu, and then select Configuration.
Review the credentials for the MySQL Database Server connection. Take note of the Domain and Alias.
Return to the Administration Service Overview page.
On the Administration Service Overview page, click Add Extract (plus icon).
On the Extract Type page, select Change Data Capture Extract, and then click Next.
Complete the Extract Options as follows, and then click Next:
- For Process Name, enter a name for the Extract, such as CDCEXT.
- For Credential Domain, select the domain from Step 5.
- For Credential Alias, select the alias from Step 5.
- For Trail Name, enter a two-character trail name, such as C1.
Enable Remote if capturing from a MySQL Database that is not using global transaction identifiers (GTIDs).
On the Extract Parameters page, add the following:
```
TABLE SRC_OCIGGLL.*;
```
Click Create and Run.
You're returned to the Administration Service Overview page, where you can observe the Extract starting.
Click Add Extract.
On the Extract Type page, select Initial Load Extract, and then click Next.
Complete the Extract Options as follows, and then click Next:
- For Process Name, enter a name, such as ILEXT.
- For Credential Domain, select the domain from Step 5
- For Credential Alias, select the alias from Step 5.
- For Trail Name, enter a two-character trail name, such as I1.
In the Extract Parameters text area, add the following, and then click Create and Run:
```
TABLE SRC_OCIGGLL.*;
```

Task 3: Create the Distribution Path for Initial Load Extract

On the Deployments page, select the target Autonomous Database deployment.
On the deployment details page, click Launch Console.
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.
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.
Create a Distribution Path.
1. 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 Trail, leave blank.
  - For Trail Name, enter the Initial Load Extract trail name (I1).
  - 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 I1.
  - 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.
In the target OCI GoldenGate 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 Initial Load

In the target OCI GoldenGate deployment console, add a checkpoint table.
1. Open the navigation menu, and then select Configuration.
2. Click the connect icon for the target Autonomous Database instance.
3. Click Add Checkpoint (plus icon).
4. For Checkpoint Table, enter SRCMIRROR_OCIGGLL.CHECKTABLE.
5. Click Submit.
  
  Tip:
  The screen will not refresh on submit. To confirm the checkpoint table was added, enter the SRCMIRROR_OCIGGLL.CHECKTABLE into the search field and then click Search.
Add the Replicat.
1. Click Administrator Service, and then click Add Replicat (plus icon).
2. On the Add Replicat page, under Replicat type, select Nonintegrated Replicat, and then click Next.
3. On the Replicat Options page, complete the following form fields, and then click Next:
  - For Process Name, enter a name, such as RIL.
  - For Credential Domain, select the domain for the Autonomous Database connection.
  - For Credential Alias, select the alias of the Autonomous Database connection.
  - For Trail Name, enter the name of the Trail from Task 2 (I1).
  - For Checkpoint Table, select the Checkpoint table you created in Step 1.
4. On the Replicat Parameters page, add the following mapping, and then click Next:
```
MAP SRC_OCIGGLL.*, TARGET SRCMIRROR_OCIGGLL.*;
```
Click Create and Run.
You're returned to the Overview page, where you can review the Replicat details.
Select the Replicat (RIL) and view its Details.
Click Statistics and review the number of Inserts. Refresh the page.
- If the number of Inserts doesn't change, then all the records from the Initial Load have been loaded and you can stop the Replicat (RIL)
- If the number of Inserts continues to increase, then keep refreshing the page until the Initial Load records are all loaded before continuing.

Task 5: Verify the initial load

In the Oracle Cloud console, open Database Actions from the Autonomous Data Warehouse database details page.
In Database Actions, under Development, click SQL.
In the SQL tool, enter each of the following statements into the worksheet and click Run Statement:
```
SELECT * FROM SRCMIRROR_OCIGGLL.SRC_CITY;
SELECT * FROM SRCMIRROR_OCIGGLL.SRC_CUSTOMER;
```

The output should return the data that was loaded into the target database tables as a result of the Initial Load.

Task 6: Create a Distribution Path for Change Data Capture

In the source MySQL deployment console, click Distribution Service.
Click Add Path.
Complete the following fields, and then click Create and Run:
1. For Path Name, enter a name.
2. For Source Extract, select the Change Data Capture Extract (CDCEXT).
3. For Trail Name, select the Change Data Capture Extract trail file (C1)
4. For Target Authentication Method, select UserID Alias.
5. For Target, select wss.
6. For Target Host, enter the target OCI GoldenGate deployment console URL, without the https:// or any trailing slashes.
7. For Port Number, enter 443.
8. For Trail Name, enter C1.
9. For Domain, enter the domain name created in task 3.
10. For Alias, enter the alias created in task 3.
In the target OCI GoldenGate deployment console, click Receiver Service, and then review the Receiver path created.

Task 7: Add a Replicat for Change Data Capture

Add a Replicat.
1. Click Administrator Service, and then click Add Replicat (plus icon).
2. On the Add Replicat page, under Replicat type, select Nonintegrated Replicat, and then click Next.
3. On the Replicat Options page, complete the following form fields, and then click Next:
  - For Process Name, enter a name, such as RCDC.
  - For Credential Domain, select the domain for the Autonomous Database connection.
  - For Credential Alias, select the alias of the Autonomous Database connection.
  - For Trail Name, enter the name of the Trail from Task 2 (C1).
  - For Checkpoint Table, select the Checkpoint table you created in Step 1.
4. On the Replicat Parameters page, add the following mapping, and then click Next:
```
MAP SRC_OCIGGLL.*, TARGET SRCMIRROR_OCIGGLL.*;
```
5. Click Create. Do not run the Replicat.
On the Administration Service Overview page, select the Replicat for Initial Load (RIL) and view its Details.
Click Statistics and review the number of Inserts. Refresh the page.
- If the number of Inserts doesn't change, then all the records from the Initial Load have been loaded and you can stop the Replicat (RIL)
- If the number of Inserts continues to increase, then keep refreshing the page until the Initial Load records are all loaded before continuing.
Return to the Administration Service Overview page and then start the Replicat for Change Data Capture (RCDC).
After starting the Replicat for Change Data Capture, review its Details and Statistics to view the number of Inserts.

Task 8: Verify Change Data Capture

Perform updates to the source OCI MySQL database to verify replication to Autonomous Data Warehouse.

In OCI Bastion, create an SSH port forwarding session using MySQL IP and port 3306. Add your public SSH key.
Connect to MySQL in Cloud Shell using your private key and port 3306.
After connecting successfully, run the following command:
```
mysqlsh admin@localhost:3306 --sql
```

Run the following script to perform inserts into the OCI MySQL 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;

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.

Note:
If the Extract captured no inserts, then restart the CDCEXT Extract.

Task 9: Monitor and maintain processes

Title and Copyright Information

Replicate data from OCI MySQL Database to Autonomous Data Warehouse

Documentation Accessibility

For information about Oracle's commitment to accessibility, visit the Oracle Accessibility Program website at http://www.oracle.com/pls/topic/lookup?ctx=acc&id=docacc.

Access to Oracle Support

Oracle customers that have purchased support have access to electronic support through My Oracle Support. For information, visit http://www.oracle.com/pls/topic/lookup?ctx=acc&id=info or visit http://www.oracle.com/pls/topic/lookup?ctx=acc&id=trs if you are hearing impaired.