canal常见问题排查(三)

数据库发生改变收不到订阅消息

查看canal是否有权限

select * from mysql.user where user='{canal-服务连接数据库的账户名字}' 

如果没有或者Select_priv不是Y

grant select, replication slave,replication client on *.* to '{用户名}'@'%';

刷新权限

flush pricileges;

查看当前canal读取的binlog日志文件和位置

cat canal安装目录/conf/example/meta.dat 

cat canal安装目录/conf/example/meta.dat 

注:这个是可以编辑的 查看是否有这个binlog日志文件 我之前就是测试环境测试过 记录的是测试环境读取到的然后在新的库就没有这个日志文件导致监听不到

position为当前读取的日志指针

 数据库执行

show binary logs可以查看当前库所有binlog日志文件

 show master status可以看到当前写入日志的文件以及写入指针位置

DDL报错

Caused by: com.alibaba.otter.canal.parse.exception.CanalParseException: column size is not match for table:canal.classes,3 vs 2

因为canal默认是通过h2数据库存储的表结构变动，比如我们当前binlog日志position 10-100  这个时候classes的字段是2个 id,name  但是101之后就加了个字段sex

我们因为某些原因导致我们canalService阻塞读取位置一直10 虽然表结构变动 及时刷入到了到h2数据库,但是重启启动canalService 刷入的是最新的表结构 id,name,sex 然后我们继续从10开始去读取表结构DDL对不上报错

解决方式就是将内存数据库改为物理持久化 canal支持数据库配置

编辑/conf/canal.properties 

1.注释掉默认的h2配置

#canal.instance.tsdb.spring.xml = classpath:spring/tsdb/h2-tsdb.xml
#tsdb走mysql
canal.instance.tsdb.spring.xml = classpath:spring/tsdb/mysql-tsdb.xml

#默认h2
#canal.instance.tsdb.url = jdbc:h2:${canal.instance.tsdb.dir}/h2;CACHE_SIZE=1000;MODE=MYSQL;

2.建表

{canal-server目录}/conf/spring/tsdb/sql/create_table.sql 有执行脚本

2.打开mysql数据库配置

url改为mysql的

canal.instance.tsdb.url=jdbc:mysql://127.0.0.1:3306/canal_tsdb
canal.instance.tsdb.dbUsername = root
canal.instance.tsdb.dbPassword = 868978

 后面我们再修改结构，canal-server收到binlog日志就会存到数据库里面

无权限异常

在example下的install.properties 加上黑名单

同理 其他不相关表sql异常可以过滤 多个，号隔开

# table black regex 黑名单
canal.instance.filter.black.regex=mysql..*,.*\.__drds__systable__leadership__

2021-04-13 16:29:44.504 [destination = kuaihe_db_test , address = rm-2zeqc826391dt0lk2.mysql.rds.aliyuncs.com/192.168.10.4:3306 , EventParser] ERROR com.alibaba.otter.canal.common.alarm.LogAlarmHandler - destination:kuaihe_db_test[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:`ucenter_test_bkuz_0000`.`__drds__systable__leadership__`
Caused by: com.alibaba.otter.canal.parse.exception.CanalParseException: com.alibaba.otter.canal.parse.exception.CanalParseException: fetch failed by table meta:`ucenter_test_bkuz_0000`.`__drds__systable__leadership__`
Caused by: com.alibaba.otter.canal.parse.exception.CanalParseException: fetch failed by table meta:`ucenter_test_bkuz_0000`.`__drds__systable__leadership__`
Caused by: java.io.IOException: ErrorPacket [errorNumber=1142, fieldCount=-1, message=SHOW command denied to user 'kuaihe'@'192.168.20.5' for table '__drds__systable__leadership__', sqlState=42000, sqlStateMarker=#]
 with command: show create table `ucenter_test_bkuz_0000`.`__drds__systable__leadership__`
        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$SimpleParserStage.onEvent(MysqlMultiStageCoprocessor.java:280)
        at com.alibaba.otter.canal.parse.inbound.mysql.MysqlMultiStageCoprocessor$SimpleParserStage.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$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$Worker.run(ThreadPoolExecutor.java:624)
        at java.lang.Thread.run(Thread.java:748)

021-04-13 16:24:35.234 [destination = kuaihe_db_test , address = rm-2zeqc826391dt0lk2.mysql.rds.aliyuncs.com/192.168.10.4:3306 , EventParser] ERROR com.alibaba.otter.canal.common.alarm.LogAlarmHandler - destination:kuaihe_db_test[com.alibaba.otter.canal.parse.exception.CanalParseException: java.io.IOException: ErrorPacket [errorNumber=1142, fieldCount=-1, message=SHOW command denied to user 'kuaihe'@'192.168.20.5' for table 'slow_log', sqlState=42000, sqlStateMarker=#]
 with command: show create table `mysql`.`event`;show create table `mysql`.`func`;show create table `mysql`.`general_log`;show create table `mysql`.`help_category`;show create table `mysql`.`help_keyword`;show create table `mysql`.`help_relation`;show create table `mysql`.`help_topic`;show create table `mysql`.`proc`;show create table `mysql`.`slow_log`;show create table `mysql`.`time_zone`;show create table `mysql`.`time_zone_leap_second`;show create table `mysql`.`time_zone_name`;show create table `mysql`.`time_zone_transition`;show create table `mysql`.`time_zone_transition_type`;
Caused by: java.io.IOException: ErrorPacket [errorNumber=1142, fieldCount=-1, message=SHOW command denied to user 'kuaihe'@'192.168.20.5' for table 'slow_log', sqlState=42000, sqlStateMarker=#]
 with command: show create table `mysql`.`event`;show create table `mysql`.`func`;show create table `mysql`.`general_log`;show create table `mysql`.`help_category`;show create table `mysql`.`help_keyword`;show create table `mysql`.`help_relation`;show create table `mysql`.`help_topic`;show create table `mysql`.`proc`;show create table `mysql`.`slow_log`;show create table `mysql`.`time_zone`;show create table `mysql`.`time_zone_leap_second`;show create table `mysql`.`time_zone_name`;show create table `mysql`.`time_zone_transition`;show create table `mysql`.`time_zone_transition_type`;
        at com.alibaba.otter.canal.parse.driver.mysql.MysqlQueryExecutor.queryMulti(MysqlQueryExecutor.java:109)
        at com.alibaba.otter.canal.parse.inbound.mysql.MysqlConnection.queryMulti(MysqlConnection.java:111)
        at com.alibaba.otter.canal.parse.inbound.mysql.tsdb.DatabaseTableMeta.dumpTableMeta(DatabaseTableMeta.java:232)
        at com.alibaba.otter.canal.parse.inbound.mysql.tsdb.DatabaseTableMeta.rollback(DatabaseTableMeta.java:182)
        at com.alibaba.otter.canal.parse.inbound.mysql.AbstractMysqlEventParser.processTableMeta(AbstractMysqlEventParser.java:137)
        at com.alibaba.otter.canal.parse.inbound.AbstractEventParser$3.run(AbstractEventParser.java:200)
        at java.lang.Thread.run(Thread.java:748)

使用admin，启动不了服务和instance都是停止

因为instance是基于配置，启动服务忘记加local ./startup.sh local