Using MySQL Database Service in OCI – Part 1: creating a MySQL DB System

This post is the first of a series of articles dedicated to MySQL Database Service (MDS):

• Using MySQL Database Service in OCI – Part 1: creating a MySQL DB System
• Using MySQL Database Service in OCI – Part 2: importing Data to MySQL DB System
• Using MySQL Database Service in OCI – Part 3: faster with MySQL HeatWave !

The goal of this series if to get familiar with MDS, HeatWave and useful MySQL Shell utilities.

In this first article we will start by creating a MySQL DB System and a Compute instance in OCI. One of the goal is also to see the benefits of HeatWave, so we will start by creating this single MySQL DB System on a HeatWave compatible shape.

We will then see how to import a large amount of data from the Internet to use as example. Usually we can load specially prepared sample data directly from MySQL’s website. In this example we will use something different.

Then we will run some queries. In the final article we will enable HeatWave and see the gain of using it. I will also show you how to optimize HeatWave with Machine Learning recommendations directly from MDS.

Creating MySQL DB System in OCI

Creating a MySQL DB instance is straightforward in Oracle Cloud Infrastructure, the most important part for us, is to use a Shape that is compatible with our future HeatWave deployment:

A compatible HeatWave shape contains the world HeatWave in its name:

Of course, as usual you need to provide the credentials for your account. Once created, you can get its private IP. As you know, MDS instances don’t get a public IP that could expose them directly on the Internet.

Compute Instance

Now we need a compute instance on which we will connect via SSH. So you need to generate a key or upload yours when creating the Compute Instance. This instance must be on the same VCN as the MDS instance but on the public Subnet and with a public IP.

We use then the public IP of this compute instance to connect from a terminal (or using Putty) in SSH:

$ ssh -i /path/to/my/ssh.key opc@129.213.xxx.xxx
Last login: Mon Dec 13 11:29:28 2021 from xxx.xxx.xxx.xxx
[opc@my-compute ~]$

MySQL Shell

We now install MySQL Shell (and MySQL’s classic client to benefits from it’s credential helper to store the credentials):

[opc@my-compute ~]$ sudo yum install -y \
                    mysql-community-client mysql-shell

We can now connect to our MDS DB System:

It’s time to setup MySQL Shell as we like. For more information about how to configure MySQL Shell, please check this post: Reminder when using MySQL Shell.

We can also install some plugins from https://github.com/lefred/mysqlshell-plugins/:

[opc@my-compute ~]$ cd .mysqlsh/
[opc@my-compute .mysqlsh]$ git clone \
     https://github.com/lefred/mysqlshell-plugins.git plugins
Cloning into 'plugins'...
remote: Enumerating objects: 1055, done.
remote: Counting objects: 100% (414/414), done.
remote: Compressing objects: 100% (305/305), done.
remote: Total 1055 (delta 211), reused 295 (delta 109), pack-reused 641
Receiving objects: 100% (1055/1055), 268.87 KiB | 0 bytes/s, done.
Resolving deltas: 100% (592/592), done.

Now if you run msyqlsh the plugins will be loaded. If some modules are missing (on default OCI compute instance, the module requests should be missing), you can install those modules using the method described in this post:

[opc@my-compute ~]$ mysqlsh –pym pip install –user requests

And I invite you to test this with the support plugin:

We are ready for the next step ! Stay tuned for part 2.

Enjoy MySQL, MySQL Shell and MySQL Database Service !