Question

MySQL HeatWave: Connecting and Managing DB Systems

Introduction

MySQL HeatWave provides a fully managed database service with robust connectivity options and comprehensive management capabilities. This guide covers DB system endpoints, secure connections, and lifecycle management through the OCI console and CLI tools.

Understanding DB System Endpoints

Endpoint Components

A MySQL HeatWave DB system endpoint consists of three key elements:

1. Private IP Address

• Assigned from the VCN subnet CIDR range
• Remains constant throughout the DB system's lifetime
• Not directly accessible from the public internet
• Provides secure, isolated connectivity

2. MySQL Port Number (3306)

Port 3306 is the default port for MySQL protocol connections (used by MySQL Client and as backup by MySQL Shell). You can specify a port between 1024-65535 when creating the DB system.

3. X Protocol Port Number (33060)

Port 33060 is the default for X Protocol connections (used by MySQL Shell). Supports asynchronous operations, CRUD operations, document store functionality, and better performance for modern applications.

Platform-Specific Differences

MySQL HeatWave on OCI: Private IP addresses within VCN, requires network path through VCN/VPN/bastion

MySQL HeatWave on AWS: Public endpoint with FQDN, requires allowed client IPs in CIDR format, TLS 1.2 encryption

MySQL HeatWave for Azure: Hybrid networking between Azure VNet and OCI VCN with private connectivity

Network Path Requirements

Because DB systems use private IP addresses, you need one of these network paths:

Option 1: Same VCN

Connect from resources within the same VCN. Lowest latency, highest security, no additional networking costs.

Option 2: Same Region (Different VCN)

Use VCN Peering or Dynamic Routing Gateway (DRG) to connect VCNs in the same region.

Option 3: On-Premises Network

Site-to-Site VPN: IPSec VPN tunnel, up to 250 Mbps per tunnel
FastConnect: Dedicated physical connection, up to 10 Gbps bandwidth

Option 4: Bastion Host

Use OCI Bastion service or self-managed bastion host in public subnet for secure administrative access.

Option 5: OCI Cloud Shell

Browser-based terminal with pre-installed MySQL Shell. Free, no local setup required.

Connecting to MySQL HeatWave

Using OCI Cloud Shell

# Launch Cloud Shell from OCI Console
mysqlsh admin@10.0.1.100:3306 --sql

# Using X Protocol
mysqlsh admin@10.0.1.100:33060

# With database name
mysqlsh admin@10.0.1.100:3306/myapp_db --sql

Using Compute Instance

Install MySQL Shell (Oracle Linux/RHEL):

sudo yum install -y mysql-shell

Connect:

mysqlsh --host <DB_PRIVATE_IP> --port 3306 --user admin --password --sql

Application Connections

Python:

import mysql.connector
config = {
    'user': 'admin',
    'password': 'your_password',
    'host': '10.0.1.100',
    'port': 3306,
    'database': 'myapp_db',
    'ssl_disabled': False
}
connection = mysql.connector.connect(**config)

Node.js:

const mysql = require('mysql2');
const connection = mysql.createConnection({
  host: '10.0.1.100',
  port: 3306,
  user: 'admin',
  password: 'your_password',
  database: 'myapp_db',
  ssl: { rejectUnauthorized: true }
});

Managing DB Systems

Accessing Management Console

1. Log in to OCI Console at cloud.oracle.com
2. Navigate: Menu → Databases → HeatWave MySQL → DB Systems
3. Select compartment and click DB system name

Lifecycle Operations

Stop a DB System

Purpose: Stops compute billing (storage billing continues)

When to use: Non-production environments, cost optimization during idle periods

Impact: Connections terminated, DB unavailable, HeatWave cluster also stops and reloads data on restart

Console: DB System details → Stop → Select shutdown type (Fast/Slow/Immediate) → Confirm

CLI:

oci mysql db-system stop --db-system-id <OCID> --shutdown-type FAST

Start an Inactive DB System

Resume a stopped system. Billing resumes, HeatWave cluster automatically restarts and reloads data.

CLI:

oci mysql db-system start --db-system-id <OCID>

Startup times: Fast shutdown (2-5 min), Slow shutdown (1-3 min), Immediate shutdown (5-15 min)

Restart an Active DB System

Shuts down then restarts immediately. Brief downtime (3-10 minutes), all connections terminated, uncommitted transactions rolled back.

CLI:

oci mysql db-system restart --db-system-id <OCID> --shutdown-type FAST

Delete a DB System

Warning: Permanently deletes DB system and all data. Cannot be undone.

Before deleting: Create final backup, export critical data, check dependencies, review deletion policy

Console: DB System details → Delete → Choose backup retention → Type DB system name → Confirm

CLI:

oci mysql db-system delete --db-system-id <OCID> --force

Understanding DB System States

State Indicators

Green (Active/Available):

• ACTIVE: Fully operational, accepting connections
• UPDATING: Configuration changes in progress

Yellow (Transitional):

• CREATING: Being provisioned (15-30 minutes)
• STARTING: Booting up after stop
• STOPPING: Shutting down
• RESTARTING: Shutdown + automatic startup

Red (Failed/Error):

• FAILED: Operation failed, may require recreation
• DELETING: Deletion in progress

Gray (Inactive/Deleted):

• INACTIVE: Stopped, no compute billing
• DELETED: Permanently removed

Monitoring States

CLI:

oci mysql db-system get --db-system-id <OCID> --query 'data."lifecycle-state"'

Expected transition times: Creating (15-30 min), Stopping (2-5 min), Starting (3-10 min), Restarting (5-15 min), Deleting (10-20 min)

DB System Details Page

General Information: Name, OCID, compartment, availability domain, MySQL version, HA status

Configuration: Shape, CPU/memory, storage capacity, configuration applied, endpoints/ports

Networking: VCN/subnet, private IP, security lists/NSGs, DNS hostname

HeatWave Cluster (if attached): State, nodes, shape, memory, Lakehouse status

Backups: Policy settings, retention period, last backup time, PITR window

Security Best Practices

1. Network Security

• Deploy in private subnets only
• Security lists: Allow only ports 3306/33060 from known sources
• Use Network Security Groups for granular control

2. Encryption

• In Transit: Always use SSL/TLS, TLS 1.2+
• At Rest: Enabled by default, Oracle-managed keys or BYOK

3. Credential Management

• Store passwords in OCI Vault
• Use principle of least privilege
• Create separate accounts per application
• Enable automatic password rotation

4. Access Monitoring

• Enable MySQL Enterprise Audit
• Enable VCN flow logs
• Monitor failed login attempts
• Regular audit log reviews

Troubleshooting Common Issues

Cannot Connect - "Connection Refused"

Causes: Security list not allowing MySQL ports, DB stopped, incorrect IP/port, no network path

Solutions: Verify DB is ACTIVE, check security lists (3306/33060), confirm private IP, test connectivity

"Access Denied" Errors

Causes: Incorrect credentials, user doesn't exist, insufficient privileges

Solutions: Verify credentials, check user exists: SELECT User, Host FROM mysql.user;, grant privileges

"Too Many Connections"

Causes: Application connection leaks, max_connections limit reached

Solutions: Check connections: SHOW PROCESSLIST;, increase max_connections, fix leaks, implement connection pooling

Slow Connection Establishment

Causes: DNS resolution delays, network latency

Solutions: Use IP instead of hostname, disable reverse DNS lookups, verify VCN routing

Conclusion

Connecting to and managing MySQL HeatWave DB systems requires understanding networking concepts, endpoint configurations, and lifecycle operations. This guide covered:

• DB system endpoints and network path requirements
• Multiple connection methods (Cloud Shell, Compute Instance, applications)
• Essential lifecycle operations (stop, start, restart, delete)
• DB system states and monitoring
• Security best practices
• Common troubleshooting scenarios

Whether connecting from Cloud Shell for quick administration or setting up production connectivity, MySQL HeatWave provides flexible, secure options for all your database needs.