canal.instance.filter.query.dml含义介绍

canal.instance.filter.query.dml过滤的不是传统意义上的增删改等操作，过滤的是binlog里被注释掉的原update sql语句(这个和mysql里的binlog_rows_query_log_events有关)

在row模式下..开启该参数,将把sql语句打印到binlog日志里面.默认是0(off);

虽然将语句放入了binlog,但不会执行这个sql,就相当于注释一样.但对于dba来说,在查看binlog的时候,很有用处.

加之前binlog是这样记录的：

### UPDATE `lots`.`test`

### WHERE

###   @1=4 /* INT meta=0 nullable=1 is_null=0 */

###   @2='zeng4' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */

### SET

###   @1=3 /* INT meta=0 nullable=1 is_null=0 */

###   @2='zeng3' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */

加之后，多了个原本的update sql：

#160429 23:36:28 server id 1073376  end_log_pos 260 CRC32 0xd9288e2a    Rows_query

# update test set id=4,name='zeng4' where id=5

# at 260

#160429 23:36:28 server id 1073376  end_log_pos 310 CRC32 0xa38557d6    Table_map: `lots`.`test` mapped to number 70

# at 310

#160429 23:36:28 server id 1073376  end_log_pos 368 CRC32 0x31ed5f26    Update_rows: table id 70 flags: STMT_END_F

### UPDATE `lots`.`test`

### WHERE

###   @1=5 /* INT meta=0 nullable=1 is_null=0 */

###   @2='zeng5' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */

### SET

###   @1=4 /* INT meta=0 nullable=1 is_null=0 */

###   @2='zeng4' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */

如果安装的是往kafka同步的canal server，记得一定要将该参数配置为true，否则源端所有库的所有sql操作（即使canal.instance.filter.regex里没有配置同步这些库，这些表）都会被同步到目标端canal.mq.topic指定的topic里，很可能导致kafka磁盘空间爆满，从而引发故障。

canal.instance.filter.query.dml=false时，canal client日志如下：

2022-08-03 18:41:43.424 [pool-7-thread-1] INFO c.a.o.canal.client.adapter.logger.LoggerAdapterExample - DML: {"data":null,"database":"","destination":"21_12canal","es":1659523303000,"groupId":"g1","isDdl":false,"old":null,"pkNames":[],"sql":"insert into c2 (id,name) values(123,'334')","table":"","ts":1659523303423,"type":"QUERY"}
2022-08-03 18:41:43.426 [pool-7-thread-1] INFO c.a.o.canal.client.adapter.logger.LoggerAdapterExample - DML: {"data":[{"id":123,"name":"334","sex":null,"sex1":null,"sex132":null,"sex133":null,"sex21":null}],"database":"cluster","destination":"21_12canal","es":1659523303000,"groupId":"g1","isDdl":false,"old":null,"pkNames":["id"],"sql":"","table":"c2","ts":1659523303423,"type":"INSERT"}
2022-08-03 18:41:43.542 [pool-6-thread-1] DEBUG c.a.o.canal.client.adapter.rdb.service.RdbSyncService - DML: {"data":{"id":123,"name":"334","sex":null,"sex1":null,"sex132":null,"sex133":null,"sex21":null},"database":"cluster","destination":"21_12canal","old":null,"table":"c2","type":"INSERT"}
2022-08-03 18:41:43.745 [pool-3-thread-1] INFO c.a.o.canal.client.adapter.logger.LoggerAdapterExample - DML: {"data":null,"database":"","destination":"12_21canal","es":1659523294000,"groupId":"g1","isDdl":false,"old":null,"pkNames":[],"sql":"INSERT INTO `cluster`.`c2` (`id`,`name`,`sex`,`sex1`,`sex132`,`sex133`,`sex21`) VALUES (123,'334',null,null,null,null,null)","table":"","ts":1659523303744,"type":"QUERY"}

将canal.instance.filter.query.dml设为true后，canal client日志里就没有了同步表的type:QUERY的查询，也没有了canal instance里没配置的表的日志。