Platform

    Get real-time insights and automation for comprehensive, seamless monitoring with agentless architecture.

    Platform Overview
  • Infrastructure
    monitoring
  • Cloud monitoring
  • Digital experience

      • Solutions

      Whether you work in MSP, Enterprise IT or somewhere in between, the solution is clear.

      Solutions Overview
    By Initiative
    By Industry

      About us

      Get to know LogicMonitor and our team.

      About us
    Get to know us
    Connect
    Services

      Documentation

      Read through our documentation, check out our latest release notes, or submit a ticket to our world-class customer service team.

      View Resources
    Support

    Product Documentation

    MySQL Monitoring

    Last updated on 17 December, 2020

    In order to monitor Mysql information, discover whether a system is a slave, is running InnoDB and/or MyISAM, etc, the LogicMonitor collector must be able to connect to a database.  So the first step is to set up the MySQL Credentials.

    LogicMonitor will initially assume that any host with a server on port 3306 is running MySql.  Until you give LogicMonitor permission to connect to the databases it finds, it will raise an error for that host, as it will be unable to collect the Mysql performance data.

    If you wish to have LogicMonitor discover mysql on additional ports, simply add them in a space separated list to the Parameters fields of the Active Discovery section of the Mysql datasources.

    Once the appropriate credentials are given, all the other MySQL datasources will be automatically applied, as appropriate.  Note that the Slave monitoring will not work correctly if you have not created a Logicmonitor database in your Mysql server, as detailed in the Mysql Credentials  section.

    Note that your MySQL version must be compatible with JDBC Driver 1.4.6. Additionally, pre-MySQL 4.1 method of password hashing is no longer supported by the LogicMonitor Collector.

    MySQL Credentials

    In order to monitor Mysql , the LogicMonitor collector must be able to successfully connect to a database.  This requires:

    • that Mysql is accepting network connections. (Note: by default LogicMonitor only attempts connections on port 3306, but this can be changed in the datasource.)
    • the collector uses a valid username and password that can connect to the database. These are defined by the properties jdbc.mysql.user and jdbc.mysql.pass.
    • a database name property is not required for the standard MySQL monitoring, but can be needed if you are specifying custom database queries. This is defined by the host property dbname.

    Allowing the LogicMonitor Collector to connect to MySQL

    The simplest way to ensure the LogicMonitor collector can connect to the database is to provide credentials for the mysql root user – then you generally need take no action on your database (so long as it is accepting network connections). However, this is not a best practice for security.

    The preferred way to monitor a mysql server is to create a specific monitoring user for that purpose, and to limit that user’s rights. You should create a specific database, and grant the monitoring user rights only to that database.

    If you are not running replication

    e.g. if your collector will be connecting to the Mysql database as user ‘collector’, with the collector running on the host 10.1.1.10:

    mysql> grant usage on logicmonitor.* to 'collector'@'10.1.1.10';

    Properties to define:
    jdbc.mysql.user = collector
    jdbc.mysql.pass= (empty)

    If you are, or may, run replication:

    Given that the MysqlReplicationLag- datasource expects a specific database and table to exist, it makes sense to create a monitoring user that has access only to that database:

    Perform these operations as the root user on your Mysql master 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 your authentication properties this way:
    jdbc.mysql.user=COLLECTORUSER
    jdbc.mysql.pass=COLLECTORPASSWORD
    DBNAME=logicmonitor

    Minimum rights table

    The rights necessary for the different datasources are:

    Datasource
    Function
    Rights Needed
    Mysql-,Mysql_Innodb-, Mysql_MyISAM-, etc
    Global statistics
    USAGE
    MysqlSlave-
    Tracks the slave’s time difference in seconds between the slave SQL thread and the slave I/O thread.
    REPLICATION CLIENT
    MysqlMaster-
    Creates a timestamp on the master for ReplicationLag- to measure on the slave.
    REPLICATION CLIENT, INSERT and DELETE privileges for the heartbeat table in the logicmonitor datasource. ReplicationLag- requires SELECT.
    Custom data queries
    Reading custom data from tables
    SELECT on the relevant database

    MySQL Slave Monitoring

    There are two kinds of slave monitoring supported by LogicMonitor, that serve two different functions:

    1.  Slave Status

    Slave Status measures the difference between the slave SQL thread and the slave I/O thread. (i.e. what the slave has read from the Master, and what it has applied.) This will be a good indication of slave lag in the absence of network issues (if the slave cannot pull new data from the master, it will report it has applied all the transactions it has, and that its lag is zero. This is not the same as being up to date with the actual Master.) This datasource also checks that the slave IO and SQL processes are running correctly.

    2.  MysqlMaster– and ReplicationLag–

    MysqlMaster- inserts a timestamp into a table every 5 seconds, and is automatically applied to Master databases. ReplicationLag- does a select on the slave, measuring the difference between the last timestamp from the master that has been applied, and now(). This reflects wall clock time lag between the master’s updates and the slave’s application of the data (assuming the hosts’ clocks are synced via ntp or other means.) Note that if you have not created a LogicMonitor database as detailed above in the Mysql Credentials section, you will receive No Data warning for this datasource from all slaves.

    Why are there two kinds of monitoring?

    Neither kind of slave monitoring is perfect in all situations. MysqlSlave- does not take account of network or some other issues;  MysqlReplicationLag- does.  However, MysqlReplicationLag- tells you nothing about the state of your slave once the Master is down – the lag from the last update from the Master and real time will increase then, because there are no updates.

    Actions to take before promoting a slave to a Master

    Before you fail over to a slave and make it a master, you want to know that the slave:

    • was able to get all the transactions from the old master.
    • has finished applying them.

    To determine this, on the LogicMonitor view of slave, check the graphs for MysqlReplicationLag- at the time the Master last was working.  If the lag is low, (it should be less than 4 seconds), then you know the slave had all  transactions from the master.  You can ignore increases in this graph after the master stopped working.  Then check the MysqlSlave- graph for slave lag: once this reaches zero, the slave has applied all the transactions it received from the master, and you can promote this slave to master. If there was MysqlReplicationLag- just before the master went down, you have a decision to make, as there are transactions on the master that never made it to the slave. Do I value previously committed transactions over uptime?  If so, you need to revive the master. Do I value uptime over honoring previously committed transactions?  If so, promote the slave to a master, but be aware that you will be missing some transactions.

    In This Article