Come join our live training webinar every other Wednesday at 11am PST and hear LogicMonitor experts explain best practices and answer common questions. We understand these are uncertain times, and we are here to help!
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.
In order to monitor Mysql , the LogicMonitor collector must be able to successfully connect to a database. This requires:
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.
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
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;
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';
Set your authentication properties this way:
The rights necessary for the different datasources are:
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:
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