MySQL HeatWave: Creating DB Systems and High Availability Setup

MySQL HeatWave is Oracle's fully managed database service that combines transactional and analytical workloads in a single platform. This guide walks you through creating a MySQL HeatWave DB System and implementing high availability for enterprise-grade reliability.

Creating a MySQL HeatWave DB System

Step 1: Access OCI Console

Log in to your Oracle Cloud Infrastructure (OCI) console at cloud.oracle.com with your credentials.

Step 2: Navigate to DB Systems

1. Open the navigation menu (hamburger icon)
2. Under Databases, select HeatWave MySQL
3. Click DB Systems from the menu

Step 3: Initiate DB System Creation

Click the Create DB System button. This opens the Create DB System dialog with several configuration options.

Step 4: Choose a Template

Oracle provides three templates to streamline setup:

• Production: Creates a high availability DB system with recommended settings for production environments
• Development or Testing: Creates a standalone DB system optimized for dev/test workloads
• Always Free: Creates a standalone system with limited resources (50GB storage, MySQL.Free shape) suitable for learning and small projects

Step 5: Configure Basic Information

Compartment Selection

• Choose the compartment where you want to create the DB system
• Compartments help organize and manage access to resources

System Identification

• Name: Provide a display name for your DB system (doesn't need to be unique)
• Description: (Optional) Add details about the system's purpose
• OCID: Oracle automatically generates a unique identifier

Step 6: Configure Database Credentials

Administrator Credentials

• Username: Specify the administrator username (avoid reserved usernames)
• Password: Create a strong password that meets security requirements
• Important: Save these credentials securely – you'll need them to connect to the database

Step 7: Configure Networking

Virtual Cloud Network (VCN)

• Select an existing VCN or create a new one
• Choose the private subnet for the DB system (recommended for security)
• Availability Domain: Select the AD where the primary instance will run
• Fault Domain: Oracle automatically selects an appropriate fault domain

Configure Security List

Ensure your VCN's security list allows MySQL traffic:

• Source CIDR: 0.0.0.0/0 (or restrict to specific IP ranges)
• Destination Ports: 3306 (MySQL) and 33060 (MySQL X Protocol)

Step 8: Select Shape

Choose a shape that defines your system's compute resources:

• ECPU Shapes (Recommended as of 2024): Architecture-agnostic shapes that can be changed after deployment
• OCPU Shapes: Being deprecated for new users (sunset date: March 13, 2026)
• Examples: MySQL.8, MySQL.16, MySQL.32, MySQL.HeatWave.VM.Standard

The shape determines CPU count, memory, and network bandwidth. You can scale up or down later based on workload demands.

Step 9: Configure Storage

Initial Data Storage Size

• Specify storage in GB (minimum: 50 GB, maximum: 131,072 GB)
• Consider your current data size plus growth projections
• Storage can be increased later but not decreased

Automatic Storage Expansion

• Enable this feature to automatically grow storage when space usage approaches the Health Monitor read-only limits
• Helps prevent storage-related outages

Step 10: Configure HeatWave Cluster (Optional)

Enable HeatWave Cluster

• Select this option to add analytics capabilities
• Configure the number of HeatWave nodes (minimum: 1, maximum: depends on shape and region)
• Choose the HeatWave node shape

MySQL HeatWave Lakehouse

• Enable to query data in Object Storage (CSV, Parquet, Aurora/Redshift backups)
• Note: Prior to MySQL 8.3.0-u2, enabling Lakehouse required disabling HA, point-in-time recovery, and read replicas

Step 11: Configure Backup Plan

Backup Settings

• Enable automatic backups: Recommended for production
• Backup retention period: Choose from 1 to 35 days
• Point-in-time recovery: Enables restoration to any point within the retention window
• Backup window: Select a time when database activity is lowest

Step 12: Advanced Options (Optional)

Configuration

• Apply a custom configuration or use the default
• High availability requires an HA-compatible configuration (configurations with "HA" suffix)

Hostname Label

• Define a DNS hostname for your DB system (requires DNS hostnames enabled in VCN)

Tags

• Add metadata for organization and cost tracking

Step 13: Create the DB System

Review all settings and click Create. The DB system enters the CREATING state and typically becomes available in 15-30 minutes.

Understanding High Availability in MySQL HeatWave

High availability is a critical feature for production workloads that require maximum uptime and fault tolerance.

What is High Availability?

A high availability DB system consists of three MySQL instances: a primary instance and two secondary instances, with the primary functioning as a read/write endpoint and secondary instances receiving asynchronous data replication from the primary.

Key Benefits

1. Business Continuity: Three MySQL instances distributed across different availability or fault domains
2. Minimal Downtime: Automatic failover promotes a secondary instance to primary when the primary fails, resuming availability with no data loss
3. Flexible Placement: Choose your preferred availability domain and fault domain for the primary instance
4. Uptime SLA: Supports 99.99% availability requirements
5. HeatWave Compatible: You can enable HeatWave cluster on high availability DB systems

Architecture Components

Primary Instance

• Handles all write operations and read queries
• Located in your chosen availability domain
• Provides the read/write endpoint with a consistent IP address

Secondary Instances (2)

• Receive replicated data from the primary asynchronously
• Placed in different availability or fault domains from the primary
• Ready to be promoted to primary during failover

Placement Models

There are two deployment models:

1. Multiple Availability Domains with Regional Subnet: Three instances spread across three different ADs (AD1, AD2, AD3)
2. Single Availability Domain with Multiple Fault Domains: All three instances in one AD but different fault domains

Replication Mechanism

MySQL HeatWave uses MySQL Group Replication for high availability, with primary keys required as a prerequisite for the group replication used by high availability. Data is copied from the primary to secondary instances over a secure, managed internal network.

Important: The binary logs of each MySQL instance are managed independently, meaning even though instances have the same data, they can have different binary log files with different names and sizes.

Prerequisites for Enabling High Availability

Before enabling high availability on a DB system, ensure the following requirements are met:

1. MySQL Version Requirement

MySQL version must be 8.0.24 or higher to enable high availability. If using an older version, upgrade before enabling HA.

2. Primary Keys on All Tables

If you are migrating data to MySQL HeatWave and have not defined primary keys on your tables, you must add them.

Why Primary Keys Matter

• Required for MySQL Group Replication
• Ensure data consistency across instances
• Impact load performance if composite or non-INTEGER

How to Check and Add Primary Keys

Use MySQL Shell to identify tables without primary keys:

SELECT DISTINCT
    table_schema,
    table_name
FROM information_schema.columns
WHERE table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
AND table_name NOT IN (
    SELECT table_name
    FROM information_schema.table_constraints
    WHERE constraint_type = 'PRIMARY KEY'
);

Adding Primary Keys Using Invisible Columns

Using invisible columns to add primary keys is a low-impact way to update existing data for use with a high availability DB system:

ALTER TABLE your_table 
ADD COLUMN id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY INVISIBLE;

3. Crash Recovery Enabled

Verify crash recovery is enabled on your DB system. This is typically enabled by default but can be checked in the configuration settings.

4. Inbound Replication Stopped

If you intend to configure an inbound replication channel on this DB system, you must import data before enabling high availability and configure your channel after high availability is enabled.

5. Compatible Configuration

All default configurations that are compatible with high availability have the HA suffix in the configuration name. If using a custom configuration, copy it from an existing HA-compatible configuration.

6. HeatWave Cluster Considerations

If a HeatWave cluster load or reload operation is ongoing, enabling or disabling high availability can fail. Wait for these operations to complete before making HA changes.

Enabling High Availability on an Existing DB System

Using the Console

1. Navigate to your DB System details page
2. From the Actions menu, select Enable high availability
3. Review the prerequisites dialog
4. Click Enable high availability

The process creates two secondary instances by cloning from the primary instance and does not cause any downtime.

Using OCI CLI

oci mysql db-system update \
  --db-system-id <DB_SYSTEM_OCID> \
  --is-highly-available true

Note: If the configuration is not compatible with high availability, you must first change the DB system to use a high availability compatible configuration before enabling it.

Understanding Failover and Switchover

Automatic Failover

When the primary instance becomes unavailable due to hardware failure, network issues, or other problems, MySQL HeatWave Service automatically initiates a failover.

Failover Process:

1. Service detects primary instance failure
2. One secondary instance is automatically promoted to primary
3. The IP address of the read/write endpoint does not change, regardless of the placement of the primary instance
4. Applications must reconnect, but the endpoint remains the same
5. No data loss occurs

For a high availability DB system with HeatWave cluster, if the primary instance fails, HeatWave Service deletes the HeatWave cluster attached to the failed instance and adds a new one to the new primary instance, with tables automatically loaded.

Post-Failover State:

• The current placement changes to the new primary's location
• The preferred placement (your original choice) remains unchanged
• A message appears: "Current placement differs from preferred placement, due to failover"

Manual Switchover

You can manually select one of the MySQL instances as the primary instance through switchover, which changes both the preferred placement and current placement to the newly selected placement.

When to Use Switchover:

• Planned maintenance on the current primary's availability domain
• Testing disaster recovery procedures
• Rebalancing after a failover to restore preferred placement

Switchover Process (for systems created/upgraded after February 25, 2025):

New transactions are blocked when switchover is initiated, running transactions are allowed to complete, and the switchover process waits until all running transactions finish and commit.

1. Navigate to DB System details page
2. Click Switchover from the Actions menu
3. Select the target availability domain or fault domain
4. Confirm the switchover

When the current placement changes in a switchover, it results in a short period of downtime while the primary instance is redirected to the newly promoted instance, and all database connections must be reopened.

Using CLI:

oci mysql db-system update \
  --db-system-id <DB_SYSTEM_OCID> \
  --availability-domain <TARGET_AD>

Important Notes:

• Uncommitted transactions are rolled back when the connection to the previous primary instance is broken
• Long-running queries offloaded to HeatWave cluster may be aborted
• After a switchover, the current binary log file name and position of the new primary may be different from the old primary

HeatWave Cluster with High Availability

You can enable HeatWave cluster on a high availability DB system for combined OLTP and analytics capabilities.

HeatWave Behavior During Placement Changes

Same Availability Domain:
If both the previous primary instance and the new primary instance are located in the same availability domain, the existing HeatWave cluster can be reused by detaching from the previous primary and reattaching to the new primary.

Different Availability Domains:
If the previous primary and new primary are in different availability domains, the existing HeatWave cluster must be detached and deleted, with a new HeatWave cluster created in the same AD as the new primary instance.

Data Recovery

The data in the HeatWave cluster is automatically recovered from:

• The HeatWave Storage Layer (fastest option)
• Reloaded from the DB system
• Reloaded from Lakehouse Object Storage

This ensures minimal disruption to analytics workloads even during failover scenarios.

Disabling High Availability

If you need to convert a high availability DB system back to standalone:

Disabling high availability creates a standalone DB system from the instance running in the preferred primary placement, with no downtime if the primary is already in the preferred placement, but a controlled failover and short downtime if it is not.

Console Method:

1. Navigate to DB System details
2. Actions menu → Disable high availability
3. Confirm the action

CLI Method:

oci mysql db-system update \
  --db-system-id <DB_SYSTEM_OCID> \
  --is-highly-available false

Tip: You can avoid downtime by switching the preferred primary placement to the current primary instance before disabling high availability.

Monitoring and Observability

Health Monitoring

MySQL HeatWave provides built-in monitoring:

• Instance health status
• Storage utilization
• Connection metrics
• Replication lag (for HA systems)

Event Notifications

When a failover happens, a MySQL - Automatic Recovery event is emitted on the DB system with the additionalDetails.isFailover property set to true.

Configure operational notifications to receive alerts for:

• Failover events
• Storage threshold warnings
• Backup completion/failure
• Maintenance notifications

Integration with Monitoring Services

• OCI Monitoring: Native integration with OCI Monitoring service
• AWS CloudWatch: Available for MySQL HeatWave on AWS
• Custom Metrics: Export metrics to third-party monitoring tools

Best Practices

Planning for High Availability

1. Always Use HA for Production: Any workload requiring uptime guarantees should use high availability
2. Test Failover Procedures: Regularly perform switchovers to verify your disaster recovery processes
3. Monitor Replication Lag: Though minimal, monitor lag between primary and secondary instances
4. Configure Application Retry Logic: Ensure applications can reconnect automatically after failover

Storage Planning

1. Oversize Storage Initially: Leave room for growth to avoid frequent expansions
2. Enable Automatic Expansion: Prevents storage-related outages
3. Monitor Storage Trends: Track usage patterns to predict future needs

Security Considerations

1. Use Private Subnets: Keep DB systems in private subnets
2. Restrict Security Lists: Limit access to known IP ranges
3. Secure Credentials: Use OCI Vault for secrets management
4. Regular Patches: Enable automatic security updates

Cost Optimization

1. Right-Size Shapes: Start with appropriate shapes and adjust based on metrics
2. Optimize Backup Retention: Balance compliance needs with storage costs
3. Use Development Templates: For non-production workloads, use lower-cost configurations
4. Monitor Resource Utilization: Use Autopilot Shape Advisor for recommendations

Conclusion

MySQL HeatWave provides a comprehensive, fully managed database service with enterprise-grade high availability features. By following this guide, you can create robust DB systems that meet demanding uptime requirements while benefiting from automatic failover, integrated analytics, and seamless scalability.

The combination of automated management, built-in high availability, and HeatWave analytics capabilities makes MySQL HeatWave an excellent choice for modern applications requiring both transactional processing and real-time analytics without the complexity of managing multiple database systems.

For the latest information, consult the official MySQL HeatWave documentation.