基于Python实现MySQL binlog实时解析

原文链接：基于Python实现MySQL binlog实时解析

简介

当我们想要对 MySQL 数据库进行监控，或者需要获取到某些数据库操作的详细内容，就可能会用到 binlog（二进制日志）。binlog 中记录了所有修改了数据或者可能修改数据的 SQL 语句。这篇文章将介绍如何使用 Python 来实现 MySQL binlog 的实时解析。

环境介绍

python环境：3.7.5

pip包：

pip install PyMySQL

pip install pymysql-replication

pymysqlreplication 这个 Python 库，它是一个纯 Python 实现的 MySQL binlog 解析库，提供了一种方便的方式来解析 binlog 事件。

同时，还需要一个可以让你访问到 binlog 的 MySQL 服务器，并且对于要解析的数据库，需要打开 binlog 功能。

代码如下

'''
Author: 745719408@qq.com 745719408@qq.com
Date: 2022-07-29 17:18:40
LastEditors: 745719408@qq.com 745719408@qq.com
LastEditTime: 2023-08-07 17:01:59
FilePath: \K8S\组件包\py脚本\python操作应用\py_mysq_binlog.py
Description: python分析mysql的binlog脚本,可以打印出已经执行过的如delete,update,insert状态的sql语句
'''

# pip install PyMySQL
# pip install pymysql-replication


from pymysqlreplication import BinLogStreamReader
from pymysqlreplication.row_event import (
    DeleteRowsEvent,
    UpdateRowsEvent,
    WriteRowsEvent,
 
)
import json
import sys
import datetime
from pymysql.err import OperationalError

# 定义一个函数，将datetime.datetime对象转化为iso的格式
def datetime_handler(x):
    if isinstance(x, datetime.datetime):
        return x.isoformat()
    elif isinstance(x, bytes): # 处理bytes
        return x.decode('utf-8')
    else:
        return str(x) # 对其他未知数据类型，转为字符串

MYSQL_SETTINGS={
    "host":"your_mysql_host",
    "port":3306,
    "user":"your_mysql_user",
    "password":"your_mysql_password"
}
try:
    stream = BinLogStreamReader(
        connection_settings=MYSQL_SETTINGS,
        server_id=100,
        only_events=[
            DeleteRowsEvent, 
            WriteRowsEvent, 
            UpdateRowsEvent]
    )

    for binlogevent in stream:
        for row in binlogevent.rows:
            event = {"schema": binlogevent.schema,
                    "table": binlogevent.table,
                    "type": type(binlogevent).__name__,
                    "row": row,
                    "timestamp": datetime.datetime.fromtimestamp(binlogevent.timestamp).isoformat(),
                    "execute_time": None
                    }
            
            if isinstance(binlogevent, DeleteRowsEvent):
                print("Before delete: ")
                print(json.dumps(event, indent=4, ensure_ascii=False, default=datetime_handler)) 
            elif isinstance(binlogevent, UpdateRowsEvent):
                print("Before update: ")
                print(json.dumps(row['after_values'], indent=4, ensure_ascii=False, default=datetime_handler))  
            elif isinstance(binlogevent, WriteRowsEvent):
                print("After insert: ")
                print(json.dumps(event, indent=4, ensure_ascii=False, default=datetime_handler))  

    stream.close()
except OperationalError as ex:
    print(f"连接到MySQL服务器时出错: {ex}")
    exit(-1)

Python

解析 binlog 具体原理:

1. 我们用BinLogStreamReader来连接 MySQL 服务器，然后监听并获取 binlog 中的事件。

2. 解析出的 binlog 事件主要包括三类: DeleteRowsEvent, WriteRowsEvent 和 UpdateRowsEvent，分别对应着删除行、插入行和更新行。

3. 根据事件的类型，我们可以得到发生的具体操作信息，并格式化为json输出。

输出示例:

Before update:

{

"id": 1,

"m": "*",

"h": "*",

"dom": "*",

"mon": "*",

"dow": "*",

"command": "",

"remark": "监控定时任务",

"type": "zentao",

"buildin": 1,

"status": "normal",

"lastTime": "2023-08-03T12:47:02"

}

Before update:

这只是一个简单的python获取mysql的binlog日志的监听程序，只取出了mysql最主要修改内容的sql语句，后续再研究更多操作。