1、canal的工作原理
1、在我们对mysql进行增删改的操作（查询除外），Master主库会将我们的操作记录写到二进制日志（binary log）中
2、Slave从库向Master主库发送dump协议，将Master主库的binary log events拷贝到它的中继日志（relay log）
3、Slava读取中继日志数据，将改变的数据同步到自己的数据库中
4、canal的工作原理就很简单，它是将自己伪装成mysql的Slave节点，从Master主库同步改变的数据到自己的库中（如下图所示）
2、如何安装和使用canal
2.1 新建一个用户权限（需要在root用户下执行）
在需要监控的mysql中执行
CREATE USER canal IDENTIFIED BY 'canal';
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'canal'@'%';
-- GRANT ALL PRIVILEGES ON *.* TO 'canal'@'%' ;
FLUSH PRIVILEGES;
针对已有的账户可通过grants查询权限
show grants for 'canal'
2.2 修改/etc/my.cnf文件
server-id= 1
log-bin=mysql-bin
binlog_format=row
#监控的数据库
binlog-do-db=gmallXXXXX
2.3 重启mysql
service mysqld restart
2.4 canal下载
https://github.com/alibaba/canal/releases
canal.deployer-1.1.3.tar.gz
2.5 修改canal的配置
vim conf/canal.properties
canal.serverMode = kafka
canal.mq.servers = 10.3.7.220:9092,10.3.7.221:9092,10.3.7.222:9092
vim conf/example/instance.properties
#不能与mysql的server-id重复
canal.instance.mysql.slaveId=0
# username/password 需要监控的MySql的账户和密码
canal.instance.dbUsername=root
canal.instance.dbPassword=DTCserver2019!
canal.instance.connectionCharset = UTF-8
#指定需要监控的某个库的某个表
canal.instance.filter.regex=dtc_platform_cloud\\.alarm_strong
#kafka的topic和分区
canal.mq.topic=ods_alarm_strong
canal.mq.partition=0
2.6 启动canal
/bin/startup.sh
常见错位问题
问题一：
ERROR c.a.otter.canal.parse.inbound.mysql.MysqlEventParser - dump address /192.168.1.50:3306 has an error, retrying. caused by
com.alibaba.otter.canal.parse.exception.CanalParseException: can’t find start position for example
原因:meta.dat 中保存的位点信息和数据库的位点信息不一致；导致canal抓取不到数据库的动作；
解决方案：删除meta.dat删除，再重启canal，问题解决；
集群操作：进入canal对应的zookeeper集群下，删除节点/otter/canal/destinations/xxxxx/1001/cursor ；重启canal即可恢复；
问题二：
java.lang.OutOfMemoryError: Java heap space
canal消费端挂了太久，在zk对应conf下节点的
/otter/canal/destinations/test_db/1001/cursor 位点信息是很早以前，导致重启canal时，从很早以前的位点开始消费，导致canal服务器内存爆掉
监听数据库变更，只有TransactionBegin/TransactionEnd,没有拿到数据的EventType；
原因可能是canal.instance.filter.black.regex=.*\…*导致，改canal.instance.filter.black.regex=再重启试试；
问题三：
ERROR com.alibaba.otter.canal.common.alarm.LogAlarmHandler - destination:fdyb_db[com.alibaba.otter.canal.parse.exception.CanalParseException: com.alibaba.otter.canal.parse.exception.CanalParseException: parse row data failed.
Caused by: com.alibaba.otter.canal.parse.exception.CanalParseException: parse row data failed.
Caused by: com.alibaba.otter.canal.parse.exception.CanalParseException: com.google.common.collect.ComputationException: com.alibaba.otter.canal.parse.exception.CanalParseException: fetch failed by table meta:mysql.pds_4490277
Caused by: com.google.common.collect.ComputationException: com.alibaba.otter.canal.parse.exception.CanalParseException: fetch failed by table meta:mysql.pds_4490277
Caused by: com.alibaba.otter.canal.parse.exception.CanalParseException: fetch failed by table meta:mysql.pds_4490277
Caused by: java.io.IOException: ErrorPacket [errorNumber=1142, fieldCount=-1, message=SELECT command denied to user ‘cy_canal’@‘11.217.0.224’ for table ‘pds_4490277’, sqlState=42000, sqlStateMarker=#]
with command: desc mysql.pds_4490277
分析：mysql系统表权限较高，canal读该表的binlog失败，位点无法移动
解决：将配置项中黑名单加上mysql下的所有表：canal.instance.filter.black.regex = mysql\…* ，修改后canal集群不需要重启即可恢复；
其它注意点：检查下CanalConnector是否调用subscribe(filter)方法；有的话，filter需要和instance.properties的canal.instance.filter.regex一致，否则subscribe的filter会覆盖instance的配置，如果subscribe的filter是.\…，那么相当于你消费了所有的更新数据。
问题四：
现象：数据库修改后，canal应用感知不到binlog，数据无法正常消费处理；
定位：1.查看canal服务器，canal应用，zk服务器的日志，确认无异常；2.查看mysql,es服务器，无异常，3.查看canal服务器，canal应用配置项，发现canal服务器的canal.properties有问题；
原因：canal.properties中配置了canal.ip和canal.zkServers，如果是zk集群模式下的canal配置了canal.ip，则会优先按IP连接canal服务器，从而让zk功能失效，位点文件则会保存到本地；一旦本地位点文件出现问题，各方无错误日志，问题就很难排查；
解决：将canal.ip配置项置为空，关掉canal服务器，canal应用，删除zk上的节点，重启canal服务器，canal应用，问题解决；
问题五：
canal启动，创建数据库的脚本为
CREATE DATABASE /!32312 IF NOT EXISTS/db_name /!40100 DEFAULT CHARACTER SET utf8mb4 /
canal启动报错，无法处理.
2019-06-13 16:18:59.318 [destination = example , address = /Ip：port , EventParser] WARN c.a.otter.canal.parse.inbound.mysql.tsdb.MemoryTableMeta - parse faield : CREATE DATABASE /!32312 IF NOT EXISTS/db_name /*!40100 DEFAULT CHARACTER SET utf8mb4 */
com.alibaba.fastsql.sql.parser.ParserException: illegal name, pos 40, line 1, column 17, token HINT
at com.alibaba.fastsql.sql.parser.SQLExprParser.name(SQLExprParser.java:1812) ~[fastsql-2.0.0_preview_855.jar:2.0.0_preview_855]
at com.alibaba.fastsql.sql.dialect.mysql.parser.MySqlStatementParser.parseCreateDatabase(MySqlStatementParser.java:6543) ~[fastsql-2.0.0_preview_855.jar:2.0.0_preview_855]
at com.alibaba.fastsql.sql.dialect.mysql.parser.MySqlStatementParser.parseCreate(MySqlStatementParser.java:292) ~[fastsql-2.0.0_preview_855.jar:2.0.0_preview_855]
at com.alibaba.fastsql.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:228) ~[fastsql-2.0.0_preview_855.jar:2.0.0_preview_855]
at com.alibaba.fastsql.sql.SQLUtils.parseStatements(SQLUtils.java:536) ~[fastsql-2.0.0_preview_855.jar:2.0.0_preview_855]
at com.alibaba.fastsql.sql.repository.SchemaRepository.console(SchemaRepository.java:439) ~[fastsql-2.0.0_preview_855.jar:2.0.0_preview_855]
at com.alibaba.otter.canal.parse.inbound.mysql.tsdb.MemoryTableMeta.apply(MemoryTableMeta.java:81) ~[canal.parse-1.1.3.jar:na]
at com.alibaba.otter.canal.parse.inbound.mysql.tsdb.DatabaseTableMeta.applyHistoryOnMemory(DatabaseTableMeta.java:463) [canal.parse-1.1.3.jar:na]
at com.alibaba.otter.canal.parse.inbound.mysql.tsdb.DatabaseTableMeta.rollback(DatabaseTableMeta.java:174) [canal.parse-1.1.3.jar:na]
at com.alibaba.otter.canal.parse.inbound.mysql.AbstractMysqlEventParser.processTableMeta(AbstractMysqlEventParser.java:106) [canal.parse-1.1.3.jar:na]
at com.alibaba.otter.canal.parse.inbound.AbstractEventParser$3.run(AbstractEventParser.java:192) [canal.parse-1.1.3.jar:na]
at java.lang.Thread.run(Thread.java:748) [na:1.8.0_192]
查询tsdb的meta_history，把相关的创建语句进行修改，去掉 /* 这些字符, 重启OK。
问题六：
rocketmq自动创建topic的队列配置为4，
在canal的具体的一个instance里面配置的instance.properties,其中 canal.mq.partitionsNum=16，错误是无限重试发送。
修复方式是canal.mq.partitionsNum=4， （但是在测试环境没有复现）
注意事项：
数据库操作存在batch update，
canal发送到rocketmq的数据，data字段存在的数据有可能是多条，且数量不一（例如：批量更新了10，可能发了3条消息，一条消息包括1条数据，1条消息包括3条数据，1条消息包括6条数据）。
问题七：
2020-09-28 11:31:31.938 [destination = mall-canal_priceDB , address = priceDB.master.com/192.168.0.223:3320 , EventParser] ERROR com.alibaba.otter.canal.common.alarm.LogAlarmHandler - destination:mall-canal_priceDB[com.alibaba.otter.canal.parse.exception.CanalParseException: com.alibaba.otter.canal.parse.exception.CanalParseException: com.alibaba.otter.canal.parse.exception.CanalParseException: fetch failed by table meta:oriceDB.t_settlement_price
Caused by: com.alibaba.otter.canal.parse.exception.CanalParseException: com.alibaba.otter.canal.parse.exception.CanalParseException: fetch failed by table meta:priceDB.t_settlement_price
Caused by: com.alibaba.otter.canal.parse.exception.CanalParseException: fetch failed by table meta:priceDB.t_settlement_price
Caused by: java.io.IOException: ErrorPacket [errorNumber=1142, fieldCount=-1, message=SHOW command denied to user ‘xxxx’@‘xx.xx.xx.xx’ for table ‘t_settlement_price’, sqlState=42000, sqlStateMarker=#]
with command: show create table priceDB.t_settlement_price
at com.alibaba.otter.canal.parse.driver.mysql.MysqlQueryExecutor.query(MysqlQueryExecutor.java:61)
at com.alibaba.otter.canal.parse.inbound.mysql.MysqlConnection.query(MysqlConnection.java:106)
at com.alibaba.otter.canal.parse.inbound.mysql.dbsync.TableMetaCache.getTableMeta(TableMetaCache.java:177)
at com.alibaba.otter.canal.parse.inbound.mysql.dbsync.LogEventConvert.getTableMeta(LogEventConvert.java:950)
at com.alibaba.otter.canal.parse.inbound.mysql.dbsync.LogEventConvert.parseRowsEventForTableMeta(LogEventConvert.java:479)
at com.alibaba.otter.canal.parse.inbound.mysql.MysqlMultiStageCoprocessor
S
i
m
p
l
e
P
a
r
s
e
r
S
t
a
g
e
.
o
n
E
v
e
n
t
(
M
y
s
q
l
M
u
l
t
i
S
t
a
g
e
C
o
p
r
o
c
e
s
s
o
r
.
j
a
v
a
:
280
)
a
t
c
o
m
.
a
l
i
b
a
b
a
.
o
t
t
e
r
.
c
a
n
a
l
.
p
a
r
s
e
.
i
n
b
o
u
n
d
.
m
y
s
q
l
.
M
y
s
q
l
M
u
l
t
i
S
t
a
g
e
C
o
p
r
o
c
e
s
s
o
r
SimpleParserStage.onEvent(MysqlMultiStageCoprocessor.java:280) at com.alibaba.otter.canal.parse.inbound.mysql.MysqlMultiStageCoprocessor
SimpleParserStage.onEvent(MysqlMultiStageCoprocessor.java:280)atcom.alibaba.otter.canal.parse.inbound.mysql.MysqlMultiStageCoprocessorSimpleParserStage.onEvent(MysqlMultiStageCoprocessor.java:246)
at com.lmax.disruptor.BatchEventProcessor.processEvents(BatchEventProcessor.java:168)
at com.lmax.disruptor.BatchEventProcessor.run(BatchEventProcessor.java:125)
at java.util.concurrent.Executors
R
u
n
n
a
b
l
e
A
d
a
p
t
e
r
.
c
a
l
l
(
E
x
e
c
u
t
o
r
s
.
j
a
v
a
:
511
)
a
t
j
a
v
a
.
u
t
i
l
.
c
o
n
c
u
r
r
e
n
t
.
F
u
t
u
r
e
T
a
s
k
.
r
u
n
(
F
u
t
u
r
e
T
a
s
k
.
j
a
v
a
:
266
)
a
t
j
a
v
a
.
u
t
i
l
.
c
o
n
c
u
r
r
e
n
t
.
T
h
r
e
a
d
P
o
o
l
E
x
e
c
u
t
o
r
.
r
u
n
W
o
r
k
e
r
(
T
h
r
e
a
d
P
o
o
l
E
x
e
c
u
t
o
r
.
j
a
v
a
:
1149
)
a
t
j
a
v
a
.
u
t
i
l
.
c
o
n
c
u
r
r
e
n
t
.
T
h
r
e
a
d
P
o
o
l
E
x
e
c
u
t
o
r
RunnableAdapter.call(Executors.java:511) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor
RunnableAdapter.call(Executors.java:511)atjava.util.concurrent.FutureTask.run(FutureTask.java:266)atjava.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)atjava.util.concurrent.ThreadPoolExecutorWorker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
原因：数据帐号没有show command执行权限，查询不到table meta数据，重新授权即可
转自：
https://www.jianshu.com/p/3e5c289a222
https://blog.csdn.net/my201110lc/article/details/77885720
https://blog.csdn.net/shuipinglp/article/details/93966818
https://www.iteye.com/blog/shift-alt-ctrl-2399603
1316
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
