mysql binlog分析脚本及binlog结构内容

最新推荐文章于 2024-05-03 16:23:00 发布

xiaoxiao树

最新推荐文章于 2024-05-03 16:23:00 发布

阅读量616

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/u010719917/article/details/99979216

版权

本文介绍了如何基于MySQL二进制日志（binlog）的结构，编写Python脚本来解析和分析binlog日志，以获取数据库操作详情。

摘要由CSDN通过智能技术生成

基上结构图写个python脚本来分析binlog日志：

#_*_ coding:utf8 _*_
import os
import sys
import  re
import  time
import  datetime
import re
# binlog_name = sys.argv[1]

binlog_path='/mysqlbinlog/'

if len(sys.argv) == 4 and 'bin' in sys.argv[1]:
    print('INPUT <binlog_name><start-datetime(format:YYYY-MM-DD HH24:MI:SS)><stop-datetime(format:YYYY-MM-DD HH24:MI:SS)>')

    binlog_name = sys.argv[1]
    start_datetime = sys.argv[2]
    stop_datetime = sys.argv[3]
elif len(sys.argv) == 3:
    start_datetime = sys.argv[1]
    stop_datetime = sys.argv[2]

# print(binlog_name)
# print(start_datetime)
# print(stop_datetime)
# binlog_name='/mysqlbinlog/master-bin.000008'
rx  = os.popen('find ' + binlog_path + ' -mindepth 1 -maxdepth 1 -name *.index ')
binlog_index = rx.read().split('\n')[0]

sql_text=''
flag_rows_query=0
dml_cnt = 0
one_trans = {}
all_trans = {}
trans_sql = {}
sql_list=[]
gtid_x=''
dml_dic = {}
dur_dic = {}
binlog_rows_query_events =0

# 将 不规格的字符串 转换为 日期
def get_dateformat(dt):
    d1 = dt.replace('  ',' ').split(' ')[0] + ' '+ dt.replace('  ',' ').split(' ')[1].zfill(8)
    return datetime.datetime.strptime(d1, '%y%m%d %H:%M:%S')

# 判断一个unicode是否是汉字
def is_chinese(uchar):
    if uchar >= u'\u4e00' and uchar <= u'\u9fa5':
        return True
    else:
        return False


# 用空格填充定义的宽度(汉字多填充一个字符)
def fill_text(text, width):
    stext = str(text)
    utext = stext.decode("utf-8")
    cn_count = 0
    for u in utext:
        if is_chinese(u):
            cn_count += 1
    return stext + (width - cn_count - len(utext)) * " "


def cut_text(text, lenth):
    textArr = re.findall('.{' + str(lenth) + '}', text)
    textArr.append(text[(len(textArr) * lenth):])
    return textArr

# print(cut_text('123456789abcdefg', 3))


def get_data(binlog_name,start_datetime,stop_datetime):
    global sql_text
    global flag_rows_query
    global dml_cnt
    global one_trans
    global all_trans
    global trans_sql
    global sql_list
    global gtid_x
    global dml_dic
    global dur_dic
    global binlog_rows_query_events
    global binlog_path

    cmd = "/home/db/mysql/product/bin/mysqlbinlog -vv --base64-output='decode-rows' "  + binlog_name + ' --start-datetime=' + "'" + start_datetime + "'" + ' --stop-datetime=' + "'" + stop_datetime +"'"
    print(cmd)
    result = os.popen(cmd)

    ## 解析binlog
    for line in  result.read().split('\n'):
        # 事务gtid
        if "SET @@SESSION.GTID_NEXT=" in line:
            gtid_x = line.split("'")[1]

        # 事务开始时间
        if "Query	thread_id" in line:
            one_trans['start_time'] = get_dateformat(line.replace("#",'').split("server id")[0])
            continue

        # 事务结束时间
        if "GTID	last_committed="  in line:
            end_time = line.replace("#",'').split("server id")[0]
            one_trans['end_time'] = get_dateformat(line.replace("#",'').split("server id")[0])
            continue

        # 事务sql语句
        if "Rows_query" in line:
            flag_rows_query = 1