#!/usr/bin/env python

# This script is supposed to be run on a host which is running a mysql
# replication slave. It should be started from the crontab every
# hour(?).
#
# This script makes sure that the mysql replication slave is fine on
# the localhost. If there is any problem, that is, if the replication
# is broken because of an error or if the lag between the master and
# the server is too important (see the MAX_LAG_SECONDS parameter),
# then an e-mail is sent to the ps-ipp-ops mailing list (actually the
# RECIPIENT parameter).
#

import MySQLdb
import smtplib
from email.mime.text import MIMEText
import socket

def send_email(recipient, subject, body):
    message = MIMEText(body)
    message['Subject'] = subject
    _from = 'ipp@ifa.hawaii.edu'
    message['From'] = _from
    _to = [recipient]
    message['To'] = recipient
    SMTP_CONNECTION = smtplib.SMTP('hale.ifa.hawaii.edu')
    SMTP_CONNECTION.sendmail(_from, _to, message.as_string())
    SMTP_CONNECTION.quit()

if __name__ == '__main__':
    # Configuration parameters
    MAX_LAG_SECONDS = 10000
    RECIPIENT = 'ps-ipp-ops@ifa.hawaii.edu'

    # Don't change anything after this
    db = MySQLdb.connect('localhost', 'root')
    cursor = db.cursor()
    cursor.execute('SHOW SLAVE STATUS')
    ( Slave_IO_State, Master_Host, Master_User, Master_Port,
      Connect_Retry, Master_Log_File, Read_Master_Log_Pos, Relay_Log_File,
      Relay_Log_Pos, Relay_Master_Log_File, Slave_IO_Running, Slave_SQL_Running,
      Replicate_Do_DB, Replicate_Ignore_DB, Replicate_Do_Table, Replicate_Ignore_Table,
      Replicate_Wild_Do_Table, Replicate_Wild_Ignore_Table, 
      Last_Errno, Last_Error, Skip_Counter, Exec_Master_Log_Pos,
      Relay_Log_Space, Until_Condition, Until_Log_File, Until_Log_Pos,
      Master_SSL_Allowed, Master_SSL_CA_File, Master_SSL_CA_Path, Master_SSL_Cert,
      Master_SSL_Cipher, Master_SSL_Key, Seconds_Behind_Master) = cursor.fetchall()[0]
    nodename = socket.gethostbyaddr(socket.gethostname())[0].split('.')[0]
    if Seconds_Behind_Master is None:
        if Last_Error != '':
            send_email(RECIPIENT, 
                       'MySQL replication problem on %s' % (nodename),
                       """Last_error value when executing SHOW SLAVE STATUS on that host is:
%s
""" % (Last_Error) )
        else:
            send_email(RECIPIENT, 
                       'MySQL replication problem on %s' % (nodename),
                       """Slave_IO_State value when executing SHOW SLAVE STATUS on that host is:
%s
""" % (Slave_IO_State) )
            print 'Replication problem: %s (is master down?)' % Slave_IO_State
    elif Seconds_Behind_Master > MAX_LAG_SECONDS:
        nodename = socket.gethostbyaddr(socket.gethostname())[0].split('.')[0]
        send_email(RECIPIENT, 
                   'MySQL replication is late on %s' % (nodename),
                   """The Seconds_Behind_Master value when executing the 'SHOW SLAVE STATUS' mysql statement on that host is:
   %s

For info, the threshold is %s seconds.

Check if there is a blocking statement executing on the slave
""" % (Seconds_Behind_Master, MAX_LAG_SECONDS) )
    else:
        pass
    cursor.close()
    db.close()
