Thursday, September 14, 2023

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).

Thursday, January 19, 2023

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 & SecurityVault 
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 .