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.
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 firstname.lastname@example.org Last login: Mon Dec 13 11:29:28 2021 from xxx.xxx.xxx.xxx [opc@my-compute ~]$
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
We are ready for the next step ! Stay tuned for part 2.
Enjoy MySQL, MySQL Shell and MySQL Database Service !