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.

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:

Connect to MySQL Database Service Using MySQL Workbench

Resources

• MySQL Database Service (MDS)
• Documentation of MySQL Database Service
• Oracle Cloud Infrastructure (OCI)
• Oracle Cloud Infrastructure Glossary
• Oracle Cloud Free Tier
• Bastion
• MySQL Shell
• MySQL Shell Utilities
• 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