A couple of Nagios scripts for monitoring MySQL
I thought I’d share a couple of basic Nagios scripts I’ve written in Python that you could use for a template or drop in to your existing Nagios instance.
check_mysql_user_security.py
#!/usr/bin/env python
import sys
from optparse import OptionParser
import MySQLdb as mdb
nagios_codes = {'OK': 0,
'WARNING': 1,
'CRITICAL': 2,
'UNKNOWN': 3,
'DEPENDENT': 4,}
def nagios_return(code, response):
""" prints the response message
and exits the script with one
of the defined exit codes
DOES NOT RETURN
"""
print response
sys.exit(nagios_codes[code])
try:
parser = OptionParser()
parser.add_option('-H', dest='hostname')
parser.add_option('-u', dest='username')
parser.add_option('-p', dest='password')
options, args = parser.parse_args()
for option in ('hostname', 'username', 'password'):
if not getattr(options, option):
nagios_return('CRITICAL', '%s not specified' % option)
conn = mdb.connect (host=options.hostname, user=options.username, passwd=options.password)
cursor=conn.cursor (mdb.cursors.DictCursor)
anon=0
wildhost=0
cursor.execute ("SELECT user, host, password FROM mysql.user where user = '' or password = '' or host='%'")
if cursor.rowcount == 0:
nagios_return('OK', 'MySQL grants are secure.')
rows = cursor.fetchall()
for row in rows:
if row['user'] == '':
anon+=1
row['user']='(anonymous)'
if row['user'] == 'root' and row['host'] == '%':
nagios_return('CRITICAL', 'Root has access from anywhere!')
if row['host'] == '%':
wildhost+=1
if row['password'] == '' and row['user'] != '(anonymous)':
nagios_return('WARNING', 'User %s has no password.' % (row['user']))
if anon > 0:
nagios_return ('WARNING', 'There are %s anonymous user(s).' % anon)
if wildhost > 0:
nagios_return ('WARNING', 'There are %s users with access from anywhere.' % wildhost)
cursor.close ()
conn.close ()
except mdb.Error, e:
print ('MySQL Server DOWN? %s') % e.args[1]
sys.exit(2)
check_mysql_repl_threads.py
#!/usr/bin/env python
import sys
from optparse import OptionParser
import MySQLdb as mdb
nagios_codes = {'OK': 0,
'WARNING': 1,
'CRITICAL': 2,
'UNKNOWN': 3,
'DEPENDENT': 4,}
def nagios_return(code, response):
""" prints the response message
and exits the script with one
of the defined exit codes
DOES NOT RETURN
"""
print response
sys.exit(nagios_codes[code])
try:
parser = OptionParser()
parser.add_option('-H', dest='hostname')
parser.add_option('-u', dest='username')
parser.add_option('-p', dest='password')
options, args = parser.parse_args()
for option in ('hostname', 'username', 'password'):
if not getattr(options, option):
nagios_return('CRITICAL', '%s not specified' % option)
conn = mdb.connect (host=options.hostname, user=options.username, passwd=options.password)
cursor=conn.cursor (mdb.cursors.DictCursor)
cursor.execute ("SHOW SLAVE STATUS")
if cursor.rowcount == 0:
nagios_return('WARNING', 'This server is not set up as a slave.')
rows = cursor.fetchall()
for row in rows:
if row['Slave_IO_Running'] == 'Yes' and row['Slave_SQL_Running'] == 'Yes':
nagios_return('OK', 'The slave threads are running properly.')
if row['Last_Error'] == '':
row['Last_Error'] = 'NULL'
if row['Slave_IO_Running'] == 'No' and row['Slave_SQL_Running'] == 'No':
nagios_return('CRITICAL', 'Neither slave thread is running! The error is: %s' % row['Last_Error'])
if row['Slave_IO_Running'] == 'No':
nagios_return('CRITICAL', 'Slave I/O thread not running! The error is: %s' % row['Last_Error'])
if row['Slave_SQL_Running'] == 'No':
nagios_return('CRITICAL', 'Slave SQL thread not running! The error is: %s' % row['Last_Error'])
cursor.close ()
conn.close ()
except mdb.Error, e:
print ('MySQL Server DOWN? %s') % (e.args[1])
sys.exit(2)
