OCI CLI is a powerful tool that does it all for OCI administration: from creating a compute instance to configuring a Virtual Cloud Network (VCN), from setting up Identity Management Service (IAM) to managing all the different storage flavours. Consult the OCI CLI command reference.

MySQL Database Service (MDS) is not an exception, everything related to the new MySQL Server service running over OCI can be easily managed with a set of commands using OCI CLI, check it here. Starting new DB Systems, creating and managing backups, dealing with the configuration or retrieving information…everything together to program complex tasks organised in scripts. All from command line, and we could also forget the web dashboard for a while. OCI CLI does not replace the dashboard but extends it, and makes possible to automate routines.

In this short post I’ll cover OCI CLI setup, before we jump into some usage examples.

Install OCI CLI

Let’s start installing OCI CLI, the documentation show different methods, using Python installer it’s one command to go.

pip install oci-cli

For more methods, just follow online documents.

Configure OCI CLI

To give access to your OCI user account, you will need to configure OCI CLI. That’s straightforward. Prepare in advance:

• User OCID
• Tenancy OCID
• Region identifier

Check this link to know how to retrieve such information. With this information you are one step away from configuring the access; just run:

oci setup config

And feed the information requested. You will be prompted for a directory to create keys and a passphrase for your private key. Go ahead and see how this command will produce the following files once it completes:

bash-3.2$ ll $HOME/.oci
total 24
-rw------- 1 mortensi staff 306 28 Nov 12:20 config
-rw------- 1 mortensi staff 1766 28 Nov 12:20 oci_api_key.pem
-rw------- 1 mortensi staff 451 28 Nov 12:20 oci_api_key_public.pem

These are private and public key pair, plus a config file (SDK and CLI config file). The config file holds all the content provided plus the link to the private key. All the information to be used to authenticate to your OCI account.

To complete the setup, you will need to feed the public key into your OCI dashboard. Just follow the instructions and… mission accomplished!

Use OCI CLI

Well, this is immediate. With OCI CLI you can do everything from console. Get information about your DB Systems, create, start, stop and so on.

oci mysql db-system get --db-system-id <MDS_OCID>
Private key passphrase:
{
"data": {
"analytics-cluster": null,
"availability-domain": "EOuL:US-ASHBURN-AD-1",
"backup-policy": {
"defined-tags": null,
"freeform-tags": null,
"is-enabled": true,
"retention-in-days": 7,
"window-start-time": "00:00"
},
"compartment-id": "xxxxxxxxxxxxxxxxxxxxx",
"configuration-id": "xxxxxxxxxxxxxxxxxxxxx",
"data-storage-size-in-gbs": 50,
"defined-tags": {
"Oracle-Tags": {
"CreatedBy": "oracleidentitycloudservice/xxxxxxxxxxxxxxxxxxxxx@oracle.com",
"CreatedOn": "2020-11-13T11:32:27.803Z"
}
},
"description": null,
"display-name": "mysql20201113123145",
"endpoints": [
{
"hostname": null,
"ip-address": "10.0.1.9",
"modes": [
"READ",
"WRITE"
],
"port": 3306,
"port-x": 33060,
"status": "ACTIVE",
"status-details": null
}
],
"fault-domain": "FAULT-DOMAIN-1",
"freeform-tags": {},
"hostname-label": null,
"id": "xxxxxxxxxxxxxxxxxxxxx",
"ip-address": "10.0.1.9",
"is-analytics-cluster-attached": false,
"lifecycle-details": null,
"lifecycle-state": "ACTIVE",
"maintenance": {
"window-start-time": "WEDNESDAY 07:21"
},
"mysql-version": "8.0.22",
"port": 3306,
"port-x": 33060,
"shape-name": "VM.Standard.E2.1",
"source": null,
"subnet-id": "xxxxxxxxxxxxxxxxxxxxx",
"time-created": "2020-11-13T11:32:29.593000+00:00",
"time-updated": "2020-11-25T07:34:31.055000+00:00"
},
"etag": "xxxxxxxxxxxxxxxxxxxxx"
}

Data migrations using MySQL Shell

So far, so good, but once the OCI CLI is working, you can also do more. Use the same setup you already created: that will be using MySQL Shell!

By now you probably know very well MySQL Shell, the powerful command line tool to administer MySQL Server in the different flavors: standalone, with ReplicaSets and InnoDB Cluster. Besides that, you can also use MySQL Shell dump utilities to export and import backups from and to OCI Object Storage!

You can use MySQL Shell to:

• Dump full on-premises instances, schemas or table from MySQL Server databases and upload data to an Object Storage bucket
• Export data from an Object Storage bucket into a DB System

MySQL Shell needs to read the config file just created, paired to OCI user account via the public/ private key pair (see above). MySQL Shell reads it by default at ~/.oci/config (or is set with parameter ociConfigFile) to gain access to OCI. Read more here.

OCI Object Storage is a regional service: to access Object Storage buckets on different regions you will need a configuration per region. Achieve it with different profiles. One profile, one region (see picture). So from your on-premises MySQL Server you will be able to upload dumps to every region where an MDS DB System is available. Complete data migrations smoothly.