利用python-my-replication读取mysql的binlog

1、用户mysql账户权限开通
同步账户需要有：REPLICATION SLAVE和REPLICATION CLIENT权限
2、打开bin_log权限
show variables like 'log_bin'：

3、bin_log设置为行复制
show variables like 'binlog_format';  

4、查看bin_log存储地址：
show variables like '%log_bin%';

5、查看master最新binlog日志
show master status;

6、通过mysqlbinlog命令查看最新binlog日志数据
/data-2/server/mysql5.6/bin/mysqlbinlog  /data/dblogs/binlog_3307/datamysql-bin.000033|more

7、启动python代码

def main():
    stream = BinLogStreamReader(
        connection_settings=MYSQL_SETTINGS,
        server_id=3,
        blocking=True,
        only_schemas=["jintui"],
        only_events=[DeleteRowsEvent, WriteRowsEvent, UpdateRowsEvent])
    producer = KafkaProducer(kafka_setting,topic_setting)
    for binlogevent in stream:
        for row in binlogevent.rows:
            event = {"schema": binlogevent.schema, "table": binlogevent.table}
            if isinstance(binlogevent, DeleteRowsEvent):
                event["action"] = "delete"
                event["values"] = dict(row["values"].items())
                event = dict(event.items())
            elif isinstance(binlogevent, UpdateRowsEvent):
                event["action"] = "update"
                event["before_values"] = dict(row["before_values"].items())
                event["after_values"] = dict(row["after_values"].items())
                event = dict(event.items())
            elif isinstance(binlogevent, WriteRowsEvent):
                event["action"] = "insert"
                event["values"] = dict(row["values"].items())
                event = dict(event.items())
            print json.dumps(event)
            sys.stdout.flush()


    stream.close()


if __name__ == "__main__":
    main()

8、show full processlist 查看dump进程连接情况

9、输出结果

{"action": "update", "table": "test", "before_values": {"Id": 122, "name": "www"}, "after_values": {"Id": 122, "name": "222"}, "schema": "pgznty"}
{"action": "update", "table": "test", "before_values": {"Id": 12, "name": "222"}, "after_values": {"Id": 12, "name": "333"}, "schema": "pgznty"}
{"action": "update", "table": "test", "before_values": {"Id": 122, "name": "222"}, "after_values": {"Id": 122, "name": "333"}, "schema": "pgznty"}