dasini.net &#8211; Diary of a MySQL expert

Watch my videos on my YouTube channel and subscribe.

Connect to MySQL Database Service Using MySQL Workbench

Thanks for using HeatWave & MySQL!

2

Filed under:
Cloud, Howto, MDS, MySQL, Tuto by Olivier DASINI

Discovering MySQL Database Service – Episode 8 – Connect to MySQL Database Service Using MySQL Shell

September 21, 2021

Tags: Cloud, MDS, OCI

MySQL, the world’s most popular open source database, is available as a managed cloud service in Oracle Cloud Infrastructure (OCI) under the name of MySQL Database Service (MDS).

MySQL Database Service is a fully managed cloud service, 100% Developed, Managed and Supported by the MySQL Team.

This is the eight episode of “Discovering MySQL Database Service“, a series of tutorials where I will show you, step by step, how to use MySQL Database Service and some other Oracle Cloud Infrastructure services.

Please also note that you can run this tutorial and thus try MySQL Database Service & the other Oracle Cloud Infrastructure services for free by starting your 30-day trial.

Planning episodes

Episode 8 – Connect to MySQL Database Service Using MySQL Shell

In the previous episode we’ve seen how to use an OCI Bastion session in order to provide a restricted and time-limited access to administer our MySQL instance.
One more step to our Discovering MySQL Database Service journey.

In this episode, we’ll learn how to connect to our MySQL Database Service instance using the fantastic MySQL Shell – an interactive Javascript, Python, or SQL interface supporting development and administration for the MySQL Server.

MySQL Shell

MySQL Shell is an advanced client & code editor for MySQL. In addition to the provided SQL functionality, it provides scripting capabilities for JavaScript and Python.

MySQL Shell includes utilities for working with MySQL, among others:

Instance & schema dump utilities support the export of all schemas or a selected schema from an on-premise MySQL instance into an OCI Object Storage bucket or a set of local files
Dump loading utility supports the import into a MySQL DB System or a MySQL Server instance of schemas or tables dumped using MySQL Shell’s Dump Utility

The complete list is available in the documentation: MySQL Shell utilities.

You’ll also find practical resources on my blog.

Create a SSH port forwarding session

In the previous episode – Discovering MySQL Database Service – Episode 7 – Use a Bastion SSH port forwarding session – we created a Bastion, a SSH port forwarding (SSH tunneling) session and generated SSH key pair.

We are now going to use these in order to connect to MDS with MySQL Shell.

If your session is not valid anymore, you must create a new one (see Create a session paragraph from Discovering MySQL Database Service – Episode 7 – Use a Bastion SSH port forwarding session).

Please note that you need a SSH client on your computer. If you are using a GNU Linux, MacOS, Windows 10 system you are good to go.

If not… all is not lost 🙂
Please read episode 10 : Connect to MySQL Database Service Using OCI Cloud Shell.

In the console menu, go to Identity & Security / Bastion, check that you are in the right compartment then click on the Bastion we previously created (BastionMDS in this example).

You are now seeing the session we already created (if not create a new one).

We now must get the SSH port forwarding command generated by OCI.

To do so, go to the right and click on the action menu (3 vertical dots on the right). A contextual menu is showing up then click on copy SSH command.

Paste this command in your shell prompt.

The SSH command looks like :

ssh -i <privateKey> -N -L <localPort>:10.0.1.231:3306 -p 22 ocid1.bastionsession.oc1.eu-frankfurt-1.amaaaaaacicuulyams3rrxazerty1uiopqsd2fghjklm3wxcvbr4m7wpu52a@host.bastion.eu-frankfurt-1.oci.oraclecloud.com

Replace <privateKey> with the path to the private key we downloaded in the previous episode – Discovering MySQL Database Service – Episode 7 – Use a Bastion SSH port forwarding session

Replace <localPort> with a free port on the local machine from which you want to connect to the bastion (ie your computer/laptop).

Example:

ssh -i ./ssh-key-discovering_MDS.key -N -L 3333:10.0.1.231:3306 -p 22 ocid1.bastionsession.oc1.eu-frankfurt-1.amaaaaaacicuulyams3rrxazerty1uiopqsd2fghjklm3wxcvbr4m7wpu52a@host.bastion.eu-frankfurt-1.oci.oraclecloud.com

Then run the command…

If you hit the following error: WARNING: UNPROTECTED PRIVATE KEY FILE!

Don’t worry 🙂
Like stated in the error message, the private key must be protected. A chmod 600 (or equivalent, it depends on your OS) will fix that issue.

$ chmod 600 ssh-key-discovering_MDS.key

Then run the command again:

ssh -i ./ssh-key-discovering_MDS.key -N -L 3333:10.0.1.231:3306 -p 22 ocid1.bastionsession.oc1.eu-frankfurt-1.amaaaaaacicuulyams3rrxazerty1uiopqsd2fghjklm3wxcvbr4m7wpu52a@host.bastion.eu-frankfurt-1.oci.oraclecloud.com

No error anymore and no prompt.
But do not close the window because it will close the SSH tunnel (thus you will need to run the command again).

Alternatively, you can add an ampersand (&) at the end of the command.

ssh -i ./ssh-key-discovering_MDS.key -N -L 3333:10.0.1.231:3306 -p 22 ocid1.bastionsession.oc1.eu-frankfurt-1.amaaaaaacicuulyams3rrxazerty1uiopqsd2fghjklm3wxcvbr4m7wpu52a@host.bastion.eu-frankfurt-1.oci.oraclecloud.com  &

Connect to MDS using MySQL Shell

Now it is the time to connect to our MySQL instance through the SSH tunnel using MySQL Shell.

The information needed are:

MySQL client is here MySQL Shell : mysqlsh
MySQL Database Service user you’ve created : admin
IP to connect to MDS (through the SSH tunnel) : localhost
Port to connect to MDS (through the SSH tunnel) : 3333
Your MDS password

$ mysqlsh admin@localhost:3333

We are now connected to our MySQL Database Service instance \o/
The server version is here 8.0.26-cloud MySQL Enterprise

By default, MySQL Shell is in JavaScript mode. We can easily switch to SQL mode with the command \sql. And run any valid SQL command

Another way to do that would be to initiate the connection in SQL mode instead of JS by providing the – – sql option when calling mysqlsh:

$ mysqlsh admin@localhost:3333 --sql
...
MySQL  localhost:3333 ssl  SQL > SHOW SCHEMAS;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
+--------------------+
5 rows in set (0.0138 sec)

MySQL  localhost:3333 ssl  SQL > SHOW TABLES IN sakila;
+----------------------------+
| Tables_in_sakila           |
+----------------------------+
| actor                      |
| actor_info                 |
...

And as expected we can see and query the data we download during Discovering MySQL Database Service – Episode 5 – Create a MySQL DB system from a MySQL Shell dump.

Well, that’s all for today!
In this episode, we have seen how to securely connect to a MySQL Database Service instance using MySQL Shell through a SSH tunnel (port forwarding session) using the OCI Bastion service.

Next episode is:

Resources

Planning episodes

Watch my videos on my YouTube channel and subscribe.

Connect to MySQL Database Service Using MySQL Shell

Thanks for using HeatWave & MySQL!

3

Filed under:
Cloud, Howto, MDS, MySQL, Tuto by Olivier DASINI

Discovering MySQL Database Service – Episode 7 – Use a Bastion SSH port forwarding session

September 14, 2021

Tags: Cloud, MDS, OCI

MySQL, the world’s most popular open source database, is available as a managed cloud service in Oracle Cloud Infrastructure (OCI) under the name of MySQL Database Service (MDS).

MySQL Database Service is a fully managed cloud service, 100% Developed, Managed and Supported by the MySQL Team.

This is the seventh episode of “Discovering MySQL Database Service“, a series of tutorials where I will show you, step by step, how to use MySQL Database Service and some other Oracle Cloud Infrastructure services.

Please also note that you can run this tutorial and thus try MySQL Database Service & the other Oracle Cloud Infrastructure services for free by starting your 30-day trial.

Planning episodes

Episode 7 – Use a Bastion SSH port forwarding session

In the previous episode we’ve seen how to allow traffic from the VCN to the MySQL Database Service instance on ports 3306 & 33060.
One more step to our Discovering MySQL Database Service journey.

In this episode, we’ll discover and use the OCI Bastion service to provide a restricted and time-limited access to administer our MySQL instance.

Bastion

Oracle Cloud Infrastructure Bastion provides restricted and time-limited access to target resources that don’t have public endpoints.

Bastions let authorized users connect from specific IP addresses to target resources using Secure Shell (SSH) sessions. When connected, users can interact with the target resource by using any software or protocol supported by SSH.

So, Bastions are logical entities that provide secured, public access to target resources in the cloud that you cannot otherwise reach from the internet.
Bastions reside in a public subnet and establish the network infrastructure needed to connect a user to a target resource in a private subnet.

Another important concept is Bastion sessions.
Bastion sessions let authorized users in possession of the private key in an SSH key pair connect to a target resource for a predetermined amount of time.
You provide the public key in the SSH key pair at the time you create the session, and then supply the private key when you connect.
In addition to presenting the private key, an authorized user must also attempt the SSH connection to the target resource from an IP address within the range allowed by the bastion’s client CIDR block allowlist.

There are 2 kind of session types, managed ssh session and ssh port forwarding session. In this article, we’re going to use only the ssh port forwarding session.
Port forwarding (also known as SSH tunneling) creates a secure connection between a specific port on the client machine and a specific port on the target resource.
Using this connection you can relay other protocols.

Note
Your OCI user must be part of a group that has the right privileges in order to use all Bastion features.
At least the following rules:
Allow group SecurityAdmins to manage bastion-family in tenancy
Allow group SecurityAdmins to manage virtual-network-family in tenancy
Allow group SecurityAdmins to read instance-family in tenancy
Allow group SecurityAdmins to read instance-agent-plugins in tenancy
Allow group SecurityAdmins to inspect work-requests in tenancy
Please see: Bastion IAM Policies and Required IAM policy to managing Bastions

Create a bastion

Go to the OCI console, in the menu, go to: Identity & Security / Bastion

Check that you are in the right compartment and click Create Bastion

You should provide:

a bastion name – BastionMDS
the target VCN, in the right compartment – Demo_VCN
the target subnet, the private one – Private Subnet-Demo_VCN
the CIDR block allowlist ie the IP from where you want to provide the secure access (e.g. 11.0.0.0/24, <your IP>/32, …). You can have multiple CIDR blocks.
You can also choose the maximum session ttl (max 3 hours, and you can redefine it per session later) if you click on Show Advanced Options
Then click on Create Bastion

Create a session

Now it is the time to create a Bastion SSH port forwarding session.

Click on your brand new Bastion (or go to the OCI console and in the menu, go to: Identity & Security / Bastion and click on your Bastion).

Click on Create Session, a new form will show up.

You should provide:

the Session Type: SSH port forwarding session
a session name: a meaningful one 🙂
how to connect to the target host : we’ll use IP Address.
the MySQL Database Service instance IP Address: the IP address that we got in Discovering MySQL Database Service – Episode 5 – Create a MySQL DB system from a MySQL Shell dump
the MySQL Database Service instance port: one of the port that we got in Discovering MySQL Database Service – Episode 5 – Create a MySQL DB system from a MySQL Shell dump. I choose 3306 but you could have chosen 33060.

Like we mentioned earlier, Bastion sessions let authorized users in possession of the private key in an SSH key pair connect to a target resource. You provide the public key in the SSH key pair at the time you create the session, and then supply the private key when you connect.

If you do not already have a SSH key pair (or want to use another one) select Generate SSH Key pair.
Download the private key so that you can connect to the instance using SSH.

Be careful because,

It will not be shown again!

You can also download the public key if needed.
Please note that you will need to modify the private key file permissions.

Example:

ex: chmod 600 ssh-key-YYYY-MM-DD.key

If you want to setup the session time-to-live click on Show Advanced Options

Then click Create Session:

Our Bastion session is now created and active.

Well, that’s all for today!
In this episode, we have seen how to use an OCI Bastion session in order to provide a restricted and time-limited access to administer our MySQL instance.

Next episode is:

Resources

Planning episodes

Watch my videos on my YouTube channel and subscribe.

Use a Bastion SSH port forwarding session

Thanks for using HeatWave & MySQL!

3

Filed under:
Cloud, Howto, MDS, MySQL, Tuto by Olivier DASINI

Discovering MySQL Database Service – Episode 6 – Update the Private Subnet Security List

September 7, 2021

Tags: Cloud, MDS, OCI

MySQL, the world’s most popular open source database, is available as a managed cloud service in Oracle Cloud Infrastructure (OCI) under the name of MySQL Database Service (MDS).

MySQL Database Service is a fully managed cloud service, 100% Developed, Managed and Supported by the MySQL Team.

This is the sixth episode of “Discovering MySQL Database Service“, a series of tutorials where I will show you, step by step, how to use MySQL Database Service and some other Oracle Cloud Infrastructure services.

Please also note that you can run this tutorial and thus try MySQL Database Service & the other Oracle Cloud Infrastructure services for free by starting your 30-day trial.

Planning episodes

Episode 6 – Update the Private Subnet Security List

In the previous episode we’ve seen how to create a MySQL DB system from a MySQL Shell dump stored into an Oracle Cloud Infrastructure object storage bucket. One more step to our Discovering MySQL Database Service journey.

In this episode, we’ll introduce the concept of Security Lists a virtual firewall to control traffic at the network packet level.

Security Lists

A security list acts as a virtual firewall for an instance, with ingress and egress rules that specify the types of traffic allowed in and out.
Each security list is enforced at the VNIC level. However, you configure your security lists at the subnet level, which means that all VNICs in a given subnet are subject to the same set of security lists.
The security lists apply to a given VNIC whether it’s communicating with another instance in the VCN or a host outside the VCN.

The default security list comes with an initial set of stateful rules, which should in most cases be changed to only allow inbound traffic from authorized subnets relevant to the region that homes that VCN or subnet.

Note
Your OCI user must be part of a group that has the right privileges in order to work with Security Lists.
At least the following rules:
Allow group SecListAdmins to manage security-lists in tenancy
Allow group SecListAdmins to manage vcns in tenancy
Please see: Working with Security Lists

Update the Security List

Go to the OCI console, in the menu, go to: Networking / Virtual Cloud Networks
And select (click on) your VCN ( Demo_VCN in this example), and please check that you are in the right compartment.

Select (click on) the private subnet (Private Subnet-Demo_VCN in this example):

Select the Security List for the Private Subnet (Security List for Private Subnet-Demo_VCN in this example):

Click on Add Ingress Rules:

Now we are adding our ingress rules in order to be able to connect to the MDS instance from the VCN on ports 3306 (MySQL classic protocol) and 33060 (MySQL X protocol).

The information to provide are (please adapt to your context):
+ Source type: CIDR
+ Source CIDR: 10.0.0.0/16
+ IP protocol: TCP
+ Source port range: All
+ Destination port range: 3306,33060

Then click on Add Ingress Rules.

You should end up with something like:

Well, that’s all for today!
In this episode, we have seen how to allow traffic from the VCN to the MySQL Database Service instance on ports 3306 & 33060.
We are now able to connect to our MySQL instance…

Next episode is:

Resources

Planning episodes

Watch my videos on my YouTube channel and subscribe.

Update the Private Subnet Security List

Thanks for using HeatWave & MySQL!

2

Filed under:
Cloud, Howto, MDS, MySQL, Tuto by Olivier DASINI

Discovering MySQL Database Service – Episode 5 – Create a MySQL DB system from a MySQL Shell dump

August 31, 2021

Tags: Cloud, MDS, OCI

MySQL, the world’s most popular open source database, is available as a managed cloud service in Oracle Cloud Infrastructure (OCI) under the name of MySQL Database Service (MDS).

MySQL Database Service is a fully managed cloud service, 100% Developed, Managed and Supported by the MySQL Team.

This is the fifth episode of “Discovering MySQL Database Service“, a series of tutorials where I will show you, step by step, how to use MySQL Database Service and some other Oracle Cloud Infrastructure services.

Please also note that you can run this tutorial and thus try MySQL Database Service & the other Oracle Cloud Infrastructure services for free by starting your 30-day trial.

Planning episodes

Episode 5 – Create a MySQL DB system from a MySQL Shell dump

In the previous episode we’ve seen how to export data from a MySQL instance to an Oracle Cloud Infrastructure bucket using the awesome MySQL Shell. One more step to our Discovering MySQL Database Service journey.

In this episode, we’ll see how these data can now easily be imported into a MySQL Database Service instance.

The workflow

Again, the workflow is quite simple.
It is the continuation of the process initiated during the previous episode: Discovering MySQL Database Service – Episode 4 – Dump your MySQL data into an Object Storage bucket.

We will create a MySQL Database Service instance which will contain the data from the MySQL Shell dump stored inside the Oracle Cloud Infrastructure object storage bucket.

Thus the needed components are:

The OCI Object Storage bucket created previously (see the previous episode)
Either
- the MySQL Shell load dump utility : util.loadDump()
- the MDS Data Import feature

I would recommend to use the later because it is the easiest way to create and populate with your own data a MDS.
So let’s see how easy it is!

MySQL Database Service

MySQL Database Service is a fully managed Oracle Cloud Infrastructure native service, developed, managed, and supported by the MySQL team in Oracle.
Oracle automates all tasks such as backup and recovery, database and operating system patching, and so on.

You are responsible solely for managing your data, schema designs, and access policies.

Note
Your OCI user must be part of a group that has the right privileges in order to work with MySQL Database Service.
At least the following rules:
Allow group to {SUBNET_READ, SUBNET_ATTACH, SUBNET_DETACH, VCN_READ, COMPARTMENT_INSPECT} in [ tenancy | compartment | compartment id ]
Allow group to manage mysql-family in [ tenancy | compartment | compartment id ]
Allow group to use tag-namespaces in tenancy
Please see: Mandatory Policy Statements / Policy Details for MySQL Database Service

Create a MySQL DB system from a MySQL Shell dump

Go to the OCI console, in the menu, go to: Databases / MySQL

Select the right compartment (left of the screen – DBA in this example) then press Create MySQL DB System.

Provide DB System information & create administrator credentials

Check that you are in the right compartment, name your MySQL instance and enter a meaningful description (optional but recommended).

Select your instance type – Standalone in this example – then create the administrator credentials, usaername & password (see this like your root account, although you obviously will not have all the privileges because it’s a PaaS).

Let’s take a quick look at the different current types of MySQL DB systems:

Standalone: specifies a single-instance DB System.
High Availability: specifies a three-instance DB System containing one primary instance, and two secondary instances. See High Availability for more information.
HeatWave: configures a Standalone DB System with a HeatWave-compatible shape (MySQL.HeatWave.VM.Standard.E3) and 1TB of data storage, by default. For more information, see HeatWave.

Back to our MDS creation.

Network configuration & configure placement

Use the VCN created in Discovering MySQL Database Service – Episode 3 – Create a Virtual Cloud Network (Demo_VCN from the DBA compartment in this example).
And very important use the private subnet (it’s a good practice to put your databases in private subnet).

You can also place your MySQL DB system in the Availability Domain of your choice if your region has several. Same logic for the Fault Domain.

Configure hardware

This is were you’re going to choose the “power” of your MySQL instance.
Currently it starting from 1 to 64 CPU cores and 8 GB to 1 TB for the memory size.

In the Data Storage Size box you can enter in GB the amount of storage to allocate to the MySQL DB System for all data and log files.

Configure Backup Plan

You can specify the backup details ie enable/disable automatic backups, setup the retention period and the backup window.

We almost there 🙂
In fact, if you want to create an empty MySQL DB system you can push the Create button right now.

However, because we want to create this MySQL instance automatically populated with our data that we previously dumped (see Discovering MySQL Database Service – Episode 4 – Dump your MySQL data into an Object Storage bucket) we have a few more steps, “hidden” under the Show Advanced Options link.

Data Import

Select the Data Import tab

In order to create the PAR URL for our MySQL Shell dump stored in the bucket, we must click on
Click here to create a PAR URL for an existing MySQL Shell dump file (@.manifest.json)

A new form appears where you need to

Select the right bucket (again check that you are in the right compartment), bucket in DBA
Select the MySQL Shell dump manifest file (@.manifest.json)
Specify an expiration time for the PAR

Brilliant!
We have all the information to Create and set PAR URL.

So we can create our MySQL DB system that will contain our data.
Click on Create.

After a while (mostly dependent on the quantity of data to import) your MySQL instance is up and running \o/

You then have access to diverse information.
Among others, the endpoint which gives you some relevant ones from the connection point of view like the private IP Address and the default MySQL 8.0 ports 3306 & 33060 respectively for classic and X protocols.

Well, that’s all for today!
In this episode, we have seen how to create a MySQL DB system from a MySQL Shell dump stored into an Oracle Cloud Infrastructure bucket.

Next episode is:

Resources

Planning episodes

Watch my videos on my YouTube channel and subscribe.

Create a MySQL DB system from a MySQL Shell dump

Thanks for using HeatWave & MySQL!

9

Filed under:
Cloud, Howto, MDS, MySQL, Tuto by Olivier DASINI

Discovering MySQL Database Service – Episode 4 – Dump your MySQL data into an Object Storage bucket

August 24, 2021

Tags: Cloud, MDS, OCI

MySQL, the world’s most popular open source database, is available as a managed cloud service in Oracle Cloud Infrastructure (OCI) under the name of MySQL Database Service (MDS).

MySQL Database Service is a fully managed cloud service, 100% Developed, Managed and Supported by the MySQL Team.

This is the fourth episode of “Discovering MySQL Database Service“, a series of tutorials where I will show you, step by step, how to use MySQL Database Service and some other Oracle Cloud Infrastructure services.

Please also note that you can run this tutorial and thus try MySQL Database Service & the other Oracle Cloud Infrastructure services for free by starting your 30-day trial.

Planning episodes

Episode 4 – Dump your MySQL data into an Object Storage bucket

In the previous episode we’ve created our Virtual Cloud Network, that provides you with complete control over your cloud networking environment that we will create in this Discovering MySQL Database Service journey.

In this episode, we’ll see what is OCI Object Storage and how to export data from it to a MySQL instance using MySQL Shell.
In fact, in the process of creating a MySQL DB system, this step is optional.
But it is especially useful if you want to create a MDS instance with data from a MySQL server on premise or in any public/private cloud.

Because it is a plausible scenario, let’s see how to do that!

The workflow

The workflow is quite simple.
We will export all or part of our MySQL instance data using MySQL Shell into an Oracle Cloud Infrastructure Object Storage. Then we will import the data from the bucket to a MySQL Database Service instance.
Today we are going to see only the first part (red rectangle in the picture below).

Thus the needed components are:

OCI Object Storage
One of the MySQL Shell dump utilities :
- util.dumpInstance()
- util.dumpSchemas()

Object Storage

What is Object Storage?

The Oracle Cloud Infrastructure Object Storage service is an internet-scale, high-performance storage platform that offers reliable and cost-efficient data durability. The Object Storage service can store an unlimited amount of unstructured data of any content type, including analytic data and rich content, like images and videos.

With Object Storage, you can safely and securely store or retrieve data directly from the internet or from within the cloud platform. You can access data from anywhere inside or outside the context of the Oracle Cloud Infrastructure, as long you have internet connectivity and can access one of the Object Storage endpoints.

Create an Object Storage Bucket

Here as well, many ways to do that. The more convenient way is probably to use the OCI console.

In the menu, go to: Storage / Object Storage & Archive Storage / Buckets

Select the right compartment (left of the screen – DBA in this example) then press Create Bucket.

The bucket must have a name (Sakila_dump in this example – if you want to test with a sample database, Sakila database is available here).

The Default Storage Tier must be Standard – the default value.

Then you good to go!

Now let’s export the data to this fresh new created object storage bucket.

Export data to the bucket

You already get it, were going to use this amazing tool called MySQL Shell. But before we need to make some configuration for allowing MySQL Shell to easily connect to the OCI object storage service.

To this end, we will create an OCI CLI configuration file.

The CLI is a small-footprint tool that you can use on its own or with the Console to complete Oracle Cloud Infrastructure tasks. The CLI provides the same core functionality as the Console, plus additional commands. Some of these, such as the ability to run scripts, extend Console functionality.

To be clear, we will not use the CLI but its configuration file. The configuration file name and default location (on Linux) is /home/opc/.oci/config.

It should have the following information:

user: OCID of the user calling the API.
fingerprint: Fingerprint for the public key that was added to this user.
key_file: Full path and filename of the private key.
tenancy: OCID of your tenancy.
region: An Oracle Cloud Infrastructure region.

For more details please click here.

As an example, mine looks like:

[DEFAULT]
user=ocid1.user.oc1..xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
fingerprint=xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx
key_file=/home/opc/.oci/oci_api_key.pem
tenancy=ocid1.tenancy.oc1..xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
compartment=ocid1.compartment.oc1..xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
region=eu-frankfurt-1

So how to get these information?

First you can create the directory where to store the config file:

$ mkdir /home/opc/.oci

$ touch /home/opc/.oci/config

In the OCI console, on the top right (icon) go to Profile / User Settings

On this page, click on API Keys, bottom left:

Click on Add API Key,

Check Generate API key Pair ;
Click on Add API Key;
Download the Private Key ;
Click Add

The Configuration File Preview is generated with almost all the relevant information \o/.

So far we have something like:

[DEFAULT]
user=ocid1.user.oc1..aaaaaaaaaaaxxxxxxaaaaa
fingerprint=ca:e1:11:11:aa:bb:cc:dd:ee:ff:gg:hh:11:ii:jj:kk
tenancy=ocid1.tenancy.oc1..aaaaaaaaaaayyyyyyaaaaa
region=eu-frankfurt-1
key_file= # TODO

Copy and paste it in the config file we’ve previously created.

And don’t forget to update the key_file path (ex: key_file=/home/opc/.oci/oci_api_key.pem)

Brilliant! However, one information is still missing: the compartment OCID.

In the menu of the console, go to: Identity & Security / Compartments
Select the relevant compartment (DBA in this example)

Copy the compartment OCID:

We can now update the config file with the last information.

So your final config file will look like:

[DEFAULT]
user=ocid1.user.oc1..aaaaaaaaaaaxxxxxxaaaaa
fingerprint=ca:e1:11:11:aa:bb:cc:dd:ee:ff:gg:hh:11:ii:jj:kk
tenancy=ocid1.tenancy.oc1..aaaaaaaaaaayyyyyyaaaaa
region=eu-frankfurt-1
key_file=/home/opc/.oci/oci_api_key.pem
compartment=ocid1.compartment.oc1..aaaaaaaaaaazzzzzzaaaaa

Awesome!
Now we have all the parts to export our MySQL data with the MySQL Shell dump utilities.

MySQL Shell

MySQL Shell is an advanced client and code editor for MySQL.
In addition to the provided SQL functionality, similar to the mysql text client, MySQL Shell provides scripting capabilities for JavaScript and Python and includes APIs for working with MySQL.
X DevAPI enables you to work with both relational and document data.
AdminAPI enables you to work with InnoDB Cluster.

In this article we are mainly interested in the utility part of MySQL Shell.
MySQL Shell includes utilities for working with MySQL. To access the utilities from within MySQL Shell, use the util global object, which is available in JavaScript and Python modes, but not SQL mode.
The util global object provides many functions like checkForServerUpgrade(), importJSON(), exportTable(), importTable(), loadDump(), util.dumpTables(), util.dumpInstance(), util.dumpSchemas(), …

In order to create a MySQL Database Service instance automatically populated with your data, you will use either util.dumpInstance() or util.dumpSchemas().

I’m going to use the later – dumpSchema() – but please keep in mind that the logic is the same for dumpInstance().

As a side note there are several articles on my blog about MySQL Shell dump & load utilities.
I would recommend you to read HeatWave – A MySQL cloud feature to speed up your queries and MySQL SHELL – The new era.

There are many relevant options. I will briefly describe them below (please RTFM for more details).

ocimds: Setting this option to true enables checks and modifications for compatibility with MySQL Database Service.
osBucketName: The name of the Oracle Cloud Infrastructure Object Storage bucket to which the dump is to be written.
osNamespace: The Oracle Cloud Infrastructure namespace where the Object Storage bucket named by osBucketName is located.
compatibility: Apply the specified requirements for compatibility with MySQL Database Service for all tables in the dump output, altering the dump files as necessary.
threads: The number of parallel threads to use to dump chunks of data from the MySQL instance. Each thread has its own connection to the MySQL instance.
dryRun: Display information about what would be dumped with the specified set of options, and about the results of MySQL Database Service compatibility checks (if the ocimds option is specified), but do not proceed with the dump. Setting this option enables you to list out all of the compatibility issues before starting the dump.
ociParManifest: Setting this option to true generates a pre-authenticated request for read access (an Object Read PAR) for every item in the dump, and a manifest file listing all the pre-authenticated request URLs. The pre-authenticated requests expire after a week by default, which you can change using the ociParExpireTime option.
ociParExpireTime: The expiry time for the pre-authenticated request URLs that are generated when the ociParManifest option is set to true. The default is the current time plus one week, in UTC format.

If your config file is not in the default location, you will also need ociConfigFile.

ociConfigFile: An Oracle Cloud Infrastructure CLI configuration file that contains the profile to use for the connection, instead of the one in the default location ~/.oci/config.

osBucketName is the name of the bucket we created previously – Sakila_dump in this article.

osNamespace is available in the OCI console.
Open the menu and go to Storage / Object Storage & Archive Storage
Click on the Bucket name (if you don’t see your bucket , check if you are in the right compartment).
Namespace field is what your are looking for.

compatibility provides many types of modification. Please read carefully the documentation in order to use the correct setup for your needs.
I’m using here: force_innodb, skip_invalid_accounts, strip_definers, strip_restricted_grants, strip_tablespaces.

If you have tables without primary keys (that is usually not a good thing) you can force the dump using the ignore_missing_pks compatibility mode.
But you will not be able to use some advanced features like MDS High Availability, nor MDS HeatWave)

Another alternative is to use create_invisible_pks instead of ignore_missing_pks.
Please check the documentation for the relevant details.

ociParManifest & ociParExpireTime are very useful in this context.
They will allow you to create a Pre-Authenticated Requests (PAR) and setup an expiration time of your choice. Thus you will be able to load your data whenever it suits you.

To create or manage pre-authenticated requests, you need PAR_MANAGE permission to the target bucket.

Note
While you only need PAR_MANAGE permission to create a pre-authenticated request, you must also have the appropriate permissions for the access type that you are granting.
Details here

Connect to your (on-premise / private or public cloud) MySQL instance using MySQL Shell:

$ mysqlsh root@localhost

Run the dump command in dry run mode:

MySQL  localhost:3306 ssl  JS> 
util.dumpSchemas(["sakila"], "", { osBucketName: "Sakila_dump", osNamespace: "azertyuiop", ocimds: true, ociParManifest: true, ociParExpireTime: "2021-08-11T00:20:00.000+02:00", threads: 8, compatibility: ["force_innodb", "skip_invalid_accounts", "strip_definers", "strip_restricted_grants",  "strip_tablespaces"], dryRun: "true"})

If no error then… Go Go Go!!!
(Without dry run)

MySQL  localhost:3306 ssl  JS> 
util.dumpSchemas(["sakila"], "", { osBucketName: "Sakila_dump", osNamespace: "azertyuiop", ocimds: true, ociParManifest: true, ociParExpireTime: "2021-08-11T00:20:00.000+02:00", threads: 8, compatibility: ["force_innodb", "skip_invalid_accounts", "strip_definers", "strip_restricted_grants",  "strip_tablespaces"]})

Acquiring global read lock
Global read lock acquired
Gathering information - done
All transactions have been started
Locking instance for backup
Global read lock has been released
Checking for compatibility with MySQL Database Service 8.0.25
... <snip> ...
1 thds dumping - 100% (47.27K rows / ~47.27K rows), 3.01K rows/s, 193.24 KB/s uncompressed, 35.48 KB/s compressed
Duration: 00:00:15s                                                                                              
Schemas dumped: 1                                                                                                
Tables dumped: 16                                                                                                
Uncompressed data size: 3.03 MB                                                                                  
Compressed data size: 557.12 KB                                                                                  
Compression ratio: 5.4                                                                                           
Rows written: 47273                                                                                              
Bytes written: 557.12 KB                                                                                         
Average uncompressed throughput: 193.22 KB/s                                                                     
Average compressed throughput: 35.48 KB/s

We can now check the bucket content:

Well, that’s all for today!
In this episode, we have seen how to export data from a MySQL instance to an Oracle Cloud Infrastructure bucket using this awesome tool called MySQL Shell. These data can now easily be imported in a MySQL Database Service instance.

Next episode is:

Resources

Planning episodes

Watch my videos on my YouTube channel and subscribe.

Dump your MySQL data into an Object Storage bucket

Thanks for using HeatWave & MySQL!

2

Filed under:
Cloud, Howto, MDS, MySQL, Tuto by Olivier DASINI

Discovering MySQL Database Service – Episode 3 – Create a Virtual Cloud Network

August 17, 2021

Tags: Cloud, MDS, OCI

MySQL, the world’s most popular open source database, is available as a managed cloud service in Oracle Cloud Infrastructure (OCI) under the name of MySQL Database Service (MDS).

MySQL Database Service is a fully managed cloud service, 100% Developed, Managed and Supported by the MySQL Team.

This is the third episode of “Discovering MySQL Database Service“, a series of tutorials where I will show you, step by step, how to use MySQL Database Service and some other Oracle Cloud Infrastructure services.

Please also note that you can run this tutorial and thus try MySQL Database Service & the other Oracle Cloud Infrastructure services for free by starting your 30-day trial.

Planning episodes

Episode 3 – Create a Virtual Cloud Network

In the previous episode we’ve created our compartment, the foundation stone, of the architecture that we will build during this Discovering MySQL Database Service journey.

In this episode, we’ll see what is a Virtual Cloud Network (VCN) and how to create one and use it.

Virtual Cloud Network (VCN)

Oracle Virtual Cloud Networks (VCNs) provide customizable and private cloud networks in OCI.
Just like a traditional data center network, the VCN provides customers with complete control over their cloud networking environment. This includes assigning private IP address spaces, creating subnets and route tables, and configuring stateful firewalls.
For more information please see VCNs and Subnets.

Create a Virtual Cloud Network

If you are a network expert, OCI provides you the tools and the granularity to create your VCN.

if, like me, your are not an expert and/or if you want to quickly create a VCN with all the relevant component, the VCN Wizard is the right feature for you.

The VCN wizard will:

Creates a VCN.
Creates an internet gateway, NAT gateway, and service gateway for the VCN.
Creates a regional public subnet with routing to the internet gateway. Instances in a public subnet may optionally have public IP addresses.
Creates a regional private subnet with routing to the NAT gateway and service gateway (and therefore the Oracle Services Network). Instances in a private subnet cannot have public IP addresses.
Sets up basic security list rules for the two subnets, including SSH access.

Brilliant! This is exactly what we need.

Select Create VCN with Internet Connectivity from the VCN Wizard.
It creates a VCN with a public subnet that can be reached from the internet. Also creates a private subnet that can connect to the internet through a NAT gateway, and also privately connect to the Oracle Services Network.

To summarize we will have the following component:

VCN
Public Subnet
Private Subnet
Internet Gateway (IG)
NAT Gateway (NAT)
Service Gateway (SG)

You should provide a name for the VCN – Demo_VCN in this example.

Check that the compartment is the good one, the one we’ve created in the previous episode – DBA in this example.

The VCN and subnet default configuration should be fine.

After clicking on the Next button, you can review your configuration:

Click on Create when you are ready…

Et voilà!

You should end up with something that looks like:

Among others an IPv4 CIDR block (10.0.0.0/16) and 2 subnets (private in 10.0.1.0/24 and public in 10.0.0.0/24)

Well, that’s all for today!
In this episode we have created an already configured Virtual Cloud Network for use with MySQL DB Systems.

Next episode is:

Resources

Planning episodes

Watch my videos on my YouTube channel and subscribe.

Thanks for using HeatWave & MySQL!

3

Filed under:
Cloud, Howto, MDS, MySQL, Tuto by Olivier DASINI

Discovering MySQL Database Service – Episode 2 – Create a compartment

August 10, 2021

Tags: Cloud, MDS, OCI

MySQL, the world’s most popular open source database, is available as a managed cloud service in Oracle Cloud Infrastructure (OCI) under the name of MySQL Database Service (MDS).

MySQL Database Service is a fully managed cloud service, 100% Developed, Managed and Supported by the MySQL Team.

This is the second episode of “Discovering MySQL Database Service“, a series of tutorials where I will show you, step by step, how to use MySQL Database Service and some other Oracle Cloud Infrastructure services.

Please also note that you can run this tutorial and thus try MySQL Database Service & the other Oracle Cloud Infrastructure services for free by starting your 30-day trial.

Planning episodes

Episode 2 – Create a compartment

In the previous episode we’ve introduced the different components that will be used during this Discovering MySQL Database Service journey.

In this episode, we’ll see what is a compartment, how to create one and use it in order to create a MySQL DB system.

Compartment

A compartment is a collection of related resources that can be accessed only by groups that have been given permission by an administrator in your organization.

Actually, compartment is a powerful Oracle Cloud Infrastructure feature for security that would allow you to organize and isolate your cloud resources.

Learn more with this article and the documentation.

Please note that it is not mandatory to create compartments, it means that all your assets would be in the root compartment (the default) but it is obviously a bad practice 🙂

Create a compartment

Create a compartment is very simple.

Assuming that you already setup your tenancy —Setting Up Your Tenancy — you can use the console menu :

Go to: Identity & Security and Compartments

Fill Name and Description fields
Then push Create Compartment button

Here we go!
The compartment is now created.

Well, that’s all for today!
In this episode, following the best practices, we have created our compartment where we will store our resources. Thus we are able to enforce security isolation and access control.

Next episode is:

Create a Virtual Cloud Network

Resources

Planning episodes

Watch my videos on my YouTube channel and subscribe.

Thanks for using HeatWave & MySQL!

2

Filed under:
Cloud, Howto, MDS, MySQL, Tuto by Olivier DASINI

Discovering MySQL Database Service – Episode 1 – Introduction

August 3, 2021

Tags: Cloud, MDS, OCI

MySQL, the world’s most popular open source database, is available as a managed cloud service in Oracle Cloud Infrastructure (OCI) under the name of MySQL Database Service (MDS).

MySQL Database Service is a fully managed cloud service, 100% Developed, Managed and Supported by the MySQL Team.

This is the first episode of “Discovering MySQL Database Service“, a series of tutorials where I will show you, step by step, how to use MySQL Database Service and some other Oracle Cloud Infrastructure services.

Please also note that you can run this tutorial and thus try MySQL Database Service & the other Oracle Cloud Infrastructure services for free by starting your 30-day trial.

Planning episodes

Episode 1 – Introduction

Like any series, in this episode I’m going to give you some context and set up the characters.

Open Source Software

As you may know, Open Source Software has been mainstream in the enterprise for quite some time.
Open Source usage still increases and it also drives innovation (you can read this and this).
This is particularly the case for Open Source Databases, like MySQL obviously.

MySQL

MySQL has been around for a while now, take a look at this funny article.

Most popular database (Jetbrains survey – Stackoverflow survey) means you’ll find experts, experienced DBA, skilled developers, tons of resources… to help you to grow your business.

Popular also means that MySQL is widely used and used in many industries that run various workloads.

MySQL is the ideal database if your use case is:

Cloud Native Apps
Move Existing Workloads
Hybrid Cloud Flexibility
SaaS Applications

MySQL is the right database for on-premise, cloud and hybrid architectures.

Oracle Cloud Infrastructure (OCI)

Oracle Cloud Infrastructure (OCI) is a deep and broad platform of public cloud services that enables customers to build and run a wide range of applications in a scalable, secure, highly available, and high-performance environment.

Some highlights of this Next-generation cloud infrastructure:

Security First
Reduce costs and enhance performance
Best support for hybrid architectures

More details here

You can freely use Oracle Cloud Infrastructure services – including MySQL Database Service – by trying the Oracle Cloud Free Tier.

What’s included with Oracle Cloud Free Tier?

MySQL Database Service (MDS)

In Short: MDS = OCI + MySQL

Ok, please allow me to elaborate a little more 🙂

MySQL Database Service is a fully managed cloud service. Meaning that low-value tasks (no direct value for the business) are done by the MySQL team at OCI:

It is also worth noting that MDS is:

100% Developed by the MySQL team
100% Managed by the MySQL team
100% Supported by the MySQL Team, 24/7
100% Up to date with the latest security fixes
100% Built on MySQL Enterprise Edition
100% Compatible with on-premises MySQL
100% Compatible with Oracle technologies

In terms of features, you will obviously find the common one, High Availability, Replication, Migration tools,…
Learn more…

However it is exclusively in OCI that you will find HeatWave : the only MySQL cloud service with a massively-scalable integrated analytics engine.
In other words, HeatWave is a MySQL cloud service with an integrated, high-performance, in-memory query accelerator that enables customers to run sophisticated analytics directly against their operational MySQL databases, eliminating the need for complex, time-consuming, and expensive data movement and integration with a separate analytics database.
Learn more…

Last but not least, with MDS you’re going to save money!

Components

A DBMS is indeed part of a chain of components. In this series we will deal with different concepts and components.
Below the main ones:

Tenancy
Region
Availability Domain
Fault Domain
Groups
Policy
Compartment
Virtual Cloud Network (VCN)
Security Lists
Object Storage
MySQL Database Service (MDS)
Bastion
SSH Client
MySQL Shell
MySQL Workbench
Cloud Shell

Tenancy
A secure and isolated partition within Oracle Cloud Infrastructure (OCI) where you can create, organize, and administer your cloud resources. When you sign up for OCI, a tenancy is created for your company. Tenancy also refers to the root compartment that contains all of your organization’s compartments and other OCI resources.

Region
A collection of availability domains located in a single geographic location.

Availability Domain
One or more isolated, fault-tolerant Oracle data centers that host cloud resources such as instances, volumes, and subnets. A region contains one or more availability domains.

Fault Domain
A logical grouping of hardware and infrastructure within an availability domain. Fault domains isolate resources during hardware failure or unexpected software changes.

Groups
A collection of users who all need a particular type of access to a set of resources or compartment.

Policy
An Identity and Access Management (IAM) document that specifies who has what type of access to your resources. Policy can refer to several types of documents: an individual statement written in the policy language, a collection of statements in a single named “policy” document, and the overall body of policies that your organization uses to control access to resources.

Compartment
A collection of related resources that can be accessed only by groups that have been given permission by an administrator in your organization.

Virtual Cloud Network (VCN)
A virtual version of a traditional network — including CIDRs, subnets, route tables, and gateways — on which your instance runs.

Security Lists
A virtual firewalls for your Compute instances and other kinds of resources.

Object Storage
An internet-scale, high-performance storage platform that offers reliable and cost-efficient data durability.

MySQL Database Service (MDS)
A fully managed database service that lets developers quickly develop and deploy secure, cloud native applications using the world’s most popular open source database.

Bastion
Provide restricted and time-limited secure access to resources that don’t have public endpoints and require strict resource access controls.

SSH Client
Software program which uses the secure shell protocol to connect to a remote computer.

MySQL Shell
An interactive Javascript, Python, or SQL interface supporting development and administration for the MySQL Server

MySQL Workbench
A unified visual tool for database architects, developers, and DBAs that provides data modeling, SQL development, and comprehensive administration tools for server configuration, user administration, backup, and much more

Cloud Shell
A web browser-based terminal accessible from the Oracle Cloud Console. It provides access to a Linux shell, with a pre-authenticated Oracle Cloud Infrastructure CLI, a pre-authenticated Ansible installation, and other useful tools.

In this series of articles, I assume that you already setup your tenancy.
More information: Setting Up Your Tenancy

Well, that’s all for today!
I have introduced you to the different characters of this Discovering MySQL Database Service series.

Not so much technical contents so far, but I swear there will be more next time 🙂

Next episode is:

Create a compartment

Resources

Planning episodes

Watch my videos on my YouTube channel and subscribe.