python监控mysql主从脚本_python监控mysql主从

监控mysql主从的python脚本，不是自己写的，非原创，可以学习借鉴。

#!/usr/bin/env python

# -*- coding: utf-8 -*-

#File:repl_wx.py

#creater:wangwei

import MySQLdb

import Queue,os,base64,time,sys,wx,threading

import ConfigParser,logging

from wx.lib.mixins.listctrl import ListCtrlAutoWidthMixin

class Repl:

def __init__(self,user,passwd,host,hostname):

self.user = user

self.passwd = passwd

self.host = host

self.hostname = hostname

def Slave(self,user,passwd,host,hostname):

try:

conn = MySQLdb.connect(host,user = self.user,passwd = self.passwd,connect_timeout = 2)

cursor = conn.cursor(cursorclass = MySQLdb.cursors.DictCursor)

sql = "SHOW VARIABLES LIKE 'version'"

cursor.execute(sql)

alldata = cursor.fetchall()

version = alldata[0]["Value"].split('.')[0]

sql = "show slave status"

cursor.execute(sql)

alldata = cursor.fetchall()

IO = alldata[0]["Slave_IO_Running"]

SQL = alldata[0]["Slave_SQL_Running"]

if version == "4":

Errno = alldata[0]["Last_errno"]

Error = alldata[0]["Last_error"]

else:

Errno = alldata[0]["Last_Errno"]

Error = alldata[0]["Last_Error"]

cursor.close()

conn.close()

return IO,SQL,Errno,Error

except:

return 0,0,0,0

class Check:

def __init__(self,app,user,passwd,host,hostname):

self.app=app

self.user = user

self.passwd = passwd

self.host = host

self.hostname = hostname

boss = Repl(user,passwd,host,hostname)

IO,SQL,Errno,Error = boss.Slave(self.user,self.passwd,self.host,self.hostname)

self.IO = IO

self.SQL = SQL

self.Errno = Errno

self.Error = Error

def Status(self):

now = time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(time.time()))

if not self.IO and not self.SQL:

errlist = [self.host,self.hostname,now,'Error:Can not Connect mysql!']

wx.CallAfter(self.app.ErrorMessage, errlist)

a = self.hostname + " " + self.host + " Can not Connect mysql DB!"

logging.basicConfig(level=logging.DEBUG,format='%(asctime)s %(levelname)s %(message)s',datefmt='%Y-%m-%d %H:%M:%S',filename='slave.log',filemode='a')

logging.error(a)

return 0

if self.IO == "Yes":

if self.SQL == "Yes":

#print now + " " + self.hostname + " OK -slave is running " + self.host

#errlist = [self.host,self.hostname,now,'OK -slave is running!']

#wx.CallAfter(self.app.ErrorMessage, errlist)

return 0

else:

#print now + " " + self.hostname + " Critical -slave SQL Error!"

errlist = [self.host,self.hostname,now,'Critical -slave SQL Error!']

wx.CallAfter(self.app.ErrorMessage, errlist)

a = self.hostname + " " + self.host + " Critical -slave SQL Error!"

logging.basicConfig(level=logging.DEBUG,format='%(asctime)s %(levelname)s %(message)s',datefmt='%Y-%m-%d %H:%M:%S',filename='slave.log',filemode='a')

logging.error(a)

return 1

else:

if self.SQL == "Yes":

#print now + " " + self.hostname + " Critical -slave IO Error!"

errlist = [self.host,self.hostname,now,'Critical -slave IO Error!']

wx.CallAfter(self.app.ErrorMessage, errlist)

a = self.hostname + " " + self.host + " Critical -slave IO Error!"

logging.basicConfig(level=logging.DEBUG,format='%(asctime)s %(levelname)s %(message)s',datefmt='%Y-%m-%d %H:%M:%S',filename='slave.log',filemode='a')

logging.error(a)

return 2

else:

#print now + " " + self.hostname + " Critical -slave IO and SQL Error!"

errlist = [self.host,self.hostname,now,'Critical -slave IO and SQL Error!']

wx.CallAfter(self.app.ErrorMessage, errlist)

a = self.hostname + " " + self.host + " Critical -slave IO and SQL Error!"

logging.basicConfig(level=logging.DEBUG,format='%(asctime)s %(levelname)s %(message)s',datefmt='%Y-%m-%d %H:%M:%S',filename='slave.log',filemode='a')

logging.error(a)

return 3

class StartScan(threading.Thread):

def __init__(self,app):

threading.Thread.__init__(self)

self.app = app

self.timeToQuit = threading.Event()

self.timeToQuit.clear()

def stop(self):

self.timeToQuit.set()

def run(self):

self.user,self.passwd,self.hostlist,self.a,self.time = self.conf()

self.times = 1

while True:

threads = []

if self.timeToQuit.isSet():

break

for i in range(self.a):

host = self.hostlist[i][0]

hostname = self.hostlist[i][1]

boss = Check(self,self.user,self.passwd,host,hostname)

t = threading.Thread(target=boss.Status,args=())

threads.append(t)

#print 'Total %s Threads is working...' %self.a + '\n'

msg = u"第" + "%s"%(self.times) + u"次扫描....."

self.app.CurrentScan(msg)

for i in range(self.a):

threads[i].start()

time.sleep(0.05)

for i in range(self.a):

threads[i].join()

self.times += 1

time.sleep(self.time)

def CurrentScan(self,msg):

wx.CallAfter(self.app.CurrentScan, msg)

def conf(self):

fp = ConfigParser.ConfigParser()

fp.readfp(open('repl.ini'))

iplist = fp.get("global", "iplist")

user = fp.get("global", "user")

passwd = fp.get("global", "passwd")

time = fp.getint("global","time")

user = base64.decodestring(user)

passwd = base64.decodestring(passwd)

hostlist = []

for i in iplist.split(";"):

hostlist.append(i.split(","))

a = len(hostlist)

return user,passwd,hostlist,a,time

def ErrorMessage(self,errlist):

wx.CallAfter(self.app.ErrorMessage, errlist)

class ScanUnit(wx.Panel):

def __init__(self, parent,id=-1,title='',port='',):

wx.Panel.__init__(self, parent,id)

self.parent = parent

self.id = id

self.title = title

self.scaning = wx.StaticText(self,-1,label=u"准备扫描……",style = wx.ALIGN_LEFT|wx.ST_NO_AUTORESIZE)

self.openBtn=wx.Button(self,-1,u'打开日志')

self.openBtn.SetForegroundColour('red')

#self.openBtn.SetBackgroundColour('purple')

self.startBtn=wx.Button(self,-1,u'扫描')

self.stopBtn=wx.Button(self,-1,u'停止')

self.stopBtn.Disable()

self.list = AutoWidthListCtrl(self)

self.list.SetTextColour("red")

self.list.InsertColumn(0, 'IP', width=120)

self.list.InsertColumn(1, u'区组名称', width=100)

self.list.InsertColumn(3, u'发生时间',width=140)

self.list.InsertColumn(4, u'返回信息',width=200)

self.list.DeleteAllItems()

self.Bind(wx.EVT_BUTTON,self.OnStart,self.startBtn)

self.Bind(wx.EVT_BUTTON,self.OnStop,self.stopBtn)

self.Bind(wx.EVT_BUTTON,self.Open,self.openBtn)

self._layout()

def _layout(self): #布局

action = wx.BoxSizer(wx.HORIZONTAL)

action.Add(self.scaning,3, wx.ALIGN_CENTER_HORIZONTAL|wx.EXPAND)#对齐没有解决

action.Add(self.startBtn,1, wx.ALIGN_CENTER_VERTICAL|wx.EXPAND)

action.Add(self.stopBtn,1, wx.ALIGN_CENTER_VERTICAL|wx.EXPAND)

action.Add(self.openBtn,1, wx.ALIGN_CENTER_VERTICAL|wx.EXPAND)

listbox = wx.BoxSizer(wx.HORIZONTAL)

listbox.Add(self.list, 1, wx.EXPAND)

self.box = wx.StaticBox(self, self.id, self.title,style = wx.SUNKEN_BORDER)

self.ScanUnit = wx.StaticBoxSizer(self.box, wx.VERTICAL)

self.ScanUnit.Add(action, 0, wx.ALL|wx.EXPAND, 2)

self.ScanUnit.Add(listbox, 5, wx.EXPAND, 2)

self.SetSizer(self.ScanUnit)

self.parent.sizer.Add(self,1,wx.EXPAND)

def OnStart(self,event):

self.startBtn.Disable()

self.stopBtn.Enable()

#self.DeleteItems()

self.thread=StartScan(self)

self.thread.setDaemon(True)

self.thread.start()

self.scaning.SetLabel(u'正在扫描……')

def OnStop(self,event):

self.stopBtn.Disable()

self.startBtn.Enable()

self.thread.stop()

self.scaning.SetLabel(u'停止扫描……')

def Open(self,event):

wx.Execute("notepad slave.log")

#os.system('notepad slave.log')

def CurrentScan(self,msg): #当前扫描动作

self.scaning.SetLabel(msg)

def ErrorMessage(self,errlist):#错误信息

index = self.list.InsertStringItem(sys.maxint, errlist[0])

self.list.SetStringItem(index, 1, errlist[1])

self.list.SetStringItem(index, 2, errlist[2])

self.list.SetStringItem(index, 3, errlist[3])

self.list.RefreshItem(index)

def DeleteItems(self):

self.list.DeleteAllItems()

class AutoWidthListCtrl(wx.ListCtrl, ListCtrlAutoWidthMixin):

def __init__(self, parent):

wx.ListCtrl.__init__(self, parent, -1, style=wx.LC_REPORT|wx.LC_HRULES|wx.LC_VRULES)

ListCtrlAutoWidthMixin.__init__(self)

class MainPanel(wx.Panel):

def __init__(self,parent):

wx.Panel.__init__(self, parent)

self.sizer = wx.GridSizer(rows=1,cols=1,hgap = 20,vgap = 15)

self.aaa = ScanUnit(self,-1,u'','8001')

self.SetSizer(self.sizer)

class CreateMenu():#创建菜单

def __init__(self,parent):

self.menuBar = wx.MenuBar()

self.file = wx.Menu()

self.close = self.file.Append(-1,u'退出(&X)')

self.menuBar.Append(self.file,u'文件(&F)')

self.help = wx.Menu()

self.about = self.help.Append(-1,u'关于(&A)')

self.menuBar.Append(self.help,u'帮助(&H)')

parent.SetMenuBar(self.menuBar)

class MyFrame(wx.App):

u'''''Mysql主从监控\nE-mail:wangwei03@gyyx.cn\nQQ:83521260'''

def OnInit(self):

self.frame=wx.Frame(parent=None,id=-1,title=u'Mysql主从监控程序',size=(650,450))

self.frame.SetIcon(wx.Icon('kankan.ico', wx.BITMAP_TYPE_ICO))

self.panel = MainPanel(self.frame)

self.frame.Center(direction=wx.BOTH)

self.menu = CreateMenu(self.frame)

self.frame.statusBar = self.frame.CreateStatusBar(3)

#print dir(self.frame.statusBar)

self.frame.statusBar.SetStatusWidths([-12,-12,-13])

self.frame.statusBar.SetForegroundColour('purple')

self.frame.statusBar.SetBackgroundColour('pink')

self.frame.StatusBar.Font.Bold = True

#self.frame.StatusBar.Font.Size = 13

self.frame.StatusBar.SetStatusText(u"好好学习",0)

self.frame.StatusBar.SetStatusText(u"天天向上",1)

self.timer=wx.PyTimer(self.Notify)

self.timer.Start(1000)

self.Notify()

self.Bind(wx.EVT_MENU,self.OnClose,self.menu.close)

self.Bind(wx.EVT_MENU,self.OnAbout,self.menu.about)

self.SetTopWindow(self.frame)

self.frame.Show()

return True

def Notify(self):

t = time.localtime(time.time())

st = time.strftime("%Y-%m-%d %H:%M:%S",t)

self.frame.StatusBar.SetStatusText(u"当前系统时间 "+st,2)

def OnClose(self,event):

self.frame.Destroy()

def OnAbout(self,event):

wx.MessageBox(self.__doc__,'Mysql Replication Status',wx.OK)

if __name__ == "__main__":

app=MyFrame(None)

app.MainLoop()

QQ截图20161101174400.png

配置文件的名字为：repl.ini

格式为：

[global]

user = cmVwbsdfsdfA==

passwd = SGMxNzVBcEdEZ0ZRTGsfdfV6aA==

time = 10

iplist = 192.168.8.11,烟雨江南;192.168.8.12,开天辟地;

说明：用户名和密码#base64.encodestring()，base64.decodestring()加密和解密，time为设置的超时时间(单位为秒)，iplist为IP和名字列表

主要用于批量的mysql丛库服务器状态监控