monitoring MySQL slave status

Question

I'm using Nagios with the mysql_check_health pugin to monitor my MySQL databases. I need to be able to return a numeric value to my plugin from an sql query to tell me if the replicated database is up and running. so here is what I have...

SHOW GLOBAL STATUS like 'slave_running'     

will return:

Variable_name   Value
Slave_running   OFF/ON

I need to retrun a numeric value from a simple query for the plugin, anyone have any ideas... my thought was to return 3-LENGTH(Slave_running) that would == 1 for ON 0 for off but having trouble using the return values in that way.

Answer 1

The global status variable will be accessible in the information_schema.GLOBAL_STATUS table, from which you can query just the value. That makes it easy to conditionally convert it to a 0 or 1 based on the ON/OFF state.

For example:

> SELECT VARIABLE_VALUE 
    FROM information_schema.GLOBAL_STATUS
    WHERE VARIABLE_NAME = 'slave_running';
+----------------+
| VARIABLE_VALUE |
+----------------+
| ON             |
+----------------+

So to convert that into a zero or one, there are a few possibilities. MySQL will treat booleans as 0 or 1, so you can just compare it = 'ON'

Wrapping the above in a subquery (since it returns one value) and comparing to 'ON':

> SELECT (
   'ON' = (SELECT VARIABLE_VALUE
    FROM information_schema.GLOBAL_STATUS
    WHERE VARIABLE_NAME = 'slave_running')
  ) AS state;
+-------+
| state |
+-------+
|     1 |
+-------+

Or a similar expression formatted as a CASE:

> SELECT CASE WHEN (
    SELECT VARIABLE_VALUE
    FROM information_schema.GLOBAL_STATUS
    WHERE VARIABLE_NAME = 'slave_running') = 'ON' THEN 1
  ELSE 0 END AS state;                                                                                                                                       
+-------+
| state |
+-------+
|     1 |
+-------+

In both of the above, I aliased the result as 'state', but you could use any column alias name to read output, replacing AS state accordingly.

What's already out there?

I couldn't help but wonder if there was already a Nagios plugin built for this purpose, and found this as a possibility.