python实现指定数据库指定表的数据同步（监听binlog）

话不多说直接上代码，简单实现，具体功能请根据业务场景自行调整

from pymysqlreplication import BinLogStreamReader
from pymysqlreplication.row_event import DeleteRowsEvent, UpdateRowsEvent, WriteRowsEvent
import sys
import threading
import traceback
import time
import pymysql

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

# 监听数据库
db_schemas = ['database_1']
# 同步数据库
sync_schemas = 'database_2'
# 监听数据表
listen_tables = ['table1', 'table2', 'table3']
# 待处理数据队列
wait_handle_queue = []


def main():
    # 实例化binlog 流对象
    stream = BinLogStreamReader(
        connection_settings=mysql_settings,
        server_id=1,  # slave标识，唯一
        blocking=True,  # 阻塞等待后续事件
        # 设定只监控写操作：增、删、改
        only_schemas=db_schemas,
        only_tables=listen_tables,
        freeze_schema=True,
        only_events=[
            DeleteRowsEvent,
            UpdateRowsEvent,
            WriteRowsEvent
        ]
    )
    print('数据库增删改事件监听启动...')
    for binlogevent in stream:
        try:
            # binlogevent.dump()  # 打印所有信息
            for row in binlogevent.rows:
                # 打印 库名 和 表名
                event = {
                    "schema": binlogevent.schema,
                    "table": binlogevent.table,
                    "primary_key": binlogevent.primary_key,
                    "status": "wait"
                }
                info = []
                if "values" in row.keys():
                    info = row["values"]
                if isinstance(binlogevent, DeleteRowsEvent):
                    event["action"] = "delete"
                    event["data"] = row["values"]
                    event['sql'] = '''DELETE FROM `%s`.`%s` WHERE %s = %s;                    
                    ''' % (sync_schemas, binlogevent.table, binlogevent.primary_key, info[binlogevent.primary_key])

                elif isinstance(binlogevent, UpdateRowsEvent):
                    info_before = row["before_values"]
                    info_after = row["after_values"]
                    info_set = []
                    for key, value in info_after.items():
                        info_set.append("%s=%s" % (key, convertData(value)))
                    event["action"] = "update"
                    event["data"] = row["after_values"]  # 注意这里不是values
                    event['sql'] = '''UPDATE `%s`.`%s` SET %s WHERE %s = %s;                    
                    ''' % (sync_schemas, binlogevent.table, ','.join(info_set), binlogevent.primary_key, info_before[binlogevent.primary_key])

                elif isinstance(binlogevent, WriteRowsEvent):
                    event["action"] = "insert"
                    event["data"] = row["values"]
                    info_set = []
                    info_insert = []
                    for key in info.keys():
                        info_set.append("%s=%s" % (key, key))
                    for value in info.values():
                        info_insert.append(convertData(value))
                    event['sql'] = '''INSERT INTO %s.%s (%s) VALUES (%s) ON DUPLICATE KEY UPDATE %s;
                    ''' % (sync_schemas, binlogevent.table, ','.join(info.keys()), ','.join(info_insert), ','.join(info_set))
                wait_handle_queue.append(event)
                sys.stdout.flush()
        except Exception as e:
            traceback.print_exc()


def convertData(value):
    if type(value) == 'datetime.datetime':
        return "'%s'" % (value.strftime('%Y-%m-%d %H:%M:%S'))
    elif value == None:
        return 'null'
    else:
        return "'%s'" % (value)


def createMysqlLink():
    # 链接数据库，并创建游标
    try:
        return pymysql.connect(host=mysql_settings['host'], port=mysql_settings['port'], user=mysql_settings['user'],
                               passwd=mysql_settings['passwd'], db=sync_schemas)
    except:
        print(traceback.format_exc())
        return False


def checkConnect(conn):
    _status = True
    while _status == True:
        try:
            conn.ping()  # 检测数据库链接是否可用
            _status = False
        except:
            conn = createMysqlLink()
            time.sleep(1)  # 获取链接等待1s
    return conn


global conn
global cur


def exec_all_sql():
    print('sql处理队列启动...')
    conn = createMysqlLink()
    print('创建同步数据库连接...')
    while True:
        time.sleep(2)  # 必须！同一时刻仅允许执行一个线程
        conn = checkConnect(conn)  # 检测链接是否正常
        cur = conn.cursor(cursor=pymysql.cursors.DictCursor)
        queue = list(
            filter(lambda x: x['status'] == 'wait', wait_handle_queue))  # 获取等待执行的数据
        for out in queue:
            try:
                print('exec', out['sql'], '\n')
                cur.execute(out['sql'])  # 执行等待执行的数据同步sql
                out['status'] = 'exec'
            except:
                out['msg'] = traceback.format_exc()  # 保存报错信息
                print(traceback.format_exc())
        conn.commit()  # 提交更改


if __name__ == '__main__':
    # 创建两个线程
    try:
        threading.Thread(target=main).start()
        threading.Thread(target=exec_all_sql).start()
    except:
        print("Error: 无法启动线程")
'''
输出数据格式
{
    "schema": "demo",    # 数据库名
    "table": "student",  # 表名
    "action": "update",  # 动作 insert、delete、update
    "status": "wait",    # 状态：wait：等待处理，exec：已执行，fail: 执行失败
    "sql": "*******",    # 待执行sql
    "data": {            # 数据，里边包含所有字段
        "id": 26, 
        "name": "haha", 
        "age": 34, 
        "update_time": "2019-06-06 16:59:06", 
        "display": 0
    }
}'''