canal同步mysql数据到elasticsearch7.15.0

1.准备

官方网址:https://github.com/alibaba/canal/wiki/QuickStart
对于自建 MySQL , 需要先开启 Binlog 写入功能，配置 binlog-format 为 ROW 模式，my.cnf 中配置如下

[mysqld]
log-bin=mysql-bin # 开启 binlog
binlog-format=ROW # 选择 ROW 模式
server_id=1 # 配置 MySQL replaction 需要定义，不要和 canal 的 slaveId 重复

注意：针对阿里云 RDS for MySQL , 默认打开了 binlog , 并且账号默认具有 binlog dump 权限 , 不需要任何权限或者 binlog 设置,可以直接跳过这一步

授权 canal 链接 MySQL 账号具有作为 MySQL slave 的权限, 如果已有账户可直接 grant

CREATE USER canal IDENTIFIED BY 'canal';  
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'canal'@'%';
-- GRANT ALL PRIVILEGES ON *.* TO 'canal'@'%' ;
FLUSH PRIVILEGES;

2.下载canal

下载 canal, 访问 release 页面 , 选择需要的包下载, 如以 1.1.15 版本为例

服务端:

waget https://github.com/alibaba/canal/releases/download/canal-1.1.5/canal.deployer-1.1.5.tar.gz

客户端：

wget https://github.com/alibaba/canal/releases/download/canal-1.1.5/canal.adapter-1.1.5.tar.gz

amdin：

wget https://github.com/alibaba/canal/releases/download/canal-1.1.5/canal.admin-1.1.5.tar.gz

3.服务端安装:

mkdir /mnt/software/canal-deployer
tar zxvf canal.deployer-1.1.5.tar.gz  -C /mnt/software/canal-deployer/

修改配置：

vi conf/example/instance.properties

## mysql serverId , v1.0.26+ will autoGen
# canal.instance.mysql.slaveId=0

# enable gtid use true/false
canal.instance.gtidon=false

# 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.master.gtid=

# rds oss binlog
#canal.instance.rds.accesskey=
#canal.instance.rds.secretkey=
#canal.instance.rds.instanceId=

# table meta tsdb info
canal.instance.tsdb.enable=true
#canal.instance.tsdb.url=jdbc:mysql://127.0.0.1:3306/canal_tsdb
#canal.instance.tsdb.dbUsername=canal
#canal.instance.tsdb.dbPassword=canal

#canal.instance.standby.address =
#canal.instance.standby.journal.name =
#canal.instance.standby.position =
#canal.instance.standby.timestamp =
#canal.instance.standby.gtid=

# username/password
#数据库账户密码
canal.instance.dbUsername=canal
canal.instance.dbPassword=canal
canal.instance.connectionCharset = UTF-8
# enable druid Decrypt database password
canal.instance.enableDruid=false
#canal.instance.pwdPublicKey=MFwwDQYJKoZIhvcNAQEBBQADSwAwSAJBALK4BUxdDltRRE5/zXpVEVPUgunvscYFtEip3pmLlhrWpacX7y7GCMo2/JM6LeHmiiNdH1FWgGCpUfircSwlWKUCAwEAAQ==

# table regex
#table正则表达式默认所有
#mysql 数据解析关注的表，Perl正则表达式.
#多个正则之间以逗号(,)分隔，转义符需要双斜杠(\\) 
#常见例子：
#1.  所有表：.*   or  .*\\..*
#2.  canal schema下所有表： canal\\..*
#3.  canal下的以canal打头的表：canal\\.canal.*
#4.  canal schema下的一张表：canal.test1
#5.  多个规则组合使用：canal\\..*,mysql.test1,mysql.test2 (逗号分隔)
#注意：此过滤条件只针对row模式的数据有效(ps. mixed/statement因为不解析sql，所以无法准确提取tableName进行过滤)
canal.instance.filter.regex=.*\\..*
# table black regex
canal.instance.filter.black.regex=mysql\\.slave_.*
# table field filter(format: schema1.tableName1:field1/field2,schema2.tableName2:field1/field2)
#canal.instance.filter.field=test1.t_product:id/subject/keywords,test2.t_company:id/name/contact/ch
# table field black filter(format: schema1.tableName1:field1/field2,schema2.tableName2:field1/field2)
#canal.instance.filter.black.field=test1.t_product:subject/product_image,test2.t_company:id/name/contact/ch

# mq config
canal.mq.topic=example
# dynamic topic route by schema or table regex
#canal.mq.dynamicTopic=mytest1.user,mytest2\\..*,.*\\..*
canal.mq.partition=0
# hash partition config
#canal.mq.partitionsNum=3
#canal.mq.partitionHash=test.table:id^name,.*\\..*
#canal.mq.dynamicTopicPartitionNum=test.*:4,mycanal:6
#################################################

服务端启动:

sh bin/startup.sh

启动以后默认有三个端口:
admin端口:11110
tcp端口:11111
metiric端口:11112
查看cannal日志:

查看install 日志:

tail -f logs/example/example.log

4.客户端安装:

mkdir /mnt/software/canal
tar zxvf canal.adapter-1.1.5.tar.gz  -C /mnt/software/canal/

配置修改:

vi conf/application.yml

server:
  #springboot启动端口
  port: 5177
spring:
  jackson:
    date-format: yyyy-MM-dd HH:mm:ss
    time-zone: GMT+8
    default-property-inclusion: non_null
canal.conf:
  #服务端tcp通信地址
  canalServerHost: 127.0.0.1:11111
  batchSize: 500
  syncBatchSize: 1000
  retries: 0
  timeout: 10
  accessKey: 1222
  secretKey: 1222
  mode: tcp
  consumerProperties:
    # canal tcp consumer
    #服务端tcp通信地址
    canal.tcp.server.host: 127.0.0.1:11111
    canal.tcp.zookeeper.hosts:
    canal.tcp.batch.size: 500
    canal.tcp.username: admin
    canal.tcp.password: admin123
  srcDataSources:
    defaultDS:
       url: jdbc:mysql://127.0.0.1:3306/test?useUnicode=true
       username: canal
       password: canal
  canalAdapters:
  # canal instance Name or mq topic name
  #注意：instance 一定要和服务端的 instance  name一致
  - instance: example
    groups:
    - groupId: g1
      outerAdapters:
       - name: logger
       - name: es7
         key: es7
         # 127.0.0.1:9200 for rest mode
         hosts: http://10.0.0.3:9200,http://10.0.0.4:9200,http://10.0.0.5:9200
         properties:
           #  only used for rest mode
           mode: rest
           security.auth: elastic:a123456
           cluster.name: elastic-application

配置 es7:
根据实际需求配置
配置参考:https://github.com/alibaba/canal/wiki/Sync-ES

vi conf/es7/customer.yml

启动:

sh bin/startup.sh

ClassCastException：

ERROR c.a.o.canal.adapter.launcher.loader.CanalAdapterLoader - Load canal adapter: es7 failed
java.lang.RuntimeException: java.lang.RuntimeException: java.lang.ClassCastException: com.alibaba.druid.pool.DruidDataSource cannot be cast to com.alibaba.druid.pool.DruidDataSource
        at com.alibaba.otter.canal.client.adapter.es7x.ES7xAdapter.init(ES7xAdapter.java:54) ~[client-adapter.es7x-1.1.5-jar-with-dependencies.jar:na]
        at com.alibaba.otter.canal.adapter.launcher.loader.CanalAdapterLoader.loadAdapter(CanalAdapterLoader.java:225) [client-adapter.launcher-1.1.5.jar:na]
        at com.alibaba.otter.canal.adapter.launcher.loader.CanalAdapterLoader.init(CanalAdapterLoader.java:56) [client-adapter.launcher-1.1.5.jar:na]
        ......
        at com.alibaba.otter.canal.client.adapter.es7x.ES7xAdapter.init(ES7xAdapter.java:52) ~[client-adapter.es7x-1.1.5-jar-with-dependencies.jar:na]
        ... 42 common frames omitted
Caused by: java.lang.ClassCastException: com.alibaba.druid.pool.DruidDataSource cannot be cast to com.alibaba.druid.pool.DruidDataSource
        at com.alibaba.otter.canal.client.adapter.es.core.ESAdapter.addSyncConfigToCache(ESAdapter.java:146) ~[client-adapter.es7x-1.1.5-jar-with-dependencies.jar:na]
        at com.alibaba.otter.canal.client.adapter.es.core.ESAdapter.init(ESAdapter.java:75) ~[client-adapter.es7x-1.1.5-jar-with-dependencies.jar:na]
        ... 43 common frames omitted

原因:canaladapter-1.1.5版本的druid包冲突
解决1：
下载v1.1.5-alpha-2版本的
解压后，将canaladapter-1.1.5的plugin里面的client-adapter.es7x-1.1.5-jar-with-dependencies.jar替换为v1.1.5-alpha-2版本中plugin里面的client-adapter.es7x-1.1.5-SNAPSHOT-jar-with-dependencies.jar

修改数据查看日志:

tail -f logs/adapter/adapter.log

5.admin安装

解压到对应目录,执行启动脚本，这里就不多说了

sh bin/startup.sh