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 ninth 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 9 – Connect to MySQL Database Service Using MySQL Workbench

In the previous episode we’ve seen how to connect to our MySQL Database Service instance with MySQL Shell using a SSH port forwarding from the OCI Bastion service.
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 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.

MySQL Workbench

MySQL Workbench is a GUI client for MySQL. It’s an unified visual tool for database architects, developers, and DBAs.

MySQL Workbench provides data modeling, SQL development, and comprehensive administration tools for server configuration, user administration, backup, and much more.

It is available on Windows, GNU Linux and Mac OS X.
You can download the community version here.

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 MySQL Workbench.

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 regarding your OS) will deliver you.

$ 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 if not 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 Workbench

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

Open MySQL Workbench.
Go to Database / Manage Connections…

The Manage Server Connections appears, then click the New button (at the bottom left).

We can now setup our MDS connection in the form.

The main information must be entered in the Connection tab:

• Connection Name : MDS1 (sorry I’m very bad at naming)
• Connection Method : Standard (TCP/IP)
• Hostname or IP to connect to MDS (through the SSH tunnel) : 127.0.0.1 (or localhost)
• Port to connect to MDS (through the SSH tunnel) : 3333
• MDS Username : admin
• MDS Password : (come on! I don’t know your password)

Test the connection:

Can you see “Successfully made the MySQL connection“. ?

If the answer is no, please check that you SSH tunnel is still enable, the host Bastion could have closed the connection.
Also check your MDS credentials and host and port.

You can close the form. Your connection is now available in MySQL Workbench.

You can find it in the connection main page or go to Database / Connect to database…

And in Stored Connection drop list select your MDS connection

Then click OK.

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 MySQL Workbench through a SSH tunnel (port forwarding session) using the OCI Bastion service.

Next episode is:

Connect to MySQL Database Service Using OCI Cloud Shell

Resources

• MySQL Database Service (MDS)
• Documentation of MySQL Database Service
• Oracle Cloud Infrastructure (OCI)
• Oracle Cloud Infrastructure Glossary
• Oracle Cloud Free Tier
• Bastion
• MySQL Workbench
• The MySQL Workbench Developer Central Site
• 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