Canal 实现 同步 到 MySQL 从库 库级镜像同步 含 DDL 语句

原文地址：canal常见问题排查(三) - 意犹未尽 - 博客园 (cnblogs.com)

【方案一】

canal.deploy 配置

vim /opt/module/canal/deployer/conf/canal.properties

canal.serverMode = tcp

# 只同步 FlinkEtl 数据库的数据
# table regex
canal.instance.filter.regex=FlinkEtl\\.tb\\d_\\d

# 配置 tsdb ，支持表结构修改
# tsdb 是为预防表结构发生变化,而在 canal 订阅 binlog 时产生问题
# canal 使用 mysql 数据库存储上一次的表结构信息，然后对比两次的表结构，可解决此错误。
# table meta tsdb info
canal.instance.tsdb.enable=true
# canal.instance.tsdb.url = jdbc:h2:${canal.instance.tsdb.dir}/h2;CACHE_SIZE=1000;MODE=MYSQL;
canal.instance.tsdb.url=jdbc:mysql://hadoop108:3306/canal_tsdb?useUnicode=true&characterEncoding=UTF-8&useSSL=false
canal.instance.tsdb.dbUsername=root
canal.instance.tsdb.dbPassword=root
# canal.instance.tsdb.spring.xml = classpath:spring/tsdb/h2-tsdb.xml
canal.instance.tsdb.spring.xml = classpath:spring/tsdb/mysql-tsdb.xml

或者 vim /opt/module/canal/deployer/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=hadoop106: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=

# tsdb 是为预防表结构发生变化,而在 canal 订阅 binlog 时产生问题
# canal使用数据库存储上一次的表结构信息，然后对比两次的表结构，可解决此错误。
# table meta tsdb info
canal.instance.tsdb.enable=true
# 解开下面三行
# 修改 tsdb 为指定的数据库 url=jdbc:mysql://127.0.0.1:3306?useUnicode=true&characterEncoding=UTF-8&useSSL=false
canal.instance.tsdb.url=jdbc:mysql://127.0.0.1:3306/canal_tsdb?useUnicode=true&characterEncoding=UTF-8&useSSL=false
canal.instance.tsdb.dbUsername=root
canal.instance.tsdb.dbPassword=root
# 新增配置,因为 tsdb 默认使用的是 java 内置数据库 h2 。增加此配置使用指定的 mysq数据库
canal.instance.tsdb.spring.xml = classpath:spring/tsdb/mysql-tsdb.xml

#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=root
canal.instance.connectionCharset = UTF-8
# enable druid Decrypt database password
canal.instance.enableDruid=false
#canal.instance.pwdPublicKey=MFwwDQYJKoZIhvcNAQEBBQADSwAwSAJBALK4BUxdDltRRE5/zXpVEVPUgunvscYFtEip3pmLlhrWpacX7y7GCMo2/JM6LeHmiiNdH1FWgGCpUfircSwlWKUCAwEAAQ==

# table regex
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,topic2:mytest2\\..*,.*\\..*
canal.mq.partition=0
# hash partition config
#canal.mq.enableDynamicQueuePartition=false
#canal.mq.partitionsNum=3
#canal.mq.dynamicTopicPartitionNum=test.*:4,mycanal:6
#canal.mq.partitionHash=test.table:id^name,.*\\..*
#################################################

canal.adapter 配置
vim /opt/module/canal/adapter/conf/rdb/mytest_user.yml

# Mirror schema synchronize config 
dataSourceKey: defaultDS 
destination: example 
groupId: g1 
outerAdapterKey: mysql1
# 是否按主键 hash 并行同步
# 并行同步的表必须保证 主键不会更改 及 主键不能为其他同步表的外键 concurrent: true 
dbMapping: 
    mirrorDb: true 
    database: FlinkEtl

canal.deployer 执行sql脚本

用来存放表结构ddl语句的表，新建数据库 canal_tsdb
vim /opt/module/canal/deployer/conf/spring/tsdb/sql/create_table.sql

create database canal_tsdb;
use canal_tsdb;

CREATE TABLE `meta_snapshot` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `gmt_create` datetime NOT NULL COMMENT '创建时间',
  `gmt_modified` datetime NOT NULL COMMENT '修改时间',
  `destination` varchar(128) DEFAULT NULL COMMENT '通道名称',
  `binlog_file` varchar(64) DEFAULT NULL COMMENT 'binlog文件名',
  `binlog_offest` bigint(20) DEFAULT NULL COMMENT 'binlog偏移量',
  `binlog_master_id` varchar(64) DEFAULT NULL COMMENT 'binlog节点id',
  `binlog_timestamp` bigint(20) DEFAULT NULL COMMENT 'binlog应用的时间戳',
  `data` longtext COMMENT '表结构数据',
  `extra` text COMMENT '额外的扩展信息',
  PRIMARY KEY (`id`),
  UNIQUE KEY `binlog_file_offest` (`destination`,`binlog_master_id`,`binlog_file`,`binlog_offest`),
  KEY `destination` (`destination`),
  KEY `destination_timestamp` (`destination`,`binlog_timestamp`),
  KEY `gmt_modified` (`gmt_modified`)
)  COMMENT='表结构记录表快照表';
 
CREATE TABLE `meta_history` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `gmt_create` datetime NOT NULL COMMENT '创建时间',
  `gmt_modified` datetime NOT NULL COMMENT '修改时间',
  `destination` varchar(128) DEFAULT NULL COMMENT '通道名称',
  `binlog_file` varchar(64) DEFAULT NULL COMMENT 'binlog文件名',
  `binlog_offest` bigint(20) DEFAULT NULL COMMENT 'binlog偏移量',
  `binlog_master_id` varchar(64) DEFAULT NULL COMMENT 'binlog节点id',
  `binlog_timestamp` bigint(20) DEFAULT NULL COMMENT 'binlog应用的时间戳',
  `use_schema` varchar(1024) DEFAULT NULL COMMENT '执行sql时对应的schema',
  `sql_schema` varchar(1024) DEFAULT NULL COMMENT '对应的schema',
  `sql_table` varchar(1024) DEFAULT NULL COMMENT '对应的table',
  `sql_text` longtext COMMENT '执行的sql',
  `sql_type` varchar(256) DEFAULT NULL COMMENT 'sql类型',
  `extra` text COMMENT '额外的扩展信息',
  PRIMARY KEY (`id`),
  UNIQUE KEY `binlog_file_offest` (`destination`,`binlog_master_id`,`binlog_file`,`binlog_offest`),
  KEY `destination` (`destination`),
  KEY `destination_timestamp` (`destination`,`binlog_timestamp`),
  KEY `gmt_modified` (`gmt_modified`)
)  COMMENT='表结构变化明细表';
————————————————
https://blog.csdn.net/wfh45678/article/details/118546668

使用 canal_tsdb 保存表结构 meta 的信息变化，canal 默认使用 h2，生产建议使用 mysql、canal_tsdb、canal_manager 等

canal_tsdb 数据库表：meta_history 、meta_snapshot

执行脚本：/opt/module/canal/deployer/conf/spring/tsdb/sql/create_table.sql

mysql -uroot -p -hhadoop108
use canal_tsdb;
source /opt/module/canal/deployer/conf/spring/tsdb/sql/create_table.sql

若 canal 宕机后很久没启动，可以删除 /opt/module/canal/deployer/conf/example/meta.dat (记录 binlog 指针位置)

然后使用 canal_tsdb 数据库的话，也可以清空这两个表，重启 canal

=======================================================================

【方案二】

报错

Caused by: java.lang.RuntimeException: java.util.concurrent.ExecutionException: java.lang.RuntimeException: java.lang.RuntimeException: Target column: dept not matched
    at com.alibaba.otter.canal.client.adapter.rdb.service.RdbSyncService.lambda$sync$2(RdbSyncService.java:135) ~[na:na]
    at java.base/java.util.ArrayList.forEach(ArrayList.java:1541) ~[na:na]
    at com.alibaba.otter.canal.client.adapter.rdb.service.RdbSyncService.sync(RdbSyncService.java:131) ~[na:na]
    at com.alibaba.otter.canal.client.adapter.rdb.service.RdbSyncService.sync(RdbSyncService.java:155) ~[na:na]
    at com.alibaba.otter.canal.client.adapter.rdb.RdbAdapter.sync(RdbAdapter.java:161) ~[na:na]
    ... 8 common frames omitted
Caused by: java.util.concurrent.ExecutionException: java.lang.RuntimeException: java.lang.RuntimeException: Target column: dept not matched
    at java.base/java.util.concurrent.FutureTask.report(FutureTask.java:122) ~[na:na]
    at java.base/java.util.concurrent.FutureTask.get(FutureTask.java:191) ~[na:na]
    at com.alibaba.otter.canal.client.adapter.rdb.service.RdbSyncService.lambda$sync$2(RdbSyncService.java:133) ~[na:na]
    ... 12 common frames omitted
Caused by: java.lang.RuntimeException: java.lang.RuntimeException: Target column: dept not matched
    at com.alibaba.otter.canal.client.adapter.rdb.service.RdbSyncService.lambda$sync$1(RdbSyncService.java:126) ~[na:na]
    ... 4 common frames omitted
Caused by: java.lang.RuntimeException: Target column: dept not matched
    at com.alibaba.otter.canal.client.adapter.rdb.service.RdbSyncService.insert(RdbSyncService.java:287) ~[na:na]
    at com.alibaba.otter.canal.client.adapter.rdb.service.RdbSyncService.sync(RdbSyncService.java:226) ~[na:na]
    at com.alibaba.otter.canal.client.adapter.rdb.service.RdbSyncService.lambda$sync$0(RdbSyncService.java:117) ~[na:na]
    at java.base/java.util.ArrayList.forEach(ArrayList.java:1541) ~[na:na]
    at com.alibaba.otter.canal.client.adapter.rdb.service.RdbSyncService.lambda$sync$1(RdbSyncService.java:117) ~[na:na]
    ... 4 common frames omitted

解决方式 : 将 内存数据库 h2 改为 物理持久化 支持数据库 canal_manager，配置 rdb 镜像同步

vim /opt/module/canal/deployer/conf/canal.properties

# table meta tsdb info
canal.instance.tsdb.enable = true
canal.instance.tsdb.dir = ${canal.file.data.dir:../conf}/${canal.instance.destination:}
# canal.instance.tsdb.url = jdbc:h2:${canal.instance.tsdb.dir}/h2;CACHE_SIZE=1000;MODE=MYSQL;
canal.instance.tsdb.url = jdbc:mysql://hadoop108:3306/canal_manager?useUnicode=true&characterEncoding=UTF-8&useSSL=false
canal.instance.tsdb.dbUsername = root
canal.instance.tsdb.dbPassword = root
# dump snapshot interval, default 24 hour
canal.instance.tsdb.snapshot.interval = 24
# purge snapshot expire , default 360 hour(15 days)
canal.instance.tsdb.snapshot.expire = 360
# 注释并新增
# canal.instance.tsdb.spring.xml = classpath:spring/tsdb/h2-tsdb.xml
canal.instance.tsdb.spring.xml = classpath:spring/tsdb/mysql-tsdb.xml

vim /opt/module/canal/adapter/conf/rdb/mytest_user.yml

# Mirror schema synchronize config
dataSourceKey: defaultDS
destination: example
groupId: g1
outerAdapterKey: mysql1
# 是否按主键 hash 并行同步, 并行同步的表必须保证 主键不会更改 及 主键不能为其他同步表的外键!!
concurrent: true 
dbMapping: 
  mirrorDb: true 
  database: FlinkEtl

然后暴力删除 /opt/module/canal/deployer/conf/example

下面的  h2.mv.db 、meta.dat 。 重启 canal。

① 直接暴力删除 存储着同步的表结构信息的 h2.mv.db 文件。

rdb镜像同步时，在删除 h2.mv.db 后，重启服务，
canal 会自动创建最新的信息 和 mysql 的表结构信息达到一致。
不再报错，就可以正常工作了。

② SQL查询 当前写入binlog日志 的文件名 以及指针位置 与 meta.dat 记录的是否相符。

# 查看当前库所有binlog日志文件
show binary logs;

# meta.dat 主要用来记录的binlog读取位置
show master logs;

# 当前写入日志的文件以及写入指针位置
show master status;

vim /opt/module/canal/deployer/conf/example/meta.dat

{
    "clientDatas":[
        {
            "clientIdentity":{
                "clientId":1001,
                "destination":"example",
                "filter":""
            },
            "cursor":{
                "identity":{
                    "slaveId":-1,
                    "sourceAddress":{
                        "address":"hadoop106",
                        "port":3306
                    }
                },
                "postion":{
                    "gtid":"",
                    "included":false,
                    "journalName":"mysql-bin.000142",
                    "position":4056,
                    "serverId":6,
                    "timestamp":1660967416000
                }
            }
        }
    ],
    "destination":"example"
}
journalName：master数据库 binlog日志 文件名。
position：读取到 binlog 的指针位置。  

若 meta.dat 记录的位置 不在SQL查询结果里面，
则表示该 binlog 已被手动删除。
可将 journalName 值改为现有的 binlog 日志，然后把 position 置为 4
或直接暴力删除 /opt/module/canal/deployer/conf/example/meta.dat

canal 启动

adapter/bin/stop.sh 
deployer/bin/stop.sh

deployer/bin/startup.sh
adapter/bin/startup.sh

vim deployer/logs/canal/canal.log 
vim adapter/logs/adapter/adapter.log 

=================== canal shell 启动脚本 ========================

vim  ~/shells/canal.sh

#!/bin/bash
function warning(){
echo ----------- 参数无效 help ---------------
echo $0 depstart -- deployer 启动并查看日志
echo $0 depstop -- deployer 停止
echo $0 deplog -- tail -f 查看 deployer canal log
echo $0 adpstart -- adapter 启动并查看日志
echo $0 adpstop -- adapter 停止
echo $0 admstart -- admin 启动
echo $0 001start -- deployer_001 启动
echo $0 001stop -- deployer_001 停止
echo $0 001examlog --查看 deployer_001 的  example.log 日志
echo $0 tomysql --转到 mysql 所在目录,并查看 binlog
}

if [ $# -gt 4  ];
then
warning
exit
fi

case $1 in
"depstart")
/opt/module/canal/deployer/bin/startup.sh
tail -f /opt/module/canal/deployer/logs/canal/canal.log
;;
"depstop")
/opt/module/canal/deployer/bin/stop.sh
;;
"adpstart")
/opt/module/canal/adapter/bin/startup.sh
tail -f /opt/module/canal/adapter/logs/adapter/adapter.log
;;
"adpstop")
/opt/module/canal/adapter/bin/stop.sh
;;
"deplog")
tail -f /opt/module/canal/deployer/logs/canal/canal.log
;;
"adplog")
tail -f /opt/module/canal/adapter/logs/adapter/adapter.log
;;
"admstart")
/opt/module/canal/admin/bin/startup.sh
;;
"admstop")
/opt/module/canal/admin/bin/stop.sh
;;
"001start")
/opt/module/canal/deployer_001/bin/startup.sh
;;
"001stop")
/opt/module/canal/deployer_001/bin/stop.sh
;;
"001examlog")
tail -f  /opt/module/canal/deployer_001/logs/example/example.log
;;
"tomysql"){
cd /var/lib/mysql
ls
};;
*){
warning
exit
};;
esac