闲来无事写了个
python监控mysql主从的脚本,算是记录
学习python阶段性的一个实战吧!
#coding=utf-8
import MySQLdb
import smtplib
from email.mime.text import MIMEText
#定义一个发邮件函数
def mail(sub,content):
mailto_list=["hzwuj@tairanchina.com"]
mail_host="smtp.tairanchina.com"
mail_uer="trcloud@tairanchina.com"
mail_pass="123456"
message = MIMEText(content,_charset='utf-8')
message['Subject'] = sub
message['From']=mail_uer
message['To'] = ";".join(mailto_list)
try:
s = smtplib.SMTP()
s.connect(mail_host)
s.login(mail_uer,mail_pass)
s.sendmail(mail_uer, mailto_list, message.as_string())
s.close()
return True
except Exception:
print 'filed'
return False
#连接mysql数据库
conn=MySQLdb.connect(host='172.30.249.57',port=3306,user='root',passwd='Welcome1>')
cursor1=conn.cursor()
cursor1.execute("show slave status")
rows=cursor1.fetchall()
#获取主从同步信息
try:
for list in rows:
Master_Host=list[1]
Master_Log_File=list[5]
Read_Master_Log_Pos=list[6]
Relay_Master_Log_File=list[9]
Exec_Master_Log_Pos=list[21]
Slave_IO_Running=list[10]
Slave_SQL_Running=list[11]
Seconds_Behind_Master=list[32]
Last_IO_Errno=list[34]
Last_IO_Error=list[35]
Last_SQL_Errno=list[36]
Last_SQL_Error=list[37]
#判断主从复制信息,对出现异常的信息发邮件告警
if Slave_IO_Running=='No' or Slave_SQL_Running=='No' :
mail('主从故障',"Master_Host:'%s' Last_IO_Errno:'%s' Last_IO_Error:'%s' Last_SQL_Errno:'%s' Last_SQL_Error:'%s'" %(Master_Host,Last_IO_Errno,Last_IO_Error,Last_SQL_Errno,Last_SQL_Error))
elif Seconds_Behind_Master>600:
mail('主从延迟',"Master_Host:'%s' Master_Log_File:'%s' Read_Master_Log_Pos:'%s' Relay_Master_Log_File:'%s'"
" Exec_Master_Log_Pos:'%s' Seconds_Behind_Master:'%s'" %(Master_Host,Master_Log_File,Read_Master_Log_Pos,Relay_Master_Log_File,Exec_Master_Log_Pos,Seconds_Behind_Master))
else:
print '从库正常'
except:
mail('连接异常','连接不上数据库')
#coding=utf-8
import MySQLdb
import smtplib
from email.mime.text import MIMEText
#定义一个发邮件函数
def mail(sub,content):
mailto_list=["hzwuj@tairanchina.com"]
mail_host="smtp.tairanchina.com"
mail_uer="trcloud@tairanchina.com"
mail_pass="123456"
message = MIMEText(content,_charset='utf-8')
message['Subject'] = sub
message['From']=mail_uer
message['To'] = ";".join(mailto_list)
try:
s = smtplib.SMTP()
s.connect(mail_host)
s.login(mail_uer,mail_pass)
s.sendmail(mail_uer, mailto_list, message.as_string())
s.close()
return True
except Exception:
print 'filed'
return False
#连接mysql数据库
conn=MySQLdb.connect(host='172.30.249.57',port=3306,user='root',passwd='Welcome1>')
cursor1=conn.cursor()
cursor1.execute("show slave status")
rows=cursor1.fetchall()
#获取主从同步信息
try:
for list in rows:
Master_Host=list[1]
Master_Log_File=list[5]
Read_Master_Log_Pos=list[6]
Relay_Master_Log_File=list[9]
Exec_Master_Log_Pos=list[21]
Slave_IO_Running=list[10]
Slave_SQL_Running=list[11]
Seconds_Behind_Master=list[32]
Last_IO_Errno=list[34]
Last_IO_Error=list[35]
Last_SQL_Errno=list[36]
Last_SQL_Error=list[37]
#判断主从复制信息,对出现异常的信息发邮件告警
if Slave_IO_Running=='No' or Slave_SQL_Running=='No' :
mail('主从故障',"Master_Host:'%s' Last_IO_Errno:'%s' Last_IO_Error:'%s' Last_SQL_Errno:'%s' Last_SQL_Error:'%s'" %(Master_Host,Last_IO_Errno,Last_IO_Error,Last_SQL_Errno,Last_SQL_Error))
elif Seconds_Behind_Master>600:
mail('主从延迟',"Master_Host:'%s' Master_Log_File:'%s' Read_Master_Log_Pos:'%s' Relay_Master_Log_File:'%s'"
" Exec_Master_Log_Pos:'%s' Seconds_Behind_Master:'%s'" %(Master_Host,Master_Log_File,Read_Master_Log_Pos,Relay_Master_Log_File,Exec_Master_Log_Pos,Seconds_Behind_Master))
else:
print '从库正常'
except:
mail('连接异常','连接不上数据库')
注:Seconds_Behind_Master是理论上显示了备库的延迟,但由于某些原因,并不总是准确的值。这里暂且用这个列数据!
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29989552/viewspace-2130147/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29989552/viewspace-2130147/