This describes the discovery and monitoring setup for the MySQL database.

Introduction

The objective of this document is to help completely new users create query based (MySQL) custom monitors development in the OpsRamp. This document provides a complete user guide to implement MySQL query based metrics and create monitors and templates. It also explains prerequisites, limitations, troubleshooting steps, and FAQs related to MySQL DataBase Monitoring.

What is MySQL Database Monitoring?

MySQL Database Monitoring offers readily available performance metrics, which enables users to effortlessly visualize the health and availability of their MySQL Database environment. Users can access and view the status updates and performance metrics specific to MySQL servers through the OpsRamp portal.

How MySQL Database Monitoring works

This monitoring functionality operates to executing MySQL queries on target MySQL servers via a Gateway using JDBC connections. Users must adhere to the following prerequisites.

Prerequisites

• Database and port(3306) that can be accessible by the gateway
• A gateway management profile mapped to a resource.
• Credentials with type set to Database before assigning a template to a resource. The Port, Connection Time-out, and Is Secure values are not used while creating credentials.

Database discovery and monitoring

The administrator can deploy an agent or gateway to support MySQL server agent-based or agentless monitoring respectively.

Discovery with the agent

The agent auto-discovers the MySQL database and collects the following metric information:

• MySQL-Engine information
• MySQL-Recommendations
• MySQL-Database information
• MySQL-Performance information
• MySQL-Slave information
• MySQL-Server information
• MySQL-Variable information
• MySQL-Status information

You can also apply agent-based templates to initiate monitoring.

Discovery with the gateway

The gateway establishes a connection to the database using Java Database Connectivity (JDBC) Java API and collects monitoring metrics using SQL queries. To monitor the MySQL database, install gateway version 5.0.0 or later.

Use one of the following mechanisms to discover MySQL servers to add them to your inventory:

• WMI
• SSH
• SNMP

Optionally, add a database server manually to the infrastructure inventory:

1. Select Infrastructure and click Resources.
2. Click the Settings icon on the top-right and click Add.
3. On the Add Device page, enter the Device Type and any other information you want to enter.
4. Click Save.

Prepare the device to start monitoring

1. Associate appropriate database credentials to the discovered database.
2. Assign one or more database-specific global monitors or templates on the device.
   Optionally, you can create custom metrics or monitors using SQL queries and assign them to the database.

To track monitoring of MySQL database in your cloud environment, see Monitoring Cloud Database using Gateway.

How to develop & create MySQL metric

OpsRamp comes with a predefined set of MySQL metrics. However, for users who want any customization and the ability to create their own monitoring solutions, we offered UI-based support for creating MySQL metrics.

To prepare a MySQL query based metric, users must return max one or two fields from the query. Follow the below usecases along with examples to understand how to write queries to monitor MySQL environment.

Prepare a MySQL metric query

Metric with no components: If the user wants to prepare a metric query for the direct value without any components then user should return only one column from the query result. The value will be consider as metric value.

Example: To monitor the free pages count of the Innodb buffer pool.

Query: show status where variable_name like “Innodb_buffer_pool_pages_free”

The above metric query output is the value of the Innodb buffer pool pages free. The framework should consider the value as metric value.

Example: To monitor count of Deleted commands (per second).

Query - show global status like ‘Com_delete’

Above metric query output is the value of the count of deleted commands, Here our framework should consider the value as metric value.

Metric with multiple components: If the user wants to prepare a metric query for multiple component based metrics, then the user should return to two columns from the query result. Here column 1 will be considered as component name and column 2 will be considered as metric value.

Example: To monitor the disk space usage for databases and tables in MySQL.

Query - SELECT table_name, round(((data_length + index_length) / 1024 / 1024), 2) AS size_in_mb FROM information_schema.TABLES

Here framework should consider first column table_schema as component name and second column total_mb as metric value.

Calculating and Formatting Metric Values

To calculate metric values like utilization, memory usage conversions, or time conversions.

Example: To monitor the uptime (in minutes) of the MySQL.

Query: select (VARIABLE_VALUE/60) as uptime from information_schema.GLOBAL_STATUS where VARIABLE_NAME=‘Uptime’;

Example: To monitors the size (MB) of each table in all databases.

Query: SELECT table_name, ROUND(((data_length + index_length) / 1024 / 1024), 2) AS size_in_mb FROM information_schema.TABLES

Enumerated Mapping and Alerting

Enumerated Map: OpsRamp monitoring framework exclusively supports integer-type metric values. Therefore, users are required to return metric values as integers rather than strings or other data types. If users wish to return metric values as strings, an option for enumerated mappings is available. Below are examples providing further clarification.

Example: To monitor the running status of MySQL Slave, it’s important to note that MySQL Slave have direct possible states. In such cases, users must prepare a query to directly return the status value.

Query: MySQL_slave_Sql_Running_Status

When creating a metric, users are required to select the Datapoint Value Conversion option as Enumerated Map and provide integer value mappings for each possible state.

Additionally, there is an option to enable Use formatted value in Alerts & Graphs. If users opt to enable this feature, the enumerated mapping values provided will affect Alerts & Graphs. Otherwise, these values will not be reflected anywhere.

Alerting: If the metric has an enumerated map enabled, users must follow the instructions below to select critical and warning alert conditions for alert creation.

• As outlined above, the prepared query returns the metric value as a string type. In accordance with this, users need to add N-number of enum maps for each possible state to different integer values.
• To enable critical or warning alerts, users must take into account the possible string-type values returned from the query.
• If the enumerated map (string to integer) is enabled, refer to the screenshot below to enabled critical alerts:

  

• If the enumerated map (integer to string) is enabled, refer to the screenshot below to enabled critical alerts:

  

How to Create a MySQL Metric

1. Login to OpsRamp platform and navigate to Setup from top menu. Navigate to Monitoring from the side bar menu and click on Metrics.

2. Click on +Add to create a new metric. The Create Metric screen appears with the following parameter fields:

   • Metric Scope: Select either Service Provider Metric or Partner or Client Metric. Based on your access level and role, you might see this menu slightly different. If you have chosen Partner or Client Metric you will be prompted to choose a Partner/Client from contextual drop down which is dynamically populated.
   • Adapter Type: Select Application from this dropdown.
   • Application Type: Select **MySQL from the dropdown.

     

   • Name: The metric name.
   • Tag Name: This field is automatically populated with the metric name.
   • Display Name: The metric display name.
   • Description: A description of the metric.
   • SQL Query: Provide the prepared MySQL query.

     

   • Data Point Type: Choose a type from the dropdown list.
     • Counter Delta: It calculates delta on top of metric value. Counter Delta = (Current poll value - Prev poll value) Note: If the result is less than zero then it returns zero.
     • Counter Rate: It calculates rate on top of metric value. Counter Rate = (Current poll value - Prev poll value) / (Current poll time - Prev poll time) Note: If the result is less than zero then it returns zero.
     • Derive Delta: No support in both agent and gateway.
     • Derive Rate: No support in both agent and gateway.
     • Gauge: Returns direct metric value, which is returning from the script.
     • Rate: It calculates rate on top of metric value. Rate = (Current poll value - Prev poll value) / (Current poll time - Prev poll time) Note: If the result is less than zero then it returns negative value.
     • Delta: It calculates delta on top of metric value. Delta = (Current poll value - Prev poll value) Note: If the result is less than zero then it returns negative value.
     • None: Same as gauge.
   • Units: Choose a suitable unit from this dropdown. For status related metrics that don’t require any units, select None.
   • Unit Multiplication Factor: Value to multiply the metric by. As this is status monitor, it doesn’t required this factor value and can use the default value 1.0.
   • Datapoint value conversion: Choose a suitable option from any of the below two.
     • Value: Choose this option when no conversion required on the metric value. This is the default value.
     • Enumerated Map: Choose this option when enumeration based conversion is required. For status-related metrics, it’s common for queries to return string-type values. However, our monitoring graphs only support integer values. Therefore, users need to provide mappings for all possible string-type values.

       

   • Metric Processing: Choose from the dropdown.
     • Graph: Select this if a graph is needed, but no alerting is needed.
     • Notification: Select if alerting is only needed, but no graphing is needed.
     • Graph and Notification: Select if both graphing and alerting is needed.
     • None: Select if both graphing and alerting is not needed.

How to Create a MySQL Monitor

1. Login to the OpsRamp platform and .lick on Setup from top menu. Navigate to Monitoring from the side bar menu and click on Monitors.
2. Click on +Add to create a new monitor. The Create a Monitor screen appears.
3. Configure the parameters on the screen:
   • Monitor Scope: Select either Service Provider Monitor or Partner or Client Monitor. Based on your access level and role, you might see this menu slightly different. If you have chosen to create the script at Partner or Client level you will be prompted to choose a Partner/Client from contextual drop down which is dynamically populated.
   • Adapter Type: Select Application from this dropdown.
   • Application Type: Select MySQL from this dropdown.
   • Name: Provide a name for the monitor.
   • Description: Provide a description for the monitor.
   • Metrics: Click on Add and select all the metrics created in the previous section.
   • Configuration Parameters: By default, you will see below 4 configuration parameters:
     • collector.application.type – Proceed with the default values, i.e MySQL
     • connection.timeout.ms – Proceed with the default value, i.e 15000 ms (If require, you can increase it but it should be well within the monitor frequency/poll time)
     • MySQL.service.port - Proceed with the default value, i.e 3306 (If target MySQL environment running on different port then change it to right port number)
     • MySQL.service.transport - Select correct secure/insecure transport type.
     • MySQL.dbname.list - Provide database instance name here.
       • If target MySQL environment have multiple database instances running on same port, provide input like InstanceName1, InstanceName2, etc.
       • If target MySQL environment have multiple database instances and each one running on different ports, provide input like InstanceName1:Port1, InstanceName2:Port2, etc.

How to create MySql Database Monitoring Template

1. Login to OpsRamp platform and click on Setup from top menu and expand Monitoring from the side bar menu and click on Templates.

2. Click on +Add to create a new template.

3. Configure the following fields on the Monitor Template screen:

Select Template Scope: Select either Service Provider Template or Partner or Client Templates. Based on your access level and role, you might see this menu slightly different. If you have chosen to create Partner or Client level you will be prompted to choose a Partner/Client from contextual drop down which is dynamically populated.

Collector Type: Choose Gateway (Note - DB Query based monitoring supports Gateway collector type only).

Monitor Type: For Gateway, select Monitors radio button.

Applicable For: Always select Device

Template Name: Provide a meaningful template name.

Description: Provide an elaborate description about this template.

Leave remaining fields as is and go to the bottom, Click on +Add against Monitors.

Select Frequency: Select monitoring frequency based on metric requirement

Monitor Definition: Choose monitor which you created in the previous section

When To Alert: Proceed with the default option. i.e Breach of a Threshold

Then click Add to save the template.

Assign templates from setup

Assign MySQL templates to one or more resources for a selected client and change the configuration parameters while assigning templates. For more information, see Assign Templates from Setup.

Assign templates at the device level

Applying MySQL templates at the device level helps you to assign one or more templates to a specific resource. You can change the configuration parameter default values while assigning the templates. For more information, see Assign Templates at the Device Level.

Template configuration parameters:

Assign template from device management policies

Device management policies help manage resources. You can assign monitoring templates, knowledge base articles, and custom attributes using device management policies. The device management policy can be applied to one or a set of resources. For more information, see Create Policies.

View resource metrics

The gateway monitors the application using the assigned templates and displays the results in graphical format. To view resource metrics, click the database resource name > resource Details > Metrics.

Points to Consider

Consider the following points when configuring MySQL Query Based Monitoring:

• Gateway collector type only supports MySQL query based monitoring.
• To assign the right MySQL database credentials (which are have proper privileges to execute metric queries) on the target MySQL server.
• If the user has instance level authentication, they need to assign all credential sets on the target MySQL server. OpsRamp Gateway will take care to connect the different instances of the MySQL server.
• The user needs to provide an correct instanceName(s) to connect the MySQL DB.
• Provide right transport type like secure or insecure
• While preparing MySQL query, User should not consider frequently changed fields as component names (like MySQL session name, userName, ..etc) or any combinations (like databaseName_sessionId, databaseName+userId, …etc).
• If any database connectivity issues occur from the Gateway, the Framework generates alerts such as ‘oracledb.auth.error.’ However, it does not generate alerts for query syntax errors, empty data, or access denied issues.

Troubleshooting

Usecase 1 - Failed to get latest metric values/Gateway is offline

When the user applies a MySQL monitoring template on devices and encounter the message “Failed to get latest metric values/Gateway is offline” while fetching latest metric data, follow these troubleshooting steps:

1. Navigate to the Overview section of the device (Infrastructure > Resources > Search using IP or Device Name) and on that device, ensure that the Gateway is online (indicated by blue color as shown below).

2. Identify whether a template is global or customer written.
3. Review any alerts associated with the template on the Overview page of the device. Alternatively, you can navigate to Command Center > Alerts page and filter using the specific server name or Ip address. The alert format is static with the metricName as MySQL.auth.error and the component name like .
4. Check key points related to MySQL monitoring.
5. Access the gateway. This can be done using SSH or by logging in using the browser console from the device page. You can navigate to the console by clicking on the console and filling in the necessary fields.

6. Check database server IpAddress & port connectivity

   • Login to the gateway
     • Refer Step-5: Access gateway
   • Type ping and press enter, then telnet as below:

   

7. Connect to the GCLI terminal in gateway:

   • Classic Gateway:
     • Login to the gateway
       • Refer Step-5: Access gateway
     • Type gcli and press enter, then gcli terminal will open.
   • Nextgen Gateway:
     • Login to the gateway
       • Refer Step-5: Access gateway
     • Execute command kubectl exec -it nextgen-gw-0 -c vprobe -n – bash Note: Here we need to replace the if applicable, otherwise execute. kubectl exec -it nextgen-gw-0 -c vprobe -- bash
     • Type gcli and press enter, then gcli terminal will open

8. Access MySQL logs By default the OpsRamp gateway capturing error logs, If user want to enable all available logs which are related to MySQL DB then follow below steps:

   • Access gcli mode by running: gcli
     • Refer Step-7: Connect to GCLI terminal in gateway
   • Use this command to enable debug logs: flag add MySQL.log on 30
   • Exit gcli prompt and run this command to observe logs: sudo tail -100f /var/log/app/vprobe.log
   • In Nextgen Gateway, If User want to copy files from the vprobe container to the gateway cli, execute the following command after exiting the container kubectl cp <namespace>/nextgen-gw-0:<source_path> <dest_path> -c vprobe Example: kubectl cp nextgen-gw-0:/var/log/app/vprobe.log /home/gateway-admin/vprobe.log -c vprobe
   • Download files from the gateway cli.

9. Execute MySQL query manually from the GCLI on the target machine.

   • Access gcli mode by running: gcli
     • Refer Step-7: Connect to GCLI terminal in gateway
   • Execute MySQL query via gcli prompt: db MySQL <Ipaddress> <username> <password> <port> <InstanceName> 15000 10000 insecure <Yes/No> <"Query">

10. If you are unable to determine the root cause of the issue from the logs or query output, or if different exceptions occur when executing an MySQL query, please raise a case and attach the relevant logs and manual query output.

Usecase 2 - Graph data is not populating for specific or all metrics

1. Validate whether the metric is retrieving data from end device by checking latest snapshot data., if not refer to Usecase 1.
2. Check if the graph is enabled or not at metric level. If its enabled, check whether the data got from latest snapshot data is a string. If it’s a string, then check if Enum Mapping is defined for that string at metric level.
3. If Enum mapping is not defined for that particular string and it’s a global template, then raise a case while attaching screenshots of latest snapshot data, Enum Mapping defined at Metric.
4. If Enum mapping is not defined for that particular string and it’s a customer written template, then suggest customer to edit the metric and add this new state in State Descriptions field.

Usecase 3 - User is observing gaps in metric graphs.

This issue might be due to the following reasons:

• Gateway going offline at that time
• The device is not reachable and port connectivity issues.
• There may be no data available for the metric on the device at that time.
• Please check the debug level logs to cross verify if Gateway was offline at that time.

If you do not find any logs related to those, then raise a case while attaching logs, to analyze query output at those specific times when graph is not populating.

Usecase 4 - Alerts not getting generated on resource for particular metric.

Check latest snapshot data to see if we are retrieving any data from device for that metric and also verify the thresholds defined for the metric.

If the latest snapshot data is also not coming for that metric, then execute the command or script manually on device, to see if any data exists for that metric.

Usecase 5 - Alerts generated do not align with the defined alert thresholds

Refer to the Alerts Hierarchy outlined below:

Alert Thresholds Precedence Order: Template level threshold > Device level threshold > Component level threshold

Alert thresholds follow a hierarchical order, starting from the component level and moving up to the template level. Each level can override the thresholds set at the previous level. Component-level settings override those set at the device level, and device-level settings take precedence over template-level settings.

This hierarchy ensures that monitoring configurations can be finely tuned at various levels of the system, allowing for granular control over alerting parameters. This approach enables more precise and effective management of alerts tailored to the specific needs of each level.

FAQ’s

1. Why is the latest snapshot data not available?

Answer: Refer to Usecase 1 - Failed to get latest metric values/Gateway is offline.

2. Why are graphs not reflecting?

Answer: Check Points to be Considered & Usecase2 - Graph data is not populating for specific or all metrics.

3. Why is the mysql.auth.error alert generated like below?

   

Answer: Refer to Usecase 1 - Failed to get latest metric values/Gateway is offline.

4. What are the possible MySQL exceptions?

Answer: Below are few possible exceptions while connecting MySQL server. - Access denied for user ‘username’@‘host’ (using password: YES/NO) - Unknown database ‘database_name’ - Unable to connect to any of the specified MySQL hosts. - Timeout and Socket Exceptions. - The connection is already open. - The connection is closed.

5. Where to check OpsRamp supported MySQL Metrics

Answer: Customers should check the Recommended Templates page within the public documentation. If the required monitoring support is not found on these pages and it’s a generic request applicable beyond the customer’s specific needs, then only submit a case to support team for Request for Enhancement (RFE).

However, if the monitoring requirement is specific to the customer’s needs, then customers need to develop their own script by following this documentation.

6. How to identify whether a template is global or customer written?

Answer: The details available in the links below only pertain to Global Templates and Metrics.

7. Latest metric snapshot data is not getting from the template.

Answer: Refer to Usecase 1 - Failed to get latest metric values/Gateway is offline.

8. What steps should an user follow if they want to create their own query based custom metric for database monitoring?

Answer: Users can refer to the Create Custom Metrics guide available in the public documentation.

This guide provides comprehensive instructions on how to develop query based custom metrics.

9. Can customer create query based metrics using Agent collector type?

Answer: No, Query-based metrics only work with collector type Gateway, not with Agent.

10. How to plot Graph for String values like health or status metrics?

Answer: To plot graph for state or status-related metrics returned as strings, utilize the Enumerated Mapping option.

11. Why metrics and monitor changes are not getting reflected in template (latest metric data or graphs or alerts)?

Answer: Refer to How to update & add metrics to Monitor & Monitor to template.

12. User wants to exclude monitoring for some components of metric. How can they achieve this?

Answer: If User wants to exclude monitoring some components of metric, then suggest them to use Component Filters option of RSE.

By using these component filters, you can monitor specific components or ignore unwanted components from monitoring.

13. For MySQL monitoring, should SSH credentials assigned on the target device or on the Gateway device?

Answer: In MySQL monitoring, No need to assign the SSH login credentials on the target device, not on the Gateway device.

14. Authentication error alerts (Dtabase Related) observed on device Overview page.

Answer: If it’s MySQL query based template, make sure that MySQL Database Type credentials are assigned in device’s credentials tab.

15. Observed any MySQL Query execution errors on device like permissions error

Answer: Assigned Database Type credentials must have sufficient permissions to execute the query on the device.

16. User wants to know the queries used for template.

Answer: Go to the Setup page. Navigate to Monitoring, then Click on Metrics and Search the exact metric based on scope. Open the metric definition. Monitoring query available on the metric definition

17. Can a user assign version 1 and version 2, 3, 4 etc of a template on the same device?

Answer: No, User cannot assign version 1 and version 2, 3, 4, etc., of a template to the same device simultaneously. In nearly all cases, the metrics present in version 1 (v1) will also be included in version 2 (v2) or later versions. The later versions typically include additional metrics, enhancements to existing metrics or methodologies, and bug fixes. Therefore, we recommend always using the latest version of the template to ensure that users benefit from these improvements and new features.

Supported templates

Supported metrics