With every new service on any cloud platform, the need to make connections is essential .This is the case with Oracle’s new MySQL Database Service (MDS). The MySQL Database Service (MDS) is the gateway to implementing MySQL Heatwave. Understanding how to connect from on-premises to the MDS is critical working with the platform.
In the case of MySQL Database Services, you have to connect via an SSH tunnel to interact with the service. If you are looking at doing this from the command line via a instance covered in a previous blog post (here). From the command line, you can use MySQL Shell to interact. At the same time, if you install MySQL Shell on your local machine, you would still have to navigate through an instance to connect to MySQL Database Service (MDS).
In this blog post, we’ll walk through how to setup third-party tools to allow you to connect and use MySQL Database Service (MDS).
Establish SSH Connection
The easiest way of doing this is via the “Connect to a database” setup process. In this case, you’ll want to setup a new connection for a MySQL 8 database.
Then on the “Connection Settings” screen, select “SSH”. Then select the pencil icon to open the “Profile”editor.
The “Profile” editor will allow you to create an SSH Tunnel Profile that can be used multiple times. Plus it saves time when wanting to connect to more than one MySQL Database Service (MDS). Notice that we are setting the User Name to “OPC”, providing the Public Key that is needed to connect to the instance, and the Public IP address of the instance that will allow passthrough connections to MySQL Database Service.
After testing the connection, the “Network Profile” screen can be closed.
Connecting to MySQL Database Service (MDS)
With the “Network Profile” configured, the connection to MDS can be established. After closing the “Network Profile” screen, DBeaver returns you back to the “Connection Settings” page for MySQL 8.0. Fill out the “Main” tab as you would do for any standard MySQL Database.
With the “Main” table filled out, navigate over to the “SSH” tab and select the profile that was created for the SSH Tunnel.
At this point, you can either do a “Test Connection” to confirm connection or click “Finish”.
Resolving Public Key Retrieval issue
After the connection is setup and tested, you may run into a “Public Key Retrieval” issue. This issue is security issue with OCI. To resolve this, update the “Driver Properties” of the connection. By default the connection is set to FALSE, to resolve the issue set to TRUE.
After changing the settings to “allowPublicKeyRetrieval”, the connection to the MySQL Database Service (MDS) is successful.
Happy MySQL working within Oracle Cloud Infrastructure (OCI).