def check_memory_usage():
"""Check host memory usage"""
memory_usage = get_memory_usage()
threshold = int(config.get("dbAlerter", "os_memory_threshold"))
memory_capacity = int((100 / float(memory_usage["total"])) * float(memory_usage["used"]))
if memory_capacity > threshold:
notify.stateful_notify(
True,
warning_state,
"MEMORY_USAGE",
"Warning",
"OS Memory usage threshold crossed",
"OS Memory usage is currently "
+ str(memory_capacity)
+ "% (Threshold currently set to "
+ str(threshold)
+ "%)",
)
else:
notify.stateful_notify(
False,
warning_state,
"MEMORY_USAGE",
"Info",
"OS Memory usage returned below threshold",
"OS Memory usage is currently "
+ str(memory_capacity)
+ "% (Threshold currently set to "
+ str(threshold)
+ "%)",
)
def check_process_list():
"""Check process list for long running commands"""
global db, process_list_state, statistics
processes = []
cursor = db.cursor(MySQLdb.cursors.DictCursor)
cursor.execute("""SHOW FULL PROCESSLIST""")
rows = cursor.fetchall()
for row in rows:
#Notify on commands taking longer than 2 minutes
if (row['Command'] != 'Sleep' and row['User'] != 'system user'
and row['User'] != 'event_scheduler' and row['Time'] > 120):
processes.append(row['Id'])
notify.stateful_notify(
True, process_list_state, row['Id'], 'Warning',
'Long running process with ID (' + str(row['Id']) +
') detected ',
"The following command has been running for over 2 minutes:\n\nId: "
+ str(row['Id']) + "\nUser: "******"\nHost: " +
row['Host'] + "\nSchema: " + (row['db'] or 'NULL') +
"\nCommand: " + row['Command'] + "\nTime: " +
str(row['Time']) + "\nState: " + row['State'] + "\nInfo: " +
row['Info'])
statistics['WARNING'] += 1
cursor.close()
#Cleanup state variable
for key in process_list_state.keys():
if not key in processes:
notify.stateful_notify(
False, process_list_state, key, 'Info',
'Long running process with ID (' + str(key) +
') has completed', 'Long running process with ID (' +
str(key) + ') has completed.')
del process_list_state[key]
def check_swap_usage():
"""Check host swap usage"""
swap_usage = get_swap_usage()
if swap_usage["total"]:
threshold = int(config.get("dbAlerter", "os_swap_threshold"))
swap_capacity = int((100 / swap_usage["total"]) * swap_usage["used"])
if swap_capacity > threshold:
notify.stateful_notify(
True,
warning_state,
"SWAP_USAGE",
"Warning",
"OS Swap usage threshold crossed",
"OS Swap usage is currently "
+ str(swap_capacity)
+ "% (Threshold currently set to "
+ str(threshold)
+ "%)",
)
else:
notify.stateful_notify(
False,
warning_state,
"SWAP_USAGE",
"Info",
"OS Swap usage returned below threshold",
"OS Swap usage is currently "
+ str(swap_capacity)
+ "% (Threshold currently set to "
+ str(threshold)
+ "%)",
)
def check_cpu_usage():
"""Check MySQL CPU usage"""
global variables
cpu_usage = float(checkos.get_cpu_usage(variables['PID']))
threshold = float(config.get('dbAlerter','mysql_cpu_usage_threshold'))
if (cpu_usage > threshold):
notify.stateful_notify(True, warning_state, 'CPU_USAGE', 'Warning', 'CPU utilisation threshold crossed', 'CPU utilisation for MySQL process (' + str(variables['PID']) + ') is currently ' + str(cpu_usage) + '% (Threshold currently set to ' + str(threshold) + '%)')
statistics['WARNING'] += 1
else:
notify.stateful_notify(False, warning_state, 'CPU_USAGE', 'Info', 'CPU utilisation returned below threshold', 'CPU utilisation for MySQL process (' + str(variables['PID']) + ') is currently ' + str(cpu_usage) + '% (Threshold currently set to ' + str(threshold) + '%)')
def check_disk_usage():
"""Check MySQL disk usage"""
global variables
mount_usage = checkos.get_mount_usage([variables['BASEDIR'], variables['DATADIR'], variables['PLUGIN_DIR'], variables['TMPDIR']])
for mount in mount_usage.keys():
mount_capacity = int(mount_usage[mount]['capacity'].replace('%',''))
inode_capacity = int(mount_usage[mount]['icapacity'].replace('%',''))
if (mount == variables['BASEDIR']):
threshold = int(config.get('dbAlerter', 'mysql_basedir_threshold'))
params = ['BASEDIR_', 'Installation directory']
elif (mount == variables['DATADIR']):
threshold = int(config.get('dbAlerter', 'mysql_datadir_threshold'))
params = ['DATADIR_', 'Data directory']
elif (mount == variables['PLUGIN_DIR']):
threshold = int(config.get('dbAlerter', 'mysql_plugindir_threshold'))
params = ['PLUGIN_DIR_', 'Plugin directory']
elif (mount == variables['TMPDIR']):
threshold = int(config.get('dbAlerter', 'mysql_tmpdir_threshold'))
params = ['TMPDIR_', 'Temporary directory']
#Check mount capacity
if (mount_capacity > threshold):
notify.stateful_notify(True, warning_state, params[0] + 'USAGE', 'Warning', params[1] + ' usage threshold crossed', params[1] + ' (' + mount + ') usage is currently ' + str(mount_capacity) + '% (Threshold currently set to ' + str(threshold) + '%)')
statistics['WARNING'] += 1
else:
notify.stateful_notify(False, warning_state, params[0] + 'USAGE', 'Info', params[1] + ' usage returned below threshold', params[1] + ' (' + mount + ') usage is currently ' + str(mount_capacity) +'% (Threshold currently set to ' + str(threshold) + '%)')
#Check inode capacity
if (inode_capacity > threshold):
notify.stateful_notify(True, warning_state, params[0] + 'IUSAGE', 'Warning', params[1] + ' inode usage threshold crossed', params[1] + ' (' + mount + ') inode usage is currently ' + str(mount_capacity) + '% (Threshold currently set to ' + str(threshold) + '%)')
statistics['WARNING'] += 1
else:
notify.stateful_notify(False, warning_state, params[0] + 'IUSAGE', 'Info', params[1] + ' inode usage returned below threshold', params[1] + ' (' + mount + ') inode usage is currently ' + str(mount_capacity) +'% (Threshold currently set to ' + str(threshold) + '%)')
def check_empty_passwords():
"""Check for empty passwords"""
global db, security_state, statistics
cursor = db.cursor(MySQLdb.cursors.DictCursor)
cursor.execute("""SELECT User, Host FROM mysql.user WHERE Password=''""")
rows = cursor.fetchall ()
if (cursor.rowcount > 0):
emptyPasswords = "The following accounts do not have passwords configured:\n\n"
for row in rows:
emptyPasswords += row['User'] + "'@'" + row['Host'] + "\n"
notify.stateful_notify(True, security_state, 'EMPTYPASS', 'Security', 'Empty passwords detected', emptyPasswords + "\nPlease see: http://dev.mysql.com/doc/refman/5.1/en/default-privileges.html (Securing the initial MySQL Accounts) for details on how to secure these accounts.")
else:
notify.stateful_notify(False, security_state, 'EMPTYPASS', 'Info', 'No Empty Passwords Detected', "No more empty passwords detected.")
cursor.close()
def check_anonymous_accounts():
"""Check for anonymous accounts"""
global db, security_state, statistics
cursor = db.cursor(MySQLdb.cursors.DictCursor)
cursor.execute("""SELECT User, Host FROM mysql.user WHERE User=''""")
rows = cursor.fetchall ()
if (cursor.rowcount > 0):
anonymous_accounts = "The following anonymous accounts were detected:\n\n"
for row in rows:
anonymous_accounts += row['User'] + "'@'" + row['Host'] + "\n"
notify.stateful_notify(True, security_state, 'ANONACCOUNT', 'Security', 'Anonymous accounts detected', anonymous_accounts + "\nPlease see: http://dev.mysql.com/doc/refman/5.1/en/default-privileges.html (Securing the initial MySQL Accounts) for details on how to secure these accounts.")
else:
notify.stateful_notify(False, security_state, 'ANONACCOUNT', 'Info', 'No anonymous accounts detected', "No anonymous accounts detected.")
cursor.close()
def check_disk_usage():
"""Check MySQL disk usage"""
global variables
mount_usage = checkos.get_mount_usage([
variables['BASEDIR'], variables['DATADIR'], variables['PLUGIN_DIR'],
variables['TMPDIR']
])
for mount in mount_usage.keys():
mount_capacity = int(mount_usage[mount]['capacity'].replace('%', ''))
inode_capacity = int(mount_usage[mount]['icapacity'].replace('%', ''))
if (mount == variables['BASEDIR']):
threshold = int(config.get('dbAlerter', 'mysql_basedir_threshold'))
params = ['BASEDIR_', 'Installation directory']
elif (mount == variables['DATADIR']):
threshold = int(config.get('dbAlerter', 'mysql_datadir_threshold'))
params = ['DATADIR_', 'Data directory']
elif (mount == variables['PLUGIN_DIR']):
threshold = int(
config.get('dbAlerter', 'mysql_plugindir_threshold'))
params = ['PLUGIN_DIR_', 'Plugin directory']
elif (mount == variables['TMPDIR']):
threshold = int(config.get('dbAlerter', 'mysql_tmpdir_threshold'))
params = ['TMPDIR_', 'Temporary directory']
#Check mount capacity
if (mount_capacity > threshold):
notify.stateful_notify(
True, warning_state, params[0] + 'USAGE', 'Warning',
params[1] + ' usage threshold crossed', params[1] + ' (' +
mount + ') usage is currently ' + str(mount_capacity) +
'% (Threshold currently set to ' + str(threshold) + '%)')
statistics['WARNING'] += 1
else:
notify.stateful_notify(
False, warning_state, params[0] + 'USAGE', 'Info',
params[1] + ' usage returned below threshold', params[1] +
' (' + mount + ') usage is currently ' + str(mount_capacity) +
'% (Threshold currently set to ' + str(threshold) + '%)')
#Check inode capacity
if (inode_capacity > threshold):
notify.stateful_notify(
True, warning_state, params[0] + 'IUSAGE', 'Warning',
params[1] + ' inode usage threshold crossed',
params[1] + ' (' + mount + ') inode usage is currently ' +
str(mount_capacity) + '% (Threshold currently set to ' +
str(threshold) + '%)')
statistics['WARNING'] += 1
else:
notify.stateful_notify(
False, warning_state, params[0] + 'IUSAGE', 'Info',
params[1] + ' inode usage returned below threshold',
params[1] + ' (' + mount + ') inode usage is currently ' +
str(mount_capacity) + '% (Threshold currently set to ' +
str(threshold) + '%)')
def check_memory_usage():
"""Check host memory usage"""
memory_usage = get_memory_usage()
threshold = int(config.get('dbAlerter', 'os_memory_threshold'))
memory_capacity = int(
(100 / float(memory_usage['total'])) * float(memory_usage['used']))
if (memory_capacity > threshold):
notify.stateful_notify(
True, warning_state, 'MEMORY_USAGE', 'Warning',
'OS Memory usage threshold crossed',
'OS Memory usage is currently ' + str(memory_capacity) +
'% (Threshold currently set to ' + str(threshold) + '%)')
else:
notify.stateful_notify(
False, warning_state, 'MEMORY_USAGE', 'Info',
'OS Memory usage returned below threshold',
'OS Memory usage is currently ' + str(memory_capacity) +
'% (Threshold currently set to ' + str(threshold) + '%)')
def check_swap_usage():
"""Check host swap usage"""
swap_usage = get_swap_usage()
if (swap_usage['total']):
threshold = int(config.get('dbAlerter', 'os_swap_threshold'))
swap_capacity = int((100 / swap_usage['total']) * swap_usage['used'])
if (swap_capacity > threshold):
notify.stateful_notify(
True, warning_state, 'SWAP_USAGE', 'Warning',
'OS Swap usage threshold crossed',
'OS Swap usage is currently ' + str(swap_capacity) +
'% (Threshold currently set to ' + str(threshold) + '%)')
else:
notify.stateful_notify(
False, warning_state, 'SWAP_USAGE', 'Info',
'OS Swap usage returned below threshold',
'OS Swap usage is currently ' + str(swap_capacity) +
'% (Threshold currently set to ' + str(threshold) + '%)')
def check_process_list():
"""Check process list for long running commands"""
global db, process_list_state, statistics
processes = []
cursor = db.cursor(MySQLdb.cursors.DictCursor)
cursor.execute("""SHOW FULL PROCESSLIST""")
rows = cursor.fetchall()
for row in rows:
#Notify on commands taking longer than 2 minutes
if (row['Command'] != 'Sleep' and row['User'] != 'system user' and row['User'] != 'event_scheduler' and row['Time'] > 120):
processes.append(row['Id'])
notify.stateful_notify(True, process_list_state, row['Id'], 'Warning', 'Long running process with ID (' + str(row['Id']) + ') detected ', "The following command has been running for over 2 minutes:\n\nId: " + str(row['Id']) + "\nUser: "******"\nHost: " + row['Host'] + "\nSchema: " + (row['db'] or 'NULL') + "\nCommand: " + row['Command'] + "\nTime: " + str(row['Time']) + "\nState: " + row['State'] + "\nInfo: " + row['Info'])
statistics['WARNING'] += 1
cursor.close()
#Cleanup state variable
for key in process_list_state.keys():
if not key in processes:
notify.stateful_notify(False, process_list_state, key, 'Info', 'Long running process with ID (' + str(key) + ') has completed', 'Long running process with ID (' + str(key) + ') has completed.')
del process_list_state[key]
def check_empty_passwords():
"""Check for empty passwords"""
global db, security_state, statistics
cursor = db.cursor(MySQLdb.cursors.DictCursor)
cursor.execute("""SELECT User, Host FROM mysql.user WHERE Password=''""")
rows = cursor.fetchall()
if (cursor.rowcount > 0):
emptyPasswords = "The following accounts do not have passwords configured:\n\n"
for row in rows:
emptyPasswords += row['User'] + "'@'" + row['Host'] + "\n"
notify.stateful_notify(
True, security_state, 'EMPTYPASS', 'Security',
'Empty passwords detected', emptyPasswords +
"\nPlease see: http://dev.mysql.com/doc/refman/5.1/en/default-privileges.html (Securing the initial MySQL Accounts) for details on how to secure these accounts."
)
else:
notify.stateful_notify(False, security_state, 'EMPTYPASS', 'Info',
'No Empty Passwords Detected',
"No more empty passwords detected.")
cursor.close()
def check_anonymous_accounts():
"""Check for anonymous accounts"""
global db, security_state, statistics
cursor = db.cursor(MySQLdb.cursors.DictCursor)
cursor.execute("""SELECT User, Host FROM mysql.user WHERE User=''""")
rows = cursor.fetchall()
if (cursor.rowcount > 0):
anonymous_accounts = "The following anonymous accounts were detected:\n\n"
for row in rows:
anonymous_accounts += row['User'] + "'@'" + row['Host'] + "\n"
notify.stateful_notify(
True, security_state, 'ANONACCOUNT', 'Security',
'Anonymous accounts detected', anonymous_accounts +
"\nPlease see: http://dev.mysql.com/doc/refman/5.1/en/default-privileges.html (Securing the initial MySQL Accounts) for details on how to secure these accounts."
)
else:
notify.stateful_notify(False, security_state, 'ANONACCOUNT', 'Info',
'No anonymous accounts detected',
"No anonymous accounts detected.")
cursor.close()
def check_cpu_usage():
"""Check MySQL CPU usage"""
global variables
cpu_usage = float(checkos.get_cpu_usage(variables['PID']))
threshold = float(config.get('dbAlerter', 'mysql_cpu_usage_threshold'))
if (cpu_usage > threshold):
notify.stateful_notify(
True, warning_state, 'CPU_USAGE', 'Warning',
'CPU utilisation threshold crossed',
'CPU utilisation for MySQL process (' + str(variables['PID']) +
') is currently ' + str(cpu_usage) +
'% (Threshold currently set to ' + str(threshold) + '%)')
statistics['WARNING'] += 1
else:
notify.stateful_notify(
False, warning_state, 'CPU_USAGE', 'Info',
'CPU utilisation returned below threshold',
'CPU utilisation for MySQL process (' + str(variables['PID']) +
') is currently ' + str(cpu_usage) +
'% (Threshold currently set to ' + str(threshold) + '%)')
def check_status():
"""Check server statistics"""
global db, global_status, statistics, warning_state
cursor = db.cursor(MySQLdb.cursors.DictCursor)
cursor.execute("""SHOW GLOBAL STATUS""")
rows = cursor.fetchall ()
for row in rows:
#Check Open File Usage
if (row['Variable_name'] == 'Open_files'):
if (int(row['Value']) > statistics['MAX_OPEN_FILES']):
statistics['MAX_OPEN_FILES'] = int(row['Value'])
connpct = int(((100 / float(variables['OPEN_FILES_LIMIT'])) * float(row['Value'])))
if (connpct > int(config.get('dbAlerter','mysql_open_files_threshold'))):
notify.stateful_notify(True, warning_state, 'OPEN_FILES', 'Warning', 'Open file usage crossed ' + config.get('dbAlerter','mysql_open_files_threshold') + '% threshold', 'Open file crossed ' + config.get('dbAlerter','mysql_open_files_threshold') + '% threshold and is currently ' + str(connpct) + '%')
statistics['WARNING'] += 1
else:
notify.stateful_notify(False, warning_state, 'OPEN_FILES', 'Info', 'Open file usage fell below ' + config.get('dbAlerter','mysql_open_files_threshold') + '% threshold', 'Open file usage fell below ' + config.get('dbAlerter','mysql_open_files_threshold') + '% threshold and is currently ' + str(connpct) + '%')
#Check Current Connection Usage
if (row['Variable_name'] == 'Threads_connected'):
if (int(row['Value']) > statistics['MAXCONN']):
statistics['MAXCONN'] = int(row['Value'])
connpct = int(((100 / float(variables['MAX_CONNECTIONS'])) * float(row['Value'])))
if (connpct > int(config.get('dbAlerter','mysql_connection_usage_threshold'))):
notify.stateful_notify(True, warning_state, 'CONNECTIONS', 'Warning', 'Connection usage crossed ' + config.get('dbAlerter','mysql_connection_usage_threshold') + '% threshold', 'Connection usage crossed ' + config.get('dbAlerter','mysql_connection_usage_threshold') + '% threshold and is currently ' + str(connpct) + "%")
statistics['WARNING'] += 1
else:
notify.stateful_notify(False, warning_state, 'CONNECTIONS', 'Info', 'Connection usage fell below ' + config.get('dbAlerter','mysql_connection_usage_threshold') + '% threshold', 'Connection usage fell below ' + config.get('dbAlerter','mysql_connection_usage_threshold') + '% threshold and is currently ' + str(connpct) + '%')
#Check Slow Queries
if (row['Variable_name'] == 'Slow_queries'):
slowqs = (int(row['Value']) - global_status['SLOW_QUERIES'])
if (slowqs > 5):
notify.notify('Warning', str(slowqs) + " Slow Queries during last " + config.get('dbAlerter','check_interval') + " seconds.", str(slowqs) + " Slow Queries during last " + config.get('dbAlerter','check_interval') + " seconds.")
statistics['WARNING'] += 1
global_status['SLOW_QUERIES'] = int(row['Value'])
statistics['SLOWQ'] += slowqs
#Server uptime
if (row['Variable_name'] == 'Uptime'):
global_status['UPTIME'] = int(row['Value'])
cursor.close()
def check_auto_increment ():
'''Check all auto_increment counters'''
global auto_increment_state, db, statistics
threshold = 50
aitables = []
cursor1 = db.cursor(MySQLdb.cursors.DictCursor)
cursor2 = db.cursor(MySQLdb.cursors.DictCursor)
cursor1.execute("""SELECT TABLE_SCHEMA, TABLE_NAME, AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE AUTO_INCREMENT > 0""")
tables = cursor1.fetchall ()
for table in tables:
cursor2.execute("""SELECT COLUMN_NAME, DATA_TYPE, COLUMN_TYPE FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = '""" + table['TABLE_SCHEMA'] + """' AND TABLE_NAME = '""" + table['TABLE_NAME'] + """' AND EXTRA = 'auto_increment'""")
columns = cursor2.fetchall ()
for column in columns:
above_threshold = False
if (lower(column['COLUMN_TYPE']).find('unsigned') == -1):
#Process signed data types
if (lower(column['DATA_TYPE']) == 'tinyint'):
currentValue = int((float(100) / float(127))*float(table['AUTO_INCREMENT']))
if (currentValue > threshold):
above_threshold = True
elif (lower(column['DATA_TYPE']) == 'smallint'):
currentValue = int((float(100) / float(32767))*float(table['AUTO_INCREMENT']))
if (currentValue > threshold):
above_threshold = True
elif (lower(column['DATA_TYPE']) == 'mediumint'):
currentValue = int((float(100) / float(8388607))*float(table['AUTO_INCREMENT']))
if (currentValue > threshold):
above_threshold = True
elif (lower(column['DATA_TYPE']) == 'int'):
currentValue = int((float(100) / float(2147483647))*float(table['AUTO_INCREMENT']))
if (currentValue > threshold):
above_threshold = True
elif (lower(column['DATA_TYPE']) == 'bigint'):
currentValue = int((float(100) / float(9223372036854775807))*float(table['AUTO_INCREMENT']))
if (currentValue > threshold):
above_threshold = True
else:
#process unsigned data types
if (lower(column['DATA_TYPE']) == 'tinyint'):
currentValue = int((float(100) / float(255))*float(table['AUTO_INCREMENT']))
if (currentValue > threshold):
above_threshold = True
elif (lower(column['DATA_TYPE']) == 'smallint'):
currentValue = int((float(100) / float(65535))*float(table['AUTO_INCREMENT']))
if (currentValue > threshold):
above_threshold = True
elif (lower(column['DATA_TYPE']) == 'mediumint'):
currentValue = int((float(100) / float(16777215))*float(table['AUTO_INCREMENT']))
if (currentValue > threshold):
above_threshold = True
elif (lower(column['DATA_TYPE']) == 'int'):
currentValue = int((float(100) / float(4294967295))*float(table['AUTO_INCREMENT']))
if (currentValue > threshold):
above_threshold = True
elif (lower(column['DATA_TYPE']) == 'bigint'):
currentValue = int((float(100) / float(18446744073709551615))*float(table['AUTO_INCREMENT']))
if (currentValue > threshold):
above_threshold = True
if (above_threshold):
aitables.append(table['TABLE_SCHEMA'] + '.' + table['TABLE_NAME'] + '.' + column['COLUMN_NAME'])
notify.stateful_notify(True, auto_increment_state, table['TABLE_SCHEMA'] + '.' + table['TABLE_NAME'] + '.' + column['COLUMN_NAME'], 'Warning', 'Auto increment threshold crossed on column [' + table['TABLE_SCHEMA'] + '.' + table['TABLE_NAME'] + '.' + column['COLUMN_NAME'] + ']', 'The column [' + column['COLUMN_NAME'] + '] within the table [' + table['TABLE_SCHEMA'] + '.' + table['TABLE_NAME'] + '] crossed the ' + str(threshold) +'% threshold.')
statistics['WARNING'] += 1
cursor1.close()
cursor2.close()
#Cleanup state variable
for key in auto_increment_state.keys():
if not key in aitables:
notify.stateful_notify(False, auto_increment_state, key, 'Info', 'Auto increment returned below threshold for column [' + key + ']', 'The column [' + key.split('.')[2] + '] within the table [' + key.split('.')[0] + '.' + key.split('.')[1] + '] returned below the ' + str(threshold) + '% threshold.')
del auto_increment_state[key]
def check_slave_status():
"""Check replication slave status"""
global db, statistics, warning_state
cursor = db.cursor(MySQLdb.cursors.DictCursor)
cursor.execute("""SHOW SLAVE STATUS""")
row = cursor.fetchone()
if row:
#Check Slave IO
if row["Slave_IO_Running"]=="No":
notify.stateful_notify(True, warning_state, 'SLAVEIO', "Warning", "Slave IO has stopped", "Warning - Slave IO has stopped")
statistics['WARNING'] += 1
elif row["Slave_IO_Running"]=="Yes":
notify.stateful_notify(False, warning_state, 'SLAVEIO', "Info", "Slave IO has started", "Info - Slave IO has started")
#Check Slave SQL
if row["Slave_SQL_Running"]=="No":
notify.stateful_notify(True, warning_state, 'SLAVESQL', "Warning", "Slave SQL has stopped", "Warning - Slave SQL has stopped")
statistics['WARNING'] += 1
elif row["Slave_SQL_Running"]=="Yes":
notify.stateful_notify(False, warning_state, 'SLAVESQL', "Info", "Slave SQL has started", "Info - Slave SQL has started")
#Check Slave Position
if row["Seconds_Behind_Master"] > 60:
notify.stateful_notify(True, warning_state, 'SLAVEPOS', "Warning", "Slave is currently " + str(row["Seconds_Behind_Master"]) + " seconds behind the Master", "Warning - Slave is currently " + str(row["Seconds_Behind_Master"]) + " seconds behind the Master")
statistics['WARNING'] += 1
elif row["Seconds_Behind_Master"] == 0:
notify.stateful_notify(False, warning_state, 'SLAVEPOS', "Info", "Slave has caught up with Master", "Info - Slave has caught up with Master")
cursor.close()
def check_slave_status():
"""Check replication slave status"""
global db, statistics, warning_state
cursor = db.cursor(MySQLdb.cursors.DictCursor)
cursor.execute("""SHOW SLAVE STATUS""")
row = cursor.fetchone()
if row:
#Check Slave IO
if row["Slave_IO_Running"] == "No":
notify.stateful_notify(True, warning_state, 'SLAVEIO', "Warning",
"Slave IO has stopped",
"Warning - Slave IO has stopped")
statistics['WARNING'] += 1
elif row["Slave_IO_Running"] == "Yes":
notify.stateful_notify(False, warning_state, 'SLAVEIO', "Info",
"Slave IO has started",
"Info - Slave IO has started")
#Check Slave SQL
if row["Slave_SQL_Running"] == "No":
notify.stateful_notify(True, warning_state, 'SLAVESQL',
"Warning", "Slave SQL has stopped",
"Warning - Slave SQL has stopped")
statistics['WARNING'] += 1
elif row["Slave_SQL_Running"] == "Yes":
notify.stateful_notify(False, warning_state, 'SLAVESQL',
"Info", "Slave SQL has started",
"Info - Slave SQL has started")
#Check Slave Position
if row["Seconds_Behind_Master"] > 60:
notify.stateful_notify(
True, warning_state, 'SLAVEPOS', "Warning",
"Slave is currently " +
str(row["Seconds_Behind_Master"]) +
" seconds behind the Master",
"Warning - Slave is currently " +
str(row["Seconds_Behind_Master"]) +
" seconds behind the Master")
statistics['WARNING'] += 1
elif row["Seconds_Behind_Master"] == 0:
notify.stateful_notify(
False, warning_state, 'SLAVEPOS', "Info",
"Slave has caught up with Master",
"Info - Slave has caught up with Master")
cursor.close()
def check_auto_increment():
'''Check all auto_increment counters'''
global auto_increment_state, db, statistics
threshold = 50
aitables = []
cursor1 = db.cursor(MySQLdb.cursors.DictCursor)
cursor2 = db.cursor(MySQLdb.cursors.DictCursor)
cursor1.execute(
"""SELECT TABLE_SCHEMA, TABLE_NAME, AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE AUTO_INCREMENT > 0"""
)
tables = cursor1.fetchall()
for table in tables:
cursor2.execute(
"""SELECT COLUMN_NAME, DATA_TYPE, COLUMN_TYPE FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = '"""
+ table['TABLE_SCHEMA'] + """' AND TABLE_NAME = '""" +
table['TABLE_NAME'] + """' AND EXTRA = 'auto_increment'""")
columns = cursor2.fetchall()
for column in columns:
above_threshold = False
if (lower(column['COLUMN_TYPE']).find('unsigned') == -1):
#Process signed data types
if (lower(column['DATA_TYPE']) == 'tinyint'):
currentValue = int((float(100) / float(127)) *
float(table['AUTO_INCREMENT']))
if (currentValue > threshold):
above_threshold = True
elif (lower(column['DATA_TYPE']) == 'smallint'):
currentValue = int((float(100) / float(32767)) *
float(table['AUTO_INCREMENT']))
if (currentValue > threshold):
above_threshold = True
elif (lower(column['DATA_TYPE']) == 'mediumint'):
currentValue = int((float(100) / float(8388607)) *
float(table['AUTO_INCREMENT']))
if (currentValue > threshold):
above_threshold = True
elif (lower(column['DATA_TYPE']) == 'int'):
currentValue = int((float(100) / float(2147483647)) *
float(table['AUTO_INCREMENT']))
if (currentValue > threshold):
above_threshold = True
elif (lower(column['DATA_TYPE']) == 'bigint'):
currentValue = int(
(float(100) / float(9223372036854775807)) *
float(table['AUTO_INCREMENT']))
if (currentValue > threshold):
above_threshold = True
else:
#process unsigned data types
if (lower(column['DATA_TYPE']) == 'tinyint'):
currentValue = int((float(100) / float(255)) *
float(table['AUTO_INCREMENT']))
if (currentValue > threshold):
above_threshold = True
elif (lower(column['DATA_TYPE']) == 'smallint'):
currentValue = int((float(100) / float(65535)) *
float(table['AUTO_INCREMENT']))
if (currentValue > threshold):
above_threshold = True
elif (lower(column['DATA_TYPE']) == 'mediumint'):
currentValue = int((float(100) / float(16777215)) *
float(table['AUTO_INCREMENT']))
if (currentValue > threshold):
above_threshold = True
elif (lower(column['DATA_TYPE']) == 'int'):
currentValue = int((float(100) / float(4294967295)) *
float(table['AUTO_INCREMENT']))
if (currentValue > threshold):
above_threshold = True
elif (lower(column['DATA_TYPE']) == 'bigint'):
currentValue = int(
(float(100) / float(18446744073709551615)) *
float(table['AUTO_INCREMENT']))
if (currentValue > threshold):
above_threshold = True
if (above_threshold):
aitables.append(table['TABLE_SCHEMA'] + '.' +
table['TABLE_NAME'] + '.' +
column['COLUMN_NAME'])
notify.stateful_notify(
True, auto_increment_state, table['TABLE_SCHEMA'] + '.' +
table['TABLE_NAME'] + '.' + column['COLUMN_NAME'],
'Warning', 'Auto increment threshold crossed on column [' +
table['TABLE_SCHEMA'] + '.' + table['TABLE_NAME'] + '.' +
column['COLUMN_NAME'] + ']', 'The column [' +
column['COLUMN_NAME'] + '] within the table [' +
table['TABLE_SCHEMA'] + '.' + table['TABLE_NAME'] +
'] crossed the ' + str(threshold) + '% threshold.')
statistics['WARNING'] += 1
cursor1.close()
cursor2.close()
#Cleanup state variable
for key in auto_increment_state.keys():
if not key in aitables:
notify.stateful_notify(
False, auto_increment_state, key, 'Info',
'Auto increment returned below threshold for column [' + key +
']',
'The column [' + key.split('.')[2] + '] within the table [' +
key.split('.')[0] + '.' + key.split('.')[1] +
'] returned below the ' + str(threshold) + '% threshold.')
del auto_increment_state[key]
def check_status():
"""Check server statistics"""
global db, global_status, statistics, warning_state
cursor = db.cursor(MySQLdb.cursors.DictCursor)
cursor.execute("""SHOW GLOBAL STATUS""")
rows = cursor.fetchall()
for row in rows:
#Check Open File Usage
if (row['Variable_name'] == 'Open_files'):
if (int(row['Value']) > statistics['MAX_OPEN_FILES']):
statistics['MAX_OPEN_FILES'] = int(row['Value'])
connpct = int(((100 / float(variables['OPEN_FILES_LIMIT'])) *
float(row['Value'])))
if (connpct > int(
config.get('dbAlerter', 'mysql_open_files_threshold'))):
notify.stateful_notify(
True, warning_state, 'OPEN_FILES', 'Warning',
'Open file usage crossed ' +
config.get('dbAlerter', 'mysql_open_files_threshold') +
'% threshold', 'Open file crossed ' +
config.get('dbAlerter', 'mysql_open_files_threshold') +
'% threshold and is currently ' + str(connpct) + '%')
statistics['WARNING'] += 1
else:
notify.stateful_notify(
False, warning_state, 'OPEN_FILES', 'Info',
'Open file usage fell below ' +
config.get('dbAlerter', 'mysql_open_files_threshold') +
'% threshold', 'Open file usage fell below ' +
config.get('dbAlerter', 'mysql_open_files_threshold') +
'% threshold and is currently ' + str(connpct) + '%')
#Check Current Connection Usage
if (row['Variable_name'] == 'Threads_connected'):
if (int(row['Value']) > statistics['MAXCONN']):
statistics['MAXCONN'] = int(row['Value'])
connpct = int(((100 / float(variables['MAX_CONNECTIONS'])) *
float(row['Value'])))
if (connpct > int(
config.get('dbAlerter',
'mysql_connection_usage_threshold'))):
notify.stateful_notify(
True, warning_state, 'CONNECTIONS', 'Warning',
'Connection usage crossed ' + config.get(
'dbAlerter', 'mysql_connection_usage_threshold') +
'% threshold', 'Connection usage crossed ' + config.get(
'dbAlerter', 'mysql_connection_usage_threshold') +
'% threshold and is currently ' + str(connpct) + "%")
statistics['WARNING'] += 1
else:
notify.stateful_notify(
False, warning_state, 'CONNECTIONS', 'Info',
'Connection usage fell below ' + config.get(
'dbAlerter', 'mysql_connection_usage_threshold') +
'% threshold', 'Connection usage fell below ' + config.get(
'dbAlerter', 'mysql_connection_usage_threshold') +
'% threshold and is currently ' + str(connpct) + '%')
#Check Slow Queries
if (row['Variable_name'] == 'Slow_queries'):
slowqs = (int(row['Value']) - global_status['SLOW_QUERIES'])
if (slowqs > 5):
notify.notify(
'Warning',
str(slowqs) + " Slow Queries during last " +
config.get('dbAlerter', 'check_interval') + " seconds.",
str(slowqs) + " Slow Queries during last " +
config.get('dbAlerter', 'check_interval') + " seconds.")
statistics['WARNING'] += 1
global_status['SLOW_QUERIES'] = int(row['Value'])
statistics['SLOWQ'] += slowqs
#Server uptime
if (row['Variable_name'] == 'Uptime'):
global_status['UPTIME'] = int(row['Value'])
cursor.close()
