OCI MySQL Database Service is a fully managed MySQL service with automatic failover in high-availability mode, built-in backup and point-in-time recovery, read replicas for read scaling, and optional HeatWave cluster for in-memory analytics. Unlike self-managed MySQL on compute instances, the managed service handles OS patching, MySQL version upgrades, backup scheduling, and failover without any operator intervention. This post covers Terraform provisioning for an HA production setup.

Step 1: HA MySQL DB System

resource "oci_mysql_mysql_db_system" "orders_mysql" {
  compartment_id      = var.compartment_id
  display_name        = "orders-mysql-production"
  shape_name          = "MySQL.8"
  availability_domain = data.oci_identity_availability_domains.ads.availability_domains[0].name
  subnet_id           = var.db_subnet_id

  admin_username = "mysqladmin"
  admin_password = var.mysql_admin_password

  # HA mode: automatic failover across fault domains
  is_highly_available = true

  data_storage_size_in_gb = 500
  mysql_version           = "8.0.36"

  backup_policy {
    is_enabled         = true
    retention_in_days  = 14
    window_start_time  = "02:00"
    pitr_policy {
      is_enabled = true
    }
  }

  maintenance {
    window_start_time = "sun 03:00"
  }

  configuration_id = var.mysql_custom_config_id

  defined_tags = { "Operations.Environment" = "production", "Operations.ManagedBy" = "terraform" }
}

output "mysql_endpoint" {
  value = oci_mysql_mysql_db_system.orders_mysql.endpoints[0].hostname
}

output "mysql_ip" {
  value = oci_mysql_mysql_db_system.orders_mysql.endpoints[0].ip_address
}

Step 2: Read Replica

resource "oci_mysql_replica" "orders_read_replica" {
  db_system_id = oci_mysql_mysql_db_system.orders_mysql.id
  display_name = "orders-mysql-read-replica"
  description  = "Read replica for reporting and analytics queries"

  # Override shape for read replica - can be smaller than primary
  replica_overrides {
    shape_name = "MySQL.4"
  }

  is_delete_protected = false

  defined_tags = { "Operations.ManagedBy" = "terraform" }
}

output "replica_endpoint" {
  value = oci_mysql_replica.orders_read_replica.endpoints[0].hostname
}

Step 3: HeatWave Cluster Attachment

resource "oci_mysql_heat_wave_cluster" "orders_heatwave" {
  db_system_id = oci_mysql_mysql_db_system.orders_mysql.id
  shape_name   = "MySQL.HeatWave.VM.Standard"
  cluster_size = 2
}

Step 4: Performance Alarms

resource "oci_monitoring_alarm" "mysql_cpu_high" {
  compartment_id        = var.compartment_id
  display_name          = "mysql-cpu-utilization-high"
  is_enabled            = true
  metric_compartment_id = var.compartment_id
  namespace             = "oci_mysql_database"
  query                 = "CpuUtilization[5m]{dbSystemId = 'MYSQL_OCID'}.mean() > 80"
  severity              = "WARNING"
  pending_duration      = "PT10M"
  destinations          = [var.ops_notification_topic_id]
  body                  = "MySQL DB System CPU above 80% for 10 minutes. Review slow query log and consider scaling to a larger shape."
}

resource "oci_monitoring_alarm" "mysql_connections_high" {
  compartment_id        = var.compartment_id
  display_name          = "mysql-connections-high"
  is_enabled            = true
  metric_compartment_id = var.compartment_id
  namespace             = "oci_mysql_database"
  query                 = "CurrentConnections[5m]{dbSystemId = 'MYSQL_OCID'}.max() > 400"
  severity              = "WARNING"
  pending_duration      = "PT5M"
  destinations          = [var.ops_notification_topic_id]
  body                  = "MySQL connection count above 400. Review application connection pool settings and add a connection proxy if needed."
}

Operational Notes

HA MySQL uses synchronous replication to a standby in a different fault domain within the same availability domain. Failover is automatic and typically completes in under 60 seconds. The endpoint hostname stays the same after failover, so your application reconnects to the new primary without any configuration change. Configure your application connection pool with a reconnect-on-failure setting and a wait time that accommodates the 60-second failover window.

Read replicas use asynchronous replication from the primary. Replication lag is typically milliseconds for write-light workloads, but can grow to seconds or minutes under heavy write loads. Direct reporting queries that can tolerate slightly stale data to the read replica endpoint. Never use the read replica for reads that need data written within the last few seconds, and never write to the read replica endpoint.

Regards,
Osama

#OCI #OracleCloud #MySQL #DatabaseService #Terraform #IaC #TechBlog #Oracle #CloudDatabase #HighAvailability #ReadReplica #OracleCloudInfrastructure #HeatWave #PITR #BackupPolicy #Replication #ManagedDatabase #PlatformEngineering #DevOps #DataManagement