一、什么是canal
**canal **，译意为水道/管道/沟渠，主要用途是基于 MySQL 数据库增量日志解析，提供增量数据订阅和消费
说白了就是，根据Mysql的binlog日志进行增量同步数据。要理解canal的原理，就要先了解mysql的主从复制原理
- 所有的create update delete操作都会进入MySQLmaster节点
- master节点会生成binlog文件，每次操作mysql数据库就会记录到binlog文件中
- slave节点会订阅master节点的binlog文件，以增量备份的形式同步数据到slave数据
canal原理就是伪装成mysql的从节点，从而订阅master节点的binlog日志，主要流程为
- canal服务端向mysql的master节点传输dump协议
- mysql的master节点接收到dump请求后推送binlog日志给canal服务端，解析binlog对象（原始为byte流）转成Json格式
- canal客户端通过TCP协议或MQ形式监听canal服务端，同步数据到ES
二、环境搭建
参考文档：
软件版本：
canal1.1.4、mysql5.6.33、elasticsearch6.4.2
操作步骤：
-
部署安装canal.deployer
- 解压后文件结构如下
image- 配置server（仅列出修改部分的内容 后面的配置文件也是）
canal.admin.port = 11111
canal.serverMode = tcp #canal-server运行模式，TCP模式表示直连客户端
canal.destinations = user_order #指定创建的实例名称
- 配置自定义user_order实例
以提供的实例example为参考 cp -a example/ user_order && vim user_order/instance.properties
canal.instance.master.address=172.18.16.1:3306 #指定要读取binlog的mysql的ip及端口
canal.instance.dbUsername=canal
canal.instance.dbPassword=canal
canal.instance.connectionCharset = UTF-8
canal.instance.filter.regex=meisha_order.tb_order #匹配库表白名单 只要指定库表的增量数据
- 启动
sh bin/startup.sh
- 查看server日志
- 查看实例日志
-
部署安装canal.adapter
-
解压后文件结构如下
image -
配置adapter conf/application.yml
mode: tcp
canalServerHost: 127.0.0.1:11111 #指定前面配置的canal-server的地址和端口
srcDataSources:
defaultDS:
url: jdbc:mysql://172.18.16.1:3306/meisha_order?useUnicode=true
username: canal
password: canalcanalAdapters:
instance: user_order
-
outerAdapters:
- name: logger
- name:es
hosts: 172.18.0.13:7200
properties:
mode: rest
cluster.name: ms-es
---
-
配置es
cp conf/es/customer.yml conf/es/order.yml && vim conf/es/order.yml
dataSourceKey: defaultDS
destination: user_order
groupId: g1
esMapping:
_index: test_meisha_order
_type: order_search
_id: _id
sql: "select t.id as _id, t.channel_company_id, t.channel_user_id, t.flash_sale_activity_id, t.is_attend_group, t.status, t.flash_sale_reduce_amount, t.coupon_reduce_amount, t.general_coupon_reduce_amount, t.revise_amount, t.benefit_reduce_amount, t.paid_at, t.pay_agency from tb_order t"
commitBatch: 3000
-
demo演示
mysql/es初始状态
imageimage
通过sql直接修改channel_user_id字段的值
update meisha_order.tb_order set channel_user_id = '0035451e98790957' where id = '1820103691417724';
实时自动同步es
2021-01-18 15:09:48.987 [pool-3-thread-1] DEBUG c.a.otter.canal.client.adapter.es.service.ESSyncService - DML: {"data":[{"id":"1820103691417724","is_business":0,"business_enterprise_id":"","user_id":"8b714c3a869495ef","platform_company_id":"e150aa75386543ff","campus_company_id":"e150aa75386543ff","campus_serial":"11","course_company_id":"e150aa75386543ff","channel_company_id":"e150aa75386543ff","channel_user_id":"0035451e98790957","channel_user_phone_no":"13580445178","invite_code":"","status":30,"type":3,"course_id":"DCA8B9A1439D7CAB","sub_type":"104","subject_id":"326","end_time":0,"begin_time":0,"time_type":0,"is_agreed":1,"ip_address":"","origin_amount":3,"order_amount":3,"revise_amount":0,"experience_revise_amount":0,"privilege_revise_amount":0,"agency_received_amount":3,"balance_received_amount":0,"agency_refund_amount":0,"balance_refund_amount":0,"fee":0,"origin_balance_transfer_out_amount":0,"origin_agency_transfer_out_amount":0,"origin_balance_transfer_in_amount":0,"origin_agency_transfer_in_amount":0,"balance_real_transfer_out_amount":0,"agency_real_transfer_out_amount":0,"balance_real_transfer_in_amount":0,"agency_real_transfer_in_amount":0,"split_pay_times":1,"pay_agency":303,"business_pay_agency":0,"payinfo_id":"1820103701415236","transaction_id":"","pos_device_id":"8F79351370B3360F","coupon_snapshot":null,"privilege_snapshot":null,"split_payment_snapshot":null,"experience_snapshot":null,"course_packages":null,"remark":"","user_remark":"","activity":"","source":"","third_party_service":"","keywords":"1820103691417724#18819464136#智能POS全日制课程#智能POS课程#张小#110101200001028333#DHJ132799#","way":3,"expired_at":1572605114,"paid_at":1572603414,"business_full_settled_at":0,"close_at":0,"update_time":1610615695,"create_time":1572603314,"is_deleted":0,"coupon_reduce_amount":0,"benefit_reduce_amount":0,"benefit_snapshot":null,"cal_commission":1,"reason_not_call_commission":null,"contract_id":"","contract_snapshot":null,"transfer_out_fee":0,"origin_transfer_out_fee":0,"agency_settle_amount":0,"balance_settle_amount":0,"settlement_status":1,"reward_introducer":0,"flash_sale_activity_id":"","flash_sale_reduce_amount":0,"merchant_id":"e150aa75386543ff","is_attend_group":0,"group_reduce_amount":0,"general_coupon_snapshot":"","general_coupon_reduce_amount":0,"auto_launch_group":0}],"database":"meisha_order","destination":"user_order","es":1610953788000,"groupId":null,"isDdl":false,"old":[{"channel_user_id":"00fb425ba4b6433e"}],"pkNames":["id"],"sql":"","table":"tb_order","ts":1610953788975,"type":"UPDATE"}
