DEV Community

Cover image for MySQL HeatWave: Backing Up and Restoring DB Systems
Ryan Giggs
Ryan Giggs

Posted on

MySQL HeatWave: Backing Up and Restoring DB Systems

#mysqlheatwave #databasebackup #pointintimerecovery

Data protection is paramount in any database environment. MySQL HeatWave provides comprehensive backup and restore capabilities to safeguard your data against accidental loss, corruption, cyberattacks, and hardware failures. Understanding how to effectively use backups and point-in-time recovery features is essential for database administrators managing critical applications.

This guide covers backup types, creation methods, management operations, and point-in-time recovery strategies for MySQL HeatWave DB systems.

Understanding Backup Types

MySQL HeatWave supports both full and incremental backup types, each serving specific purposes in your data protection strategy.

Full Backup

A full backup backs up all data contained in the DB system. It creates a complete snapshot of your database at a specific point in time.

Characteristics:

  • Captures all data in the DB system
  • Backup size is usually smaller than DB system storage size because unused storage space does not need to be backed up
  • If all data blocks have been used, backup size may be slightly larger due to metadata
  • Self-contained and can be restored independently
  • Takes longer to complete than incremental backups
  • Consumes more storage space

When to Use:

  • Initial backup of a new DB system
  • Before major database changes or upgrades
  • Compliance requirements for complete snapshots
  • When starting a new backup strategy

Incremental Backup

An incremental backup backs up only the data that was added or changed since the last full or incremental backup. This approach optimizes backup time and storage utilization.

Characteristics:

  • The first incremental backup is always a full backup
  • Subsequent backups capture only changes (deltas)
  • Faster to create than full backups
  • Requires less storage space
  • Default backup type for MySQL HeatWave

Important Note: For data recovery purposes, there is no functional difference between an incremental backup and a full backup. You can restore all data from any of your incremental or full backups.

Backup Merging Behavior

MySQL HeatWave implements intelligent backup management:

When you delete an incremental or full backup, it is merged into the following incremental backup. When the latest incremental or full backup is deleted, it is not merged as there is no subsequent incremental backup available.

Example:

  • Day 1: Full backup created (100 GB)
  • Day 2: Incremental backup created (20 GB changes)
  • Day 3: Incremental backup created (15 GB changes)

If you delete the Day 1 full backup:

  • It merges into the Day 2 backup
  • Day 2 backup now contains full data (120 GB)
  • Day 3 remains incremental (15 GB)

If you delete the Day 3 backup:

  • It is not merged (no subsequent backup)
  • Simply removed from storage

Backup Creation Types

MySQL HeatWave offers four distinct backup creation types to meet different operational needs.

1. Manual Backup

Manual backups are created by users via the OCI Console, CLI, or REST API.

Characteristics:

  • Retention period: minimum 1 day, maximum 365 days
  • Can be created on inactive (stopped) DB systems
  • Can be either full or incremental backup
  • Not automatically deleted with DB system
  • Cannot be created on Always Free DB systems

When to Use:

  • Before major application deployments
  • Prior to database upgrades or migrations
  • For long-term archival purposes
  • Creating backup before risky operations
  • Compliance and audit requirements

Creating a Manual Backup (Console):

  1. Navigate to DB System details page
  2. Under Resources, click Backups
  3. Click Create manual backup
  4. Configure backup settings:
    • Display name: Descriptive name for the backup
    • Description: Purpose or context of the backup
    • Backup type: Select FULL or INCREMENTAL (default)
    • Retention period: 1-365 days
    • Soft delete: Enable to retain backup for 7 additional days after deletion
  5. Click Create manual backup

Using OCI CLI:

oci mysql backup create \
  --db-system-id <DB_SYSTEM_OCID> \
  --backup-type INCREMENTAL \
  --display-name "Pre-Upgrade-Backup" \
  --description "Backup before MySQL 8.4 upgrade" \
  --retention-in-days 30 \
  --soft-delete ENABLED
Enter fullscreen mode Exit fullscreen mode

2. Automatic Backup

Automatic backups are created automatically at a time selected while creating the DB system.

Characteristics:

  • Retention period: 1-35 days (default: 7 days)
  • Automatic backups are created even when DB system is inactive
  • First backup is always full, subsequent backups are incremental
  • Deleted when DB system is deleted unless retention policy is configured
  • For Always Free systems: retention is fixed at 1 day
  • For HA systems: automatic backups are enabled by default and cannot be disabled

Backup Schedule:

  • Runs daily at configured backup window
  • Binary log files are rotated and backed up every five minutes
  • Minimal performance impact during backup window

Configuring Automatic Backups:

When creating a DB system:

  1. In the Configure backup section
  2. Enable Automatic backups
  3. Set Retention period (1-35 days)
  4. Configure Backup window (select start time)
  5. Enable Point-in-time recovery (optional but recommended)

Modifying Backup Policy (CLI):

oci mysql db-system update \
  --db-system-id <DB_SYSTEM_OCID> \
  --backup-policy '{
    "isEnabled": true,
    "retentionInDays": 14,
    "windowStartTime": "02:00",
    "pitrPolicy": {"isEnabled": true}
  }'
Enter fullscreen mode Exit fullscreen mode

3. Final Backup

Final backups are created immediately before deleting a DB system if you select the Create final backup option.

Characteristics:

  • Created automatically during DB system deletion
  • Retention period: 365 days (fixed)
  • Acts as safety net for inadvertent deletions
  • Cannot be modified after creation
  • Useful for compliance and disaster recovery

When to Use:

  • Decommissioning production systems
  • Temporary system removal with potential restore need
  • Meeting audit requirements for data retention
  • Insurance against accidental deletion

Creating Final Backup During Deletion:

  1. Navigate to DB System details page
  2. Click Delete
  3. In deletion dialog:
    • Select Create final backup option
    • Backup will be created with 365-day retention
    • Provide backup name (or use auto-generated)
  4. Confirm deletion

4. Operator Backup

Operator backups are created by the MySQL Support team to assist in investigating potential issues with your service.

Characteristics:

  • Deleted automatically after investigation
  • Always full backups (not incremental)
  • Do not affect your service limits
  • You can delete them, but it's not recommended
  • Used for troubleshooting and support

Important: Operator backups are service-initiated and not under direct user control. They appear in your backup list but are managed by Oracle Support.

Managing Backups

Once backups are created, you can perform various management operations to organize and maintain them effectively.

Listing Backups

Using Console:

  1. Navigate to DB System details page
  2. Under Resources, click Backups
  3. View all backups with details:
    • Backup name
    • Creation type (MANUAL, AUTOMATIC, FINAL, OPERATOR)
    • Backup type (FULL, INCREMENTAL)
    • Creation date/time
    • Retention period
    • State (ACTIVE, CREATING, DELETING, DELETED)

Using OCI CLI:

# List all backups for a compartment
oci mysql backup list --compartment-id <COMPARTMENT_OCID>

# List backups for a specific DB system
oci mysql backup list \
  --compartment-id <COMPARTMENT_OCID> \
  --db-system-id <DB_SYSTEM_OCID>
Enter fullscreen mode Exit fullscreen mode

Updating Backup Details

You can modify certain backup attributes after creation.

Using Console:

  1. Navigate to Backup details page
  2. Click Edit
  3. Update:
    • Display name
    • Description
    • Retention period (within allowed range)
  4. Click Save changes

Using OCI CLI:

oci mysql backup update \
  --backup-id <BACKUP_OCID> \
  --display-name "Updated-Backup-Name" \
  --description "Updated description" \
  --retention-in-days 20
Enter fullscreen mode Exit fullscreen mode

Moving Backups Between Compartments

Backups can be moved to different compartments for organizational purposes.

Using Console:

  1. Navigate to Backup details page
  2. Click Move resource
  3. Select target compartment
  4. Click Move resource

Using OCI CLI:

oci mysql backup change-compartment \
  --backup-id <BACKUP_OCID> \
  --compartment-id <TARGET_COMPARTMENT_OCID>
Enter fullscreen mode Exit fullscreen mode

Copying Backups to Another Region

For disaster recovery, you can copy backups to different OCI regions.

Regional Backup Copy:

  • Schedule automatic backup copies to another region
  • Provides geographic redundancy
  • Protects against regional disasters
  • Available on OCI (not AWS or Azure)

Using Console:

  1. Navigate to DB System details page
  2. Under Backup policy, enable Regional backup copy
  3. Select target region
  4. Configure schedule

Using CLI:

oci mysql backup copy \
  --backup-id <BACKUP_OCID> \
  --source-region <SOURCE_REGION> \
  --destination-region <DEST_REGION>
Enter fullscreen mode Exit fullscreen mode

Deleting Backups

Remove backups that are no longer needed to optimize storage costs.

Soft Delete Feature:
When a backup with soft delete enabled is due for deletion after its retention period, it is retained for additional 7 days in the DELETE_SCHEDULED state before it is permanently deleted.

Using Console:

  1. Navigate to Backup details page
  2. Click Delete
  3. Confirm deletion
  4. If soft delete enabled, backup enters DELETE_SCHEDULED state for 7 days
  5. After 7 days, permanently deleted

Using OCI CLI:

oci mysql backup delete \
  --backup-id <BACKUP_OCID> \
  --force
Enter fullscreen mode Exit fullscreen mode

Important:

  • Automatic backups are deleted when DB system is deleted (unless retention configured)
  • Manual backups persist after DB system deletion
  • Operator backups are managed by Oracle Support
  • Deleting a backup merges it into the next backup (if exists)

Validating Backups

Backup validation ensures backups can be successfully restored.

Validation Process:
The validation process restores the backup to a standalone DB system and the DB system is deleted immediately at the end of the validation process. This temporary standalone DB system is not visible to customer and cannot accept client connections.

Validation States:

  • NOT_VALIDATED: Backup has not been validated
  • VALIDATED: Backup validated successfully
  • NEEDS_VALIDATION: Last validation encountered transient errors, retry needed
  • FAILED: Backup failed validation

Why Validate:

  • Verify backup integrity
  • Ensure successful restoration capability
  • Meet compliance requirements
  • Proactive disaster recovery testing

Point-in-Time Recovery (PITR)

Point-in-Time Recovery (PITR) allows you to restore a DB system to a specific point in time, achieving a Recovery Point Objective (RPO) of approximately five minutes.

What is PITR?

MySQL HeatWave enables you to restore a DB system to a precise point before a mistake or error occurs, not just to the time of the last backup.

How PITR Works:

  1. MySQL HeatWave takes an initial full backup, then subsequent incremental backups
  2. Binary log files are rotated and backed up every five minutes
  3. When you initiate PITR, MySQL HeatWave identifies the latest backup before the selected point-in-time
  4. System restores from that backup
  5. Applies binary log changes up to the specific moment
  6. Creates new DB system with data at that exact point

Benefits of Point-in-Time Recovery

PITR safeguards against:

  1. Accidental Data Loss

    • Accidentally dropped tables or databases
    • Mistaken DELETE or UPDATE statements
    • Schema changes that corrupt data

  2. Cyberattacks or Malware

    • Ransomware encryption
    • SQL injection attacks
    • Malicious data modifications

  3. Equipment Failure

    • Hardware corruption
    • Storage failures
    • System crashes that corrupt data

  4. Human Error

    • Incorrect batch operations
    • Failed application deployments
    • Configuration mistakes

Enabling Point-in-Time Recovery

Requirements:

  • Operations such as stop, start, restart, or upgrade do not impact PITR operation
  • Enabling or disabling PITR does not impact the DB system
  • Prior to MySQL 8.3.0-u2, you cannot enable PITR on a DB system with MySQL HeatWave Lakehouse enabled

When Creating DB System:

  1. In the Configure backup section
  2. Enable Automatic backups
  3. Enable Point-in-time recovery checkbox
  4. PITR will be active once first backup completes

On Existing DB System (Console):

  1. Navigate to DB System details page
  2. Click Edit
  3. Under Backup policy, enable Point-in-time recovery
  4. Click Save changes

Using CLI:

oci mysql db-system update \
  --db-system-id <DB_SYSTEM_OCID> \
  --backup-policy '{
    "isEnabled": true,
    "retentionInDays": 14,
    "pitrPolicy": {"isEnabled": true}
  }'
Enter fullscreen mode Exit fullscreen mode

PITR Recovery Window

You can restore to any specific point-in-time within the earliest and latest time window. The earliest available time depends on the backup retention period.

Example:

  • Backup retention period: 12 days
  • PITR enabled: January 1
  • First backup: January 1 at 2:00 AM
  • Earliest restore point: January 1 at 2:00 AM (or when PITR enabled, whichever is later)
  • Latest restore point: Current time (within last 5 minutes)

Important Considerations:

  • When a DB system is deleted, automatic backups are retained if retain automatic backups is enabled. Starting from Sept 25, 2024, deleting a DB system that retains automatic backups also retains the backed-up binary log files
  • RPO is approximately 5 minutes (binary log backup frequency)
  • RTO depends on backup size and amount of log replay needed

Supported PITR Scenarios

MySQL HeatWave Service supports point-in-time recovery of standalone or high availability DB systems to a new DB system.

Restore Possibilities:

  • Standalone → Standalone DB system
  • Standalone → High Availability DB system
  • High Availability → Standalone DB system
  • High Availability → High Availability DB system

Requirements for Restoring to HA:

  • Primary keys exist on every table in the DB system
  • MySQL version is 8.0.24 or higher

Restoring from Backups

You can restore backups to create new DB systems in the same tenancy.

Restoring from a Backup (Console)

  1. Navigate to DB System details page
  2. Under Resources, click Backups
  3. Find the backup to restore
  4. Click Restore backup
  5. In the Restore backup to new DB system dialog:
    • Data source: Backup ID displayed
    • Display name: Name for new DB system
    • Description: Purpose description
  6. Configure system settings:
    • Admin credentials: No administrator username and password are requested because the restored DB system uses the same credentials that were in effect when the backup was created
    • Compartment: Where to create new system
    • Shape: Select compute shape
    • Storage: Configure storage capacity
    • Networking: Select VCN and subnet
    • High Availability: Enable if desired (subject to requirements)
  7. Configure optional features:
    • HeatWave cluster: Provisioning a MySQL HeatWave Cluster is not supported when restoring a backup to a high availability DB System on AWS
    • Backup policy: Configure new backup settings
    • Configuration: Select MySQL configuration
  8. Click Restore

Restoring Using Point-in-Time Recovery

Using Console:

  1. Navigate to DB System details page
  2. Click Actions → Restore to point-in-time
  3. Select restore option:
    • Latest available time: Most recent recovery point
    • Select a specific point-in-time: Choose exact timestamp
  4. The earliest and latest time window is displayed under the Select a specific point-in-time option
  5. Configure new DB system settings (same as backup restore)
  6. Click Restore

Using OCI CLI:

oci mysql db-system create-from-backup \
  --admin-username admin \
  --admin-password <PASSWORD> \
  --compartment-id <COMPARTMENT_OCID> \
  --shape-name MySQL.8 \
  --subnet-id <SUBNET_OCID> \
  --display-name "PITR-Restored-System" \
  --source-type PITR \
  --source-backup-id <BACKUP_OCID> \
  --point-in-time-recovery-details '{
    "pointInTime": "2024-11-15T14:30:00.000Z"
  }'
Enter fullscreen mode Exit fullscreen mode

Restoration Process

What Happens During Restore:

  1. New DB system is provisioned with specified configuration
  2. Latest backup before selected point-in-time is restored
  3. Binary logs are replayed up to the exact point-in-time
  4. DB system transitions to ACTIVE state
  5. Original DB system remains unchanged

Restoration Time:

  • Depends on backup size
  • Amount of binary log replay needed
  • Chosen shape and storage performance
  • Additional features (HA, HeatWave cluster)
  • Typically 15-45 minutes for moderate-sized databases

This estimate excludes additional time required to perform point-in-time recovery and set up additional features such as version upgrade, high availability, HeatWave cluster, REST, read endpoint, and Database Management

Important Restoration Notes

Credentials:
When you create a new DB system from an existing DB system at a point-in-time, it retains the administrator credentials of the original DB system

Original System:

  • Source DB system is not affected by restoration
  • Continues operating normally
  • Can be kept or deleted independently

Network Configuration:

  • Restored system can be in different VCN/subnet
  • Different compartment allowed
  • Must meet standard networking requirements

Best Practices for Backup and Recovery

1. Implement the 3-2-1 Backup Rule

  • 3 copies of data (production + 2 backups)
  • 2 different storage types (local + remote)
  • 1 off-site copy (different region)

2. Enable Automatic Backups

  • Always enable for production systems
  • Set retention period based on requirements (7-35 days)
  • Schedule backup window during low-traffic periods
  • Enable PITR for critical systems

3. Create Manual Backups for Major Changes

  • Before application deployments
  • Prior to database upgrades
  • Before schema modifications
  • For long-term archival (up to 365 days)

4. Test Backup Restores Regularly

  • Validate backup integrity
  • Practice restoration procedures
  • Verify RTO and RPO metrics
  • Train operations team

5. Use Regional Backup Copies

  • Enable for mission-critical systems
  • Protects against regional disasters
  • Meets geographic redundancy requirements
  • Consider compliance needs

6. Monitor Backup Health

  • Set up alerts for backup failures
  • Monitor backup size trends
  • Track backup duration
  • Review backup logs regularly

7. Document Recovery Procedures

  • Maintain runbooks for restoration
  • Document PITR decision criteria
  • Track backup retention policies
  • Record tested recovery scenarios

8. Optimize Backup Costs

  • Right-size retention periods
  • Use incremental backups (default)
  • Clean up obsolete manual backups
  • Leverage soft delete for safety net

Backup Limitations and Considerations

Always Free DB Systems

  • Cannot create manual backups
  • Automatic backup retention fixed at 1 day
  • PITR not supported
  • Limited backup capabilities

Platform-Specific Differences

MySQL HeatWave on OCI:

  • Full backup and PITR support
  • Regional backup copies available
  • All backup types supported

MySQL HeatWave on AWS:

  • DB System backups are Amazon EBS snapshots automatically saved to Amazon S3
  • HeatWave cluster provisioning not supported when restoring backup to HA system
  • Backups are incremental, but if the period between backups is too long, a backup operation may take a long time to complete due to the volume of changes

High Availability Systems

  • Automatic backups cannot be disabled
  • Backups taken from primary instance
  • PITR supported (MySQL 8.0.24+)
  • Primary keys required for restoration to HA

Troubleshooting Backup and Restore Issues

Backup Creation Failures

Symptoms: Backup stuck in CREATING state or transitions to FAILED

Common Causes:

  • Insufficient storage space
  • Long-running transactions blocking backup
  • Database connectivity issues
  • Resource constraints

Solutions:

  1. Check DB system state is ACTIVE
  2. Verify sufficient storage available
  3. Review error messages in backup details
  4. Check for long-running transactions: SHOW PROCESSLIST;
  5. Retry backup creation
  6. Contact Oracle Support if persistent

PITR Restore Taking Too Long

Symptoms: Restoration exceeds expected duration

Common Causes:

  • Large amount of binary log replay
  • Slow storage performance
  • Long-running transactions at restore point
  • Complex database with many tables

Solutions:

  1. Choose earlier restore point with less log replay
  2. Use larger shape for faster restore
  3. Be patient – complex restores take time
  4. Monitor work request progress
  5. Consider restoring from regular backup instead

Restore Validation Failures

Symptoms: Backup validation shows FAILED status

Possible Causes:

  • Backup corruption
  • Compatibility issues
  • Storage problems

Solutions:

  1. Retry validation (may be transient error)
  2. Test restore to actual DB system
  3. Create new backup and validate
  4. Contact Oracle Support with validation details

Effective backup and recovery strategies are fundamental to maintaining data integrity and business continuity in MySQL HeatWave. By understanding the different backup types, leveraging point-in-time recovery, and following best practices, you can protect your critical data against various failure scenarios.

Key takeaways:

  • Use both backup types: Full and incremental backups work together seamlessly
  • Enable automatic backups: Essential for all production systems
  • Leverage PITR: Provides granular recovery with ~5-minute RPO
  • Test regularly: Validate backups and practice restoration procedures
  • Plan for disasters: Use regional copies and maintain off-site backups
  • Monitor and maintain: Track backup health and optimize retention policies

Whether dealing with accidental deletions, system failures, or security incidents, MySQL HeatWave's comprehensive backup and restore capabilities ensure you can recover your data quickly and reliably.

Additional Resources:

Top comments (0)