Manage MySQL Database Service (MDS) with Python
You only have to choose your favorite flavour to setup, administer, configure, backup or attach an analytics cluster to your MDS DB System, as Oracle maintains SDKs in several languages to integrate your software with Oracle Cloud Infrastructure.
And in particular today I’d like to test the SDK for Python, you can find an introduction here (but you have also SDKs for Java, Go, Ruby, Javascript, .NET). Read an introduction to SDKs.
First of all let’s create a virtual environment (venv) to work in a containerized environment.
bash-3.2$ python3 -m venv .
bash-3.2$ ll
total 8
drwxr-xr-x 14 mortensi staff 448 20 Dic 09:30 bin
drwxr-xr-x 2 mortensi staff 64 20 Dic 09:30 include
drwxr-xr-x 3 mortensi staff 96 20 Dic 09:30 lib
-rw-r--r-- 1 mortensi staff 75 20 Dic 09:30 pyvenv.cfg
Use the venv:
bash-3.2$ source bin/activate
(Software) bash-3.2$
Install Python SDK from Python package Index.
(Software) bash-3.2$ pip install oci
Collecting oci
Downloading oci-2.26.0-py2.py3-none-any.whl (7.2 MB)
|████████████████████████████████| 7.2 MB 6.1 MB/s
Collecting certifi
Downloading certifi-2020.12.5-py2.py3-none-any.whl (147 kB)
|████████████████████████████████| 147 kB 5.8 MB/s
Collecting cryptography==3.2.1
Downloading cryptography-3.2.1-cp35-abi3-macosx_10_10_x86_64.whl (1.8 MB)
|████████████████████████████████| 1.8 MB 5.7 MB/s
Collecting configparser==4.0.2
Using cached configparser-4.0.2-py2.py3-none-any.whl (22 kB)
Collecting python-dateutil<3.0.0,>=2.5.3
Using cached python_dateutil-2.8.1-py2.py3-none-any.whl (227 kB)
Collecting pytz>=2016.10
Using cached pytz-2020.4-py2.py3-none-any.whl (509 kB)
Collecting pyOpenSSL<=19.1.0,>=17.5.0
Downloading pyOpenSSL-19.1.0-py2.py3-none-any.whl (53 kB)
|████████████████████████████████| 53 kB 2.3 MB/s
Collecting six>=1.4.1
Downloading six-1.15.0-py2.py3-none-any.whl (10 kB)
Collecting cffi!=1.11.3,>=1.8
Using cached cffi-1.14.4-cp39-cp39-macosx_10_9_x86_64.whl (177 kB)
Collecting pycparser
Using cached pycparser-2.20-py2.py3-none-any.whl (112 kB)
Installing collected packages: certifi, six, pycparser, cffi, cryptography, configparser, python-dateutil, pytz, pyOpenSSL, oci
Successfully installed certifi-2020.12.5 cffi-1.14.4 configparser-4.0.2 cryptography-3.2.1 oci-2.26.0 pyOpenSSL-19.1.0 pycparser-2.20 python-dateutil-2.8.1 pytz-2020.4 six-1.15.0
WARNING: You are using pip version 20.2.3; however, version 20.3.3 is available.
You should consider upgrading via the '/Users/mortensi/Software/bin/python3 -m pip install --upgrade pip' command.
(Software) bash-3.2$
And check that installation is all right.
(Software) bash-3.2$ pip list
Package Version
--------------- ---------
certifi 2020.12.5
cffi 1.14.4
configparser 4.0.2
cryptography 3.2.1
oci 2.26.0
pip 20.2.3
pycparser 2.20
pyOpenSSL 19.1.0
python-dateutil 2.8.1
pytz 2020.4
setuptools 49.2.1
six 1.15.0
And get also information about the SDK package.
(Software) bash-3.2$ pip show oci
Name: oci
Version: 2.26.0
Summary: Oracle Cloud Infrastructure Python SDK
Home-page: https://oracle-cloud-infrastructure-python-sdk.readthedocs.io/en/latest/index.html
Author: Oracle
Author-email: joe.levy@oracle.com
License: Universal Permissive License 1.0 or Apache License 2.0
Location: /Users/mortensi/Software/lib/python3.9/site-packages
Requires: pyOpenSSL, certifi, python-dateutil, pytz, cryptography, configparser
Required-by:
Now it’s time to make a test. Create a hello-world-like user.py file and edit:
import oci
config = oci.config.from_file("~/.oci/config", "DEFAULT")
identity = oci.identity.IdentityClient(config)
user = identity.get_user(config["user"]).data
print(user)
And test all works nicely (you must have set the right OCI SDK configuration file in “~/.oci/config”, read the how to here).
(Software) bash-3.2$ python user.py
{
"capabilities": {
"can_use_api_keys": true,
"can_use_auth_tokens": true,
"can_use_console_password": false,
"can_use_customer_secret_keys": true,
"can_use_o_auth2_client_credentials": true,
"can_use_smtp_credentials": true
},
"compartment_id": "ocid1.tenancy.oc1..[...]",
"defined_tags": {},
"description": "[...]",
[...]
"time_created": "2019-04-14T21:04:04.293000+00:00"
}
All good so far? Now time to start something interesting to manage our MySQL DB System! Check the API here. Let’s say we urgently need a new DB System in an existing subnet which we can access from an existing bastion host. Here’s what we would do.
- Create a CreateDbSystemDetails with all the information needed to request OCI to create a MySQL DB System
- Pass it to create_db_system
Find an example here. And here’s a working script with required parameters
import oci
config = oci.config.from_file("~/.oci/config", "DEFAULT")
mysql_client = oci.mysql.DbSystemClient(config)
COMPARTMENT_ID="ocid1.compartment..."
SHAPE_NAME="VM.Standard.E2.1"
SUBNET_ID="ocid1.subnet..."
ADMIN_USERNAME="root"
ADMIN_PASSWORD="Oracle1*"
STORAGE_SIZE=50
AD="EOuL:US-ASHBURN-AD-1"
# Send the request to service, some parameters are not required, see API
# doc for more info
create_db_system_response = mysql_client.create_db_system(
create_db_system_details=oci.mysql.models.CreateDbSystemDetails(
compartment_id=COMPARTMENT_ID,
shape_name= SHAPE_NAME,
subnet_id=SUBNET_ID,
admin_username=ADMIN_USERNAME,
admin_password=ADMIN_PASSWORD,
data_storage_size_in_gbs=STORAGE_SIZE,
availability_domain=AD))
# Get the data from response
print(create_db_system_response.data)
Which, once launched will produce:
{
"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"
},
"channels": [],
"compartment_id": "ocid1.compartment.oc1...",
"configuration_id": "ocid1.mysqlconfiguration...",
"data_storage_size_in_gbs": 50,
"defined_tags": {
"Oracle-Tags": {
"CreatedBy": "oracleidentitycloudservice/...",
"CreatedOn": "2020-12-23T18:01:18.203Z"
}
},
"description": null,
"display_name": "mysqldbsystem20201223180120",
"endpoints": [],
"fault_domain": null,
"freeform_tags": {},
"hostname_label": null,
"id": "ocid1.mysqldbsystem.oc1.iad....",
"ip_address": null,
"is_analytics_cluster_attached": false,
"lifecycle_details": null,
"lifecycle_state": "CREATING",
"maintenance": {
"window_start_time": "MONDAY 07:35"
},
"mysql_version": null,
"port": null,
"port_x": null,
"shape_name": "VM.Standard.E2.1",
"source": null,
"subnet_id": "ocid1.subnet...",
"time_created": "2020-12-23T18:01:20.148000+00:00",
"time_updated": "2020-12-23T18:01:20.148000+00:00"
}
Now you can interrogate OCI about this DB System create_db_system_response object to understand when the instance is ready from lifecycle_state. You can use this other script passing the DB System id returned from the DB System creation response, for instance.
import oci
config = oci.config.from_file("~/.oci/config", "DEFAULT")
mysql_client = oci.mysql.DbSystemClient(config)
get_db_system_response = mysql_client.get_db_system("ocid1.mysqldbsystem...")
# Get the data from response
print(get_db_system_response.data)
When the instance is ready (“lifecycle_state”: “ACTIVE”), you are ready to use the instance.
This concludes this brief how-to administer your OCI MDS DB Systems straight from your intranet, no VPN needed. Easier, impossible.