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
- Log in to OCI Console at cloud.oracle.com
- Navigate: Menu → Databases → HeatWave MySQL → DB Systems
- 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.