python3数据库同步_python进行mysql数据实时同步的正确姿势（使用canal）

简介

最近项目组有一个需求，需要让异地的两个数据库中的某几个配置表实时同步，本想自己写个服务利用binlog2sql工具解析出sql语句进行同步，考虑到需要实时监控binlog并解析，且binlog2sql解析较慢，决定利用canal进行部署。

首先介绍一下canal，它是阿里的一个mysql增量订阅&消费工具，附github主页：https://github.com/alibaba/canal

canal的框架比较简单，分为服务端和客户端，以一种比较易懂的方式解释就是：

服务端可以理解为一个mysql服务端(即高可用架构中的从节点)，为了让canal服务端生效，我们需要进行一些简单的配置，让canal服务端向真正的mysql服务端发送获取binlog请求，并且将binlog解析以后存在本地的数据结构中。这样在canal服务端运行以后，我们可以按照官方的规范去连接服务端获取数据；

客户端可以理解为某种意义上的数据库客户端，通过一些简单的编码，我们可以获取存在canal服务端的已被解析的binlog数据(增量数据)，获取数据以后，即可进行定制化的处理

部署

服务端

在实际配置时，有一些信息我觉得可以备注一下：

## mysql serverId

# 目前最新的服务端版本已不需要配置serverId参数

canal.instance.mysql.slaveId = 1234

#position info，需要改成自己的数据库信息

canal.instance.master.address = 127.0.0.1:3306

canal.instance.master.journal.name =

canal.instance.master.position =

canal.instance.master.timestamp =

#canal.instance.standby.address =

#canal.instance.standby.journal.name =

#canal.instance.standby.position =

#canal.instance.standby.timestamp =

#username/password，需要改成自己的数据库信息

canal.instance.dbUsername = canal

canal.instance.dbPassword = canal

canal.instance.defaultDatabaseName =

canal.instance.connectionCharset = UTF-8

# table regex

# binlog解析的过滤规则，采用正则表达式

canal.instance.filter.regex = .\*\\\\..\*

客户端

在按照官方说明配置完成后，测试主页上的demo时发现了一个小问题，首先来看默认demo(自己加了一些注释方便理解)：

import time

from canal.client import Client

from canal.protocol import EntryProtocol_pb2

from canal.protocol import CanalProtocol_pb2

# 建立与canal服务端的连接

client = Client()

client.connect(host='127.0.0.1', port=11111) # canal服务端部署的主机IP与端口

client.check_valid(username=b'', password=b'') # 自行填写配置的数据库账户密码

# destination是canal服务端的服务名称， filter即获取数据的过滤规则，采用正则表达式

client.subscribe(client_id=b'1001', destination=b'example', filter=b'.*\\..*')

while True:

message = client.get(100)

# entries是每个循环周期内获取到数据集

entries = message['entries']

for entry in entries:

entry_type = entry.entryType

if entry_type in [EntryProtocol_pb2.EntryType.TRANSACTIONBEGIN, EntryProtocol_pb2.EntryType.TRANSACTIONEND]:

continue

row_change = EntryProtocol_pb2.RowChange()

row_change.MergeFromString(entry.storeValue)

event_type = row_change.eventType

header = entry.header

# 数据库名

database = header.schemaName

# 表名

table = header.tableName

event_type = header.eventType

# row是binlog解析出来的行变化记录，一般有三种格式，对应增删改

for row in row_change.rowDatas:

format_data = dict()

# 根据增删改的其中一种情况进行数据处理

if event_type == EntryProtocol_pb2.EventType.DELETE:

format_data['before'] = dict()

for column in row.beforeColumns:

#format_data = {

# column.name: column.value

#}

#此处注释为原demo，有误，下面是正确写法

format_data['before'][column.name] = column.value

elif event_type == EntryProtocol_pb2.EventType.INSERT:

format_data['after'] = dict()

for column in row.afterColumns:

#format_data = {

# column.name: column.value

#}

#此处注释为原demo，有误，下面是正确写法

format_data['after'][column.name] = column.value

else:

# format_data['before'] = format_data['after'] = dict() 采用下面的写法应该更好

format_data['before'] = dict()

format_data['after'] = dict()

for column in row.beforeColumns:

format_data['before'][column.name] = column.value

for column in row.afterColumns:

format_data['after'][column.name] = column.value

# data即最后获取的数据，包含库名，表明，事务类型，改动数据

data = dict(

db=database,

table=table,

event_type=event_type,

data=format_data,

)

print(data)

time.sleep(1)

client.disconnect()

使用数据

这个demo间隔一秒获取一次服务端的增量数据，并作相应的解析，代码中我已经做了简单的注释帮助理解，最后获取的data就是某个sql语句改动某一行的完整记录，通常有三种情况：

# 设库test中有表test1，分别有id(int)和name(varchar)字段

# insert操作：insert into test.test1 values (1，'a')

# 此时data中应是如下情况

data = {'db':'test', 'table':'test1', 'event_type':1, 'data':{'after':{'id':'1', 'name':'a'}}}

# update操作：update test.test1 set id=2, name='b' where id=1

# 此时的data

data = {'db':'test', 'table':'test1', 'event_type':2, 'data':{'before':{'id':'1', 'name':'a'}, 'after':{'id':'2', 'name':'b'}}}

# delete操作:delete from test.test1 where id=2

# 此时的data

data = {'db':'test', 'table':'test1', 'event_type':3, 'data':{'before':{'id':'2', 'name':'b'}}}

如上，可根据生成的data做进一步处理，有较大的自由度，而此处我需要的是直接插入到另一台主机上的同样的库表中，因此我需要将data再解析为sql语句：

def data_to_sql(data: dict) -> str:

db = data['db']

table = data['db']

sql = ''

# insert

if data['event_type'] == 1:

dic_data = data['data']['after']

insert_value = ""

for key in dic_data.keys():

insert_value = insert_value + f"'{dic_data[key]}'" + ','

insert_value = insert_value[:-1]

sql = f"insert into{db}.{table}values ({insert_value});"

return sql

# update

elif data['event_type'] == 2:

before_data = data['before']

after_data = data['after']

update_value = ""

update_condition = ""

for key in before_data.keys():

update_condition = update_condition + f"'{before_data[key]}' and "

update_condition = update_condition[:-5]

for key in after_data.keys():

update_value = update_value + key + f"='{after_data[key]}',"

update_value = update_value[:-1]

sql = f"update{db}.{table}set{update_value}where{update_condition};"

# delete

else:

dic_data = data['data']['before']

delete_condition = ""

for key in dic_data.keys():

delete_condition = delete_condition + f"'{dic_data[key]}' and "

delete_condition = delete_condition[:-5]

sql = f"delete from{db}.{table}where{delete_condition};"

return sql

注意点：

1、由于我使用的是内网pypi不是最新，无法获取canal-python，将主页上源码下载后放在python环境目录下的site-package目录下即可；

2、在使用canal-python时需要安装一些依赖包，可能存在指定版本不存在的情况，在requirements.txt文件中删除包后面指定的版本号即可；

3、通过上面的代码可以看出无论原始数据是int还是varchar，解析出来的数据都是字符串类型；