MySQL Monitoring

LogicMonitor offers Oracle MySQL monitoring, providing visibility and analytics into database performance. It automatically detects and monitors key metrics, including query throughput, connections, replication status, and resource utilization; and enables proactive management and alerting to maintain optimal performance and availability of MySQL environments.

Oracle MySQL Version 8 is a major update that introduces significant enhancements in performance, security, and usability. It includes features like window functions, common table expressions (CTEs), JSON support, atomic DDLs, improved InnoDB storage engine, and advanced role-based access control, solidifying its position as a robust choice for enterprise database management solutions.

In the latest release of Oracle MySQL the jdbc connection string has been exposed as a resource property. This allows for connections to databases that are different from “out of the box” configurations.  All of the monitoring is now handled with scripted methods.

Compatibility

The MySQL modules have been tested with version 5.7–8.0.3x of Oracle MySQL and MariaDB version 11.

Setup Requirements

LogicMonitor requires a user and password, and this user needs the correct permissions to connect to the databases.

If you have MySQL running on multiple ports, add the ports in a comma separated list to the server resource as a property.

After the appropriate credentials are provided, the MySQL datasources are automatically applied where applicable.

Credentials

To monitor MySQL, the LogicMonitor collector must connect to a database. This requires the following:

• MySQL accepts network connections

• The collector uses a valid username and password that can connect to the database. These are defined by the following properties:
  • jdbc.mysql.user 
  • jdbc.mysql.pass

User Rights Needed for Monitoring

The following rights are necessary for each DataSource:

Add Resources into Monitoring

Add your MySQL Server hosts into monitoring. For more information on adding resources into monitoring, see Adding Devices.

Running MySQL Replica Monitoring

The Oracle MySQL ReplicaStatus datasource expects a specific database and table to exist. Create a monitoring user that has access only to that database:

1. Perform the following operations as the root user on your Mysql source database:

create database logicmonitor;
 
use logicmonitor;
 
CREATE TABLE heartbeat (
 
id int NOT NULL PRIMARY KEY,
 
ts datetime NOT NULL
 
);
 
INSERT INTO heartbeat(id,ts) VALUES(1,now());
 
grant select,insert,delete on logicmonitor.* to 'COLLECTORUSER'@'COLLECTORHOST' \
 
identified by 'COLLECTORPASSWORD';
 
grant replication client on *.* to 'COLLECTORUSER'@'COLLECTORHOST' \
 
  identified by  'COLLECTORPASSWORD';
 
flush privileges;

2. Set the authentication properties as the following:

jdbc.mysql.user=COLLECTORUSER
jdbc.mysql.pass=COLLECTORPASSWORD

Replica Status

Oracle_MySQL_ReplicaStatus measures the difference between the replica SQL thread and the replica I/O thread (what the replica has read from the Source, and what it has applied). This indicates replica lag in the absence of network issues. If the replica cannot pull new data from the source, it reports it has applied all transactions and the lag is zero.

This DataSource also checks that the Replica IO and SQL processes are running correctly.

In situations where the Source server does not update the Replica server, the field heartBeatTimeDifference reports the delay. Since the heartbeat is only updated on a collection cycle it only updates every minute.

Assign Properties to MySQL Server Resources

LogicMonitor attempts to automatically discover all properties needed for MySQL Server configurations. However, there may be use cases where you must manually enter properties or override auto-discovered properties. For more information on assigning properties, see Resource and Instance Properties.

The following properties can be set on the Oracle MySQL Server resource within LogicMonitor:

 LogicMonitor:

Migration to Latest Modules

In December 2023, LogicMonitor’s MySQL Server package received significant updates to enhance performance and have a lower impact on Collector and server load. As a result of these updates, all previous DataSources are obsolete and have been replaced with new DataSources. The previous DataSources can co-exist with the new modules so in-place updates can work. 

The following DataSources are deprecated:

• Mysql-
• Mysql_Innodb-
• Mysql_Slave
• MySql_Replica
• MysqlReplicationLag
• MySqlMaster

LogicModules in Package

LogicMonitor’s package for Oracle MySQL consists of the following LogicModules. For full coverage, ensure all of the following LogicModules are imported into your LogicMonitor platform.

platform.

When setting static datapoint thresholds on the various metrics tracked by this package’s DataSources, LogicMonitor follows the technology owner’s best practice KPI recommendations.

ConnectionStatus DataSource

The  Oracle_MySQL_ConnectionStatus DataSource is used to check connectivity from the collector to an MySQL database. By default, the DataSource attempts to connect to the MySQL database three times before alerting:

• First attempt fails: DataSource will wait three seconds and try to connect again.
• Second attempt fails: DataSource will wait 12 seconds and try to connect again.
• Third attempt fails: An alert is sent.   

The number of tries, as well as the length of time between tries, can be set with device properties. Care must be taken to prevent the DataSource from hitting a timeout or exceeding the collection interval. The collection interval is 60 seconds timeout and it’s best to keep the retries withing the collection interval.  The timeout for datasources defaults to 180 seconds, but it can be changed in the collector properties.  Keep in mind these time constraints.  If there is a slow unreliable connection the changing the collection interval to 3 minutes or more is recommended.  On most servers the default is fine.

When a connection to the database is successful, the DataSource runs the select statement and reports the number of rows returned and the time for execution. This select statement can be overridden with a device property. Properties are in the form of mysql.propertyName or mysql.port.propertyName where port is the port number running MySQL.  When mysql.port.propertyName is used, the property will be used for a specific MySQL instance running on a port.  When there is no port specified, the DataSource will use the same property for each MySQL instance on the resource.

Properties

The following properties are optional, but can be leveraged for slower connections: