监控Mysql主从同步脚本

Shell版本

#!/bin/bash
#Auth: lucky.chen

hosts="192.168.3.9:3305
192.168.3.10:3306
"
for i in $hosts 
do
        alert=0
	host=`echo $i|awk -F':' '{print $1}'`
	port=`echo $i|awk -F':' '{print $2}'`
	declare -i alert	
	IO=`mysql -uwrite -P$port -p'write@jkb' -h${host}  -e "show slave status\G"|grep Slave_IO_Running: |awk '{print $NF}'`
	SQL=`mysql -uwrite -P$port -p'write@jkb' -h${host}  -e "show slave status\G"|grep Slave_SQL_Running: |awk '{print $NF}'`
	declare -i BEHIN=`mysql -uwrite -P$port -p'write@jkb' -h${host}  -e "show slave status\G"|grep Seconds_Behind_Master|awk '{print $NF}'`
	
	if [ $IO != Yes ] ;then
        status="${status} \n IO is $IO"
	alert=1
	fi

	if [ $SQL != Yes ] ;then
	stauts="${status} \n SQL is $SQL"
	alert=1
	fi

	if [[ $BEHIN -gt 100 ]] ;then
       	status="${status} \n behind master $BEHIN second"
	alert=1
	fi


	if [[ alert -eq 1 ]] ;then
	echo -e "$host : $status"
       php /usr/local/bin/sendmail/tongbu.php  "$host $status" "$status"
	fi

done

python简易版本

#!/usr/bin/env python
# _[i]_coding: utf8_[/i]_
import MySQLdb
from MySQLdb import cursors
import threading

slaveList = [
             'ip list'
             ]
def getSlaveTime(host):
    try:
        username = 'username'
        passwd = 'password'
        conn = MySQLdb.connect(user = username, passwd = passwd,  host = host, connect_timeout = 5, cursorclass = cursors.DictCursor)
        cur = conn.cursor()
        cur.execute('''show slave status''')
        fallSec = cur.fetchone()['Seconds_Behind_Master']
        cur.close()
        conn.close()
        print  host + ' 落后 ' + str(fallSec)
    except:
        print host + ' 落后 ' + str(10000000)
    
for host in slaveList:
    s = threading.Thread(target = getSlaveTime,args = (host,))
    s.start()