Connecting to and managing HeatWave on AWS

It's a multi-cloud world today, and that's why we made MySQL HeatWave available on Amazon Web Services. MySQL HeatWave is the only service that combines OLTP, analytics, and machine learning, within a single MySQL database. AWS users can run transaction processing, analytics, and machine learning workloads in one service, without requiring time-consuming ETL duplication between separate databases such as Amazon Aurora for transaction processing, Amazon Redshift or Snowflake on AWS for analytics, and SageMaker for machine learning.

Oracle designed MySQL HeatWave so developers could focus on their applications. You don't have to manage two databases for OLTP and OLAP, and you don't have to create complex ETL pipelines. MySQL HeatWave is a fully managed service automating tasks such as backup, recovery, and database and operating system patching. It additionally includes machine learning-powered automation with MySQL Autopilot, saving developers and DBAs significant time and effort. MySQL HeatWave is developed and supported by the MySQL development team at Oracle.

Let's get started!

MySQL HeatWave on AWS resides in an Oracle-managed tenancy on AWS. You can access it from the browser-based HeatWave Console or from a MySQL client or application. For this article, we'll just dip our toe in by signing in, provisioning the service, and seeing an overview of what the HeatWave Console offers.

Prerequisites

The MySQL HeatWave Console supports browser platforms supported by Oracle Jet, such as the following browsers and versions:

• Google Chrome 69 or later
• Safari 12.1 or later
• Firefox 62 or later
• The Console does not support Firefox Private Browsing.

You'll also need an OCI account, optionally an AWS account as well.

If you do not already have an Oracle Cloud account, you gan get one here . If you already have an account, skip to step 6.'

Connecting to a DB System in Workspaces

By now you've got MySQL HeatWave on AWS provisioned, and you want to log into your DB System. We do this in the Workspaces tab in the console.

A Connection Information dialog will appear, and you choose a DB System from the drop-down, enter the proper username/password combo, and click Connect.

Managing HeatWave Cluster Data

Also in the Workspaces tab, we can use the Console to load or unload data from a HeatWave cluster. Here's how:

1. In the DB System workspace, click the Manage Data in HeatWave tab.
   
2. Select the databases and tables that you want to load or unload. When a database is selected, the tables from the selected database appear in the Tables from selected databases pane.
   
3. There's lots to see there but we're interested in selecting the tables we want to load or unload, so click Load into HeatWave or Unload from HeatWave.
4. A MySQL Autopilot dialog will show up, providing a summary of the load/unload operation about to happen.
   
5. Click Load/unload Tables to start the parallel load operation.

NOTE: The Refresh button refreshes the page, displaying the current state of databases and tables loaded in HeatWave.

Creating backups

To create a DB System backup:

1. In the HeatWave Console, select the MySQL tab.
   
2. In the list of DB Systems, find the DB System you want to create a backup for, and do one of the following:
   • Click on the row of the DB System to highlight it, and choose Create Backup from the Actions menu.
     
   • Click the name of the DB System to open the DB System Details page. Click Create Backup. TheCreate Backup dialog is displayed.
     
3. Edit the fields as required:
4. Display Name: The name of the backup. If you do not define a name, one is generated in the format DB-System-Name - Backup - Date&Time.
5. Description: The description of the backup. If you do not define a description, one is generated in the format DB-System-Name - Manual Backup - Date&Time.
6. Click Create to create the backup.

Maintenance

Good news! Essential patching and maintenance of MySQL DB Systems is an automatic process. Patches of the underlying operating system, update versions (-uN) of the MySQL server, and any underlying hardware are performed during the Maintenance Window defined on the DB System. A Maintenance Window Start Time is defined for you, automatically, and can be viewed on the DB System Details page in the MySQL HeatWave on AWS Console.

When maintenance is performed, your DB System's status changes to UPDATING and the DB System may be unavailable for a short time while the maintenance completes.

Such maintenance is performed infrequently, and only when absolutely necessary. This is usually for security or reliability issues.

Upgrading MySQL Server

Use the Console to manually upgrade the MySQL Server of your DB system.

NOTE: It is recommended to perform a full backup of your DB system before upgrading.

1. In the HeatWave Console, select the MySQL DB Systems tab.
2. Under MySQL, select DB Systems.
3. Find the DB system you want to upgrade, and do one of the following:
4. Click on the row of the DB System to highlight it, and choose Edit DB System from the Actions menu.
5. Click the name of the DB System to open the DB System Details page. Click Edit DB System.
6. The Edit DB System dialog is displayed.
   
7. In the Database version section, select Update to update the instance.
8. Click Save Changes.

The DB system enters the UPDATING state while the MySQL Server is upgraded.

Managing a HeatWave Cluster

When a HeatWave cluster is stopped through a stop or restart action, the data loaded in HeatWave cluster memory is unloaded.

Starting, stopping, or restarting a HeatWave Cluster

These actions have no effect on the DB System to which the HeatWave cluster is attached. However, start, stop, or restart actions on the DB System also affect the attached HeatWave cluster. When a HeatWave cluster is stopped as a result of a stop or restart action on the DB System, any data that was loaded on the HeatWave cluster must be reloaded when the HeatWave cluster is restarted.

To start, stop, or restart a HeatWave cluster:

1. In the HeatWave Console, select the HeatWave Clusters tab.
2. In the list of HeatWave clusters, find the HeatWave cluster you want to start, stop, or restart, and do one of the following:
3. Click on the row of the HeatWave cluster to highlight it, then choose the required action from the Actions menu.
4. Click the name of the HeatWave cluster to open the HeatWave Cluster Details page. On this page you can stop, start, or restart the HeatWave cluster.
   
5. Select one of the following actions:
6. Start: Starts a stopped HeatWave cluster. After the HeatWave cluster is started, the Stop action is enabled and the Start option is disabled.
7. Stop: Stops a running HeatWave cluster. After the HeatWave cluster is stopped, the Start action is enabled.
8. Restart: Shuts down a HeatWave cluster and restarts it.

Deleting a HeatWave Cluster

Deleting a HeatWave cluster removes the HeatWave cluster nodes permanently. The DB System to which the HeatWave cluster is attached is unaffected. If you just want to crunch some data for a bit, while keeping costs low, you can stop the HeatWave cluster. You don't get billed when the cluster is stopped. If you delete it, the cluster will need to be set up from scratch again, so there's that.

To delete a HeatWave cluster:

1. In the HeatWave Console, select the HeatWave Clusters tab.
2. In the list of HeatWave clusters, find the HeatWave cluster you want to delete, and do one of the following:
3. Click on the row of the HeatWave cluster to highlight it, and choose the Delete action from the Actions menu.
4. Click the name of the HeatWave cluster to open the HeatWave Cluster Details page. Click the Delete button. The Delete HeatWave Cluster dialog is displayed.
   
5. Click Delete HeatWave cluster.

User Access Control

Eventually you'll need to create users and groups (for various levels of access control), and luckily you can access the OCI Identity Management from the MySQL HEatWave on AWS Console:

1. Sign into the HeatWave Console as an Account Administrator.
2. From the profile menu, select Administration. You are directed to the MySQL HeatWave on AWS Administration page in the OCI Console.
3. Select Identity Service. This will take you to the Identity section in the OCI Account Center.
   

Summary

That's a top-level overview of getting your HeatWave on AWS cluster up and running, and a few management tricks sprinkled in.