canal数据同步

mysql配置

mysql配置文件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;

canal-deploy

安装canal-deploy

修改配置文件

conf/canal.properties

canal.port = 11111
canal.serverMode = tcp
canal.destinations = example

conf/example/instance.properties

canal.instance.master.address=192.168.1.201:8306
canal.instance.dbUsername=canal
canal.instance.dbPassword=canal1234
canal.instance.filter.regex=testdb.name_info

管理服务

[root@master1 canal-deployer]# ls ./bin/
restart.sh  startup.bat  startup.sh  stop.sh

查看日志

[root@master1 canal-deployer]# tree logs/
logs/
├── canal
│   ├── canal.log
│   ├── canal_stdout.log
│   └── rocketmq_client.log
└── example          
    ├── example.log     
    └── meta.log

错误

ERROR com.alibaba.otter.canal.common.alarm.LogAlarmHandler - destination:example[com.alibaba.otter.canal.parse.exception.PositionNotFoundException: can't find start position for example

解决方法：

1.清空/删除 conf/example/meta.dat文件

2.重置mysql，binlog日志。reset master

canal-adapter

安装canal-adapter

修改配置文件

conf/application.yml

server:
  port: 9081
spring:
  jackson:
    date-format: yyyy-MM-dd HH:mm:ss
    time-zone: GMT+8
    default-property-inclusion: non_null
​
canal.conf:
  mode: tcp #tcp kafka rocketMQ rabbitMQ
  flatMessage: true
  syncBatchSize: 1000
  retries: -1
  timeout:
  accessKey:
  secretKey:
  consumerProperties:
    # canal tcp consumer
    canal.tcp.server.host: 127.0.0.1:11111
    canal.tcp.zookeeper.hosts:
    canal.tcp.batch.size: 500
    canal.tcp.username:
    canal.tcp.password:
  srcDataSources:
    defaultDS:
      url: jdbc:mysql://192.168.1.201:8306/testdb?useUnicode=true&useUnicode=true&characterEncoding=utf8&autoReconnect=true&useSSL=false
      username: root
      password: 123456
  canalAdapters:
  - instance: example # canal instance Name or mq topic name
    groups:
    - groupId: g1
      outerAdapters:
      #- name: logger
      - name: rdb
        key: mysql2
        properties:
          jdbc.driverClassName: com.mysql.jdbc.Driver
          jdbc.url: jdbc:mysql://192.168.1.201:3306/testdb?useUnicode=true&useUnicode=true&characterEncoding=utf8&autoReconnect=true&useSSL=false
          jdbc.username: root
          jdbc.password: qUpxhZwr3nqdPaZr
          druid.stat.enable: false
          druid.stat.slowSqlMillis: 1000

conf/rdb/name_info_user.yml

[root@master1 canal-adapter]# cat conf/rdb/name_info_user.yml 
dataSourceKey: defaultDS
destination: example
groupId: g1
outerAdapterKey: mysql2
concurrent: true
dbMapping:
  database: testdb
  table: name_info
  targetTable: name_info
#  targetPk:
#    id: id
  mapAll: true
  etlCondition: "where c_time>={}"
  commitBatch: 3000 # 批量提交的大小

错误

2023-10-23 22:08:53.947 [main] ERROR c.a.o.canal.adapter.launcher.loader.CanalAdapterLoader - Load canal adapter: rdb failed
java.lang.RuntimeException: No rdb adapter found for config key: mysql1

解决方法：

1. 查看conf/rdb目录下面的文件是不是以.yml 结尾，以.yaml结尾都不行。

2. 查看conf/rdb目录下面的文件内容是否存在outerAdapterKey: mysql1内容。

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
com.alibaba.druid.pool.DruidDataSource errorCode 0, state 08S01

解决方法：

数据库连接添加内容： ?useUnicode=true&characterEncoding=utf8&autoReconnect=true&useSSL=false

官方文档：

canal从mysql同步数据到其它MySQL数据库： https://github.com/alibaba/canal/wiki/Sync-RDB

canal是基于java环境的，因此运行前需要先安装jdk，这里我安装的是jdk11。

canal1.1.5使用jdk1.8即可，但是以下我示例的是canal1.1.6, 该版本需要使用jdk11+，否则会报错NoSuchMethodError，详细报错信息如下：

java.lang.NoSuchMethodError: java.nio.ByteBuffer.clear()Ljava/nio/ByteBuffer;
        at com.alibaba.otter.canal.client.impl.SimpleCanalConnector.readNextPacket(SimpleCanalConnector.java:412) ~[na:na]
        at com.alibaba.otter.canal.client.impl.SimpleCanalConnector.readNextPacket(SimpleCanalConnector.java:397) ~[na:na]
        at com.alibaba.otter.canal.client.impl.SimpleCanalConnector.doConnect(SimpleCanalConnector.java:155) ~[na:na]
        at com.alibaba.otter.canal.client.impl.SimpleCanalConnector.connect(SimpleCanalConnector.java:116) ~[na:na]
        at com.alibaba.otter.canal.connector.tcp.consumer.CanalTCPConsumer.connect(CanalTCPConsumer.java:63) ~[na:na]
        at com.alibaba.otter.canal.adapter.launcher.loader.AdapterProcessor.process(AdapterProcessor.java:185) ~[client-adapter.launcher-1.1.6.jar:na]