OCI MySQL Database Service – Using a backup as source for a new instance

Let’s continue our journey of deploying the MySQL Database System on OCI with Terraform.
This time we will see how we can use a backup (see [1] and [2]) as a source (initial data) for a new instance.

Within the oci_mysql_mysql_db_system it’s possible to define a source detailing how to provision the initial data of the db system.

Let’s deploy a new MySQL Database Instance using a manual backup we made earlier:

We can also find the backup’s ocid using the oci cli command:

 $ oci mysql backup list --compartment-id=\
   ocid1.compartment.oc1..xxxx --lifecycle-state=ACTIVE | \
   jq '.[][] | {ocid: ."id", description: ."description",
   "creation-time": ."time-created", type: ."creation-type"}'
{
  "ocid": "ocid1.mysqlbackup.oc1.xxxxgrf2x3u4ha",
  "description": "Initial - Backup - Thu, Oct 6, 2022, 14:15:52 UTC",
  "creation-time": "2022-10-06T14:16:08.068000+00:00",
  "type": "MANUAL"
}

The backup includes all the data that we created on that initial instance, and in this example, I’ve created a schema called restaurants.

Now we just need to define the resource in Terraform like this and specify the backup as source (lines 33 -36):

resource "oci_mysql_mysql_db_system" "MDSinstance" {
    admin_password = var.admin_password
    admin_username = var.admin_username
    availability_domain = var.availability_domain
    compartment_id = var.compartment_ocid
    configuration_id = oci_mysql_mysql_configuration.mds_mysql_configuration.id
    shape_name = var.mysql_shape
    subnet_id = var.subnet_id
    data_storage_size_in_gb = var.mysql_data_storage_in_gb
    display_name = var.display_name
   
    freeform_tags = {"backup_defined_by"="Terraform"}

    count = var.existing_mds_instance_id == "" ? 1 : 0

    is_highly_available = var.deploy_ha

    maintenance {
      window_start_time = "sun 01:00"
    }

    backup_policy {
       is_enabled        = "true"
       retention_in_days = "3"
       window_start_time = "01:00-00:00"
       freeform_tags = {"backup_defined_by"="Terraform"}
       pitr_policy {
            #Required
            is_enabled = "true"
       }
    }

    source {
      backup_id   = "ocid1.mysqlbackup.oc1.xxxxgrf2x3u4ha"
      source_type = "BACKUP"
    }
    
}

When the deployment is finished, we can check if the data from the backup is already present on the system:

This concludes the mini series of articles dedicated to deployment of MySQL Database Service in OCI with Terraform and backups. We have learned how to create backup policies and how to use a backup as source for a new database instance deployment.

Happy automated deployment on OCI with Terraform and enjoy MySQL !