Using MySQL Enterprise Monitor to monitor MySQL Heatwave Service

This tutorials walk through the process to monitor the MySQL Heatwave Service using MySQL Enterprise Monitor(MEM) , Below are steps you needed to follow. 

Prerequisite:-  1. Installed MySQL Enterprise Monitor(MEM).  2. Putty - https://www.putty.org/  3. Up & Running MySQL Heatwave Service on Oracle Cloud Infrastructure (OCI).  4. Up and Running Compute instance in OCI.  Note:- To download MySQL Enterprise Monitor trail version by below links:-  https://edelivery.oracle.com/  Note:- download only service manager , AGENT is not required for MySQL Heatwave Service

How to Install MySQL Enterprise Monitor?  Below steps is to be followed (assuming installation host is Linux- OL/RHEL/CentOS)  Installation of Service Manager on Compute Instance of OCI  Step 1:-goto location of downloaded location of service manager & Give the persmission  #cd /home/opc/bootcamp/Monitor/  #chmod +x mysqlmonitor-8.0.31.1413-linux-x86_64-installer.bin  Step 2:- Execute the file  #./mysqlmonitor-8.0.31.1413-linux-x86_64-installer.bin  Step 3:- Follow the instructions prompted on screen, always keep default value so keep pressing ENTER  Please keep all password as example like this:- MySQL8.0 # one Caps , Number ,Special Character ,Letters, 8 long  Step 4:- To Check Service Running or not:-  #service mysql-monitor-server status  #####In case any error occurred below while installation , please execute below to fix it  Error: error while loading  shared libraries: libtinfo.so.5: cannot open shared object file: No such file  Fix:  #sudo yum install ncurses-compat-libs  Step 5 : Login to MySQL Enterprise Monitor(MEM)  To Login Web Browser , assume google chrome running on your local machine  make sure IP address is whitelisted to access from your local machine. https://129.154.228.159:18443/ #<use your public IP address>  Create User “Manager” Role:- manager/manager  Create User “Agent” Role:- agent/agent  Open MySQL Enterprise Monitor(MEM)  Go to Configuration >>MySQL Instances >> Add MySQL Instance  Monitoring From:- Built-in Agent  Connection Using:- TCP/IP  Instance Address:- You needed to give MySQL Heatwave Service Private IP Address  Admin User :- MySQL Heatwave Service User Name  Port :- MySQL Heatwave Port number  MySQL User Name :- <give user name of MySQL database>  Admin Password :- <give the password>*********  Auto-Create Less Privileged Users : NO  Enter above details as below.  Once connection to heatwave service established then below output look like. Visual Query Analysis  Monitor real-time query performance, check execution statistics, filter and pinpoint SQL code that is causing a slow-down. Using the Performance Schema with MySQL Server 5.6, data is gather  Find and Fix Expensive Queries  Correlated graphs enable developers and DBAs to compare execution parameters, such as the server load, thread statistics, or RAM usage against the queries that were executing at that time. Simply highlight a time slice on a graph to find the most expensive queries and locate a potential cause for the larger performance issue.  To review the list of queries running from MySQL Heatwave Service , click on "Query" tab to review it  and click on each individual queries to get deep insight about what's going on with the queries. some of screenshot as below Conclusion  MySQL Enterprise Monitor provides real-time visibility into the performance and availability of all your MySQL databases  including hybrid environment (OnP and Cloud) and Start monitoring MySQL within 10 minutes with zero configuration and no agents.  MySQL Heatwave Database Service is a fully managed database service that enables organizations to deploy cloud-native applications using the world's most popular open source database.  It is 100% developed, managed and supported by the MySQL Team.  You will get one database for OLTP ,OLAP,ML,Lakehouse which is very unique solutions available in multi-cloud environment(OCI ,AWS,Azure).

Using Oracle APEX with MySQL Heatwave Service for Data Visualisation and Reporting

 

Using ORACLE APEX to Build and Visualize and Analyse MySQL Database

 Main objective of this blog is to connect MDS with APEX tool and visualize and get insights of MySQL data with low code solution called Oracle APEX. 

Below is the glimpse of topic to be covered in this blog 

1. What is all about Oracle Apex 

2. What are benefit of using Oracle Apex 

3. Installation and Configure Oracle APEX with MDS 

4. Conclusion 

Let’s get started… 

What is all about Oracle Apex ? 

It is database centric web application development framework which you can use to Develop desktop & mobile web apps ,visualize and maintain data, leverage SQL skills and database capabilities. 
Basically,Oracle APEX (APEX) is a low-code development platform that enables you to build scalable, secure enterprise applications with world-class features that can be deployed anywhere. 
Oracle APEX is also certified with MySQL database service(MDS) which help MySQL customer to build modern, beautiful, responsive applications without the need to become specialised experts. 
That’s a another benefit MDS customer get on Oracle Cloud Infrastructure(OCI) to leverages home grown services. 

 In this blog I am referring to fully managed APEX offering which runs on OCI 

Configuration, patching, monitoring, and upgrading of all Oracle APEX components is fully managed by Oracle, leaving you free to focus on developing your solutions and solving your business problems. 

With Oracle APEX and low code, your organization can be more agile and develop solutions faster, for less cost, and with greater consistency. 

You can adapt to changing requirements with ease. And you can empower professional developers and everyone else in your organisation to be a part of the solution. 

More info :- 

https://docs.oracle.com/en/cloud/paas/autonomous-database/adbsa/apex-intro.html#GUID-F275EA9F-F9A4-4A72-B777-5548362FDDA5

High Level Architecture for MDS+ APEX 

APEX access data from MDS via OCI database tools service , actually APEX do require “REST enabled SQL end point” to get data from MDS , that is only possible at this current release by using “middle man” , OCI Database Tools Service. 

Once data get accessed from DB service then you’re free to customize ,developed interactive user friendly dashboard to visualize and analyze data. 

What are benefit of using Oracle Apex ? 

Oracle APEX is to empower developers to easily build compelling apps with superior functionality, performance, and end user experience. In order to deliver on this mission, Oracle APEX aims to eliminate the enormous complexities of developing and deploying enterprise apps. 
Here are a few reasons why choosing Oracle APEX makes sense for your next development project. 
- Eliminating Complexity 
- Simple Development 
- Secure 
- Powerful and Proven , Portable 

More info:- https://apex.oracle.com/en/platform/why-oracle-apex/ 

Installation and Configure Oracle APEX with MDS 

Prerequisite 

To connect APEX with MDS you must have to first configure 
1. Access to Virtual Cloud Network(VCN) 
2. MySQL Database Service(MDS) instance up & running 
3. database tools service which will provide Rest enabled SQL end points which is required during configuration of APEX service. 

Setup OCI network 

Please review below blog 

https://docs.oracle.com/en-us/iaas/mysql-database/doc/creating-virtual-cloud-network.html#GUID-A5D4A49C-53AD-4A6B-978A-5A7435E73762 

Setup MySQL Database Service(MDS) 

Please follow below tutorial 

https://docs.oracle.com/en-us/iaas/mysql-database/doc/creating-db-system1.html#GUID-F2CB52E0-6C6A-407F-A8AC-D82371186D2D 

Setup Database Tools connection with MDS 

Below is the step to follow from OCI console page:- 
1. Create Vault >> Identity & Security→Vault 
2. Create Master Encryption Keys inside Vault 
3. Create Private End Point 
4. Create Connection 
To get started click as below 

Here is the final snap of the connection details 

Make a note of OCID ,we do require during APEX configuration . 

Let’s Getting Started with APEX 

Login into APEX console 

Click on “Launch APEX” 

Click on workspace utilities from App Builder >> Web Credentials. 

Web Credentials 
Store authentication credentials for external REST services or REST Enabled SQL services. 
The Client Secret will be stored encrypted, and it can only be used by Oracle APEX and cannot be retrieved in clear text. 
Also, credential information will not be included in application export files. After importing an application into the target workspace, prompts will be displayed to re-enter the credentials. 

To get the details of OCI User ID ,OCI Private Key , Tenancy ID , Public Key Fingerprint  Click on user profile , you see right top >user settings>API KEY Create a “Rest Enabled SQL Services” in APEX App Builder  Example of an endpoint URL:  https://sql.dbtools.<OCI Region >.oci.oraclecloud.com/20201005/ords/<OCID of of Database Tools Connection Details >  you may get OCID for database tools connection details from navigating to from Database Tools>>Connections>>Connection Details>>OCID  Create an APEX application with MDS  Conclusion  Using Managed APEX Service together with MDS, you can quickly build nice-looking web applications through RESTful API’s.  APEX easily able to connect with MDS and build next gen dashboard and analyze the data from the MySQL table .