JR

3 minute read

If you need more output than what the existing Check of MySQL Status Variables includes, such as Seconds_Behind_Master or Relay_Log_Pos, consider adding a quick line to the bash call for _mysqlstatus on the agent. This assumes you’ve installed themysql_status-1.0.1.mkp and deployed the necessary agent files to the servers you’d like to monitor. This also assumes you’ve correctly configured the credentials needed to run the plugin and successfully receive (standard) output from the agent plugin.

Now to get Slave Status information from the same plugin:

  1. On the server you’d like to monitor (agent side), open _/usr/lib/check_mk_agent/plugins/mysqlstatus
  2. __After the existing call to mysql, add the following:

    1. mysql –defaults-extra-file=$MK_CONFDIR/mysql.cfg -e “show slave status\G” -s | sed ’s/:/ /g’ | sed -e ’s/1*//’
  3. That will show a fairly comprehensive selection of slave status indicators, remove the colon, and remove leading whitespace. This generates output that the existing plug-in can understand

Digging around in the existing plug-in (hosted on the monitoring server), I noticed the following comment from the plug-in author:

# No need to list Gauge type -  assumed per default
 # If you add a counter/bool to your .mk file, make sure it
 # is known in here

Since I really want to monitorSeconds_Behind_Master and I’m certain this isn’t a counter (e.g. rate) , or boolean (e.g. ON/OFF), then assuming ‘Gauge’ type is fairly safe — which means we don’t need to modify the plug-in source if that’s all you want to monitor. Looking at the output of  mysql -e “show slave status\G”, most of the items appear to be ‘Gauge’ type values, with the exception of a few booleans.

If you did want to monitor a counter or boolean value, make sure you add a definition in _/usr/lib/check_mk_agent/plugins/mysql_status_. For example, to add a check for ‘Master_SSL_Allowed’, add the following to the bottom of the ‘mysql_status_types’ declaration:

mysql_status_types = {
    "Aborted_clients"                : "Counter",
    "Aborted_connects"               : "Counter",
    "Compression"                    : "Boolean",
    "Connections"                    : "Counter",
    "Bytes_received"                 : "Counter",
    "Bytes_sent"                     : "Counter",
    "Created_tmp_files"              : "Counter",
    "Table_locks_waited"             : "Counter",
    "Slow_queries"                   : "Counter",
    "Slow_launch_threads"            : "Counter",
    "Qcache_hits"                    : "Counter",
    "Qcache_not_cached"              : "Counter",
    "Qcache_low_mem_prunes"          : "Counter",
    "Slave_retried_transactions"     : "Counter",
    "Slave_running"                  : "Boolean",
    "Queries"                        : "Counter",
    "Created_tmp_disk_tables"        : "Counter",
    "Created_tmp_tables"             : "Counter",
    "Slave_retried_transactions"     : "Counter",
    "Slave_running"                  : "Boolean",
    "Master_SSL_Allowed"             : "Boolean",
}

Now that the correct output is being requested, we need to instruct the monitoring server on how to inventory, handle, and alarm on the output. I prefer to configure these checks for auto-inventory:

  1. On the monitoring/check_mk server, open yourmysqlmon.mkplug-in definition file. This will vary depending on your config, but mine’s kept in /etc/check_mk/conf.d/mysqlmon.mk
  2. Add to your existing mysql_status_vars declaration. In this case I’m adding the last two vars to my definition: Seconds_Behind_Master and Master_SSL_Allowed

    1. if "mysql_status" in inventorable_checktypes("all"):
          mysql_status_vars = [
               ( "Innodb_os_log_pending_fsyncs",          5, 10),
               ( "Innodb_data_pending_fsyncs",          50, 100),
               ( "Innodb_buffer_pool_pages_free",    None, None),
               ( "Qcache_hits",                      None, None),
               ( "Aborted_clients",                      10, 20),
               ( "Aborted_connects",                   909, 500),
               ( "Bytes_received",                   None, None),
               ( "Bytes_sent",                1*1024*1024, 10*1024*1024),
               ( "Slave_running",                    "OFF", "OFF"),
               ( "Seconds_Behind_Master",             1, 5),
               ( "Master_SSL_Allowed",                "Yes", "Yes"),
          ]
    2. Be mindful of the output and thresholds you’re dealing with… I’m instructing check_mk to WARN at 1 second behind master, and CRIT at 5 seconds. This is WAY too conservative, but this is just an example. As for the boolean value, most MySQL booleans will display ON or OFF. In the case of Master_SSL_Allowed, it shows a YES or NO. In this example, I’ll alarm WARN or CRIT ifMaster_SSL_Allowed shows ‘Yes’. This is stupid, but it’s really just for demonstration purposes.

  3. Once finished, you’ll need to (re)inventory your affected hosts. If using WATO, you’ll need to perform a FULL SCAN. You may also opt to remove/re-add the host, but it really isn’t necessary.

    1.  

That’s really all there is to it… activate your ‘missing’ service checks and you’re ready to monitor your MySQL Slave/Master status in detail.

comments powered by Disqus