MySQL HeatWave: Backing Up and Restoring DB Systems

Question

MySQL HeatWave provides comprehensive backup and restore capabilities to safeguard data against accidental loss, corruption, cyberattacks, and hardware failures. This guide covers backup types, creation methods, management operations, and point-in-time recovery strategies.

Understanding Backup Types

Full Backup

A full backup backs up all data in the DB system, creating a complete snapshot at a specific point in time.

Characteristics:

• Captures all data in the DB system
• Backup size usually smaller than DB storage (unused space not backed up)
• Self-contained and can be restored independently
• Takes longer than incremental backups
• Consumes more storage space

Incremental Backup

An incremental backup backs up only data added or changed since the last backup.

Characteristics:

• First incremental backup is always a full backup
• Subsequent backups capture only changes (deltas)
• Faster to create than full backups
• Default backup type for MySQL HeatWave

Important: For data recovery, there's no functional difference between incremental and full backups. You can restore all data from any backup.

Backup Merging

When you delete a backup, it merges into the following incremental backup. The latest backup is not merged as no subsequent backup exists.

Backup Creation Types

1. Manual Backup

Created by users via OCI Console, CLI, or REST API.

Characteristics:

• Retention: 1-365 days
• Can be created on inactive DB systems
• Not automatically deleted with DB system
• Cannot be created on Always Free systems

CLI Example:

oci mysql backup create \
  --db-system-id <DB_SYSTEM_OCID> \
  --backup-type INCREMENTAL \
  --display-name "Pre-Upgrade-Backup" \
  --retention-in-days 30

2. Automatic Backup

Created automatically at scheduled time.

Characteristics:

• Retention: 1-35 days (default: 7 days)
• Created even when DB system is inactive
• Binary logs backed up every 5 minutes
• For HA systems: cannot be disabled
• Deleted with DB system (unless retention configured)

Configure via CLI:

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

3. Final Backup

Created before deleting a DB system.

Characteristics:

• Retention: 365 days (fixed)
• Acts as safety net for inadvertent deletions
• Cannot be modified after creation

4. Operator Backup

Created by MySQL Support team for troubleshooting.

Characteristics:

• Always full backups
• Deleted automatically after investigation
• Do not affect service limits
• Managed by Oracle Support

Managing Backups

Listing Backups

CLI:

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

# List for specific DB system
oci mysql backup list \
  --compartment-id <COMPARTMENT_OCID> \
  --db-system-id <DB_SYSTEM_OCID>

Updating Backups

CLI:

oci mysql backup update \
  --backup-id <BACKUP_OCID> \
  --display-name "Updated-Name" \
  --retention-in-days 20

Moving Backups

CLI:

oci mysql backup change-compartment \
  --backup-id <BACKUP_OCID> \
  --compartment-id <TARGET_COMPARTMENT_OCID>

Deleting Backups

Soft Delete: When enabled, backups are retained for additional 7 days in DELETE_SCHEDULED state before permanent deletion.

CLI:

oci mysql backup delete --backup-id <BACKUP_OCID> --force

Validating Backups

Validation restores backup to a temporary standalone DB system (invisible to customer), then immediately deletes it.

Validation States: NOT_VALIDATED, VALIDATED, NEEDS_VALIDATION, FAILED

Point-in-Time Recovery (PITR)

PITR allows restoring a DB system to a specific point in time, achieving ~5-minute RPO.

How PITR Works

1. MySQL HeatWave takes initial full backup, then incremental backups
2. Binary logs backed up every 5 minutes
3. On PITR initiation, identifies latest backup before selected point
4. Restores from backup, applies binary log changes
5. Creates new DB system with data at exact point

Benefits

PITR safeguards against:

• Accidental data loss: Dropped tables, mistaken DELETE/UPDATE
• Cyberattacks: Ransomware, SQL injection, malicious modifications
• Equipment failure: Hardware corruption, storage failures
• Human error: Incorrect batch operations, failed deployments

Enabling PITR

Requirements:

• Automatic backups must be enabled
• Prior to MySQL 8.3.0-u2, cannot enable with HeatWave Lakehouse

CLI:

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

PITR Recovery Window

Earliest restore point: When PITR enabled or first backup time (whichever is later)
Latest restore point: Current time (within last 5 minutes)

Supported Scenarios

• Standalone → Standalone or HA
• HA → Standalone or HA

Requirements for restoring to HA:

• Primary keys on every table
• MySQL 8.0.24 or higher

Restoring from Backups

Console Method

1. Navigate to DB System details → Resources → Backups
2. Select backup → Click Restore backup
3. Configure new DB system settings
4. No admin credentials requested (uses original credentials)
5. Click Restore

PITR Restore (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"
  }'

Restoration Process

1. New DB system provisioned
2. Backup restored
3. Binary logs replayed (for PITR)
4. DB system transitions to ACTIVE
5. Original DB system unchanged

Duration: Typically 15-45 minutes for moderate databases (excludes PITR replay and additional features like HA, HeatWave cluster)

Best Practices

1. Implement 3-2-1 Rule

• 3 copies of data
• 2 different storage types
• 1 off-site copy

2. Enable Automatic Backups

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

3. Create Manual Backups

• Before major deployments
• Prior to upgrades
• For long-term archival (up to 365 days)

4. Test Regularly

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

5. Use Regional Copies

• Enable for mission-critical systems
• Protects against regional disasters
• Available on OCI only

6. Monitor Backup Health

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

7. Document Procedures

• Maintain restoration runbooks
• Document PITR decision criteria
• Track retention policies

8. Optimize Costs

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

Platform-Specific Differences

MySQL HeatWave on OCI:

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

MySQL HeatWave on AWS:

• Backups are Amazon EBS snapshots saved to S3
• HeatWave cluster provisioning not supported when restoring to HA
• Long periods between backups may slow operations

Always Free Systems:

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

Troubleshooting

Backup Creation Failures

Causes: Insufficient storage, long-running transactions, connectivity issues

Solutions:

1. Check DB system is ACTIVE
2. Verify sufficient storage
3. Review error messages
4. Check long-running transactions: SHOW PROCESSLIST;
5. Retry backup creation

PITR Restore Too Long

Causes: Large binary log replay, slow storage, complex database

Solutions:

1. Choose earlier restore point
2. Use larger shape
3. Monitor work request progress
4. Consider regular backup instead

Validation Failures

Causes: Backup corruption, compatibility issues

Solutions:

1. Retry validation
2. Test restore to actual DB system
3. Create new backup and validate
4. Contact Oracle Support

Effective backup and recovery strategies are fundamental for data integrity and business continuity in MySQL HeatWave. Key takeaways:

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

MySQL HeatWave's comprehensive backup and restore capabilities ensure quick, reliable data recovery from accidental deletions, system failures, or security incidents.