[MYSQL案例][014] mysql主从延迟增大排查

#!/usr/bin/env python
import struct
import sys
import os

def btoint(bdata,t='little'):
	return int.from_bytes(bdata,t)

def event_header(bdata):
	timestamp, event_type, server_id, event_size, log_pos, flags = struct.unpack("<LBLLLh",bdata[0:19])
	return {"timestamp":timestamp,'event_type':event_type,'server_id':server_id,'event_size':event_size,'log_pos':log_pos,'flags':flags,}


if len(sys.argv) != 2:
	print(f'Usage: python {sys.argv[0]} binlog')
filename=sys.argv[1]

if not os.path.exists(filename):
	print(f'file {filename} not exists')

def first_event(bdata):
	#FORMAT_DESCRIPTION_EVENT
	ethl = len(bdata) - 57 #2 50 4 1 var
	ff = f'<h50sLB{ethl}s'
	binlog_version, mysql_server_version, create_timestamp, event_header_length, event_type_header_length = struct.unpack(ff,bdata)
	mysql_server_version = mysql_server_version.decode('ascii').replace('\x00','') #美化一下
	event_type_header_length = [ int(x) for x in event_type_header_length ] #event specific header length. 比如TABLE_MAP_EVENT = 8 (table_id:6 + flag:2) #记录其它event的post header的长度
	return {'binlog_version':binlog_version, 'mysql_server_version':mysql_server_version, 'create_timestamp':create_timestamp, 'event_header_length':event_header_length, 'event_type_header_length':event_type_header_length,}


def table_map_event(bdata):
	post_header = {'table_id':btoint(bdata[0:6]), 'flags':btoint(bdata[6:8])} #flags保留字段
	offset = 8
	database_length = btoint(bdata[offset:offset+1])
	offset +=1
	database_name = bdata[offset:offset+database_length].decode() #0x00 结尾
	offset += database_length + 1
	table_length = btoint(bdata[offset:offset+1])
	offset +=1
	table_name = bdata[offset:offset+table_length].decode() #0x00 结尾, 但是我不读,计数的时候别忘了就行
	offset += table_length + 1
	column_count = btoint(bdata[offset:offset+1]) #Packed Integer 我只考虑0-250个字段. 也就是占用1字节 计算方式https://dev.mysql.com/doc/dev/mysql-server/latest/classbinary__log_1_1Binary__log__event.html#packed_integer
	offset += 1
	column_type_list = []
	for x in range(column_count):
		column_type_list.append(btoint(bdata[offset:offset+1])) #先不做转换了.具体类型参考https://dev.mysql.com/doc/dev/mysql-server/latest/classbinary__log_1_1Table__map__event.html
		offset += 1

	#metadata_length和column_count一样, 但是我不想写了
	#省略 metadata_length metadata null_bits optional metadata fields
	return {
		'post_header':post_header,
		'body':{
			'database_name':database_name,
			'table_name':table_name,
			'column_type_list':column_type_list,
		}
		}

if __name__ == '__main__':
	#只解析table_map event
	tabledict = {}
	with open(filename,'rb') as f:
		magic = f.read(4)
		if magic != b'\xfebin':
			print(f'{filename} is not binlog')
		while True:
			try:
				common_header = event_header(f.read(19))
			except:
				break
			if common_header == b'':
				break #读完了这个文件
			event_bdata = f.read(common_header['event_size']-19)
			if common_header['event_type'] == 19: #table_map event
				event_data = table_map_event(event_bdata)
				table_name = f"{event_data['body']['database_name']}.{event_data['body']['table_name']}"
				if table_name in tabledict:
					n = tabledict[table_name] + 1
					tabledict[table_name] = n
				else:
					tabledict[table_name] = 0

	for x in tabledict:
		print(f'表名: {x}  操作次数: {tabledict[x]}')