Creating a MySQL HeatWave Instance With the OCI CLI
![](https://res.cloudinary.com/strozstuff/image/fetch/https://idmqjyw9i2ib.objectstorage.us-ashburn-1.oci.customer-oci.com/n/idmqjyw9i2ib/b/blog/o/2024/oci-cli-create-heatwave-instance/header.jpg)
In a previous series, we discussed how we can leverage the TypeScript/JavaScript SDK for Oracle Cloud Infrastructure (OCI) to manage MySQL HeatWave instances. This new series demonstrates how to use the OCI CLI to complete similar tasks. In this post, we will show how to create a MySQL HeatWave instance and retrieve reference information that can help create this new instance.
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.
Note: Many examples require a parameter named --compartment-id
. Because I am part of a tenancy shared by many colleagues, I must ensure I only ever touch resources in my ‘sandbox’. To make this easier, I followed the instructions here to set up default values for the CLI - including the compartment-id
for my ‘sandbox’.
Listing Reference Data
In my previous series, I put this part at the end, but I think it is better to talk about the ‘reference data’ in the beginning. When we manage MySQL HeatWave instances, we need to provide specific values for some parameters—three that come to mind are configuration
, shape
, and version
. When we provide these values, they need to be valid options. This section will show you how to get a list of all the valid options for each.
Configurations
To make provisioning MySQL HeatWave instances easier and more consistent, we can use a configuration
when creating an instance. The list of configurations we retrieve contains default and custom configurations we may create. The list of items that is returned is sorted by:
- Shape name - ascending
DEFAULT
beforeCUSTOM
- Display name - ascending
To see the complete list of configurations, use the following command.
oci mysql configuration list --all --compartment-id {compartment Id}
The result will look similar to the text below.
{
"data": [
{
"compartment-id": null,
"defined-tags": null,
"description": "Default standalone configuration for the BM.Standard.E2.64 MySQL shape",
"display-name": "BM.Standard.E2.64.Standalone",
"freeform-tags": null,
"id": "ocid1.mysqlconfiguration.oc1..aaaaaaaah6o6qu3gdbxnqg6aw56amnosmnaycusttaa7abyq2tdgpgubvsgh",
"lifecycle-state": "ACTIVE",
"shape-name": "BM.Standard.E2.64",
"time-created": "2018-09-21T10:00:00+00:00",
"time-updated": null,
"type": "DEFAULT"
},
{
"compartment-id": null,
"defined-tags": null,
"description": "Default standalone configuration for the VM.Standard.E2.1 MySQL shape",
"display-name": "VM.Standard.E2.1.Standalone",
"freeform-tags": null,
"id": "ocid1.mysqlconfiguration.oc1..aaaaaaaah6o6qu3gdbxnqg6aw56amnosmnaycusttaa7abyq2tdgpgubvsgi",
"lifecycle-state": "ACTIVE",
"shape-name": "VM.Standard.E2.1",
"time-created": "2018-09-21T10:00:00+00:00",
"time-updated": null,
"type": "DEFAULT"
}
...
]
}
If we use a configuration when creating a new instance, we would use the value of the id
property.
Check out the configuration list documentation to learn about other options.
Shapes
Every MySQL HeatWave instance must have a defined `shape. ’ This value can be defined in a configuration like we see above or provided as a separate parameter when creating a MySQL HeatWave instance. To see the list of valid shapes, we would use the command:
oci mysql shape list --compartment-id {compartment Id}
The output from this command should resemble this text:
{
"data": [
{
"cpu-core-count": 1,
"is-supported-for": [
"DBSYSTEM"
],
"memory-size-in-gbs": 8,
"name": "VM.Standard.E2.1"
},
{
"cpu-core-count": 2,
"is-supported-for": [
"DBSYSTEM"
],
"memory-size-in-gbs": 16,
"name": "VM.Standard.E2.2"
},
...
]
}
When we specify a shape
when creating a new MySQL HeatWave instance, we use the value of the name
property.
Check out the shape list documentation to learn about other options.
Versions
When creating a new MySQL HeatWave instance, the latest version available will be used if a version is not specified. To retrieve a list of valid versions, use the following command:
oci mysql version list --compartment-id {compartment Id}
The output from this command will resemble:
{
"data": [
{
"version-family": "8.0",
"versions": [
{
"description": "8.0.32",
"version": "8.0.32"
},
{
"description": "8.0.33",
"version": "8.0.33"
},
{
"description": "8.0.34",
"version": "8.0.34"
},
{
"description": "8.0.35",
"version": "8.0.35"
},
{
"description": "8.0.36",
"version": "8.0.36"
}
]
},
{
"version-family": "8 - Innovation",
"versions": [
{
"description": "8.2.0",
"version": "8.2.0"
},
{
"description": "8.2.0 HeatWave Preview",
"version": "8.2.0-HeatWave-Preview"
},
{
"description": "8.3.0",
"version": "8.3.0"
},
{
"description": "8.3.0 HeatWave Preview",
"version": "8.3.0-HeatWave-Preview"
},
{
"description": "8.4.0 HeatWave Preview",
"version": "8.4.0-HeatWave-Preview"
}
]
}
]
}
Note that there are two different version families - one for 8.0
and one for 8 - Innovation
. When specifying the version when creating a new instance, we would use the value of the version
property.
Check out the version list documentation to learn about other options.
Subnets
Another piece of information we need when creating a MySQL HeatWave instance is the subnet where it will be created. To see the list of available subnets in your compartment, use the following command:
oci network subnet list --all --compartment-id {compartment Id}
The result will resemble the text below.
{
"data": [
{
"availability-domain": null,
"cidr-block": "10.0.0.0/24",
"compartment-id": "ocid1.compartment.oc1.{more text}",
"defined-tags": {
"Oracle-Recommended-Tags": {
"ResourceOwner": "default/sstroz"
},
"Oracle-Tags": {
"CreatedBy": "default/sstroz",
"CreatedOn": "2023-10-05T16:32:06.583Z"
}
},
"dhcp-options-id": "ocid1.dhcpoptions.oc1.{more text}",
"display-name": "public subnet-MySQL-CLI-Demo",
"dns-label": "sub10051632010",
"freeform-tags": {
"VCN": "VCN-2023-10-05T16:31:34"
},
"id": "ocid1.subnet.oc1.{more text}",
"ipv6-cidr-block": null,
"ipv6-cidr-blocks": null,
"ipv6-virtual-router-ip": null,
"lifecycle-state": "AVAILABLE",
"prohibit-internet-ingress": false,
"prohibit-public-ip-on-vnic": false,
"route-table-id": "ocid1.routetable.oc1.{more text}",
"security-list-ids": [
"ocid1.securitylist.oc1.{more text}"
],
"subnet-domain-name": "sub10051632010.mysqlopenvpndem.oraclevcn.com",
"time-created": "2023-10-05T16:32:07.196000+00:00",
"vcn-id": "ocid1.vcn.oc1.{more text}",
"virtual-router-ip": "10.0.0.1",
"virtual-router-mac": "00:00:17:28:44:8E"
},
{
"availability-domain": null,
"cidr-block": "10.0.1.0/24",
"compartment-id": "ocid1.compartment.oc1.{more text}",
"defined-tags": {
"Oracle-Recommended-Tags": {
"ResourceOwner": "default/sstroz"
},
"Oracle-Tags": {
"CreatedBy": "default/sstroz",
"CreatedOn": "2023-10-05T16:32:06.616Z"
}
},
"dhcp-options-id": "ocid1.dhcpoptions.oc1.{more text}",
"display-name": "private subnet-MySQL-CLI-Demo",
"dns-label": "sub10051632011",
"freeform-tags": {
"VCN": "VCN-2023-10-05T16:31:34"
},
"id": "ocid1.subnet.oc1.{more text}",
"ipv6-cidr-block": null,
"ipv6-cidr-blocks": null,
"ipv6-virtual-router-ip": null,
"lifecycle-state": "AVAILABLE",
"prohibit-internet-ingress": true,
"prohibit-public-ip-on-vnic": true,
"route-table-id": "ocid1.routetable.oc1.iad.{more text}",
"security-list-ids": [
"ocid1.securitylist.oc1.{more text}"
],
"subnet-domain-name": "sub10051632011.mysqlopenvpndem.oraclevcn.com",
"time-created": "2023-10-05T16:32:06.669000+00:00",
"vcn-id": "ocid1.vcn.oc1.{more text}",
"virtual-router-ip": "10.0.1.1",
"virtual-router-mac": "00:00:17:28:44:8E"
}
]
}
When creating a MySQL HeatWave instance, we use the value of the id
property as the subnet-id
.
Check out the subnet list documentation to learn about other options.
Availability Domains
One last piece of information we need is the availability domain where the MySQol HetWave instance will be deployed. We can get a list of availability domains by running the command:
oci iam availability-domain list
The list will resemble the following:
{
"data": [
{
"compartment-id": "ocid1.compartment.oci.{more text}",
"id": "ocid1.availabilitydomain.oc1..aaaaaaaaztunlny6ae4yw2vghp5go2zceaonwp6wiioe3tnh2vlaxjjl2n3a",
"name": "mMVr:US-ASHBURN-AD-1"
},
{
"compartment-id": "ocid1.compartment.oci.{more text}",
"id": "ocid1.availabilitydomain.oc1..aaaaaaaauvt2n7pijol7uqgdnnsoojcukrijtmcltvfwxazmitk235wyohta",
"name": "mMVr:US-ASHBURN-AD-2"
},
{
"compartment-id": "ocid1.compartment.oci.{more text}",
"id": "ocid1.availabilitydomain.oc1..aaaaaaaatrwxaogr7dl4yschqtrmqrdv6uzis3mgbnomiagqrfhcb7mxsfdq",
"name": "mMVr:US-ASHBURN-AD-3"
}
]
}
When specifying an availability domain when creating a new MySQL HeatWave instance, we would use the value of the name
property.
Check out the availability-domain list documentation to learn about other options.
Creating the Instance
There are quite a few options when creating a new MySQL HeatWave instance. If you don’t believe me, go check out the documentation for creating a new MySQL db-system
.
I am a poor typist, and when a command has many parameters, and some of them have very long values, I often need help identifying typos. There is also an issue with running the command more than once. Yes, you can cycle through the command history, but this could also be difficult if there were typos in previous attempts to run the command.
There is a feature of the OCI CLI that was explicitly designed for people like me. Many commands have an option named --from-json
where you can specify a JSON file containing the instance’s configuration information. Another nifty feature is generating a sample JSON file for commands with the --from-json
option.
Generate Command JSON
To generate a JSON file that contains all the possible options we can use when creating a MySQL HeatWave instance, we use the following command:
oci mysql db-system create --generate-full-command-json-input > heatwave-create.json
This command will create a file named heatwave-create.json
in the directory where we executed the command. The content of the file will resemble:
{
"adminPassword": "string",
"adminUsername": "string",
"availabilityDomain": "string",
"backupPolicy": {
"definedTags": {
"tagNamespace1": {
"tagKey1": "tagValue1",
"tagKey2": "tagValue2"
},
"tagNamespace2": {
"tagKey1": "tagValue1",
"tagKey2": "tagValue2"
}
},
"freeformTags": {
"tagKey1": "tagValue1",
"tagKey2": "tagValue2"
},
"isEnabled": true,
"pitrPolicy": {
"isEnabled": true
},
"retentionInDays": 0,
"windowStartTime": "string"
},
"compartmentId": "string",
"configurationId": "string",
"crashRecovery": "ENABLED|DISABLED",
"dataStorageSizeInGbs": 0,
"databaseManagement": "ENABLED|DISABLED",
"definedTags": {
"tagNamespace1": {
"tagKey1": "tagValue1",
"tagKey2": "tagValue2"
},
"tagNamespace2": {
"tagKey1": "tagValue1",
"tagKey2": "tagValue2"
}
},
"deletionPolicy": {
"automaticBackupRetention": "string",
"finalBackup": "string",
"isDeleteProtected": true
},
"description": "string",
"displayName": "string",
"faultDomain": "string",
"freeformTags": {
"tagKey1": "tagValue1",
"tagKey2": "tagValue2"
},
"hostnameLabel": "string",
"ipAddress": "string",
"isHighlyAvailable": true,
"maintenance": {
"windowStartTime": "string"
},
"maxWaitSeconds": 0,
"mysqlVersion": "string",
"port": 0,
"portX": 0,
"secureConnections": {
"certificateGenerationType": "string",
"certificateId": "string"
},
"shapeName": "string",
"source": [
"This parameter should actually be a JSON object rather than an array - pick one of the following object variants to use",
{
"backupId": "string",
"sourceType": "BACKUP"
},
{
"sourceType": "NONE"
},
{
"dbSystemId": "string",
"recoveryPoint": "2017-01-01T00:00:00+00:00",
"sourceType": "PITR"
},
{
"sourceType": "IMPORTURL",
"sourceUrl": "string"
}
],
"subnetId": "string",
"waitForState": [
"ACCEPTED|IN_PROGRESS|FAILED|SUCCEEDED|CANCELING|CANCELED"
],
"waitIntervalSeconds": 0
}
Set Our Option Values
I will trim this file down to just the required fields and some other helpful ones and provide values for them. My file looks like this:
{
"adminPassword": "MySQL8IsGre@t!!",
"adminUsername": "admin",
"availabilityDomain": "mMVr:US-ASHBURN-AD-1",
"backupPolicy": {
"isEnabled": true,
"pitrPolicy": {
"isEnabled": true
},
"retentionInDays": 14
},
"compartmentId": "ocid1.compartment.oc1.{more text}",
"crashRecovery": "ENABLED",
"dataStorageSizeInGbs": 50,
"databaseManagement": "ENABLED",
"description": "A HeatWave instance created with the OCI CLI",
"displayName": "OCI CLI Demo",
"isHighlyAvailable": false,
"mysqlVersion": "8.3.0",
"shapeName": "VM.Standard.E2.1",
"subnetId": "ocid1.subnet.oc1.{more text}",
"waitForState": ["SUCCEEDED"]
}
Let’s look at what options we are setting:
- The
adminPassword
andadminUsername
are the password and username for a MySQL admin (notroot
) user when the instance is created. - We specify the
availabilityDomain
. - We enable the
backupPolicy
and also turn on ‘point-in-time’ recovery (pitrPolicy
) and set theretentionPeriod
for the backups to 14 days. - We set the
compartmentId
. - We enable
crashRecovery
. - We set the
dataStorageSizeInGbs
to 50 (the lowest possible value for a MySQL HeatWave instance). - We enable
databaseManagement
. - We set the instance’s
description
anddisplayName
. - We set
isHighlyAvailable
tofalse
. - We set the
mysqlVersion
to8.3.0
. - We set the
shapeName
toVM.Standard.E2.1
- We specify the
subnetId
where the instance will reside. - We set the
waitForState
option to"SUCCEEDED"
.- This will cause the command to wait until it reaches a state of
SUCCEEDED
.
- This will cause the command to wait until it reaches a state of
If any values specified in this JSON file are also specified when the create
command is executed, the values in the command will override the values in the JSOn file.
Running the Command
Now that our options are configured in our JSON file, let’s create a new MySQL HeatWave instance. We use the following command to create a new instance using the JSON file we generated (and modified) earlier.
oci mysql db-system create --from-json file://{path to JSON file}
When we run this command, we should see the following text letting us know that the command has entered a state of SUCCEEDED
.
Action completed. Waiting until the work request has entered state: ('SUCCEEDED',)
While waiting for the instance to be created, we can log in to Oracle Cloud and check the progress. You can see our new instance is being created.
The command may return as SUCCEEDED
before the instance is available. When the command is successful, we will see the output in the terminal similar to the text below:
{
"data": {
"compartment-id": "ocid1.compartment.oc1.{more text}",
"id": "ocid1.mysqlworkrequest.oc1.{more text}",
"operation-type": "CREATE_DBSYSTEM",
"percent-complete": 100.0,
"resources": [
{
"action-type": "IN_PROGRESS",
"entity-type": "mysqldbsystem",
"entity-uri": "/dbSystems/ocid1.mysqldbsystem.oc1.{more text",
"identifier": "ocid1.mysqldbsystem.oc1.{more text}"
}
],
"status": "SUCCEEDED",
"time-accepted": "2024-04-03T14:38:31.457000+00:00",
"time-finished": "2024-04-03T14:54:16.559000+00:00",
"time-started": "2024-04-03T14:38:56.940000+00:00"
}
}
You will see the following when the instance is fully available in Oracle Cloud.
Wrap-up
These few examples show that the OCI CLI offers extensive functionality for managing Oracle Cloud resources, including MySQL HeatWave instances. In future posts, I will explore the functionality for managing MySQL HeatWave resources.
Photo by Jake Walker on Unsplash
Related Entries
- Listing and Updating MySQL HeatWave Instances with the OCI CLI
- Backing up and Restoring a MySQL HeatWave Instance with the OCI CLI
- Creating a MySQL HeatWave Read Replica with the OCI CLI
- Creating a MySQL HeatWave Configuration with the OCI CLI
- Creating a MySQL HeatWave Replication Channel with the OCI CLI