Connecting To and Managing MySQL HeatWave on OCI

One of the cool things about MySQL HeatWave is the ability to run analytics directly against your existing transactional data, so there’s no need to shuffle that data off to a separate system when you need to perform massively parallel analysis. It’s surprisingly easy.

MySQL HeatWave is a fully managed service and, like any database, is best when kept shielded from the public internet. We’ll still need to connect somehow so we can work with our data, so let’s explore the use of OCI Cloud Shell (which we covered briefly in the Getting Started guide), a DIY Bastion host, and connecting with MySQL Workbench using the OCI Bastion service (read more about Bastion) as a secure tunnel.

If you don’t already have a MySQL HeatWave system up and running, head over to Getting Started with MySQL HeatWave on OCI and get one going. When you’re all set, keep reading here to explore the various connection options, data loading techniques, and cluster management capabilities.

Prerequisites

• An OCI account (Trial or Paid)
• Some experience with MySQL Shell
• A MySQL HeatWave DB System
• A text editor

A Quick Review of Your Options

For those that prefer a command line interface, you may opt to connect via Cloud Shell or through a self-managed bastion host, where you can utilize your favorite flavor of Linux. For those that prefer the GUI, you can head on over to the Secure Tunnel section. But first…a few preparatory steps!

Before you go any further, ensure you have gathered the requisite endpoint details from your DB System. You’ll need the private IP address and the MySQL Port which can be obtained by navigating Databases -> DB Systems, where you’ll click the name of the MySQL database you created previously.

Next, you’ll need an SSH key for secure connectivity to the different intermediary systems (whether you choose to use Bastion or a secure tunnel). We’ll use Cloud Shell, available right in our OCI Console to create the SSH key – super handy. (Note – we can also connect to the database from Cloud Shell…we’ll get to that soon)

Cloud Shell is like a little VM running a Bash shell, and it comes loaded with features. In fact, MySQL and MySQL Shell are already installed, along with a variety of OCI tools. So it’ll take a minute to start up, but when it does we’ll be able to do a lot within the CLI right in your browser.

Once the shell is up, create an SSH Key using:

ssh-keygen -b 2048 -t rsa

Just keep hitting Enter (to accept default filename and proceed without password), and soon the public (~/.ssh/id_rsa.pub) and private (~/.ssh/id_rsa) SSH keys will ready to go.

So let’s cd ~/.ssh and ls -al to see what’s inside:

If this is the first time you’ve created SSH keys in Cloud Shell, you should see just the two files. Keep the private key safe and don’t share it. The public key will be needed for certain activities and can be uploaded to some systems, as well as copied and pasted to facilitate secure communications in the cloud.

Before we leave the Cloud Shell, we’ll want to grab the public key. Open up a text editor like Notepad, and if you’re still in Cloud Shell, copy the id_rsa.pub content the notepad with cat ~/.ssh/id_rsa.pub, select that blob of text (that’s your key), and paste into the text editor of your choice. It should start with ssh-rsa. You can also click the sprocket in the top right corner of Cloud Shell and select Download. Enter the path to the public key:

The public key will be placed in the folder designated by your OS for downloaded files. Do the same for .ssh/id_rsa (the private key) and store it in a safe location. On Mac or Linux, make sure you chmod 400 the private key file.

OCI Cloud Shell

Since you’re already in the Cloud Shell, let’s start here with a quick refresher. Because the tools are already installed, you can connect with either MySQL Client or Shell. In our example, we’ll use MySQL Shell.

First things first, you will need to change the network setting for Cloud Shell to private.

When prompted, select the VCN and subnet where you deployed the MySQL instance and [Connect to this network].

NOTE: This feature lives in your home region. If you’ve not deployed MySQL there, you can follow the instructions in the prompt to establish remote VCN peering.

Time to log into the database:

mysqlsh -uadmin -p -h 10.0.1.... --sql

When prompted, enter the password you designated when creating the DB system.

If you loaded the sample data when creating your database, you can run a quick query to validate the contents of the DB:

SELECT table_name, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'airportdb';

DIY Bastion Host

Compute time! Go to Compute > Instances. Stay in the same compartment as before and click Create Instance. Give it a name, keep the selected Availability Domain and Shape. Select the image for your favorite Linux flavor. In this example, we are using Oracle Linux 8.

The important part here is to use the VCN you set up earlier. Select the public subnet that was created and be certain that Assign a public IP address is set to Yes.

In the Add SSH keys section, paste the public key from the notepad.

Then click Create to start it up! It’ll take a few minutes, of course.

Once it turns green and is running, the instance page will show us a public IP address and private IP under Instance access.

Copy and save the public IP.

Now we’ll connect to the MySQL DB System from our Bastion host.

So now you have the public IP of your Compute instance, your private SSH key, and the private IP of your DB System. We’ll put the first two together to connect via SSH. Point to where the private key is, and the username will be opc.

For example (Mac OS or Linux terminal; PowerShell on Windows):

ssh -i ~/.ssh/id_rsa opc@

Let’s install MySQL Shell using (and just keep answering y to all the prompts):

sudo yum install mysql-shell -y

Your endpoint will be the private IP you copied into your notepad earlier. You’ll also need the password for the database you stood up earlier, of course.

Example:

mysqlsh -uadmin -p -h 10.0.1.... --sql

When prompted, enter the password you designated when creating the DB system.

If you loaded the sample data when creating your database, you can run a quick query to validate the contents of the DB:

SELECT table_name, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'airportdb';

OCI Managed Bastion

The OCI Bastion service makes it easy to create secure, temporary connections into the private resources inside your Virtual Cloud Network. Because MySQL is a fully managed DB system, we cannot connect to the OS. Instead, we will create a secure tunnel through the Bastion service and utilize the MySQL Workbench to connect.

To get started, navigate to Security -> Bastion in the OCI console. Create a Bastion instance – this is essentially a container for specific session configurations.

Next, create a session. The type will be SSH port forwarding session. You will need to enter the private IP address of the MySQL instance, along with the DB port (3306 by default). For this exercise you can use the SSH key pair you generated earlier. In the real world you should probably generate different key pairs.

Click [Create session].

Once the session successfully creates, you can click the 3-dot menu on the right to view SSH commands. Copy the command to a text editor and replace with the full path to the key that was created / downloaded earlier. Replace with the local port on the machine from which you plan to connect. You can use any available port.

For Mac or Linux, you can open a terminal window and run the command. On Windows, use PowerShell. If prompted to continue connecting, type ‘yes’ and press enter.

Next, open your database client (i.e. MySQL Workbench) and configure the connection.

• Hostname: 127.0.0.1
• Port: 3306
• Username: admin (in my case)
• Password: [Store in Vault …]

Click [Test Connection] – if all goes well, you should see:

Save your settings and open the connect. Now you can poke around at existing and/or load new data.

Loading data into HeatWave Cluster

At this point we’re ready to load data from the InnoDB into HeatWave so we can really kick things up a notch.

Let’s jump back into MySQL shell so we can kick off the data load.

On the command line:

mysqlsh -uadmin -p -h 10.0.1... --sql

Enter your database password.

Use the Auto Parallel Load command to load the airportdb tables into HeatWave:

CALL sys.heatwave_load(JSON_ARRAY('airportdb'), NULL);

Just to make sure, we’ll verify that the tables are loaded in the HeatWave cluster. Loaded tables have an AVAIL_RPDGSTABSTATE load status.

Again, in the MySQL Shell:

USE performance_schema;

It will tell you the default schema has been set to performance_schema.

SELECT NAME, LOAD_STATUS FROM rpd_tables,rpd_table_id WHERE rpd_tables.ID = rpd_table_id.ID;

Of course, from here you can run queries and do all the of the database functions you need. You can also turn HeatWave on and off to compare query times and see just how much faster these run with HeatWave on.

To see if HeatWave is on, use the following command:

SHOW VARIABLES LIKE 'use_secondary_engine%';

To turn it OFF, use:

SET SESSION use_secondary_engine=OFF;

And to turn it back on again, use:

SET SESSION use_secondary_engine=ON;

Should you find a need to unload tables from the HeatWave cluster, simply alter the table like so:

ALTER TABLE flightschedule SECONDARY_UNLOAD;

Managing a HeatWave Cluster

After creating a HeatWave cluster, you still can adjust the number of nodes. You can do this by editing the Cluster using the HeatWave menu item in DB Systems. Note that cluster is resized online, there is no downtime during the operation, and data is automatically rebalanced after resize.

There is also an option to estimate the number of nodes that may be necessary for the amount of data you wish to analyze. Generating an estimate can take several minutes but helps take the guess work out of right-sizing your cluster. If the estimator suggests a change to your configuration, you can apply it right there.

At any time, you may choose to stop your HeatWave cluster (as billing will also stop), or restart to freshen up the cluster. When a HeatWave cluster is stopped through a stop or restart action, the data loaded in HeatWave cluster memory is lost. When you start or restart the cluster, any data that was previously loaded will be automatically reloaded. Data changes that occurred within the DB while HeatWave was stopped are included in the reloaded data.

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. Bear in mind, the cluster will need to be set up from scratch again after deletion, which means reloading data into HeatWave.

Summary

That concludes our cursory review of connectivity strategies and management capabilities for MySQL HeatWave on OCI. We learned how to connect a DB System through various channels, how to load / unload data in HeatWave, and how to turn the secondary engine on and off. From here, you can try running some queries or even load your own data in and see how fast HeatWave can go!

For more on MySQL HeatWave, read the docs here.

Want to know more? Join the discussion in our public Slack channel!