MySQL HeatWave provides a fully managed database service with robust connectivity options and comprehensive management capabilities. Understanding how to properly connect to your DB system and perform essential management operations is fundamental for database administrators and developers working with MySQL HeatWave.

This guide covers everything from understanding DB system endpoints and establishing secure connections to managing the lifecycle of your DB system through the OCI console and command-line tools.

Understanding DB System Endpoints

What is a DB System Endpoint?

The database system endpoint is where clients such as MySQL Shell, MySQL Client, MySQL Workbench, and your applications can connect to MySQL HeatWave. The endpoint serves as the connection point for all database operations.

Endpoint Components

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

1. Private IP Address

The endpoint comprises private IP addresses and port numbers that accept client connections, providing secure, private connectivity to your database instance. This private IP address is assigned from the subnet where the DB system resides.

Key Characteristics:

• 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 to a database system (as used by the MySQL Client, and as a backup by MySQL Shell). You can specify a port number between 1024 and 65535 when creating the DB system.

Usage:

• Classic MySQL protocol connections
• Standard MySQL client connections
• Legacy application compatibility
• Default connection method for most tools

3. X Protocol Port Number (33060)

Port 33060 is the default port for X Protocol connections to a database system (as used by MySQL Shell). The X Protocol is a modern, high-performance protocol that supports:

Advantages of X Protocol:

• Asynchronous operations
• CRUD (Create, Read, Update, Delete) operations
• Document store functionality
• Better performance for modern applications
• Support for NoSQL-style operations

Platform-Specific Endpoint Differences

MySQL HeatWave on OCI:

• Uses private IP addresses within VCN
• Requires network path through VCN, VPN, or bastion
• Supports both MySQL protocol (3306) and X Protocol (33060)

MySQL HeatWave on AWS:

• For MySQL client connections to the DB System, a public endpoint is exposed as a fully qualified domain name (the host name of the DB System)
• Public endpoint with FQDN (Fully Qualified Domain Name)
• Requires allowed client IP addresses in CIDR format
• Connections encrypted via TLS 1.2

MySQL HeatWave for Azure (ODSA):

• The virtual network for a MySQL HeatWave deployment includes an Azure VNet, an Oracle Cloud Infrastructure (OCI) VCN, and the connections between the two
• Hybrid networking between Azure VNet and OCI VCN
• Private connectivity through interconnect
• Seamless cross-cloud integration

Network Path Requirements for Connectivity

Because the database system endpoint has a private IP address, you cannot connect directly to it from a remote IP address. To connect to a MySQL HeatWave DB system, you need one of the following network paths:

Option 1: Same Virtual Cloud Network (VCN)

Description: Connect from a resource within the same VCN as the DB system.

Use Cases:

• Application servers in the same VCN
• Compute instances for database administration
• Development and testing environments
• Microservices architecture

Setup Requirements:

• Compute instance or application server in same VCN
• Security list or NSG allowing traffic on ports 3306 and 33060
• Proper IAM policies for resource access

Advantages:

• Lowest latency
• Highest security (no internet exposure)
• No additional networking costs
• Simplest configuration

Option 2: Same OCI Region (Different VCN)

Description: Connect from a different VCN within the same OCI region.

Implementation Methods:

VCN Peering:

• Local VCN Peering for VCNs in same region
• Requires peering configuration
• Direct, private connectivity
• No bandwidth limitations

Dynamic Routing Gateway (DRG):

• Hub-and-spoke topology
• Connects multiple VCNs
• Centralized routing
• Scalable for complex architectures

Setup Requirements:

• Configure VCN peering or DRG
• Update route tables in both VCNs
• Security lists allowing MySQL ports
• Proper CIDR planning to avoid overlaps

Option 3: On-Premises Network

Description: Connect from your on-premises data center to the DB system.

Implementation Methods:

Site-to-Site VPN:

• IPSec VPN tunnel over the internet
• Encrypted connectivity
• Cost-effective solution
• Up to 250 Mbps per tunnel
• Suitable for moderate bandwidth needs

FastConnect (Dedicated Private Connection):

• Dedicated physical connection
• Up to 10 Gbps bandwidth
• Predictable network performance
• Lower latency than VPN
• Higher cost but better for production workloads

Setup Requirements:

• Customer-Premises Equipment (CPE) configuration
• VPN or FastConnect setup
• Route table updates
• Security list configuration
• Network Address Translation (NAT) if needed

Option 4: Remote Access via Bastion Host

Description: Use OCI Bastion service or bastion host for secure administrative access.

OCI Bastion Service:

• Managed bastion service
• Time-limited session access
• No need to maintain bastion instances
• Audit trail of access
• Integration with IAM

Self-Managed Bastion Host:

• Compute instance with public IP
• Acts as jump server
• Install MySQL clients on bastion
• SSH tunneling for connections
• Full control over configuration

Setup Steps:

1. Create bastion host in public subnet
2. Configure security lists for SSH (22) and MySQL (3306, 33060)
3. SSH into bastion host
4. From bastion, connect to DB system using private IP
5. Implement proper access controls and monitoring

Option 5: OCI Cloud Shell

Description: Use the built-in browser-based terminal with pre-installed tools.

Advantages:

• No local setup required
• Pre-installed MySQL Shell
• Automatic authentication
• Quick testing and administration
• Free to use

How to Use:

1. Log in to OCI Console
2. Click the Cloud Shell icon (terminal icon in top navigation)
3. Cloud Shell launches in browser
4. Use MySQL Shell to connect to DB system

Example Connection:

mysqlsh admin@<PRIVATE_IP>:3306 --sql

Connecting to MySQL HeatWave DB System

Method 1: Using OCI Cloud Shell

Step-by-Step Process:

Launch Cloud Shell

• Log in to OCI Console
• Click the Cloud Shell icon in the top-right corner
• Wait for the shell to initialize (typically 10-20 seconds)

Verify MySQL Shell Installation

mysqlsh --version

1. Connect Using MySQL Shell

mysqlsh admin@10.0.1.100:3306 --sql

Replace:

• admin with your DB system administrator username
• 10.0.1.100 with your DB system’s private IP address

Enter Password

• When prompted, enter the administrator password you set during DB system creation

Verify Connection

SELECT USER(), DATABASE();
SHOW DATABASES;

Alternative Connection Formats:

Using X Protocol (recommended for modern applications):

mysqlsh admin@10.0.1.100:33060

With Database Name:

mysqlsh admin@10.0.1.100:3306/myapp_db --sql

URI Format:

mysqlsh mysqlx://admin@10.0.1.100:33060

Method 2: Using a Compute Instance

Prerequisites:

• Compute instance in same VCN or with network connectivity to DB system VCN
• MySQL Shell or MySQL Client installed

Installation Steps:

For Oracle Linux/CentOS/RHEL:

# Install MySQL Shell
sudo yum install -y mysql-shell

# Or install MySQL Client
sudo yum install -y mysql

For Ubuntu/Debian:

# Install MySQL Shell
wget https://dev.mysql.com/get/Downloads/MySQL-Shell/mysql-shell-<version>-linux-glibc2.12-x86-64bit.tar.gz
tar -xvf mysql-shell-<version>-linux-glibc2.12-x86-64bit.tar.gz
sudo mv mysql-shell-<version>-linux-glibc2.12-x86-64bit /usr/local/mysql-shell
export PATH=/usr/local/mysql-shell/bin:$PATH

# Or use APT for MySQL Client
sudo apt update
sudo apt install -y mysql-client

Connection from Compute Instance:

Using MySQL Shell:

mysqlsh --host <DB_SYSTEM_PRIVATE_IP> \
--port 3306 \
--user admin \
--password \
--sql

Using MySQL Client:

mysql -h <DB_SYSTEM_PRIVATE_IP> \
-P 3306 \
-u admin \
-p

Method 3: Using MySQL Workbench

Setup Requirements:

• MySQL Workbench installed locally
• Network connectivity (VPN, bastion, or local network with VCN access)
• DB system endpoint information

Connection Steps:

1. Open MySQL Workbench
2. Click “+” to add new connection

Configure Connection:

• Connection Name: Give it a descriptive name (e.g., “Production HeatWave DB”)
• Hostname: Enter DB system private IP address
• Port: 3306 (or custom port if specified)
• Username: DB system administrator username
• Password: Click “Store in Vault” to save password securely

Test Connection

• Click “Test Connection” button
• Enter password if not stored
• Verify successful connection

Configure SSL/TLS (recommended):

• Go to SSL tab
• Use SSL if available: Required
• Download and specify CA certificate

Method 4: Application Connections

JDBC Connection String (Java applications):

String url = "jdbc:mysql://10.0.1.100:3306/myapp_db?" +
"useSSL=true&" +
"requireSSL=true&" +
"serverTimezone=UTC";

Connection conn = DriverManager.getConnection(url, "admin", "password");

Python (using mysql-connector-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 (using mysql2):

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
}
});

PHP (using PDO):

<?php
$dsn = 'mysql:host=10.0.1.100;port=3306;dbname=myapp_db;charset=utf8mb4';
$options = [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_EMULATE_PREPARES => false,
];

$pdo = new PDO($dsn, 'admin', 'your_password', $options);
?>

Managing a DB System

Once your DB system is created, you can perform various management operations through the OCI Console, OCI CLI, or REST API.

Accessing DB System Management

Via OCI Console:

Log in to OCI Console

• Navigate to cloud.oracle.com
• Sign in with your credentials

Navigate to DB Systems Page

• Click the hamburger menu (☰) in the top-left corner
• Under Databases, select HeatWave MySQL
• Click DB Systems

Select Your DB System

• Choose your compartment from the List Scope dropdown
• Find your DB system in the list
• Click the DB system name to open the details page

DB System Lifecycle Operations

You can perform the following operations on a DB system after it has been created:

1. Stop a DB System

Purpose: Stopping a DB System with the Stop button stops billing for it. However, billing continues for storage.

When to Stop:

• Non-production environments after hours
• Development systems during weekends
• Temporary systems no longer needed immediately
• Cost optimization during idle periods

Impact:

• All active connections are terminated
• DB system becomes unavailable
• Storage costs continue
• Compute costs stop
• Start, stop, or restart operations on a DB System also affect an associated MySQL HeatWave Cluster. When a DB System restarts, the MySQL HeatWave Cluster also restarts, and it reloads automatically the previously-loaded data from the storage layer

How to Stop (Console):

1. Navigate to DB System details page
2. Click the Stop button
3. Select shutdown type:

• Fast: Flush dirty pages before shutdown (recommended)
• Slow: Flush dirty pages and purge undo logs (slower shutdown, faster startup)
• Immediate: Stop immediately without flushing (fastest shutdown, slower startup)

1. Click Stop to confirm

Using OCI CLI:

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

2. Start an Inactive DB System

Purpose: Resume a stopped DB system to make it available again.

Process:

• Billing for the DB System resumes when the DB System starts again
• Startup time depends on shutdown type used
• All data remains intact
• HeatWave cluster (if attached) automatically restarts and reloads data

How to Start (Console):

1. Navigate to DB System details page
2. Click the Start button (enabled only when DB system is stopped)
3. Wait for DB system to transition from INACTIVE to ACTIVE

Using OCI CLI:

oci mysql db-system start \
--db-system-id <DB_SYSTEM_OCID>

Startup Duration:

• Fast shutdown: 2-5 minutes typical startup
• Slow shutdown: 1-3 minutes typical startup
• Immediate shutdown: 5-15 minutes typical startup (longest due to crash recovery)

3. Restart an Active DB System

Purpose: Restarting a DB System with the Restart button shuts down the DB System, and then restarts it immediately.

When to Restart:

• After configuration changes requiring restart
• To apply certain parameter changes
• Troubleshooting connection or performance issues
• Routine maintenance procedures

Impact:

• Brief downtime (typically 3-10 minutes)
• All active connections terminated
• Uncommitted transactions rolled back
• After any planned or unplanned restart, data is recovered into the associated MySQL HeatWave Cluster from the storage layer

How to Restart (Console):

1. Navigate to DB System details page
2. Click the Restart button
3. Select shutdown type (Fast, Slow, or Immediate)
4. Click Restart to confirm

Using OCI CLI:

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

4. Delete a DB System

Purpose: Permanently remove a DB system and all associated resources.

Critical Warning: Deleting a DB system permanently deletes it, along with all of the data in the database. This operation cannot be undone.

Before Deleting:

Create Final Backup:

• Ensure you have recent backups
• Consider creating a manual backup immediately before deletion
• Verify backup integrity

Export Important Data:

• Use MySQL dump utilities for critical data
• Export to Object Storage or download locally
• Document any custom configurations

Check Dependencies:

• Verify no applications are actively using the DB system
• Check for any automated processes connecting to the database
• Review read replicas (if any)

Review Deletion Policy:

• Configure whether to retain or delete automatic backups
• Decide on final backup creation

How to Delete (Console):

1. Navigate to DB System details page
2. Click the Delete button or select Delete from Actions menu
3. In the confirmation dialog:

• Review the warning message

• Choose backup retention option:

• Delete all backups: Removes DB system and all backups

• Retain automatic backups: Keeps backups per retention policy

• Create final backup: Takes one last backup before deletion

1. Type the DB system name to confirm
2. Click Delete DB System

Using OCI CLI:

oci mysql db-system delete \
--db-system-id <DB_SYSTEM_OCID> \
--force

What Gets Deleted:

• The DB system and all three instances (for HA systems)
• All data and schemas
• Attached HeatWave cluster (if any)
• Endpoints and network configurations
• Optionally: automatic backups (based on policy)

What Remains:

• Manual backups (until you delete them separately)
• Automatic backups (if retention policy selected)
• VCN and network resources (not automatically deleted)
• IAM policies and configurations

Understanding DB System States

The DB system details page displays the current state of your DB system using color-coded indicators that make it easy to understand the system’s status at a glance.

State Indicators and Colors

Green (Active/Available States)

ACTIVE:

• DB system is fully operational and accepting connections
• All services running normally
• Can perform queries and transactions
• HeatWave cluster (if attached) is available

UPDATING:

• Configuration changes being applied
• Shape changes in progress
• May have temporary reduced availability
• Do not perform additional operations until complete

Yellow (Transitional States)

CREATING:

• DB system is being provisioned
• Initial setup in progress
• Typically takes 15-30 minutes
• Cannot connect until state changes to ACTIVE

STARTING:

• DB system is booting up after being stopped
• Services initializing
• Will transition to ACTIVE when complete
• Duration depends on previous shutdown type

STOPPING:

• DB system is shutting down
• Flushing data to storage
• Connections being terminated
• Will transition to INACTIVE when complete

RESTARTING:

• DB system is restarting
• Shutdown followed by automatic startup
• Brief service interruption
• Will return to ACTIVE when complete

Red (Failed/Error States)

FAILED:

• DB system creation or operation failed
• System may not be usable
• Check error messages in console
• May require deletion and recreation
• Contact Oracle Support if persistent

DELETING:

• DB system deletion in progress
• Resources being removed
• Cannot be stopped or reversed
• Will transition to DELETED when complete

Gray (Inactive/Deleted States)

INACTIVE:

• DB system is stopped
• No compute billing
• Storage billing continues
• Can be started again

DELETED:

• DB system has been permanently removed
• Only appears briefly before being removed from list
• Cannot be recovered (unless backups exist)
• All data and configurations removed

Monitoring State Transitions

In the Console:

• States display in the DB systems list
• Color-coded icons for quick identification
• Refresh page to see current state
• Details page shows state history

Using OCI CLI:

# Get current state
oci mysql db-system get \
--db-system-id <DB_SYSTEM_OCID> \
--query 'data."lifecycle-state"'

# Monitor for state change
while true; do
oci mysql db-system get \
--db-system-id <DB_SYSTEM_OCID> \
--query 'data."lifecycle-state"'
sleep 30
done

Expected State Transition Times:

• CREATING → ACTIVE: 15-30 minutes
• STOPPING → INACTIVE: 2-5 minutes
• INACTIVE → STARTING → ACTIVE: 3-10 minutes
• RESTARTING: 5-15 minutes total
• DELETING → DELETED: 10-20 minutes

Additional Management Operations

Viewing DB System Details

The DB system details page provides comprehensive information about your system:

General Information:

• DB system name and OCID
• Compartment and availability domain
• MySQL version
• High availability status
• Current and preferred placement (for HA systems)

Configuration Details:

• Shape and CPU/memory allocation
• Storage capacity and utilization
• Configuration applied
• Endpoints and ports

Networking Information:

• VCN and subnet assignments
• Private IP address
• Security lists and NSGs
• DNS hostname (if configured)

HeatWave Cluster (if attached):

• Cluster state
• Number of nodes
• Node shape
• Memory per node
• Lakehouse status

Backups:

• Backup policy settings
• Retention period
• Last backup time
• Point-in-time recovery window

Editing DB System Configuration

You can modify certain DB system properties after creation:

Modifiable Attributes:

• Display name and description
• Shape (compute resources)
• Storage capacity (can only increase)
• Configuration
• Backup settings
• Maintenance window
• Tags

Non-Modifiable Attributes:

• MySQL version (requires upgrade operation)
• VCN and subnet
• Availability domain (for standalone systems)
• Initial storage size (can only expand)

To Edit (Console):

1. Navigate to DB System details page
2. Click Edit button
3. Modify desired settings
4. Click Save changes

Monitoring and Metrics

Built-in Monitoring:

• CPU utilization
• Memory usage
• Storage consumption
• Active connections
• Query throughput
• Network I/O

Setting Up Alerts:

1. Navigate to Monitoring service
2. Create alarm definitions
3. Set thresholds for key metrics
4. Configure notification topics
5. Add email or webhook destinations

Security Best Practices for Connections

1. Use Private Subnets

Always deploy DB systems in private subnets, never public subnets. This ensures:

• No direct internet access to database
• Protection through multiple network layers
• Compliance with security standards
• Reduced attack surface

2. Implement Network Security

Security Lists:

• Allow only necessary ports (3306, 33060)
• Restrict source CIDR blocks to known application servers
• Use separate security lists for different tiers
• Regular audit of rules

Network Security Groups (NSGs):

• More granular than security lists
• Can be applied to specific resources
• Support stateful inspection
• Easier to manage complex rules

3. Enable Encryption

Data in Transit:

• Always use SSL/TLS connections
• Verify server certificates
• Use TLS 1.2 or higher
• Disable insecure protocols

Data at Rest:

• Enabled by default for all DB systems
• Uses Oracle-managed encryption keys
• Option to bring your own keys (BYOK)
• Transparent to applications

4. Manage Credentials Securely

OCI Vault Integration:

• Store database passwords in Vault
• Automatic password rotation
• Access control through IAM
• Audit trail of secret access

Application Credentials:

• Use principle of least privilege
• Create separate accounts per application
• Avoid using admin account for applications
• Implement password policies

5. Monitor Access

Database Audit:

• Enable MySQL Enterprise Audit
• Log all administrative actions
• Monitor failed login attempts
• Regular review of audit logs

Network Flow Logs:

• Enable VCN flow logs
• Monitor connection patterns
• Detect anomalous traffic
• Integration with SIEM tools

Troubleshooting Common Connection Issues

Issue 1: Cannot Connect - “Connection Refused”

Possible Causes:

• Security list/NSG not allowing MySQL ports
• DB system is stopped or in wrong state
• Incorrect IP address or port
• Network path doesn’t exist

Solutions:

1. Verify DB system is in ACTIVE state
2. Check security lists allow source IP on ports 3306/33060
3. Confirm using correct private IP address
4. Test network connectivity with ping or telnet
5. Verify route tables in VCN

Issue 2: “Access Denied” Errors

Possible Causes:

• Incorrect username or password
• User doesn’t exist in MySQL
• User doesn’t have necessary privileges
• Connection from unauthorized host

Solutions:

1. Verify credentials are correct
2. Check user exists: SELECT User, Host FROM mysql.user;
3. Grant necessary privileges
4. Ensure user allowed from connection source

Issue 3: “Too Many Connections”

Possible Causes:

• Application connection leaks
• max_connections limit reached
• Connection pool misconfigured

Solutions:

1. Check current connections: SHOW PROCESSLIST;
2. Increase max_connections in configuration
3. Fix application connection leaks
4. Implement connection pooling properly
5. Kill idle or long-running connections if necessary

Issue 4: Slow Connection Establishment

Possible Causes:

• DNS resolution delays
• Network latency
• Reverse DNS lookups enabled

Solutions:

1. Use IP address instead of hostname
2. Disable reverse DNS lookups
3. Check network latency
4. Verify VCN routing is optimal

Connecting to and managing MySQL HeatWave DB systems requires understanding of networking concepts, endpoint configurations, and lifecycle management operations. By following the guidance in this article, you can:

• Establish secure, reliable connections from various sources
• Implement appropriate network paths for your use case
• Perform essential lifecycle operations (stop, start, restart, delete)
• Monitor DB system states and transitions
• Troubleshoot common connectivity issues
• Apply security best practices

Whether you’re connecting from Cloud Shell for quick administration, setting up application connectivity, or managing production databases, MySQL HeatWave provides flexible options to meet your needs while maintaining security and performance.

Additional Resources:

• MySQL HeatWave Networking Documentation
• Connecting to a DB System
• Managing DB Systems
• OCI Networking Best Practices