Welcome 微信登录

首页 / 数据库 / MySQL / MySQL 5.5实时监控基于CentOS

MySQL5.5 real time monitor at linux（CentOS）——MySQL5.5实时监控基于CentOS。利用值班时间用python写了个mysql 实时监控脚本，使用前要确认安装python和mysqldb:yum install python MySQL-python -y直接执行脚本就可以了 python monitor.py下面是执行后的结果，硬盘和网卡监控尚未加入：
  1. """"" 
  2. Created on 2012-8-16 
  3. MySQL real time status 
  4. @author: tudou@b2c.xiaomi.com 
  5. """  
  6. import MySQLdb,os,time  
  7. from decimal import Decimal  
  8. mysql_host="localhost"  
  9. unix_socket="/tmp/mysql.sock"  
  10. mysql_user="root"  
  11. mysql_pwd="123456"  
  12. mysql_db="test"  
  13. disk_list=["sda"]  
  14. sleep_time=2  
  15.   
  16. class mysqlrealtimestatus（object）:  
  17.       
  18.     def __init__（self,conf）:  
  19.         self.conf=conf  
  20.         self.db=db（conf）  
  21.         self.previoustatus=None  
  22.         self.nextstatus=None  
  23.         self.previousdisk=None  
  24.         self.nextdisk=None  
  25.           
  26.           
  27.     def run（self）:  
  28.         while 1:  
  29.             i=os.system（"clear"）  
  30.             self.getstatus（）  
  31.             time.sleep（self.conf["sleep"]）;  
  32.               
  33.               
  34.     def getstatus（self）:  
  35.         self.previoustatus = self.nextstatus  
  36.         sql = "show global status;"  
  37.         self.nextstatus = dict（self.db.execute（sql））  
  38.         #print self.nextstatus   
  39.         sql="show full processlist;"  
  40.         set = self.db.execute（sql,"dict"）  
  41.           
  42.         self.now = time.strftime（"%H:%M:%S",time.localtime（time.time（）））  
  43.         if self.previoustatus！=None and long（self.nextstatus["Uptime_since_flush_status"]）>long（self.previoustatus["Uptime_since_flush_status"]）:  
  44.             self.computer（）;  
  45.         print（"==========================slow sql=========================="）  
  46.         #mysqlrealtimestatus.printl（（"id","user","host","db","command","time","state","info"）,8）   
  47.         for process in set:  
  48.             if str（process["Command"]）=="Query" and int（process["Time"]）>2:  
  49.                 print（"Id:"+str（process["Id"]）+" "+  
  50.                 "User:"+str（process["User"]）+" "+  
  51.                 "Host:"+str（process["Host"]）+" "+  
  52.                 "db:"+str（process["db"]）+" "+  
  53.                 "Command:"+str（process["Command"]）+" "+  
  54.                 "Time:"+str（process["Time"]）+" "+  
  55.                 "State:"+str（process["State"]））  
  56.                 print（"Info:"+str（process["Info"]））  
  57.                 print（"---------------------------------------------------------------------------------"）  
  58.                   
  59.           
  60.     def computer（self）:  
  61.         ops=Decimal（self.relcount（"Questions"））/Decimal（self.relcount（"Uptime_since_flush_status"））  
  62.         tps=（Decimal（self.relcount（"Com_commit"））+Decimal（self.relcount（"Com_rollback"）））/Decimal（self.relcount（"Uptime_since_flush_status"））  
  63.         sps=Decimal（self.relcount（"Com_select"）+self.relcount（"Qcache_hits"））/Decimal（self.relcount（"Uptime_since_flush_status"））  
  64.         ips=Decimal（self.relcount（"Com_insert"）+self.relcount（"Com_insert_select"））/Decimal（self.relcount（"Uptime_since_flush_status"））  
  65.         ups=Decimal（self.relcount（"Com_update"）+self.relcount（"Com_update_multi"））/Decimal（self.relcount（"Uptime_since_flush_status"））  
  66.         dps=Decimal（self.relcount（"Com_delete"）+self.relcount（"Com_delete_multi"））/Decimal（self.relcount（"Uptime_since_flush_status"））  
  67.         rps=Decimal（self.relcount（"Com_replace"）+self.relcount（"Com_replace_select"））/Decimal（self.relcount（"Uptime_since_flush_status"））  
  68.           
  69.         bsent_ps=Decimal（self.relcount（"Bytes_sent"））/Decimal（self.relcount（"Uptime_since_flush_status"））  
  70.         if（bsent_ps<0）:  
  71.             bsent_ps=Decimal（self.status["Bytes_sent"]）/Decimal（self.status["Uptime_since_flush_status"]）  
  72.         breceived_ps=Decimal（self.relcount（"Bytes_received"））/Decimal（self.relcount（"Uptime_since_flush_status"））  
  73.         if（breceived_ps<0）:  
  74.             breceived_ps=Decimal（self.status["Bytes_received"]）/Decimal（self.status["Uptime_since_flush_status"]）  
  75.         if Decimal（self.relcount（"Innodb_buffer_pool_read_requests"））>0:  
  76.             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"））  
  77.         else:  
  78.             ib_read_hits=1  
  79.           
  80.         ib_used_percent=1-Decimal（self.nextstatus["Innodb_buffer_pool_pages_free"]）/Decimal（self.nextstatus["Innodb_buffer_pool_pages_total"]）  
  81.         ib_dirty_page_percent=Decimal（self.nextstatus["Innodb_buffer_pool_pages_dirty"]）/Decimal（self.nextstatus["Innodb_buffer_pool_pages_total"]）  
  82.           
  83.         if（self.nextstatus.has_key（"Innodb_row_lock_waits"））:  
  84.             ir_lock_waits_ps=Decimal（self.relcount（"Innodb_row_lock_waits"））/Decimal（self.relcount（"Uptime_since_flush_status"））  
  85.         else:  
  86.             ir_lock_waits_ps=0  
  87.         if（self.relcount（"Questions"）>0）:  
  88.             sq_percent=Decimal（self.relcount（"Slow_queries"））/Decimal（self.relcount（"Questions"））  
  89.         else:  
  90.             sq_percent=0  
  91.         sq_ps=Decimal（self.relcount（"Slow_queries"））/Decimal（self.relcount（"Uptime_since_flush_status"））  
  92.         if（self.relcount（"Created_tmp_tables"）>0）:  
  93.             td_percent=Decimal（self.relcount（"Created_tmp_disk_tables"））/Decimal（self.relcount（"Created_tmp_tables"））  
  94.         else:  
  95.             td_percent=0  
  96.         opened_tables_ps=Decimal（self.relcount（"Opened_tables"））/Decimal（self.relcount（"Uptime_since_flush_status"））  
  97.         if（self.nextstatus.has_key（"Opened_files"））:  
  98.             opened_files_ps=Decimal（self.relcount（"Opened_files"））/Decimal（self.relcount（"Uptime_since_flush_status"））  
  99.         else:  
  100.             opened_files_ps=0  
  101.         if（self.relcount（"Connections"）>0）:  
  102.             thread_cache_hits=1-Decimal（self.relcount（"Threads_created"））/Decimal（self.relcount（"Connections"））  
  103.         else:  
  104.             thread_cache_hits=1  
  105.           
  106.         mysqlrealtimestatus.printl（（"time","ops","tps","sps","ips","ups","dps","rps","bsps","brps","%ihpct","%upct","%dpct","ilwps","%sqpct","%tdpct","ofps","%tcpct"））  
  107.         mysqlrealtimestatus.println（（self.now,  
  108.                                      mysqlrealtimestatus.dFormat（ops）,  
  109.                                      mysqlrealtimestatus.dFormat（tps）,  
  110.                                      mysqlrealtimestatus.dFormat（sps）,  
  111.                                      mysqlrealtimestatus.dFormat（ips）,  
  112.                                      mysqlrealtimestatus.dFormat（ups）,  
  113.                                      mysqlrealtimestatus.dFormat（dps）,  
  114.                                      mysqlrealtimestatus.dFormat（rps）,  
  115.                                      mysqlrealtimestatus.dFormat（bsent_ps）,  
  116.                                      mysqlrealtimestatus.dFormat（breceived_ps）,  
  117.                                      mysqlrealtimestatus.perF（ib_read_hits）,  
  118.                                      mysqlrealtimestatus.perF（ib_used_percent）,  
  119.                                      mysqlrealtimestatus.perF（ib_dirty_page_percent）,  
  120.                                      mysqlrealtimestatus.dFormat（ir_lock_waits_ps）,  
  121.                                      mysqlrealtimestatus.perF（sq_percent）,  
  122.                                      mysqlrealtimestatus.perF（td_percent）,  
  123.                                      mysqlrealtimestatus.dFormat（opened_files_ps）,  
  124.                                      mysqlrealtimestatus.perF（thread_cache_hits）  
  125.                                      ））  
  126.         #i=os.system（"dstat -cglmpdy --tcp"）   
  127.         loadavg=self.load_stat（）  
  128.         mem=self.memory_stat（）  
  129.         swap=self.swap_stat（）  
  130.         self.previousdisk=self.nextdisk  
  131.         self.nextdisk=self.disk_stat（）  
  132.         mysqlrealtimestatus.printl（（"time","lavg1","lavg5","lavg15","mTotal","mUsed","Buffer","Cached","mFree","swapt","swapu",）,8）  
  133.         mysqlrealtimestatus.println（（self.now,  
  134.                                      mysqlrealtimestatus.dFormat（loadavg["lavg_1"]）,  
  135.                                      mysqlrealtimestatus.dFormat（loadavg["lavg_5"]）,  
  136.                                      mysqlrealtimestatus.dFormat（loadavg["lavg_15"]）,  
  137.                                      mysqlrealtimestatus.dFormat（Decimal（str（mem["MemTotal"]）））,  
  138.                                      mysqlrealtimestatus.dFormat（Decimal（str（mem["MemUsed"]）））,  
  139.                                      mysqlrealtimestatus.dFormat（Decimal（str（mem["Buffers"]）））,  
  140.                                      mysqlrealtimestatus.dFormat（Decimal（str（mem["Cached"]）））,  
  141.                                      mysqlrealtimestatus.dFormat（Decimal（str（mem["MemFree"]）））,  
  142.                                      mysqlrealtimestatus.dFormat（Decimal（str（swap["swapt"]））*1024）,  
  143.                                      mysqlrealtimestatus.dFormat（Decimal（str（swap["swapu"]））*1024）  
  144.                                      ）,8）  
  145.         #print    
  146.     #！/usr/bin/env python   
  147.     def load_stat（self）:  
  148.         loadavg = {}  
  149.         f = open（"/proc/loadavg"）  
  150.         con = f.read（）.split（）  
  151.         f.close（）  
  152.         loadavg["lavg_1"]=Decimal（con[0]）  
  153.         loadavg["lavg_5"]=Decimal（con[1]）  
  154.         loadavg["lavg_15"]=Decimal（con[2]）  
  155.         return loadavg  
  156.     #！/usr/bin/env python   
  157.     def memory_stat（self）:  
  158.         mem = {}  
  159.         f = open（"/proc/meminfo"）  
  160.         lines = f.readlines（）  
  161.         f.close（）  
  162.         for line in lines:  
  163.             if len（line） < 2: continue  
  164.             name = line.split（":"）[0]  
  165.             var = line.split（":"）[1].split（）[0]  
  166.             mem[name] = long（var） * 1024.0  
  167.         mem["MemUsed"] = mem["MemTotal"] - mem["MemFree"] - mem["Buffers"] - mem["Cached"]  
  168.         return mem  
  169.     def disk_stat（self）:  
  170.         disk=[]  
  171.         f = open（"/proc/diskstats"）  
  172.         lines = f.readlines（）  
  173.         f.close（）  
  174.         for disk_name in disk_list:  
  175.             for row in lines:  
  176.                 if str（row）.find（" "+disk_name+" "）>0:  
  177.                     con=str（row）.split（" "）  
  178.                     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],}）  
  179.                     break  
  180.                   
  181.         return disk  
  182.     def swap_stat（self）:  
  183.         swap={}  
  184.         f = open（"/proc/swaps"）  
  185.         l = f.readlines（）  
  186.         f.close（）  
  187.         con=str（l[1]）.split（" "）  
  188.         swap["swapt"]=con[1]  
  189.         swap["swapu"]=con[2]  
  190.         return swap  
  191.       
  192.     #！/usr/bin/env python   
  193.     def net_stat（self）:  
  194.         net = []  
  195.         f = open（"/proc/net/dev"）  
  196.         lines = f.readlines（）  
  197.         f.close（）  
  198.         for line in lines[2:]:  
  199.             con = line.split（）  
  200.               
  201.             intf = {}  
  202.             intf["interface"] = con[0].lstrip（":"）  
  203.             intf["ReceiveBytes"] = int（con[1]）  
  204.             intf["ReceivePackets"] = int（con[2]）  
  205.             intf["ReceiveErrs"] = int（con[3]）  
  206.             intf["ReceiveDrop"] = int（con[4]）  
  207.             intf["ReceiveFifo"] = int（con[5]）  
  208.             intf["ReceiveFrames"] = int（con[6]）  
  209.             intf["ReceiveCompressed"] = int（con[7]）  
  210.             intf["ReceiveMulticast"] = int（con[8]）  
  211.             intf["TransmitBytes"] = int（con[9]）  
  212.             intf["TransmitPackets"] = int（con[10]）  
  213.             intf["TransmitErrs"] = int（con[11]）  
  214.             intf["TransmitDrop"] = int（con[12]）  
  215.             intf["TransmitFifo"] = int（con[13]）  
  216.             intf["TransmitFrames"] = int（con[14]）  
  217.             intf["TransmitCompressed"] = int（con[15]）  
  218.             #intf["TransmitMulticast"] = int（con[16]）   
  219.             """ 
  220.             intf = dict（ 
  221.                 zip（ 
  222.                     （ "interface","ReceiveBytes","ReceivePackets", 
  223.                       "ReceiveErrs","ReceiveDrop","ReceiveFifo", 
  224.                       "ReceiveFrames","ReceiveCompressed","ReceiveMulticast", 
  225.                       "TransmitBytes","TransmitPackets","TransmitErrs", 
  226.                       "TransmitDrop", "TransmitFifo","TransmitFrames", 
  227.                       "TransmitCompressed","TransmitMulticast" ）, 
  228.                     （ con[0].rstrip（":"）,int（con[1]）,int（con[2]）, 
  229.                       int（con[3]）,int（con[4]）,int（con[5]）, 
  230.                       int（con[6]）,int（con[7]）,int（con[8]）, 
  231.                       int（con[9]）,int（con[10]）,int（con[11]）, 
  232.                       int（con[12]）,int（con[13]）,int（con[14]）, 
  233.                       int（con[15]）,int（con[16]）） 
  234.                 ） 
  235.             ） 
  236.             """  
  237.             net.append（intf）  
  238.         return net  
  239.       
  240.     def relcount（self,param）:  
  241.         return Decimal（self.nextstatus[param]）-Decimal（self.previoustatus[param]）  
  242.      
  243.     @staticmethod  
  244.     def println（param,s=7）:  
  245.         p=""  
  246.         for i in param:  
  247.             if type（i）==type（""）:  
  248.                 p+=i+" "  
  249.             else:  
  250.                 p+=str（i[0]）.ljust（s）  
  251.           
  252.         print p  
  253.     @staticmethod  
  254.     def printl（param,s=7）:  
  255.         p=""  
  256.         for i in param:  
  257.             if str（i）=="time":  
  258.                 p+=str（i）+"     "  
  259.             else:  
  260.                 p+=str（i）.ljust（s）  
  261.           
  262.         print p  
  263.          
  264.     @staticmethod  
  265.     def perF（param）:  
  266.         return mysqlrealtimestatus.dFormat（param*100）  
  267.      
  268.     @staticmethod  
  269.     def dFormat（val）:  
  270.         k=1024  
  271.         m=k*k  
  272.         g=k*m  
  273.         t=k*g  
  274.         p=k*t  
  275.         dp=0  
  276.         dm=""  
  277.         if（val！=0）:  
  278.             if（val>p）:  
  279.                 dp=p  
  280.                 dm="P"  
  281.             elif（val>t）:  
  282.                 dp=t  
  283.                 dm="T"  
  284.             elif（val>g）:  
  285.                 dp=g  
  286.                 dm="G"  
  287.             elif（val>m）:  
  288.                 dp=m  
  289.                 dm="M"  
  290.             elif（val>k）:  
  291.                 dp=k  
  292.                 dm="k"  
  293.             else:  
  294.                 dp=1  
  295.             return ["%2.2f" % （Decimal（val）/Decimal（dp）） +dm]  
  296.         else:  
  297.             return ["%2.2f" % 0]  
  298. """"" 
  299.  
  300. """  
  301. class MySQLHelper（object）:  
  302.     @staticmethod  
  303.     def getConn（conf）:  
  304.         pot = 3306  
  305.         if（conf.has_key（"port"））:  
  306.             pot=conf["port"]  
  307.         dbname="test"  
  308.         if（conf.has_key（"db"））:  
  309.             dbname=conf["db"]  
  310.           
  311.         if（conf.has_key（"socket"））:  
  312.             return MySQLdb.connect（host=conf["host"],unix_socket=conf["socket"],user=conf["user"],passwd=conf["pwd"],db=dbname）  
  313.         else:  
  314.             return MySQLdb.connect（host=conf["host"],port=pot,user=conf["user"],passwd=conf["pwd"],db=dbname）  
  315. """"" 
  316.  
  317. """  
  318. class db （object）:  
  319.     def __init__（self,conf）:  
  320.         self.conn=None  
  321.         self.conn=MySQLHelper.getConn（conf）  
  322.       
  323.     def execute（self,sql,mod=""）:  
  324.         if（mod=="dict"）:  
  325.             cursor=self.conn.cursor（MySQLdb.cursors.DictCursor）  
  326.         else:  
  327.             cursor=self.conn.cursor（）  
  328.         cursor.execute（sql）  
  329.         set=cursor.fetchall（）  
  330.         return set  
  331.       
  332.     def executeNoQuery（self,sql,param={}）:  
  333.         cursor=self.conn.cursor（）  
  334.         try:  
  335.             if（param=={}）:  
  336.                 rownum=cursor.execute（sql）  
  337.             else:  
  338.                 rownum=cursor.executemany（sql,param）  
  339.             self.conn.commit（）  
  340.             return rownum  
  341.         finally:  
  342.             cursor.close（）  
  343.       
  344.     def __del__（self）:  
  345.         if （self.conn！=None）:  
  346.             self.conn.close（）  
  347.   
  348. if __name__ == "__main__":  
  349.     conf={"host":mysql_host,"socket":unix_socket,"user":mysql_user,"pwd":mysql_pwd,"db":mysql_db,"sleep":sleep_time}  
  350.     status=mysqlrealtimestatus（conf）;  
  351.     status.run（）;  
mysql innodb创建数据文件失败MySQL Cluster 共享权限相关资讯      MySQL实时监控  本文评论 查看全部评论 （0）
表情： 姓名： 字数