MySQL binlog分析DML脚本

最新推荐文章于 2022-01-17 09:30:00 发布

_梓杰_

最新推荐文章于 2022-01-17 09:30:00 发布

阅读量424

点赞数 1

CC 4.0 BY-SA版权

分类专栏： python MySQL

本文链接：https://blog.csdn.net/qq_42979842/article/details/114809820

MySQL 同时被 2 个专栏收录

126 篇文章

订阅专栏

python

30 篇文章

订阅专栏

#!/usr/bin/python
# -*- coding: utf-8 -*-
#python binlog_analyze.py binlog.000008  '2020-03-01 00:00:00' '2022-03-01 00:00:00'
import os,sys
binlog_name = sys.argv[1]
start_datetime = sys.argv[2]
stop_datetime = sys.argv[3]
binlog_command = "mysqlbinlog --no-defaults -vvv --start-datetime='%s' --stop-datetime='%s' %s" % (start_datetime,stop_datetime,binlog_name)
binlog_line=os.popen(binlog_command).readlines()
binlog_list=[]
for line in binlog_line:
    if ('UPDATE' in line):
            update = ' '.join(line.split()[:3]).replace("### ","")
            binlog_list.append(update)
    if ('INSERT' in line):
            insert = ' '.join(line.split()[:4]).replace("### ","")
            binlog_list.append(insert)
    if ('DELETE' in line):
            delete = ' '.join(line.split()[:4]).replace("### ","")
            binlog_list.append(delete)
    if ('alter table' in line):
            alter = ' '.join(line.split()[:3])
            binlog_list.append(alter)
binlog_set = list(set(binlog_list))
result = []
for item in binlog_set:
        result.append([binlog_list.count(item),(item)])
result.sort()
for i in result:
        print (str(i[0])+'\t'+i[1].upper())

[root@zijie binlog]# python3 binlog_analyze.py binlog.000008  '2020-03-01 00:00:00' '2022-03-01 00:00:00'
1	DELETE FROM `TEST`.`T2`
1	INSERT INTO `TEST`.`T2`
1	UPDATE `TEST`.`T2`
1	ALTER TABLE T1
2	DELETE FROM `TEST`.`T1`
2	INSERT INTO `TEST`.`T1`
2	UPDATE `TEST`.`T1`