读取 Mysql Binlog 日志文本

作为DBA应该清楚到底哪张表的DML操作频繁，用刚学的Python写了一个脚本：

#!/bin/env python
# -*- encoding: utf-8 -*-
#-------------------------------------------------------------------------------
# Name:        readbinlog.py
# Purpose:     读MySQL Binlog文本
# Author:      zhoujy
# Created:     2012-09-28
# update:      2012-09-28
# Copyright:   (c) zhoujy 2012
# Licence:     zhoujy
#-------------------------------------------------------------------------------
import sys
Insert  = []
Insdict = {}
Update  = []
Upddict = {}
Delete  = []
Deldict = {}
def read_file(file):
    f = open(file)
    while True:
        line = f.readline()
        if line.lower().find('insert') > -1:
            line_list = line.lower().split()
            for idx,name in enumerate(line_list):
                if name == 'into' and line_list[0]=='insert':
                    Insert.append(line_list[idx+1].split('(')[0])
        if line.lower().find('update') > -1:
            line_list = line.lower().split()
            for idx,name in enumerate(line_list):
                if name == 'set' and line_list[0]=='update':
                    Update.append(line_list[idx-1])
        if line.lower().find('delete') > -1:
            line_list = line.lower().split()
            for idx,name in enumerate(line_list):
                if name == 'from' and line_list[0]=='delete':
                    Delete.append(line_list[idx+1])
        if line == '':
            break
    f.close()
    print ('INSERT').center(60,'*')
    for n in Insert:
        Insdict[n] = Insdict.get(n,0)+1
    List = sorted(Insdict.items(),key=lambda i: -i[1])
    Sort = 0
    for kv in List:
        if Sort < 10:
            print kv[0].rjust(30) + ' : ' + str(kv[1])
            Sort += 1
    print ('UPDATE').center(60,'*')
    for n in Update:
        Upddict[n] = Upddict.get(n,0)+1
    List = sorted(Upddict.items(),key=lambda i: -i[1])
    Sort = 0
    for kv in List:
        if Sort < 10:
            print kv[0].rjust(30) + ' : ' + str(kv[1])
            Sort += 1
    print ('DELETE').center(60,'*')
    for n in Delete:
        Deldict[n] = Deldict.get(n,0)+1
    List = sorted(Deldict.items(),key=lambda i: -i[1])
    Sort = 0
    for kv in List:
        if Sort < 10:
            print kv[0].rjust(30) + ' : ' + str(kv[1])
            Sort += 1
if __name__ == '__main__':
    Insert = read_file(sys.argv[1])

效果：python read_binlog.py binlog1.txt

***************************INSERT***************************
                         snase : 77807
                        snpost : 62554
                      justhits : 13842
                       snoblog : 9115
                      ucaction : 1700
                       jutload : 1281
                       snsaper : 1103
                       jutcord : 663
                        sfeeds : 654
                           snT : 594
***************************UPDATE***************************
                         juted : 10919
                            js : 9663
                         jpost : 8704
                           jer : 6883
                            jd : 6222
                           sse : 1270
                           jft : 565
                           jut : 561
                            jd : 521
                            jc : 504
***************************DELETE***************************
                           sne : 182
                          snsg : 149
                          snew : 74
                         juine : 66
                          sage : 48
                          snst : 41
                         sopic : 22
                       jutward : 21
                        jurite : 18
                         juage : 17