Creating a Highly Available HeatWave Database System Using Oracle Cloud Shell

HeatWave is the only fully managed MySQL database service that combines transactions, analytics, machine learning, and GenAI services, without ETL duplication. HeatWave also includes HeatWave Lakehouse, allowing users to query data stored in object storage, MySQL databases, or a combination of both. Users can deploy HeatWave MySQL–powered apps on a choice of public clouds: Oracle Cloud Infrastructure (OCI), Amazon Web Services (AWS), and Microsoft Azure.

A high availability solution guarantees reliability, optimal performance, and uninterrupted business continuity, making it indispensable for modern enterprises. Today’s customers want a high availability (HA) solution that is simple to manage and scale with minimal downtime. HeatWave’s HA solution ticks these boxes and when coupled with Oracle Cloud Infrastructure (OCI) Cloud Shell, creating a HeatWave HA Database system and getting it up and running quickly and efficiently has never been easier.

In this blog, we will walk through the process of creating a HeatWave HA Database system using Oracle Cloud Shell. But first, let’s do a quick overview of HeatWave’s HA DB system and Cloud Shell.

HeatWave HA DB System Overview

A High Availability (HA) enabled HeatWave DB system will remain available even in the case of a critical event because it has no single point of failure. By default, three MySQL instances are created when setting up a HA DB system: one primary instance and two secondary instances serving as standby replicas. In the event of the primary failing, one of the secondary instances automatically resumes the role of the new primary, while the failed primary transitions into a secondary role and rejoins the group once the error is resolved.

The HA DB system is provisioned based on your selected region and subnet type. Within this configuration, the primary instance acts as the read/write endpoint, whereas the secondary instances provide high availability. Any data written to the primary instance is automatically replicated to the secondary instances, ensuring data consistency across all nodes. A single secondary instance failure should not impact the primary. However, if both secondary instances become unavailable, the HA capability is compromised. Although the primary instance continues to support read operations, it cannot accept write requests since the quorum requirement is no longer met due to the majority of the group being offline.

OCI Cloud Shell Overview

Oracle Cloud Infrastructure (OCI) Cloud Shell is a web browser-based terminal and provides a Linux shell, a pre-authenticated command-line interface (CLI) and preinstalled developer tools like Git, Java, Ansible, and Terraform, for easily managing Oracle Cloud resources. You can run cli commands without any setup. To know more about Oracle cloud shell, please look here.

Create a HeatWave HA DB System

To create a HeatWave HA DB system using the Cloud Shell, several mandatory parameters must be specified. These parameters ensure proper configuration and deployment of the HA setup.

Let’s discuss the mandatory parameters to create a HA DB system.

More information about all the possible parameters for creating a HA DB system can be found here.
From cloud shell prompt, let’s create the HA DB system:

The result of the above command will resemble the following:

From the above command, we can see that the shape name is missing. If the user misses any required or mandatory parameters while creating a HA DB system, an error message will be displayed. Let’s update the command with the missing –shape-name parameter and execute it again.

Output from the above command will resemble the following:

The above result shows that the DB system state (lifecycle-state) is CREATING.

Verify the current state of HA DBS

Let’s verify the current state of our HA DB system by using the get db-system command and specifying the db-system-id.

The result of the above “get” command will resemble the following:

With the DB system’s lifecycle state now showing as ACTIVE, we can confirm that our HA DB system has been successfully created and is ready for use. In addition to using the CLI commands, we can also view the DB system details from the OCI console.
 

Figure 1. Look for the DB system from the OCI Console

When clicking on the DB system, we’re presented with a list of all the DB systems created within our compartment, along with their respective states. This provides a centralized view of our DB systems, allowing us to quickly assess their status and take any necessary actions.

Figure 2. List of DB systems

During the creation process, we specified the –display-name parameter as ‘HA_DBS’. Now, when viewing the list of DB systems in the console, we indeed see ‘HA_DBS’ listed, confirming that our HA DB system was successfully created and is currently active.

Figure 3. Get the state of the HA DB system

Accessing Endpoints of Primary MySQL Instance

We can click on the Endpoints tab within the same console page to get the details of Private IP, port number.

Figure 4. Click on the Endpoints to get the connection parameters.

From the image below, we can see endpoint details like Private IP address (the IP address to which the user can connect to the MySQL instance), MySQL port.

Figure 5. Get the endpoint details like IP address, port number

Accessing HA member state from the Performance Schema

The MySQL Performance Schema is a diagnostic tool designed to monitor MySQL Server execution at a low level and is enabled by default. It provides performance-related statistics about server execution, helping database administrators (DBAs) and developers analyse and optimize queries, detect bottlenecks, and troubleshoot performance issues. To know more about performance_schema database, please look here.

From the performance_schema database, the user can query the ‘replication_group_members’ table to get all the HA group member details. In this case, the output reveals that one member is acting as the primary, while the other two members are designated as secondaries, all 3 nodes are with an ONLINE state. This confirms that our HA DB system is properly configured, with the primary member handling writes and the secondary members replicating data for high availability.

Figure 6. Get the HA group members details from the performance schema

Connect to the Primary MySQL Instance

At this stage, we can proceed with connecting to the DB system and performing various operations, such as creating databases, tables, as well as configuring additional features like backups and monitoring.

Let’s connect to the primary MySQL instance and create a database with a few tables. To do this, we use the following command:

                  mysql -uroot -p”<root password>” -h'<primary ip address>’ -P”3306″

Figure 7. Create a database and tables

Let’s insert a few rows into the table say “t1” and check the row count.

Figure 8. Insert data into the table

Summary

In today’s world, customers need a high availability (HA) solution to ensure their systems remain operational with minimal downtime, even in the face of failures whether from a hardware, software, or network-related issue. Businesses operate 24/7 and cannot afford outages. Heatwave offers customers a HA DB system that remains available even in the case of a critical event because it has no single point of failure. With three instances created by default, failover to a standby replica instance means the DB service does not experience any loss of service.

Creating a HeatWave HA DB system has never been easier then when combined with Oracle’s Cloud Shell. As demonstrated in this blog, a single command can set up, create, and activate a HA DB system. This is a winning combination for any customer wanting a reliable, scalable and simple to manage HA DB system.

References:

1.   High Availability