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 tenth 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.

Episode 10 – Connect to MySQL Database Service Using OCI Cloud Shell

In the previous episode we’ve seen how to connect to our MySQL Database Service instance with MySQL Workbench using a SSH port forwarding from the OCI Bastion service.

And in episode 8 – Discovering MySQL Database Service – Episode 8 – Connect to MySQL Database Service Using MySQL Shell – we’ve seen how to connect to our MySQL Database Service instance with MySQL Shell using here again a SSH port forwarding from the OCI Bastion service.

Two more steps to our Discovering MySQL Database Service journey.

In this episode, we’ll learn how to connect to our MySQL Database Service instance using the Oracle Cloud Infrastructure Cloud Shell.
It provides a pre-authenticated Oracle Cloud Infrastructure CLI and preinstalled developer tools for easily managing Oracle Cloud resources.

Cloud Shell

Oracle Cloud Infrastructure Cloud Shell gives you access to an always available Linux shell directly in the Oracle Cloud Infrastructure Console.

You can use the shell to interact with resources like MySQL Database Service, Oracle Container Engine for Kubernetes cluster, Oracle Autonomous Database, …

Cloud Shell provides:

• An ephemeral machine to use as a host for a Linux shell, pre-configured with the latest version of the OCI Command Line Interface (CLI) and a number of useful tools
• 5GB of storage for your home directory
• A persistent frame of the Console which stays active as you navigate to different pages of the console

For more information, please see the Cloud Shell documentation 

Create a SSH port forwarding session

In episode 7 – 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 Cloud Shell. However if you remember well the CIDR block allowlist is set to your location (IP of your office, of your home if you WFH, …), but not the Cloud Shell IP.

So many possibilities, we can setup this bastion only for Cloud Shell, we can add another bastion, dedicated for the Cloud Shell, we can update the current bastion. I’ll go for this last option.

So the plan is to connect to Cloud Shell and get the IP address.

Open the OCI console and click on the Cloud Shell icon, on the top right corner:

It will open the Cloud Shell in the bottom of the browser (the first time it can takes some time).

To get the IP address run the following command:

$ curl ifconfig.me

We can now update (or setup) the Bastion.

Go to the OCI console, in the 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 – or create a new one if you prefer/can.

Click on Edit

Update the CIDR Block Allowlist with the IP that you got when you executed curl ifconfig.me.
Don’t forget the /32 (CIDR block)

Then saves the changes.

We can now create a session and then get the SSH port forwarding command generated by OCI.

Click on Create Session

And like we have seen in Discovering MySQL Database Service – Episode 7 – Use a Bastion SSH port forwarding session, create the session with the relevant parameters:

• 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.

Then you can either generate (a new) SSH key pair or choose the public key we’ve created in Discovering MySQL Database Service – Episode 7 – Use a Bastion SSH port forwarding session.

The private SSH key must be download into the Cloud Shell and be protected (chmod 600)

Drag and drop your private SSH key to the Cloud Shell and execute chmod 600 on the file

$ chmod 600 myPrivateKey.key

For the last steps we must now get the SSH port forwarding command generated by OCI and copy/paste it to the Cloud Shell.

To do so, when the session is active, 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 Cloud 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’ve just downloaded.

Replace <localPort> with a free port on the Cloud Shell – 3306 (and 33060) should be free.

Example:

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

Before run the command add an ampersand (&) at the end.

FYI, the error message “bind: Cannot assign requested address” is not a problem, this is just because the Cloud Shell tries to bind on ipv6 too.
If you want to avoid it, just add -4 between ssh and -i like this: ssh -4 -i

Connect to MDS using MySQL Shell in Cloud Shell

As you can see in the picture above, MySQL Shell is part of Cloud Shell.

So we will use MySQL Shell to connect to our MySQL instance through the SSH tunnel using MySQL

I recommend you to read Discovering MySQL Database Service – Episode 8 – Connect to MySQL Database Service Using MySQL Shell for more details.

Basically you’ll need the following information for the connection:

• 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) : 3306
• Your MDS password

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

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 the Oracle Cloud Infrastructure Cloud Shell through a SSH tunnel (port forwarding session) using the OCI Bastion service.

Next episode is:

MySQL Database Service Features Overview

Resources

• MySQL Database Service (MDS)
• Documentation of MySQL Database Service
• Oracle Cloud Infrastructure (OCI)
• Oracle Cloud Infrastructure Glossary
• Oracle Cloud Free Tier
• Bastion
• Oracle Cloud Infrastructure Cloud Shell
• Announcing Oracle Cloud Shell
• Cloud Shell Documentation
• Using OCI Cloud Shell & Bastion with MySQL Database Service
• MySQL — The world’s most popular open source database

Follow me on Linkedin

Watch my videos on my YouTube channel and subscribe.

My Slideshare account.

My Speaker Deck account.

Thanks for using HeatWave & MySQL!

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