python binlog python binlog 解析

转载

flyingsmiling 2023-06-20 15:40:52

文章标签 python binlog 程序开发 mysql MySQL github 文章分类 Python 后端开发

Pure Python Implementation of MySQL replication protocol build on top of PyMYSQL. This allow you to receive event like insert, update, delete with their datas and raw SQL queries.

python-mysql-replication 是基于python实现的 MySQL复制协议工具，我们可以用它来解析binlog 获取日志的insert，update，delete等事件，并基于此做其他业务需求。比如数据更改时失效缓存，监听dml事件通知下游业务方做对应处理。

一.使用场景

1.MySQL to NoSQL database replication

2.MySQL to search engine replication

3.Invalidate cache when something change in database

4.Audit

5.Real time analytics

二.主要模块

1.BinLogStreamReader

python-mysql-replication 的入口的类。这个类支持用户传入mysql配置，slave需要同步的信息等，同时实现了__iter__，注册slave，读packet。

使用该工具时需要实例化一个BinLogStreamReader()对象 stream。BinLogStreamReader 通过 ReportSlave 向主库注册作为一个slave角色，用于接受MySQL的binlog广播。

文件名字为 binlogstream.py

2.BinLogPacketWrapper

BinLogPacketWrapper类，mysql网络包序列化和反序列化。

存放的文件为packet.py

3.事件类

各个event（select、update、insert、delete、rollback、heartbeat等）对应的实现类，全都继承子BinlogEvent，在BinLogPacketWrapper类中把获取到的event映射到对应的evnet处理类

存放的文件为event.py

4.依赖的基础文件

pymysql中的connnections.py：Connection类，实现连接、读写mysql包（具体包格式由protocol实现）。

pymysql中的protocol.py：MysqlPackge类，具体包的格式和读写。

三.已有功能实现（案例）

Projects	Remark	Github
binlog2sql	a popular binlog parser that could convert raw binlog to sql and also could generate flashback sql from raw binlog	https://github.com/danfengcao/binlog2sql
mymongo	MySQL to mongo replication	https://github.com/njordr/mymongo
MySQLStreamer	MySQLStreamer is a database change data capture and publish system	https://github.com/Yelp/mysql_streamer
BitSwanPump	A real-time stream processor	https://github.com/LibertyAces/BitSwanPump
Aventri MySQL Monitor	MySQL Monitor is an application which continuously watches a MySQL database for data updates and publishes information about those changes to a message queue on RabbitMQ.	https://github.com/aventri/mysql-monitor
elasticsearch-river-mysql	MySQL River Plugin for ElasticSearch	https://github.com/scharron/elasticsearch-river-mysql
pg_chameleon	Migration and replica from MySQL to PostgreSQL	https://github.com/the4thdoctor/pg_chameleon
python-mysql-eventprocessor	Daemon interface for handling MySQL binary log events.	https://github.com/jffifa/python-mysql-eventprocessor
Python MySQL Replication Blinker	This package read events from MySQL binlog and send to blinker's signal.	https://github.com/tarzanjw/python-mysql-replication-blinker