python读取MySQL的binlog

Python 3.8.8rc1

1.这里我们使用mysql-replication，直接pip安装

pip install mysql-replication

2.UpdateRowsEvent 为update的sql、WriteRowsEvent 为insert的sql、DeleteRowsEvent为delete的sql

log_file 为你开始读取的binlog文件，MySQL可以使用 show binary logs; 查看binlog文件列表；

log_pos为读取binlog文件的position

如果是只读取某个schema或者某些table，需要配置only_schemas 和only_tables 参数

only_schemas=['schema1'],
only_tables=['table_name_1','table_name_2'],

如果只需要读取update 的sql，only_events可以只配置

UpdateRowsEventonly_events=[UpdateRowsEvent]

下面是一个读取binlog的示例，仅供参考：

#!/usr/bin/env python
# -*- encoding: utf-8 -*-

from pymysqlreplication import BinLogStreamReader
from pymysqlreplication.row_event import UpdateRowsEvent, WriteRowsEvent, DeleteRowsEvent
from datetime import datetime

# 配置数据库信息
mysql_settings = {
    'host': '127.0.0.1',
    'port': 3306,
    'user': 'mysql user',
    'passwd': 'mysql password'
    }


def main():
    # 实例化binlog 流对象
    stream = BinLogStreamReader(
        connection_settings=mysql_settings,
        resume_stream=True,
        log_file = 'binlog.000001',
        log_pos = 4,
        only_schemas=['schema1'],
        only_tables=['table_name_1','table_name_2'],
        server_id=100,
        blocking=True,
        only_events=[UpdateRowsEvent, WriteRowsEvent, DeleteRowsEvent]
    )

    for binlogevent in stream:
        print(datetime.now().strftime('%Y-%m-%d %H:%M:%S')+'=>log_file:' + str(stream.log_file) + '&log_pos:' + str(stream.log_pos))
        for row in binlogevent.rows:
            if binlogevent.table == "table_name_1" and binlogevent.schema == "schema1":
                if isinstance(binlogevent, WriteRowsEvent):
                    #do something
                    pass
                elif isinstance(binlogevent, UpdateRowsEvent):
                    #do something
                    pass
                elif isinstance(binlogevent, DeleteRowsEvent):                      
                    #do something
                    pass
            elif binlogevent.table == "table_name_2" and binlogevent.schema == "schema1":
                if isinstance(binlogevent, WriteRowsEvent):
                    #do something
                    pass
                elif isinstance(binlogevent, UpdateRowsEvent):
                    #do something
                    pass

if __name__ == '__main__':
    main()