canal-server+canal-adapter实现mysql数据同步

目录

• canal-server+canal-adapter实现mysql数据同步
  • 角色及功能
  • canal-server部署
  • canal-adapter部署
  • 配置详解
    • canal-server
      • instance
    • canal-adapter
      • application.yml
      • client.yml

canal-server+canal-adapter实现mysql数据同步

canal-adapter 为 alibaba/canal 官方提供的canal客户端

git地址

Docker镜像

角色及功能

角色	功能
canal-server	监听源库，获取源数据
canal-adapter	订阅canal-adapter，向目标库写入数据

---

canal-server部署

详见k8s集群中部署canal集群 - 大胡萝卜没有须 - 博客园

需要新建instance监听源库数据，建议使用canal-admin页面进行配置

具体配置详解见下文

---

canal-adapter部署

使用容器的方式进行部署，本文直接使用官方镜像

slpcat/canal-adapter:v1.1.5-jdk8

容器启动中需要配置2个挂载：

canal-adapter主配置文件
/opt/canal-adapter/conf/application.yml

向目标库写入时的相关配置，若canal-adapter实例需要向多个目录库写入，可在/opt/canal-adapter/conf/rdb目录下配置多个[client].yml文件
/opt/canal-adapter/conf/rdb/client.yml

---

配置详解

canal-server

instance

#################################################
## mysql serverId , v1.0.26+ will autoGen
# canal.instance.mysql.slaveId=0
 
# enable gtid use true/false
canal.instance.gtidon=false
 
# position info
# mysql连接信息
canal.instance.master.address=192.168.1.10:3306
canal.instance.master.journal.name=
canal.instance.master.position=
canal.instance.master.timestamp=
canal.instance.master.gtid=
 
# rds oss binlog
# rds oss 授权
# 此项配置原本是为了源库为阿里云rds实例时，rds会定时将binlog文件上传至oss
# 上传后实例本地便找不到binlog日志了，会导致canal-server读取binlog文件失败
# 添加此配置后canal-server可以去oss中主动拉取binlog文件，以实现读取
# ---！！！但是！！！---
# 现版本的阿里云rds实例已经不支持这个操作了！！！
# 所以这几行配置啥也不是！！！
canal.instance.rds.accesskey=LT123456789
canal.instance.rds.secretkey=zZ3s123456789
canal.instance.rds.instanceId=rm-uf123456789
 
# 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=root
canal.instance.dbPassword=abc^123456789
canal.instance.connectionCharset = utf8mb4
# enable druid Decrypt database password
canal.instance.enableDruid=false
#canal.instance.pwdPublicKey=MFwwDQYJKoZIhvcNAQEBBQADSwAwSAJBALK4BUxdDltRRE5/zXpVEVPUgunvscYFtEip3pmLlhrWpacX7y7GCMo2/JM6LeHmiiNdH1FWgGCpUfircSwlWKUCAwEAAQ==
 
# table regex
# 采集白名单
# 1、采集实例下所有库表
# canal.instance.filter.regex=.*\\..*
# 2、采集某库下的所有表
# canal.instance.filter.regex=databeses1\..*
# canal.instance.filter.regex=databeses1\..*,databeses2\..*
# 3、采集某库下的指定表
# canal.instance.filter.regex=databeses1.table1
# canal.instance.filter.regex=databeses1.table1,databeses1.table2
canal.instance.filter.regex=databeses1.table1
# table black regex
# 采集黑名单，写法同上
canal.instance.filter.black.regex=
# 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-adapter

application.yml

server:
# 服务端口
  port: 8081
spring:
  jackson:
    date-format: yyyy-MM-dd HH:mm:ss
    time-zone: GMT+8
    default-property-inclusion: non_null
 
canal.conf:
  # 订阅canal-server的方式
  mode: tcp #tcp kafka rocketMQ rabbitMQ
  flatMessage: true
  zookeeperHosts:
  syncBatchSize: 1000
  retries: 0
  timeout:
  accessKey:
  secretKey:
  consumerProperties:
    # canal tcp consumer
    # canal-server tcp方式的链接地址
    canal.tcp.server.host: 192.168.1.30:11111
    # zk的链接地址，若为单点canal，此项为空
    canal.tcp.zookeeper.hosts:
    canal.tcp.batch.size: 500
    # 连接canal-server的账号/密码
    canal.tcp.username: admin
    canal.tcp.password: 123456
    # kafka consumer
    #kafka.bootstrap.servers: 127.0.0.1:9092
    #kafka.enable.auto.commit: false
    #kafka.auto.commit.interval.ms: 1000
    #kafka.auto.offset.reset: latest
    #kafka.request.timeout.ms: 40000
    #kafka.session.timeout.ms: 30000
    #kafka.isolation.level: read_committed
    #kafka.max.poll.records: 1000
    # rocketMQ consumer
    #rocketmq.namespace:
    #rocketmq.namesrv.addr: 127.0.0.1:9876
    #rocketmq.batch.size: 1000
    #rocketmq.enable.message.trace: false
    #rocketmq.customized.trace.topic:
    #rocketmq.access.channel:
    #rocketmq.subscribe.filter:
    # rabbitMQ consumer
    #rabbitmq.host:
    #rabbitmq.virtual.host:
    #rabbitmq.username:
    #rabbitmq.password:
    #rabbitmq.resource.ownerId:
 
#  srcDataSources:
#    testDS:
#      url: jdbc:mysql://10.10.101.40:3306/mytest2?useUnicode=true
#      username: root
#      password: h0uBPTVtmzF>1xuW
  # canal-adapter执行器配置
  canalAdapters:
  # canal instance Name or mq topic name
  # canal-server中配置的instance名称
  - instance: instance-cmoon
    groups:
    # 自定义gid
    - groupId: g-cmoon
      outerAdapters:
      - name: rdb
        # 自定义key
        key: mysql-cmoon
        # 目标库信息
        properties:
          # 使用的写入引擎，此处为mysql
          jdbc.driverClassName: com.mysql.jdbc.Driver
          # 目标库的链接方式
          jdbc.url: jdbc:mysql://192.168.2.43:3306/databeses1?useUnicode=true&characterEncoding=utf-8
          # 目标库的用户名
          jdbc.username: root
          # 目标库的密码
          jdbc.password: CMOON^123456789
#      - name: rdb
#        key: oracle1
#        properties:
#          jdbc.driverClassName: oracle.jdbc.OracleDriver
#          jdbc.url: jdbc:oracle:thin:@localhost:49161:XE
#          jdbc.username: mytest
#          jdbc.password: m121212
#      - name: rdb
#        key: postgres1
#        properties:
#          jdbc.driverClassName: org.postgresql.Driver
#          jdbc.url: jdbc:postgresql://localhost:5432/postgres
#          jdbc.username: postgres
#          jdbc.password: 121212
#          threads: 1
#          commitSize: 3000
#      - name: hbase
#        properties:
#          hbase.zookeeper.quorum: 127.0.0.1
#          hbase.zookeeper.property.clientPort: 2181
#          zookeeper.znode.parent: /hbase
#      - name: es
#        hosts: 127.0.0.1:9300 # 127.0.0.1:9200 for rest mode
#        properties:
#          mode: transport # or rest
#          # security.auth: test:123456 #  only used for rest mode
#          cluster.name: elasticsearch
#        - name: kudu
#          key: kudu
#          properties:
#            kudu.master.address: 127.0.0.1 # ',' split multi address

client.yml

# 源数据源的key, 对应上面配置的srcDataSources中的值
# dataSourceKey: defaultDS
# cannal的instance或者MQ的topic
destination: instance-cmoon
# 对应MQ模式下的groupId, 只会同步对应groupId的数据
groupId: g-cmoon
# adapter key, 对应上面配置outAdapters中的key
outerAdapterKey: mysql-cmoon
# 是否按主键hash并行同步, 并行同步的表必须保证主键不会更改及主键不能为其他同步表的外键!!
concurrent: true
dbMapping:
  # 源数据源的database/shcema
  database: databeses1
   # 源数据源表名
#  table: adid2_customer
   # 目标数据源的库名.表名
#  targetTable: adid2_customer
  # 主键映射
#  targetPk:
    # 如果是复合主键可以换行映射多个
#    id: id
  # 是否整表映射, 要求源表和目标表字段名一模一样 (如果targetColumns也配置了映射,则以targetColumns配置为准)
  mapAll: true
  # 开启mysql库间镜像scheme
  mirrorDb: true
  # 字段映射, 格式: 目标表字段: 源表字段, 如果字段名一样源表字段名可不填
#  targetColumns: