MySQL Monitoring
Last updated on 03 October, 2024LogicMonitor 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
Note: LogicMonitor assumes that any host with a server on port 3306 that responds to a connection request with a MySQL error is running MySQL.
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.
Note: The replica monitoring will not fully work if you have not created a LogicMonitor database in your MySQL server.
Credentials
To monitor MySQL, the LogicMonitor collector must connect to a database. This requires the following:
- MySQL accepts network connections
Note: By default LogicMonitor attempts connections on port 3306, but this can be changed with resource properties.
- The collector uses a valid username and password that can connect to the database. These are defined by the following properties:
jdbc.mysql.userjdbc.mysql.pass
User Rights Needed for Monitoring
The following rights are necessary for each DataSource:
| Function | Rights Needed |
| Global statistics | USAGE |
| Table level monitoring – Space and Fragmentation of large tables. | SELECT on the relevant database |
| To monitor a replication client | REPLICATION CLIENT |
| To monitor a Source server in a replication scenario | REPLICATION CLIENT, INSERT and DELETE privileges for the heartbeat table in the logicmonitor datasource. ReplicationLag- requires SELECT. |
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:
- 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;- Set the authentication properties as the following:
jdbc.mysql.user=COLLECTORUSER
jdbc.mysql.pass=COLLECTORPASSWORDReplica 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.
Note: This is not the same as being up to date with the actual Source.
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:
| Property | Description | Required? |
| jdbc.mysql.port | The ports used my Oracle MySQL Enter a comma delimited string if there are MySQL instances on multiple ports (for example, 3306,3307,3309). Default port of 3306 is always checked. | No |
| jdbc.mysql.user jdbc.mysql.pass | MySQL Server username and passwordThis cannot be a Windows user account.This account must have the minimum SQL Server permissions. For more information, see Provide Credentials. | Yes |
| mysql.nossl | Adds the following to the connection string when true:?allowPublicKeyRetrieval=true&useSSL=falseDoes nothing if blank or set to false.Use this property when the SSL keys are not configured on the MySQL server. This is intended for development environments. | No |
| mysql.url_driver_properties | When configuring JDBC connections for MySQL, there are various driver properties that can be adjusted to optimize the connection and its behavior. These properties include settings for connection stability, like auto-reconnect options. Security features, such as SSL encryption, can also be toggled. The driver properties allows adjustments for character encoding and timezone settings to ensure data consistency and correct interpretation across different environments. In addition, properties related to performance, like query fetch size and connection timeouts, can be fine-tuned. Note: LogicMonitor does not lower security settings on connections. You must initiate this action. For example, mysql.driver_properties = ?allowPublicKeyRetrieval=true&useSSL=false adds the following string to the connection string for this device:?allowPublicKeyRetrieval=true&useSSL=false | No |
| (auto).mysql.port.url | For many configurations, this is set using the property source in auto-configuration. LogicMonitor does not control all aspects of the mysql connection manual entries. Manual configurations always override automatic configurations. For example, the following is a connection string for the MySQL server at the port: mysql.3306.url=jdbc:mysql://10.52.130.164:3306?allowPublicKeyRetrieval=true,useSSL=false | |
| mysql.exclude_databases | Use to exclude specific databases from monitoring. A comma delimited string of databases names. For example: mysql.exlcudedatabases = mysql,customers,products excludes these databases from monitoring. | No |
| mysql.remove_url | Set to True to delete any old mysql.port.url. Defaults to false and to be used sparingly. Only use when removing old MySQL servers. Removing MySQL connection strings deletes old instances and their history. | No |
| Mysql.sqltest | SQL Statement used by Oracle_MySQL_ConnectionStatus. The default SQL statement is SELECT CURRENT_DATE() as CurrentDate;Custom SQL statements must have the following: End with a semicolonCannot contain the words insert, update, or deleteCannot contain more than one semicolon | No |
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_SlaveMySql_ReplicaMysqlReplicationLagMySqlMaster
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.
| Display Name | Type | Description |
| addCategory_OracleMySQL | PropertySource | Identifies if the host is running MySQL server and adds a system category of ‘MySQL’. |
| MySQL Connection Status | DataSource | Checks the ability of the collector to connect to the MySQL instance. Also reports the round trip time for a select statement. Tries to determine the cause of the failure and report the error. Won’t create any alerts until the SQL server is collecting data to prevent un-configured servers from alerting. |
| MySQL Databases | DataSource | Monitors databases on a MySQL server for total size in rows and bytes. The row count is only an estimate. It executes the select statement: SELECT table_schema , sum(data_length + index_length) as dbSize, sum(table_rows) as dbRowcount FROM information_schema.tables WHERE table_schema not in (‘sys’,’information_schema’,’performance_schema’) GROUP BY table_schema; |
| MySQL InnoDB Statistics | DataSource | Monitors MySQL InnoDB performance, providing key metrics for optimizing database efficiency and stability. |
| MySQL Replica Status | DataSource | Executes “Show Replica status;” command and requires replication client privileges assigned to the monitoring user. Also need to have Select access to the logicmonitor.heartbeat table. |
| MySQL Source | DataSource | Updates timestamps of heartbeat table on the Source in a failover configuration. Must be set to 1 minute poll times. Requires LogicMonitor database to be installed. The purpose of this datasource is to show that the host is a Source in a failover configuration. It’s not supposed to show anything on the graph. |
| MySQL Status | DataSource | General statistics for Oracle MySQL or MariaDB servers. |
| MySQL Table Sizes | DataSource | Monitors the top ten tables by table size. |
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.
Recommendation: If necessary, adjust the predefined thresholds to meet the unique needs of your environment. For more information, see Tuning Static Thresholds for Datapoints.
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:
| Property Name | Description | Example | Default |
| mysql.sqltest or mysql.port.sqltest for specific port | SQL statement run each time the connection test is run. The jdbc.mysql.user must have access to this table for the statement to succeed. | SELECT user, FROM mysql.user; | SELECT CURRENT_DATE() as currentDate; |
| mysql.max_retries or mysql.port.max_retries | Number of retries before generating an alert. Setting this number too high may result in DataSource timeouts, and the alert will not be sent. The combination of max_retries and wait_time needs to be less than 180 seconds. | 5 | 3 |
| mysql.wait_time or mysql.port.wait_time | Wait time in seconds for the first try. Subsequent tries will be wait_time * retrycount. Setting this number too high will cause a collector timeout, as well as a possible missed alert. | 2 | 3 |