python分析mysql binlog日志工具代码

发布时间:2020-10-01编辑:脚本学堂
用python实现的mysql binlog日志的分析工具,在mysql数据库中binlog日志很重要,数据量太大的binlog日志文件需要用脚本分析,可以参考下这里python代码。

一个用来分析bin-log的小工具,找出增删改查的表,并按照操作次数降序排列。
可用于分析与处理mysql binlog日志文件

完整代码:
 

复制代码 代码示例:
#!/usr/bin/python
# www.jb200.com
#for mysql5.5 binlog
import os,sys
#python binlog.py binglog-0001 '2013-07-01 00:00:00' '2013-07-02 00:00:00'
def log_w(type,text):
    logfile = "%s.txt" % (type,text)
    #now = time.strftime("%Y-%m-%d %H:%M:%S")
    tt = str(text) + "n"
    f = open(logfile,'a+')
    f.write(tt)
    f.close()
logname = sys.argv[1]
start_time = sys.argv[2]
end_time = sys.argv[3]
comn = "/usr/bin/mysqlbinlog --start-datetime='%s' --stop-datetime='%s' %s" % (start_time,end_time,logname)
aa=os.popen(comn).readlines()
mylist=[]
for a in aa:
    if ('UPDATE' in a):
            update = ' '.join(a.split()[:2])
            mylist.append(update)
    if ('INSERT INTO' in a):
            update = ' '.join(a.split()[:3]).replace("INTO ","")
            mylist.append(update)
    if ('DELETE from' in a):
            update = ' '.join(a.split()[:3]).replace("from ","")
            mylist.append(update)
mylist.sort()
bb = list(set(mylist))
bb.sort()
cc = []
for item in bb:
        cc.append([mylist.count(item),(item)])
cc.sort()
cc.reverse()
for i in cc:
   print str(i[0])+'t'+i[1]

执行结果:
mysql binlog日志