mysql 5.7 oom_MySQL异常OOM排查

#!/usr/bin/python#coding: utf8

"""跟踪一段时间内dml趋势，用ES分析

Usage:

python dml2es.py | logstash -f /etc/logstash/conf.d/t_binlog.conf"""

importjsonimportosimportsysimportloggingfrom datetime importdatetimefrom pymysqlreplication importBinLogStreamReaderfrom pymysqlreplication.event importQueryEvent, RotateEventfrom pymysqlreplication.row_event import(

WriteRowsEvent,

UpdateRowsEvent,

DeleteRowsEvent,

)definit_log():#get root logger

mylogger =logging.getLogger()

mylogger.setLevel(logging.DEBUG)#create file handler and add formatter to handler

verbose_log_file = "/tmp/%s.verbose.log" %(os.path.basename(sys.argv[0]))

fh=logging.FileHandler(verbose_log_file)

fh.setLevel(logging.DEBUG)

fh.setFormatter(logging.Formatter(

fmt="%(asctime)s %(filename)s:%(lineno)d %(levelname)s %(message)s",

datefmt="%a %d %b %Y %H:%M:%S"))#create stream handler and add formatter to handler

sh =logging.StreamHandler(sys.stdout)

sh.setLevel(logging.INFO)

sh.setFormatter(logging.Formatter(

fmt="%(asctime)s %(levelname)-8s %(message)s",

datefmt="%H:%M:%S"))#add two handler to logger

mylogger.addHandler(fh)

mylogger.addHandler(sh)returnmyloggerif __name__ == "__main__":

logger=init_log()

mysql_settings={'host': '127.0.0.1','port': 3306,'user': 'xxx','passwd': 'xxx'}

binlog_file= 'mysql-bin.005298'start_position= 123stop_position= 1031510925

#不需要blocking

stream =BinLogStreamReader(

connection_settings=mysql_settings,

server_id=9999,

log_file=binlog_file,

log_pos=start_position,

resume_stream=True,

blocking=False

)for binlogevent instream:

data={}if binlogevent.packet.log_pos >=stop_position:break

#当输出 "@timestamp" : binlogevent.timestamp

#即 "@timestamp" => 1582624830

#logstash 会有warning

#[WARN ] 2020-02-25 18:00:31.313 [[main]

#所以需要进行下面的格式转换

#使用 datetime.fromtimestamp 将解析为 当前操作系统时区的 datetime

ifisinstance(binlogevent, WriteRowsEvent):

data={"schema": binlogevent.schema,"table": binlogevent.table,"dmltype" : "insert","@timestamp" :datetime.fromtimestamp(binlogevent.timestamp).strftime('%Y-%m-%dT%H:%M:%S') ,

}printjson.dumps(data)elifisinstance(binlogevent, DeleteRowsEvent):

data={"schema": binlogevent.schema,"table": binlogevent.table,"dmltype" : "delete","@timestamp" :datetime.fromtimestamp(binlogevent.timestamp).strftime('%Y-%m-%dT%H:%M:%S') ,

}printjson.dumps(data)elifisinstance(binlogevent, UpdateRowsEvent):

data={"schema": binlogevent.schema,"table": binlogevent.table,"dmltype" : "update","@timestamp" :datetime.fromtimestamp(binlogevent.timestamp).strftime('%Y-%m-%dT%H:%M:%S') ,

}printjson.dumps(data)elifisinstance(binlogevent, RotateEvent):if binlogevent.packet.log_pos <= 1655:

logger.debug( u"next_binlog:" +binlogevent.next_binlog )

stream.close()