python3 ：mariadb decoded binlog tx parser

用python3编写的用来分析decoded后的binlog中的事务相关信息，包含每个事务的xid，binlog size，针对每个表的dml次数，方便的找出一段时间内的事务是否激增，事务量是否激增，便于分析相关问题

import sys
import re


def parseblock(block):
    insert = []
    delete = []
    update = []
    startscn = 0
    stopscn = 0
    xid = 0
    begin_flag = True

    for line in block.split('\n'):
        mat1 = re.match(r'.*server id.*end_log_pos.*', line.strip())
        if mat1 and begin_flag:
            startscn = line.split()[6]
            begin_flag = False
        if line.startswith('### INSERT INTO'):
            insert.append(line[16:].replace('`', '').strip())
        if line.startswith('### UPDATE'):
            update.append(line[11:].replace('`', '').strip())
        if line.startswith('### DELETE FROM'):
            delete.append(line[16:].replace('`', '').strip())
        mat = re.match(r'.*server id.*end_log_pos(.*)Xid =(.*)', line.strip())
        if mat:
            stopscn = line.split()[6]
            xid = line.split()[-1]

    alllist = insert
    alllist.extend(update)
    alllist.extend(delete)
    alllist = list(set(alllist))

    print(startscn)
    print(stopscn)
    size = int(stopscn) - int(startscn)
    dml = len(insert)+len(update)+len(delete)
    print('Xid:%s Binlog Size:%s(bytes) DML:%s INSERT:%s UPDATE:%s DELETE:%s' % (xid, size, dml, len(insert), len(update), len(delete)))
    print('---------------------------------------------------------')
    for key in alllist:
        print('Table:%s INSERT:%s UPDATE:%s DELETE:%s \n' % (key, insert.count(key), update.count(key), delete.count(key)))
    print('\n')


def parseblocks(file):
    with open(file, 'r') as f:
        a = f.read()
        block = re.compile(r'BEGIN./\*!\*/;.*?COMMIT/\*!\*/;', re.DOTALL)
        tm = re.compile(r'([0-9].*)')
        blocks = block.findall(a)

        for i in blocks:
            parseblock(i)


if __name__ == '__main__':
    parseblocks(sys.argv[1])

运行结果

Xid:9 Binlog Size:114(bytes) DML:2 INSERT:2 UPDATE:0 DELETE:0
---------------------------------------------------------
Table:test.t2 INSERT:2 UPDATE:0 DELETE:0 

Xid:17 Binlog Size:709058(bytes) DML:98304 INSERT:98304 UPDATE:0 DELETE:0
---------------------------------------------------------
Table:test.t1 INSERT:98304 UPDATE:0 DELETE:0 

Xid:62 Binlog Size:63(bytes) DML:1 INSERT:1 UPDATE:0 DELETE:0
---------------------------------------------------------
Table:test.t1 INSERT:1 UPDATE:0 DELETE:0