生产环境因用户没有SELECT权限而报错，但是本地测试没有SELECT权限的用户也能同步Binlog，无法说服DBA。 由于DBA对数据库用户权限把控比较严格，需要明确Canal用户是否必需SELECT权限。

MySQL版本：5.6.46 Canal版本：1.1.5-alpha1 使用组件: canal-deployer

本地测试环境下，canal用户仅开通了REPLICATION SLAVE, REPLICATION CLIENT两个权限，连接docker容器中的MySQL，可以正常消费Binlog数据写入Kafka。

查看用户权限命令： show grants for canal

执行结果： GRANT REPLICATION SLAVE, REPLICATION CLIENT ON . TO 'canal'@'%' IDENTIFIED BY PASSWORD 'xxxxxxxx' WITH GRANT OPTION

但是在生产环境下，Canal使用仅开通了REPLICATION SLAVE, REPLICATION CLIENT权限的用户连接数据库时报错（库名/表名/IP地址已打码）：

2020-12-15 10:28:39.370 [canal-instance-scan-0] INFO c.a.o.c.i.spring.support.PropertyPlaceholderConfigurer - Loading properties file from class path resource [canal.properties] 2020-12-15 10:28:39.378 [canal-instance-scan-0] INFO c.a.o.c.i.spring.support.PropertyPlaceholderConfigurer - Loading properties file from class path resource [prod_db/instance.properties] 2020-12-15 10:28:39.517 [canal-instance-scan-0] WARN o.s.beans.GenericTypeAwarePropertyDescriptor - Invalid JavaBean property 'connectionCharset' being accessed! Ambiguous write methods found next to actually used [public void com.alibaba.otter.canal.parse.inbound.mysql.AbstractMysqlEventParser.setConnectionCharset(java.lang.String)]: [public void com.alibaba.otter.canal.parse.inbound.mysql.AbstractMysqlEventParser.setConnectionCharset(java.nio.charset.Charset)] 2020-12-15 10:28:39.548 [canal-instance-scan-0] INFO c.a.o.c.i.spring.support.PropertyPlaceholderConfigurer - Loading properties file from class path resource [canal.properties] 2020-12-15 10:28:39.549 [canal-instance-scan-0] INFO c.a.o.c.i.spring.support.PropertyPlaceholderConfigurer - Loading properties file from class path resource [prod_db/instance.properties] 2020-12-15 10:28:39.861 [canal-instance-scan-0] INFO c.a.otter.canal.instance.spring.CanalInstanceWithSpring - start CannalInstance for 1-prod_db 2020-12-15 10:28:39.868 [canal-instance-scan-0] WARN c.a.o.canal.parse.inbound.mysql.dbsync.LogEventConvert - --> init table filter : ^prod_db.prod_table$ 2020-12-15 10:28:39.868 [canal-instance-scan-0] WARN c.a.o.canal.parse.inbound.mysql.dbsync.LogEventConvert - --> init table black filter : 2020-12-14 10:55:05.074 [destination = prod_db , address = /xx.xx.xx.xx:3306 , EventParser] WARN c.a.o.c.p.inbound.mysql.rds.RdsBinlogEventParserProxy - ---> begin to find start position, it will be long time for reset or first position 2020-12-14 10:55:05.074 [destination = prod_db , address = /xx.xx.xx.xx:3306 , EventParser] WARN c.a.o.c.p.inbound.mysql.rds.RdsBinlogEventParserProxy - prepare to find start position just show master status 2020-12-14 10:55:25.491 [destination = prod_db , address = /xx.xx.xx.xx:3306 , EventParser] WARN c.a.o.c.p.inbound.mysql.rds.RdsBinlogEventParserProxy - ---> find start position successfully, EntryPosition[included=false,journalName=mysql-bin.002389,position=308178700,serverId=1014867,gtid=,timestamp=1607914505000] cost : 20411ms , the next step is binlog dump 2020-12-14 10:55:25.710 [destination = prod_db , address = /xx.xx.xx.xx:3306 , EventParser] ERROR c.a.o.c.p.inbound.mysql.rds.RdsBinlogEventParserProxy - dump address /xx.xx.xx.xx:3306 has an error, retrying. caused by 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:prod_db.prod_table Caused by: com.alibaba.otter.canal.parse.exception.CanalParseException: com.alibaba.otter.canal.parse.exception.CanalParseException: fetch failed by table meta:prod_db.prod_table Caused by: com.alibaba.otter.canal.parse.exception.CanalParseException: fetch failed by table meta:prod_db.prod_table Caused by: java.io.IOException: ErrorPacket [errorNumber=1142, fieldCount=-1, message=SHOW command denied to user 'canal'@'xx.xx.xx.xxx' for table 'prod_table', sqlState=42000, sqlStateMarker=#] with command: show create table prod_db.prod_table at com.alibaba.otter.canal.parse.driver.mysql.MysqlQueryExecutor.query(MysqlQueryExecutor.java:61) ~[canal.parse.driver-1.1.5-SNAPSHOT.jar:na] at com.alibaba.otter.canal.parse.inbound.mysql.MysqlConnection.query(MysqlConnection.java:106) ~[canal.parse-1.1.5-SNAPSHOT.jar:na] at com.alibaba.otter.canal.parse.inbound.mysql.dbsync.TableMetaCache.getTableMeta(TableMetaCache.java:177) ~[canal.parse-1.1.5-SNAPSHOT.jar:na] at com.alibaba.otter.canal.parse.inbound.mysql.dbsync.LogEventConvert.getTableMeta(LogEventConvert.java:950) ~[canal.parse-1.1.5-SNAPSHOT.jar:na] at com.alibaba.otter.canal.parse.inbound.mysql.dbsync.LogEventConvert.parseRowsEventForTableMeta(LogEventConvert.java:479) ~[canal.parse-1.1.5-SNAPSHOT.jar:na] at com.alibaba.otter.canal.parse.inbound.mysql.MysqlMultiStageCoprocessor$SimpleParserStage.onEvent(MysqlMultiStageCoprocessor.java:274) ~[canal.parse-1.1.5-SNAPSHOT.jar:na] at com.alibaba.otter.canal.parse.inbound.mysql.MysqlMultiStageCoprocessor$SimpleParserStage.onEvent(MysqlMultiStageCoprocessor.java:246) ~[canal.parse-1.1.5-SNAPSHOT.jar:na] at com.lmax.disruptor.BatchEventProcessor.processEvents(BatchEventProcessor.java:168) ~[disruptor-3.4.2.jar:na] at com.lmax.disruptor.BatchEventProcessor.run(BatchEventProcessor.java:125) ~[disruptor-3.4.2.jar:na] at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) ~[na:1.8.0_212] at java.util.concurrent.FutureTask.run(FutureTask.java:266) ~[na:1.8.0_212] at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) ~[na:1.8.0_212] at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) ~[na:1.8.0_212] at java.lang.Thread.run(Thread.java:748) [na:1.8.0_212]

instance配置如下：

#################################################

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=xx.xx.xx.xx: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=

table meta tsdb info

canal.instance.tsdb.enable=true #canal.instance.tsdb.url=jdbc:mysql://127.0.0.1:3306/canal_tsdb #canal.instance.tsdb.dbUsername=canal #canal.instance.tsdb.dbPassword=canal

#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=canal canal.instance.dbPassword=xxxx canal.instance.connectionCharset = UTF-8

enable druid Decrypt database password

canal.instance.enableDruid=true canal.instance.pwdPublicKey=xxxx

table regex

canal.instance.filter.regex=prod_db\.prod_table

table black regex

canal.instance.filter.black.regex=

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=binlog-temp

dynamic topic route by schema or table regex

#canal.mq.dynamicTopic=mytest1.user,mytest2\..,.\..* canal.mq.partition=0

hash partition config

#canal.mq.partitionsNum=3 #canal.mq.partitionHash=test.table:id^name,.\.. #################################################

原提问者GitHub用户disaeye