def __init__(self, canal_server_host: str, canal_port: int, canal_mysql_username: str, canal_mysql_password: str,
client_id: int, destination: str, sleep_time: int = 1, get_count: int = 100,
thread_pool_count: int = 20):
self.client = Client()
self.canal_server_host = canal_server_host
self.canal_port = canal_port
self.canal_mysql_username = canal_mysql_username
self.canal_mysql_password = canal_mysql_password
self.client_id = client_id
self.destination = destination
self.sleep_time = sleep_time
self.get_count = get_count
self.init_canal_client()
self.executor = ThreadPoolExecutor(max_workers=thread_pool_count)
def run(self, execution):
"""
execution: 执行函数，要对mysql的binlog记录做何种操作，函数输入必须是rowChangeRec那样的字典
"""
client = Client()
client.connect(host=self.ip)
client.check_valid()
client.subscribe()
while True:
message = client.get(100)
entries = message['entries']
for entry in entries:
entryType = entry.entryType
if entryType in [
EntryProtocol_pb2.EntryType.TRANSACTIONBEGIN,
EntryProtocol_pb2.EntryType.TRANSACTIONEND
]:
continue
rowChanges = EntryProtocol_pb2.RowChange()
rowChanges.MergeFromString(entry.storeValue)
eventType = rowChanges.eventType
header = entry.header
database = header.schemaName
table = header.tableName
for rowChange in rowChanges.rowDatas:
formatData = dict()
if eventType == EntryProtocol_pb2.EventType.DELETE:
for column in rowChange.beforeColumns:
formatData[column.name] = column.value
elif eventType == EntryProtocol_pb2.EventType.INSERT:
for column in rowChange.afterColumns:
formatData[column.name] = column.value
else:
formatData['before'], formatData['after'] = dict(
), dict()
for column in rowChange.beforeColumns:
formatData['before'][column.name] = column.value
for column in rowChange.afterColumns:
formatData['after'][column.name] = column.value
rowChangeRec = dict(
db=database,
table=table,
event_type=eventType,
data=formatData,
)
execution(rowChangeRec)
sleep(self.interval)
def run(self):
from kafka import KafkaProducer
from canal.client import Client
client = Client()
producer = KafkaProducer(
bootstrap_servers=self.bootstrap_servers.split(','))
try:
client.connect(host=self.canal_host, port=self.canal_port)
client.check_valid(username=self.canal_username.encode(),
password=self.canal_password.encode())
client.subscribe(client_id=self.canal_client_id.encode(),
destination=self.canal_destination.encode(),
filter=self.canal_table_filter.encode())
self.run_forever(client, producer)
finally:
client.disconnect()
producer.close()
class CanalSubject(Subject, ABC):
"""
CanalSubject
"""
def __init__(self, canal_server_host: str, canal_port: int, canal_mysql_username: str, canal_mysql_password: str,
client_id: int, destination: str, sleep_time: int = 1, get_count: int = 100,
thread_pool_count: int = 20):
self.client = Client()
self.canal_server_host = canal_server_host
self.canal_port = canal_port
self.canal_mysql_username = canal_mysql_username
self.canal_mysql_password = canal_mysql_password
self.client_id = client_id
self.destination = destination
self.sleep_time = sleep_time
self.get_count = get_count
self.init_canal_client()
self.executor = ThreadPoolExecutor(max_workers=thread_pool_count)
def __str__(self):
return f'canal_server_host:{self.canal_server_host} canal_port:{self.canal_port} canal_mysql_username:'******'{self.canal_mysql_username} canal_mysql_password{self.canal_mysql_password} destination{self.destination}'
def __repr__(self):
return f'canal_server_host:{self.canal_server_host} canal_port:{self.canal_port} canal_mysql_username:'******'{self.canal_mysql_username} canal_mysql_password{self.canal_mysql_password} destination{self.destination}'
def init_canal_client(self):
"""
init canal client
:return:
"""
self.client.connect(host=self.canal_server_host, port=self.canal_port)
self.client.check_valid(username=self.canal_mysql_username.encode('utf-8'),
password=self.canal_mysql_password.encode('utf-8'))
self.client.subscribe(client_id=str(self.client_id).encode('utf-8'),
destination=self.destination.encode('utf-8'), filter=b'.*\\..*')
def get_message(self):
"""
get message by kafka or canal_server
"""
while True:
try:
message = self.client.get(100)
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)
header = entry.header
database = header.schemaName
table = header.tableName
event_type = header.eventType
format_data = dict()
for row in row_change.rowDatas:
if event_type == EntryProtocol_pb2.EventType.DELETE:
for column in row.beforeColumns:
format_data[column.name] = column.value
elif event_type == EntryProtocol_pb2.EventType.INSERT:
for column in row.afterColumns:
format_data[column.name] = column.value
else:
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 = dict(
db=database,
table=table,
event_type=event_type,
data=format_data,
)
LOGGER.info(f"receive canal server message: {data}")
self.executor.submit(self.notify, data)
time.sleep(self.sleep_time)
except:
self.client.disconnect()
sys.exit(1)
try:
import MySQLdb
except Exception as e:
print('pip install MySQLdb')
sys.exit(1)
# 打开数据库连接
db = MySQLdb.connect("127.0.0.1", "root", "test123", "testdb2", charset='utf8')
# 使用cursor()方法获取操作游标
cursor = db.cursor()
localtime = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
client = Client()
client.connect(host='192.168.1.1', port=11111)
client.check_valid(username=b'', password=b'')
client.subscribe(client_id=b'106',
destination=b'example',
filter=b'testdb\\.test,testdb\\.test2')
def deleteRow(table, id):
sql = "DELETE FROM %s WHERE id=%s" % (table, id)
print(localtime)
print(sql)
try:
# 执行SQL语句
cursor.execute(sql)
# 提交修改
import time
from canal.client import Client
from canal.protocol import EntryProtocol_pb2
from canal.protocol import CanalProtocol_pb2
host = '172.16.83.226'
client = Client()
client.connect(host=host, port=11111)
client.check_valid(username=b'', password=b'')
client.subscribe(client_id=b'10011', destination=b'example', filter=b'.*\\..*')
while True:
message = client.get(100)
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
for row in row_change.rowDatas:
# print("insert:", row)
import json
import time
from kafka import KafkaProducer
from pprint import pprint
import pandas as pd
from canal.client import Client
from canal.protocol import EntryProtocol_pb2
client = Client()
client.connect(host='10.200.5.117', port=11111)
client.check_valid(username=b'maxwell', password=b'123456')
database = b'afanti.canal_test'
# database=b'maxwell.test'
client.subscribe(client_id=b'1001',
destination=b'example',
filter=b'afanti.rec_useraction')
while True:
message = client.get(100)
# print(message)
entries = message['entries']
format_data = dict()
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()