List Backups for a DB System

Stop/Start a DB System

Notice we see a status of SUCCEEDED. If the work request were still running, we would see the appropriate status. This is how you can track your work requests with the CLI, and it is a common mechanism.

Like start/stop, you can also delete DB Systems as well as list the DB Systems in a compartment. There are many more commands available for DB Systems. Follow the links below in the For More Information section to learn more about the commands.

Ok, let’s look at one more example but this time we will use a complex operation: creating a new DB System.

Create a DB System

Creating a DB System requires a more complex set of parameters and options. In this case, we need, at a minimum, the compartment OCID, subnet OCID, shape name (not OCIDs), and availability domain name. We will add some more optional parameters, but let’s start assuming we do not know the OCIDs for these objects. Never fear, we can list them with the CLI!

Next, we need the MySQL shapes available. Since this list could be long, we will see a paged output that will require multiple calls to get the complete list. If you’re like me, you don’t have the patience for that, so we can use the --all parameter.

There is one more option we should discuss – the option to have the operation wait for a specific state. We do this with the --wait-for-state <state> option and one of the states (ACCEPTED, CANCELED, CANCELING, FAILED, IN_PROGRESS, SUCCEEDED). You can specify this option multiple times to wait for multiple operations. This operation asynchronously executes the operation and uses a work request to track the progress. Multiple states can be specified, returning on the first state. If timeout is reached, a return code of 2 is returned. For any other error, a return code of 1 is returned.

Ok, now the operation has returned, and it succeeded. If we visit the OCI console, we can find the DB System and display its details page as demonstrated in Figure 9-1.

A Database page of My SQL C L I Create with details of general information, D B system configuration, backup, heatwave, high availability, networking, placement, and endpoint.

Now that we’ve seen a few of the more common operations including listing, creating, and deleting resources, let’s look at the documentation available for the other MDS operations available in the CLI.

Install the Python SDK

Before we can use the Python SDK, we must install it. Recall, we mentioned that the Python SDK is part of the OCI CLI, but it is installed in its own environment, so it is not accessible by a typical Python client. Fortunately, we can install the Python SDK alongside the OCI CLI without affecting either product. The following demonstrates how to install the Python SDK on Windows 11, but details for installing on other platforms or with other methods can be found at https://docs.oracle.com/en-us/iaas/tools/python/2.79.0/installation.html.

Getting Started

The basic layout of any Python SDK script begins with an import section where you import the getpass and oci modules. We need the getpass module because we must read the SSH key (called a passphrase) from the user (command-line).

This is followed by creating an instance of the oci.config class. This is an operation that reads your configuration file and prepares the SDK objects for use with your credentials. We then read and add the passphrase to the configuration file dictionary. Next, we connect to whichever API class we want to use and, if the connection is successful, we can proceed to make the API method calls.

Listing 9-16 shows the basic Python script you need to access the OCI API classes. The script is named basic_api.py.

Now, let’s look at some of the examples we used in the CLI section to see how to accomplish the same using the Python SDK. Let’s start by listing the MySQL shapes using the oci.mysql.MysqlaasClient class.

Listing the MySQL Shapes

To list the MySQL shapes, we use the list_shapes() method from the oci.mysql.MysqlaasClient class, which takes as a parameter the compartment id, which is common among many of the API methods.

This returns a list or ShapeSummary classes, which we can use to loop through the shapes and print the name, which is a lot cleaner (and fewer lines) than a JSON output like the CLI. You can find the description of the ShapeSummary class at https://docs.oracle.com/en-us/iaas/tools/python/2.79.0/api/mysql/models/oci.mysql.models.ShapeSummary.html#oci.mysql.models.ShapeSummary.

Now, let’s take the complexity up a bit. Let’s see how to control DB Systems.

Stop/Start DB System

When working with DB Systems, we will use the oci.mysql.DbSystemClient class and its methods. To stop a DB System, we use the stop_db_system() method and to start a DB System, we use the start_db_system() method. As you can see, the API methods are often quite self-explanatory.

This example is a bit more complicated because we will have to provide a DB System id (OCID) in the form of another class named stop_db_system_details() for the stop or start operation. This class uses a model to populate the data. In this case, we need the DB System OCID and the shutdown type.

We are also going to make it a bit more complex by first listing the DB Systems in our compartment and select the one with a specific name. You may be amazed at how difficult it is to remember OCIDs, but it is much easier to remember a DB System name. We’ll display a list for the user to choose the DB System to control. To list the DB Systems, we use the list_db_systems() method and pass in the compartment id, which we have saved in an environment file.

In summary, we will import the libraries we need, read the configuration file, read the SSH key passphrase from the user, read the compartment id from the environment variable, then read the DB System name from the user. Once we have that information, we can call the method to list the DB Systems in the compartment, select one that matches the name, then either stop or start it.

This script would make a good starting point for other operations you may want to perform on your DB Systems. Simply modify it to your needs!

The last example we will look at is the operation to create a new DB System. While the Python code may seem more complex, but it uses the same features of this example only with a more sophisticated Python programming style. However, there are more API methods, and the parameters are more complex.

Creating a DB System

Recall from the CLI example, we used four CLI commands; one each to get the shape name, subnet id, and availability domain, and one to perform the create. This example using the Python API will do the same thing only in a single Python script.

Like the last example, we will display a simple user interface to allow the user to choose the items from the lists asking only for the display name and data size as prompts. We will also prompt for the MySQL administrative user and password.

Rather than go through line-by-line, let’s summarize what we will be doing. We know we must get the subnet, shape, and availability domain so we will write methods to get that information using a helper method that presents a list to the user prompting her to choose one from the list. Thus, we will get these three data items from the user.

In doing so, we will explore more API classes including the oci.core.VirtualNetworkClient, oci.mysql.MysqlaasClient, and oci.identity.IdentityClient classes calling the appropriate methods for getting a list of those resources.

The optional parameters; MySQL administrator user and password, data storage size, and display name are read from the user with prompts. We also set the HA to disabled by default.

Once we have all the information, we use the create_db_system() method of the oci.mysql.DbSystemClient class to create the DB System. This method requires the population of a model named oci.mysql.models.CreateDbSystemDetails.

Finally, since the create_db_system() method returns immediately, we pole the DB System by fetching its details with the get_db_system() method passing in the OCID for the new DB System as returned in the create_db_system_response.data.id variable. We pole the DB System until its lifecycle_state equals ACTIVE. There are other methods for doing this including some helper methods in the OCI API, but those are a bit more advanced for those getting started. Polling the lifecycle_state is an acceptable initial solution if you want the script to execute and wait until the resource is ready.

That’s about it. There are more nuances to this example especially the Python programming parts, but we leave those details to you to ponder as an exercise. There are no magical or arcane code constructs here, just normal Python code around the Python API for OCI.

Take some time and experiment with this script for your own uses. You may find it helpful to getting your own automation in Python going for MDS.

However, in case you’re curious, Figure 9-2 shows the DB System details page from the console.

A Database page of TEST A P I with details of general information, D B system configuration, backup, heatwave, high availability, networking, placement, and endpoint.

But wait, there is more. Let’s add a bonus example for you to try on your own.

Delete a DB System

Your challenge is to take the control DB System script above and add the delete operation. To get started, you will need to use the delete_db_system () method of the oci.mysql.DbSystemClient class. You can find the documentation for this method and an example Python script at https://docs.oracle.com/en-us/iaas/tools/python/2.79.0/api/mysql/client/oci.mysql.DbSystemClient.html#oci.mysql.DbSystemClient.delete_db_system. You should not have to add much to the control DB System script (control_db_system.py) to accomplish this task, but it will be a good exercise for those just starting out with Python and the API.

Well, that’s our tour of the API! Although the tour of the MDS API and the Python SDK was brief, it should give you an idea of what is possible and the basis to begin researching your own Python applications to work with MDS resources.