Connect to OCI MySQL Heatwave from MySQL Workbench

 - Introduction: 

• MySQL Heatwave is a fully managed cloud database service (PaaS) that combines transactions, analytics, and machine learning services into one MySQL Database, without the complexity, latency, and cost of ETL duplication.
• The database system endpoint is where clients such as MySQL Shell, MySQL Client, MySQL Workbench, and your applications can connect to Oracle MySQL Heatwave database.
• MySQL Heatwave Cloud Database System endpoint has a private IP address. You cannot connect directly to it from a remote IP address. There is no SSH access to DB system endpoint. Instead, you need to connect to the endpoint by one of these methods: 
• Connect from an Oracle OCI compute instance that is on the same OCI VCN as the database system. With this method, you install the client software on the compute instance like MySQL Shell.
• Use a secured access service, such as a VPN connection or Oracle Cloud Infrastructure’s Bastion service, to bridge from your local network to the OCI VCN with access to the database system. With this method, you install the client software on your local machine, and connect directly to the database system endpoint.
• What is MySQL Workbench?. MySQL Workbench is a 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.

In this blog, we will demonstrate the steps to connect to OCI MySQL Heatwave database from your local machine using MySQL Workbench.

 

- Prerequisites:

• MySQL Workbench 8 installed on your local machine.
• An Oracle cloud free trial or paid account.
• A VCN with public subnet, private subnet, security lists, and route tables.
• One OCI compute instance VM located in VCN’s public subnet with API RSA private key.
• One MySQL Heatwave database system located in VCN's private subnet.
• Private subnet security list has below Ingress and Egress rules to allow access from public subnet to private subnet where MySQL Heatwave database system is located.

Rule Type	Source	IP Protocol	Source Port Range	Destination Port Range
Ingress	<Private Subnet CIDR>	TCP	All	3306 (MySQL default port)
Egress	<Private Subnet CIDR>	TCP	All	All

• Public subnet security list has below Ingress and Egress rules to allow access from Internet to public subnet where VM (bastion) is located. 

Rule Type	Source	IP Protocol	Source Port Range	Destination Port Range
Ingress	0.0.0.0/0	TCP	All	22
Egress	0.0.0.0/0	TCP	All	All

 Step #1: Start MySQL Workbench on your local machine

Step #2: Create MySQL Connection to MySQL Heatwave

1. In Welcome page, click + sign to create new MySQL connection.

2. In "Setup New Connection" dialog box select "Connection Method" = "Standard TCP/IP over SSH".

3. Use connection parameters as highlighted in screenshot below then click "Test connection" button.

4. Enter DB user password then click "OK" button.

5. Click "OK" button to save the connection. 

6. Click new connection name to connect to MySQL database.

7. Here we go !. We are connected now to OCI MySQL Heatwave database.

Thanks for reading. Hope you like it !!!.