MySQL5.5 real time monitor at linux（centos）——MySQL5.5实时监控基于centos

原文链接：http://blog.csdn.net/ylqmf/article/details/7878498

利用值班时间用python写了个mysql 实时监控脚本，使用前要确认安装python和mysqldb:

yum install python MySQL-python -y

直接执行脚本就可以了 python monitor.py

下面是执行后的结果，硬盘和网卡监控尚未加入：

'''
Created on 2012-8-16
MySQL real time status
@author: [e-mail]
'''
import MySQLdb,os,time
from decimal import Decimal
mysql_host='localhost'
unix_socket='/tmp/mysql.sock'
mysql_user='root'
mysql_pwd='123456'
mysql_db='test'
disk_list=['sda']
sleep_time=2

class mysqlrealtimestatus(object):
    
    def __init__(self,conf):
        self.conf=conf
        self.db=db(conf)
        self.previoustatus=None
        self.nextstatus=None
        self.previousdisk=None
        self.nextdisk=None
        
        
    def run(self):
        while 1:
            i=os.system('clear')
            self.getstatus()
            time.sleep(self.conf['sleep']);
            
            
    def getstatus(self):
        self.previoustatus = self.nextstatus
        sql = "show global status;"
        self.nextstatus = dict(self.db.execute(sql))
        #print self.nextstatus
        sql="show full processlist;"
        set = self.db.execute(sql,'dict')
        
        self.now = time.strftime('%H:%M:%S',time.localtime(time.time()))
        if self.previoustatus!=None and long(self.nextstatus['Uptime_since_flush_status'])>long(self.previoustatus['Uptime_since_flush_status']):
            self.computer();
        print('==========================slow sql==========================')
        #mysqlrealtimestatus.printl(('id','user','host','db','command','time','state','info'),8)
        for process in set:
            if str(process['Command'])=='Query' and int(process['Time'])>2:
                print('Id:'+str(process['Id'])+'\t'+
                'User:'+str(process['User'])+'\t'+
                'Host:'+str(process['Host'])+'\t'+
                'db:'+str(process['db'])+'\t'+
                'Command:'+str(process['Command'])+'\t'+
                'Time:'+str(process['Time'])+'\t'+
                'State:'+str(process['State']))
                print('Info:'+str(process['Info']))
                print('---------------------------------------------------------------------------------')
                
        
    def computer(self):
        ops=Decimal(self.relcount('Questions'))/Decimal(self.relcount('Uptime_since_flush_status'))
        tps=(Decimal(self.relcount('Com_commit'))+Decimal(self.relcount('Com_rollback')))/Decimal(self.relcount('Uptime_since_flush_status'))
        sps=Decimal(self.relcount('Com_select')+self.relcount('Qcache_hits'))/Decimal(self.relcount('Uptime_since_flush_status'))
        ips=Decimal(self.relcount('Com_insert')+self.relcount('Com_insert_select'))/Decimal(self.relcount('Uptime_since_flush_status'))
        ups=Decimal(self.relcount('Com_update')+self.relcount('Com_update_multi'))/Decimal(self.relcount('Uptime_since_flush_status'))
        dps=Decimal(self.relcount('Com_delete')+self.relcount('Com_delete_multi'))/Decimal(self.relcount('Uptime_since_flush_status'))
        rps=Decimal(self.relcount('Com_replace')+self.relcount('Com_replace_select'))/Decimal(self.relcount('Uptime_since_flush_status'))
        
        bsent_ps=Decimal(self.relcount('Bytes_sent'))/Decimal(self.relcount('Uptime_since_flush_status'))
        if(bsent_ps<0):
            bsent_ps=Decimal(self.status['Bytes_sent'])/Decimal(self.status['Uptime_since_flush_status'])
        breceived_ps=Decimal(self.relcount('Bytes_received'))/Decimal(self.relcount('Uptime_since_flush_status'))
        if(breceived_ps<0):
            breceived_ps=Decimal(self.status['Bytes_received'])/Decimal(self.status['Uptime_since_flush_status'])
        if Decimal(self.relcount('Innodb_buffer_pool_read_requests'))>0:
            ib_read_hits=1-Decimal(self.relcount('Innodb_buffer_pool_reads')+self.relcount('Innodb_buffer_pool_read_ahead'))/Decimal(self.relcount('Innodb_buffer_pool_read_requests'))
        else:
            ib_read_hits=1
        
        ib_used_percent=1-Decimal(self.nextstatus['Innodb_buffer_pool_pages_free'])/Decimal(self.nextstatus['Innodb_buffer_pool_pages_total'])
        ib_dirty_page_percent=Decimal(self.nextstatus['Innodb_buffer_pool_pages_dirty'])/Decimal(self.nextstatus['Innodb_buffer_pool_pages_total'])
        
        if(self.nextstatus.has_key('Innodb_row_lock_waits')):
            ir_lock_waits_ps=Decimal(self.relcount('Innodb_row_lock_waits'))/Decimal(self.relcount('Uptime_since_flush_status'))
        else:
            ir_lock_waits_ps=0
        if(self.relcount('Questions')>0):
            sq_percent=Decimal(self.relcount('Slow_queries'))/Decimal(self.relcount('Questions'))
        else:
            sq_percent=0
        sq_ps=Decimal(self.relcount('Slow_queries'))/Decimal(self.relcount('Uptime_since_flush_status'))
        if(self.relcount('Created_tmp_tables')>0):
            td_percent=Decimal(self.relcount('Created_tmp_disk_tables'))/Decimal(self.relcount('Created_tmp_tables'))
        else:
            td_percent=0
        opened_tables_ps=Decimal(self.relcount('Opened_tables'))/Decimal(self.relcount('Uptime_since_flush_status'))
        if(self.nextstatus.has_key('Opened_files')):
            opened_files_ps=Decimal(self.relcount('Opened_files'))/Decimal(self.relcount('Uptime_since_flush_status'))
        else:
            opened_files_ps=0
        if(self.relcount('Connections')>0):
            thread_cache_hits=1-Decimal(self.relcount('Threads_created'))/Decimal(self.relcount('Connections'))
        else:
            thread_cache_hits=1
        
        mysqlrealtimestatus.printl(('time','ops','tps','sps','ips','ups','dps','rps','bsps','brps','%ihpct','%upct','%dpct','ilwps','%sqpct','%tdpct','ofps','%tcpct'))
        mysqlrealtimestatus.println((self.now,
                                     mysqlrealtimestatus.dFormat(ops),
                                     mysqlrealtimestatus.dFormat(tps),
                                     mysqlrealtimestatus.dFormat(sps),
                                     mysqlrealtimestatus.dFormat(ips),
                                     mysqlrealtimestatus.dFormat(ups),
                                     mysqlrealtimestatus.dFormat(dps),
                                     mysqlrealtimestatus.dFormat(rps),
                                     mysqlrealtimestatus.dFormat(bsent_ps),
                                     mysqlrealtimestatus.dFormat(breceived_ps),
                                     mysqlrealtimestatus.perF(ib_read_hits),
                                     mysqlrealtimestatus.perF(ib_used_percent),
                                     mysqlrealtimestatus.perF(ib_dirty_page_percent),
                                     mysqlrealtimestatus.dFormat(ir_lock_waits_ps),
                                     mysqlrealtimestatus.perF(sq_percent),
                                     mysqlrealtimestatus.perF(td_percent),
                                     mysqlrealtimestatus.dFormat(opened_files_ps),
                                     mysqlrealtimestatus.perF(thread_cache_hits)
                                     ))
        #i=os.system('dstat -cglmpdy --tcp')
        loadavg=self.load_stat()
        mem=self.memory_stat()
        swap=self.swap_stat()
        self.previousdisk=self.nextdisk
        self.nextdisk=self.disk_stat()
        mysqlrealtimestatus.printl(('time','lavg1','lavg5','lavg15','mTotal','mUsed','Buffer','Cached','mFree','swapt','swapu',),8)
        mysqlrealtimestatus.println((self.now,
                                     mysqlrealtimestatus.dFormat(loadavg['lavg_1']),
                                     mysqlrealtimestatus.dFormat(loadavg['lavg_5']),
                                     mysqlrealtimestatus.dFormat(loadavg['lavg_15']),
                                     mysqlrealtimestatus.dFormat(Decimal(str(mem['MemTotal']))),
                                     mysqlrealtimestatus.dFormat(Decimal(str(mem['MemUsed']))),
                                     mysqlrealtimestatus.dFormat(Decimal(str(mem['Buffers']))),
                                     mysqlrealtimestatus.dFormat(Decimal(str(mem['Cached']))),
                                     mysqlrealtimestatus.dFormat(Decimal(str(mem['MemFree']))),
                                     mysqlrealtimestatus.dFormat(Decimal(str(swap['swapt']))*1024),
                                     mysqlrealtimestatus.dFormat(Decimal(str(swap['swapu']))*1024)
                                     ),8)
        #print 
    #!/usr/bin/env python
    def load_stat(self):
        loadavg = {}
        f = open("/proc/loadavg")
        con = f.read().split()
        f.close()
        loadavg['lavg_1']=Decimal(con[0])
        loadavg['lavg_5']=Decimal(con[1])
        loadavg['lavg_15']=Decimal(con[2])
        return loadavg
    #!/usr/bin/env python
    def memory_stat(self):
        mem = {}
        f = open("/proc/meminfo")
        lines = f.readlines()
        f.close()
        for line in lines:
            if len(line) < 2: continue
            name = line.split(':')[0]
            var = line.split(':')[1].split()[0]
            mem[name] = long(var) * 1024.0
        mem['MemUsed'] = mem['MemTotal'] - mem['MemFree'] - mem['Buffers'] - mem['Cached']
        return mem
    def disk_stat(self):
        disk=[]
        f = open("/proc/diskstats")
        lines = f.readlines()
        f.close()
        for disk_name in disk_list:
            for row in lines:
                if str(row).find(' '+disk_name+' ')>0:
                    con=str(row).split(' ')
                    disk.append({'disk_name':disk_name,'rcount':con[2],'rrcount':con[3],'rdcount':con[3],'rtime':con[4],'wcount':con[5],'rwcount':con[6],'wdcount':con[7],'wtime':con[8],})
                    break
                
        return disk
    def swap_stat(self):
        swap={}
        f = open("/proc/swaps")
        l = f.readlines()
        f.close()
        con=str(l[1]).split('\t')
        swap['swapt']=con[1]
        swap['swapu']=con[2]
        return swap
    
    #!/usr/bin/env python
    def net_stat(self):
        net = []
        f = open("/proc/net/dev")
        lines = f.readlines()
        f.close()
        for line in lines[2:]:
            con = line.split()
            
            intf = {}
            intf['interface'] = con[0].lstrip(":")
            intf['ReceiveBytes'] = int(con[1])
            intf['ReceivePackets'] = int(con[2])
            intf['ReceiveErrs'] = int(con[3])
            intf['ReceiveDrop'] = int(con[4])
            intf['ReceiveFifo'] = int(con[5])
            intf['ReceiveFrames'] = int(con[6])
            intf['ReceiveCompressed'] = int(con[7])
            intf['ReceiveMulticast'] = int(con[8])
            intf['TransmitBytes'] = int(con[9])
            intf['TransmitPackets'] = int(con[10])
            intf['TransmitErrs'] = int(con[11])
            intf['TransmitDrop'] = int(con[12])
            intf['TransmitFifo'] = int(con[13])
            intf['TransmitFrames'] = int(con[14])
            intf['TransmitCompressed'] = int(con[15])
            #intf['TransmitMulticast'] = int(con[16])
            """
            intf = dict(
                zip(
                    ( 'interface','ReceiveBytes','ReceivePackets',
                      'ReceiveErrs','ReceiveDrop','ReceiveFifo',
                      'ReceiveFrames','ReceiveCompressed','ReceiveMulticast',
                      'TransmitBytes','TransmitPackets','TransmitErrs',
                      'TransmitDrop', 'TransmitFifo','TransmitFrames',
                      'TransmitCompressed','TransmitMulticast' ),
                    ( con[0].rstrip(":"),int(con[1]),int(con[2]),
                      int(con[3]),int(con[4]),int(con[5]),
                      int(con[6]),int(con[7]),int(con[8]),
                      int(con[9]),int(con[10]),int(con[11]),
                      int(con[12]),int(con[13]),int(con[14]),
                      int(con[15]),int(con[16]))
                )
            )
            """
            net.append(intf)
        return net
    
    def relcount(self,param):
        return Decimal(self.nextstatus[param])-Decimal(self.previoustatus[param])
    
    @staticmethod
    def println(param,s=7):
        p=""
        for i in param:
            if type(i)==type(""):
                p+=i+" "
            else:
                p+=str(i[0]).ljust(s)
        
        print p
    @staticmethod
    def printl(param,s=7):
        p=""
        for i in param:
            if str(i)=='time':
                p+=str(i)+"     "
            else:
                p+=str(i).ljust(s)
        
        print p
        
    @staticmethod
    def perF(param):
        return mysqlrealtimestatus.dFormat(param*100)
    
    @staticmethod
    def dFormat(val):
        k=1024
        m=k*k
        g=k*m
        t=k*g
        p=k*t
        dp=0
        dm=""
        if(val!=0):
            if(val>p):
                dp=p
                dm="P"
            elif(val>t):
                dp=t
                dm="T"
            elif(val>g):
                dp=g
                dm="G"
            elif(val>m):
                dp=m
                dm="M"
            elif(val>k):
                dp=k
                dm="k"
            else:
                dp=1
            return ["%2.2f" % (Decimal(val)/Decimal(dp)) +dm]
        else:
            return ["%2.2f" % 0]
'''

'''
class MySQLHelper(object):
    @staticmethod
    def getConn(conf):
        pot = 3306
        if(conf.has_key('port')):
            pot=conf['port']
        dbname='test'
        if(conf.has_key('db')):
            dbname=conf['db']
        
        if(conf.has_key('socket')):
            return MySQLdb.connect(host=conf['host'],unix_socket=conf['socket'],user=conf['user'],passwd=conf['pwd'],db=dbname)
        else:
            return MySQLdb.connect(host=conf['host'],port=pot,user=conf['user'],passwd=conf['pwd'],db=dbname)
'''

'''
class db (object):
    def __init__(self,conf):
        self.conn=None
        self.conn=MySQLHelper.getConn(conf)
    
    def execute(self,sql,mod=''):
        if(mod=='dict'):
            cursor=self.conn.cursor(MySQLdb.cursors.DictCursor)
        else:
            cursor=self.conn.cursor()
        cursor.execute(sql)
        set=cursor.fetchall()
        return set
    
    def executeNoQuery(self,sql,param={}):
        cursor=self.conn.cursor()
        try:
            if(param=={}):
                rownum=cursor.execute(sql)
            else:
                rownum=cursor.executemany(sql,param)
            self.conn.commit()
            return rownum
        finally:
            cursor.close()
    
    def __del__(self):
        if (self.conn!=None):
            self.conn.close()

if __name__ == '__main__':
    conf={'host':mysql_host,'socket':unix_socket,'user':mysql_user,'pwd':mysql_pwd,'db':mysql_db,'sleep':sleep_time}
    status=mysqlrealtimestatus(conf);
    status.run();

原文链接：http://blog.csdn.net/ylqmf/article/details/7878498