o11y IM How to configure MySQL replication monitoring with collectd/mysql

MySQL cluster replication monitoring with collectd/mysql is possible by configuring the following elements:

1. Configure monitoring of statuses of your master and/or slave nodes. This can be achieved by utilizing the MasterStats, SlaveStats, and SlaveNotifications options as described in 10-mysql.conf. In this particular case, you're interested in setting the SlaveStats, and SlaveNotifications in the Smart Agent's configuration for the host that acts as a slave. This can be also achieved by setting collectd/custom

Example 10-mysql.conf:

<LoadPlugin "mysql">
Interval 10
</LoadPlugin>
 
<Plugin mysql>
<Database "test">ReportHost false
Host "<hosts_IP>"
Port <port>
Database "<monitored_database_name"
User "<account_set_for_monitoring>"
Password "<password>"
SlaveStats true
SlaveNotifications true
</Database>
</Plugin>

2. Except for the monitoring configuration, the account set for monitoring purposes requires the following permissions:

CREATE USER '<account_set_for_monitoring>'@'localhost' IDENTIFIED BY '<password>';-- Give appropriate permissions
 
-- ("GRANT USAGE" is synonymous to "no privileges")
 
GRANT USAGE ON *.* TO '<account_set_for_monitoring>'@'localhost';
 
-- Permissions for the MasterStats and SlaveStats options
 
GRANT REPLICATION CLIENT ON *.* TO '<account_set_for_monitoring>'@'localhost';

Along with the metrics listed in integrations MySQL. there will be an additional metric called time_offset made available. This will show the actual Seconds_Behind_Master value listed by calling SHOW SLAVE STATUS on the MySQL node.
 

NOTE: this one is documented on the collectd\wiki site however it has been tested.