6.2 The basics - OCI CLI (MySQL DB System)

Prerequisites: Install and configure OCI CLI

This example takes place in root compartment, therefore there is a layer of simplicity when implemeting the deployment of an MySQL instance using OCI CLI

root@deploymentmachine:~/.oci# export TENANCY="ocid1.tenancy.oc1..aaaaaasomefaketenancyidinhereaaaaa323423421"
root@deploymentmachine:~/.oci# export COMPARTMENT="ocid1.tenancy.oc1..aaaaaasomefakecompartmentidinhereaaaaa323423422"
root@deploymentmachine:~/.oci# export USER="ocid1.user.oc1..aaaaaasomefakeuseridinhereaaaaa323423423"

You will be needing the Compartment ID for extracting the necessary data.

1. Get a list of supported Oracle Database versions.

root@deploymentmachine:/home/tests/mydb# oci db version list -c $COMPARTMENT --db-system-shape "VM.Standard1.1" --all --output table
+-----------------------------+-----------------------+----------------------+--------------+-----------------+
| is-latest-for-major-version | is-preview-db-version | is-upgrade-supported | supports-pdb | version         |
+-----------------------------+-----------------------+----------------------+--------------+-----------------+
| True                        | False                 | False                | False        | 11.2.0.4        |
| False                       | False                 | False                | False        | 11.2.0.4.200114 |
| False                       | False                 | False                | False        | 11.2.0.4.200414 |
| False                       | False                 | False                | False        | 11.2.0.4.200714 |
| False                       | False                 | False                | False        | 11.2.0.4.201020 |
| True                        | False                 | False                | True         | 12.1.0.2        |
| False                       | False                 | False                | True         | 12.1.0.2.200114 |
| False                       | False                 | False                | True         | 12.1.0.2.200414 |
[.................................skipping................................skipping............................]
| False                       | False                 | False                | True         | 18.10.0.0       |
| False                       | False                 | False                | True         | 18.11.0.0       |
| False                       | False                 | False                | True         | 18.12.0.0       |
| False                       | False                 | False                | True         | 18.9.0.0        |
| True                        | False                 | True                 | True         | 19.0.0.0        |
| False                       | False                 | False                | True         | 19.6.0.0        |
| False                       | False                 | True                 | True         | 19.7.0.0        |
| False                       | False                 | True                 | True         | 19.8.0.0        |
| False                       | False                 | True                 | True         | 19.9.0.0        |
| True                        | False                 | False                | True         | 21.0.0.0        |
| False                       | False                 | False                | True         | 21.1.0.0        |
+-----------------------------+-----------------------+----------------------+--------------+-----------------+

2. For this example, going with 19.6.0.0 version

For surpressing further warning messages when running OCI CLI, perform following:

export OCI_CLI_SUPPRESS_FILE_PERMISSIONS_WARNING=True

3. Obtain Subnet ID:

3.1 Obtain VCN ID:

root@deploymentmachine:/home/tests/mydb# export VCN=$(oci network subnet list -c $COMPARTMENT | \
grep vcn-id | awk {'print $2'} | \
sed s/[\",]//g)
root@deploymentmachine:/home/tests/mydb# echo $VCN
ocid1.vcn.oc1.eu-frankfurt-1.faaaakeVCNIDDDD123243asasdaasd

3.2 Obtain the Subnet ID corresponding to the VCN ID:

root@deploymentmachine:/home/tests/mydb# oci network subnet list -c $COMPARTMENT --vcn-id $VCN | \
grep -E '^\"id:\"|ocid1.subnet' | \
awk {'print $2'} | sed s/[\",]//g
ocid1.subnet.oc1.eu-frankfurt-1.aaaaaaaafakesubnetidoverhere123242343
root@deploymentmachine:/home/tests/mydb#

n.b: Those that wish to know more about the json file involved in creating MySQL instance, can run the following command:

root@deploymentmachine:/home/tests/mydb#  oci db system launch --generate-full-command-json-input > createdb.json
root@deploymentmachine:/home/tests/mydb# head createdb.json
{
  "adminPassword": "string",
  "autoBackupEnabled": true,
  "availabilityDomain": "string",
  "backupNetworkNsgIds": [
    "string",
    "string"
  ],
  "backupSubnetId": "string",
  "characterSet": "string",
root@deploymentmachine:/home/tests/mydb#

Since this is a very simple example, our final json file to use for building the MySQL instance:

root@deploymentmachine:/home/tests/mydb# more mydb.json
{
  "adminUsername": "usertest",
  "adminPassword": "wildpasswp0rdhere",
  "compartmentId": "ocid1.tenancy.oc1..aaaaaasomefakecompartmentidinhereaaaaa323423422",
  "display-name": "MysqlSmallTest",
  "availabilityDomain": "Aodz:EU-FRANKFURT-1-AD-1",
  "databaseEdition": "STANDARD_EDITION",
  "dataStorageSizeInGbs": 50,
  "dbName": "mysqlTest",
  "dbVersion": "19.6.0.0",
  "description": "Just a small mysql db test",
  "port": 3306,
  "port-x": 33060,
  "hostname": "mysqltest",
  "listener-port": 1521,
  "node-count": 1,
  "maxWaitSeconds": 0,
  "shapeName": "MySQL.VM.Standard.E3.1.8GB",
  "subnetId": "ocid1.subnet.oc1.eu-frankfurt-1.aaaaaaaafakesubnetidoverhere123242343"
}

4. To create the MySQL instance, run the following command:

root@deploymentmachine:/home/tests/mydb# oci mysql db-system create --from-json file://mydb.json

n.b: If any issues with your json file, you would receive an error output.

Suppose I have allocated only 40Gb for my dataStorageSizeInGb, I would be receivig a ServiceError as below:

root@deploymentmachine:/home/tests/mydb# oci mysql db-system create --from-json file://mydb.json
ServiceError:
{
    "code": "InvalidParameter",
    "message": "dataStorageSizeInGBs must be greater than or equal to 50",
    "opc-request-id": "F7701BCD165B457F9A3BE0DE2A4154EE/1E2E000F4E221DE35B00BAC58EA0FBC4/5E77DF6AAB3A1D3DEEB8DF9003D0694C",
    "status": 400
}

4.1 If all good when running command, for the our json file we should receive an output as following:

root@deploymentmachine:/home/tests/mydb# oci mysql db-system create --from-json file://mydb.json
{
  "data": {
    "analytics-cluster": null,
    "availability-domain": "Aodz:EU-FRANKFURT-1-AD-1",
    "backup-policy": {
      "defined-tags": null,
      "freeform-tags": null,
      "is-enabled": true,
      "retention-in-days": 7,
      "window-start-time": "00:00"
    },
    "channels": [],
    "compartment-id": "ocid1.tenancy.oc1..aaaaaasomefakecompartmentidinhereaaaaa323423422",
    "configuration-id": "ocid1.mysqlconfiguration.oc1..aaaaaaaasomefakedatamysqlhere34345",
    "data-storage-size-in-gbs": 50,
    "defined-tags": {
      "Oracle-Tags": {
        "CreatedBy": "[e-mail]",
        "CreatedOn": "2021-01-31T17:34:54.378Z"
      }
    },
    "description": "Just a small mysql db test",
    "display-name": "MysqlSmallTest",
    "endpoints": [],
    "fault-domain": null,
    "freeform-tags": {},
    "hostname-label": null,
    "id": "ocid1.mysqldbsystem.oc1.eu-frankfurt-1.aaaaaaaaosomefakedatahere45a",
    "ip-address": null,
    "is-analytics-cluster-attached": false,
    "lifecycle-details": null,
    "lifecycle-state": "CREATING",
    "maintenance": {
      "window-start-time": "TUESDAY 04:56"
    },
    "mysql-version": null,
    "port": 3306,
    "port-x": 33060,
    "shape-name": "MySQL.VM.Standard.E3.1.8GB",
    "source": null,
    "subnet-id": "ocid1.subnet.oc1.eu-frankfurt-1.aaaaaaaasomefakedatahere72q",
    "time-created": "2021-01-31T17:34:55+00:00",
    "time-updated": "2021-01-31T17:34:55+00:00"
  },
  "etag": "2e11b8b02b5342da1cfa916f755342da9d0316f1b8b02b5342da9d0316f6",
  "opc-work-request-id": "ocid1.mysqlworkrequest.oc1.eu-frankfurt-1.somefakedatahere"
}

5. Check the creation from OCI UI:

6. Once the MySQL DB System is available, an private IP will be provided to it: