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
################################################# ## 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,.*\\..* #################################################
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.sqlmysql -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
# 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 的表结构信息达到一致。
不再报错，就可以正常工作了。
# 查看当前库所有binlog日志文件
show binary logs;
# meta.dat 主要用来记录的binlog读取位置
show master logs;
# 当前写入日志的文件以及写入指针位置
show master status;
{
"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
