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'<Mysql主从>','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丛库服务器状态监控