Creating a MySQL HeatWave Replication Channel with the OCI CLI

This is the sixth (and final) post in a series dedicated to showing how to use the OCI CLI to manage MySQL HeatWave resources. This post will discuss how to create a MySQL HeatWave inbound replication channel.

Prerequisites

Before you can run any of the examples below, you need to install the OCI CLI. If you do not have the CLI installed, follow the instructions here to install and configure the CLI.

Creating Inbound Replication Channel

A description of an inbound replication channel can be found on the Overview of Inbound Replication page:

Inbound replication uses a replication channel configured in MySQL HeatWave Service to copy transactions from another location to a DB system. The channel connects the source (a MySQL instance or another DB system) to the replica (a DB system), and copies data from the source to the replica.

The source can be another HeatWave or external MySQL instance. For replication to work, the replica must be able to communicate with the source. Because the replication is asynchronous, the replica need not always be connected to the source. When a connection between the source and the replica is re-established, the replica will get any new updates since they were last connected.

Setting up the Source

Our source needs some configuration to facilitate replication. For more details, check out this link.

While it is not required, enabling GTID on the source is recommended, which makes setting up and maintaining the replica easier.

If GTID is not enabled on the source, run the following commands:

SET @@GLOBAL.enforce_gtid_consistency = ON;

Next, check the value of gtid_mode by running:

select @@GLOBAL.gtid_mode;

If any value other than ON is returned, we must run at least one more command.

If gtid_mode is OFF, run the command:

SET @@GLOBAL.gtid_mode = OFF_PERMISSIVE;

If gtid_mode value is OFF_PERMISSIVE (or you just ran the command above), run the command:

SET @@GLOBAL.gtid_mode = ON_PERMISSIVE;

If gtid_mode value is ON_PERMISSIVE (or you just ran the command above), run the command:

SET @@GLOBAL.gtid_mode = ON;

With gtid_mode now set to ON, we are ready to set up the channel.

Generating Command JSON

As we have done throughout this series, we will first generate a template JSON file to create our new configuration. The command to create this JSON file is:

oci mysql channel create-from-mysql --generate-full-command-json-input > channel-create.json

When this command completes, the file named channel-create.json will resemble the following:

{
  "compartmentId": "string",
  "definedTags": {
    "tagNamespace1": {
      "tagKey1": "tagValue1",
      "tagKey2": "tagValue2"
    },
    "tagNamespace2": {
      "tagKey1": "tagValue1",
      "tagKey2": "tagValue2"
    }
  },
  "description": "string",
  "displayName": "string",
  "freeformTags": {
    "tagKey1": "tagValue1",
    "tagKey2": "tagValue2"
  },
  "isEnabled": true,
  "maxWaitSeconds": 0,
  "sourceAnonymousTransactionsHandling": [
    "This parameter should actually be a JSON object rather than an array - pick one of the following object variants to use",
    {
      "lastConfiguredLogFilename": "string",
      "lastConfiguredLogOffset": 0,
      "policy": "ASSIGN_MANUAL_UUID",
      "uuid": "string"
    },
    {
      "lastConfiguredLogFilename": "string",
      "lastConfiguredLogOffset": 0,
      "policy": "ASSIGN_TARGET_UUID"
    },
    {
      "policy": "ERROR_ON_ANONYMOUS"
    }
  ],
  "sourceHostname": "string",
  "sourcePassword": "string",
  "sourcePort": 0,
  "sourceSslCaCertificate": [
    "This parameter should actually be a JSON object rather than an array - pick one of the following object variants to use",
    {
      "certificateType": "PEM",
      "contents": "string"
    }
  ],
  "sourceSslMode": "string",
  "sourceUsername": "string",
  "targetApplierUsername": "string",
  "targetChannelName": "string",
  "targetDbSystemId": "string",
  "targetDelayInSeconds": 0,
  "targetFilters": [
    {
      "type": "string",
      "value": "string"
    },
    {
      "type": "string",
      "value": "string"
    }
  ],
  "targetTablesWithoutPrimaryKeyHandling": "string",
  "waitForState": [
    "ACCEPTED|IN_PROGRESS|FAILED|SUCCEEDED|CANCELING|CANCELED"
  ],
  "waitIntervalSeconds": 0
}

As with other demos in this series, I will trim down this JSON to include only the values we will use.

{
  "compartmentId": "ocid1.compartment.oc1.{more text}",
  "description": "OCI CLI Inbound replication demo",
  "displayName": "OCI CLI Channel Demo",
  "isEnabled": true,
  "sourceHostname": "{host name or IP address}",
  "sourcePassword": "myPassword",
  "sourcePort": 3306,
  "sourceSslMode": "required",
  "sourceUsername": "myUser",
  "targetDbSystemId": "ocid1.mysqldbsystem.oc1.{more text}",
  "waitForState": ["SUCCEEDED"]
}

We provide the compartmentId for the compartment where the channel will be created. We give the channel a description and displayName and ensure enabled is set to true. We provide information for the source, such as sourceHostname, sourceUsername, sourcePassword, sourcePort, and sourceSslMode. We also specify the targetDbSystemID, the OCID for the MySQL HeatWave instance that will serve as the replica. Lastly, we tell the CLI to wait until the command reaches a state of SUCCEEDED before returning any information.

Run the Command

To create a new replication channel using this JSON config file, we run the command:

oci mysql channel create-from-mysql --from-json file://{path to file}

When we run this command, we will see the following text in the command/terminal window:

Action completed. Waiting until the work request has entered state: ('SUCCEEDED',)

Once the command has reached a state of SUCCEEDED, we will see more information about the replica:

{
  "data": {
    "compartment-id": "ocid1.compartment.oc1.{more text}",
    "id": "ocid1.mysqlworkrequest.oc1.{more text}",
    "operation-type": "CREATE_CHANNEL",
    "percent-complete": 100.0,
    "resources": [
      {
        "action-type": "RELATED",
        "entity-type": "mysqldbsystem",
        "entity-uri": "/dbSystems/ocid1.mysqldbsystem.oc1.{more text}",
        "identifier": "ocid1.mysqldbsystem.oc1.{more text}"
      },
      {
        "action-type": "CREATED",
        "entity-type": "mysqlchannel",
        "entity-uri": "/channels/ocid1.mysqlchannel.oc1.{more text}",
        "identifier": "ocid1.mysqlchannel.oc1.{more text}"
      }
    ],
    "status": "SUCCEEDED",
    "time-accepted": "2024-04-24T13:21:20.278000+00:00",
    "time-finished": "2024-04-24T13:23:03.226000+00:00",
    "time-started": "2024-04-24T13:21:39.569000+00:00"
  }
}

Remember that the command may be in a SUCCEEDED state before the replication channel is available. It is also possible that after the channel is created, it shows as NEEDS ATTENTION in the OCI web interface. If this is the case, please check out this post for help with troubleshooting.

Wrap-up

As we have seen in this series, the OCI CLI is a powerful tool we have at our disposal to manage MySQL HeatWave resources. We can create new HeatWave instances, back up and restore instances, and create read replicas, configurations, & inbound replication channels. While researching and writing this series, I discovered that the OCI CLI has become my preferred way to manage HeatWave resources. For me, using JSON to configure resources is faster and easier than using the Oracle Cloud web interface.

Image by CoolVid-Shows from Pixabay