canal mysql select权限粒度
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了canal mysql select权限粒度,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含7881字,纯文字阅读大概需要12分钟。
内容图文
今天产品问了一个问题,问懵了
产品:canal在开通mysql权限时需要哪些权限
我:SELECT, REPLICATION SLAVE, REPLICATION CLIENT
产品:那SELECT权限要开通到表级还是库级(我们使用canal同步数据时最终选择到表)
我:en..... 晚会给你答案吧^-^.
于是乎今天就测试一下,不测不知道,一测怎加不加select权限都能正常同步binlog
场景一
本地有个mysql,直接在本地测试,删除库的select权限,也能正常同步binlog,于是乎就不淡定了,在网上查也没有个所以然
场景二
于是启动本地基于mha搭建的mysql高可用集群,再来使用canal同步主库binlog,将所要同步库的select权限删除,发现报错:
16:07:42.791 [WARN ] [destination = example7 , address = s129/192.168.3.129:3306 , EventParser] MysqlEventParser:463 | ERROR ## parse this event has an error , last position : [EntryPosition[included=false,journalName=mysql-bin.000009,position=4667,serverId=1,gtid=,timestamp=1615016220000]] com.alibaba.otter.canal.parse.exception.CanalParseException: parse row data failed. Caused by: com.alibaba.otter.canal.parse.exception.CanalParseException: com.google.common.util.concurrent.UncheckedExecutionException: com.alibaba.otter.canal.parse.exception.CanalParseException: fetch failed by table meta:`canal`.`test_canal_2_hive` Caused by: com.google.common.util.concurrent.UncheckedExecutionException: com.alibaba.otter.canal.parse.exception.CanalParseException: fetch failed by table meta:`canal`.`test_canal_2_hive` at com.google.common.cache.LocalCache$Segment.get(LocalCache.java:2203) ~[guava-18.0.jar:na] at com.google.common.cache.LocalCache.get(LocalCache.java:3937) ~[guava-18.0.jar:na] at com.google.common.cache.LocalCache.getOrLoad(LocalCache.java:3941) ~[guava-18.0.jar:na] at com.google.common.cache.LocalCache$LocalLoadingCache.get(LocalCache.java:4824) ~[guava-18.0.jar:na] at com.google.common.cache.LocalCache$LocalLoadingCache.getUnchecked(LocalCache.java:4830) ~[guava-18.0.jar:na] at com.alibaba.otter.canal.parse.inbound.mysql.dbsync.TableMetaCache.getTableMeta(TableMetaCache.java:195) ~[canal.parse-1.1.4.jar:na] at com.alibaba.otter.canal.parse.inbound.mysql.dbsync.LogEventConvert.getTableMeta(LogEventConvert.java:950) ~[canal.parse-1.1.4.jar:na] at com.alibaba.otter.canal.parse.inbound.mysql.dbsync.LogEventConvert.parseRowsEventForTableMeta(LogEventConvert.java:479) ~[canal.parse-1.1.4.jar:na] at com.alibaba.otter.canal.parse.inbound.mysql.dbsync.LogEventConvert.parseRowsEvent(LogEventConvert.java:500) ~[canal.parse-1.1.4.jar:na] at com.alibaba.otter.canal.parse.inbound.mysql.dbsync.LogEventConvert.parseRowsEvent(LogEventConvert.java:491) ~[canal.parse-1.1.4.jar:na] at com.alibaba.otter.canal.parse.inbound.mysql.dbsync.LogEventConvert.parse(LogEventConvert.java:125) ~[canal.parse-1.1.4.jar:na] at com.alibaba.otter.canal.parse.inbound.mysql.dbsync.LogEventConvert.parse(LogEventConvert.java:69) ~[canal.parse-1.1.4.jar:na] at com.alibaba.otter.canal.parse.inbound.AbstractEventParser.parseAndProfilingIfNecessary(AbstractEventParser.java:417) ~[canal.parse-1.1.4.jar:na] at com.alibaba.otter.canal.parse.inbound.AbstractEventParser$3$1.sink(AbstractEventParser.java:217) ~[canal.parse-1.1.4.jar:na] at com.alibaba.otter.canal.parse.inbound.mysql.MysqlConnection.dump(MysqlConnection.java:178) [canal.parse-1.1.4.jar:na] at com.alibaba.otter.canal.parse.inbound.AbstractEventParser$3.run(AbstractEventParser.java:279) [canal.parse-1.1.4.jar:na] at java.lang.Thread.run(Thread.java:748) [na:1.8.0_181] Caused by: com.alibaba.otter.canal.parse.exception.CanalParseException: fetch failed by table meta:`canal`.`test_canal_2_hive` Caused by: java.io.IOException: ErrorPacket [errorNumber=1142, fieldCount=-1, message=SELECT command denied to user 'canal_repl_user'@'192.168.3.1' for table 'test_canal_2_hive', sqlState=42000, sqlStateMarker=#] with command: desc `canal`.`test_canal_2_hive` at com.alibaba.otter.canal.parse.driver.mysql.MysqlQueryExecutor.query(MysqlQueryExecutor.java:61) ~[canal.parse.driver-1.1.4.jar:na] at com.alibaba.otter.canal.parse.inbound.mysql.MysqlConnection.query(MysqlConnection.java:106) [canal.parse-1.1.4.jar:na] at com.alibaba.otter.canal.parse.inbound.mysql.dbsync.TableMetaCache.getTableMetaByDB(TableMetaCache.java:92) ~[canal.parse-1.1.4.jar:na] at com.alibaba.otter.canal.parse.inbound.mysql.dbsync.TableMetaCache.access$000(TableMetaCache.java:32) ~[canal.parse-1.1.4.jar:na] at com.alibaba.otter.canal.parse.inbound.mysql.dbsync.TableMetaCache$1.load(TableMetaCache.java:63) ~[canal.parse-1.1.4.jar:na] at com.alibaba.otter.canal.parse.inbound.mysql.dbsync.TableMetaCache$1.load(TableMetaCache.java:53) ~[canal.parse-1.1.4.jar:na] at com.google.common.cache.LocalCache$LoadingValueReference.loadFuture(LocalCache.java:3527) ~[guava-18.0.jar:na] at com.google.common.cache.LocalCache$Segment.loadSync(LocalCache.java:2319) ~[guava-18.0.jar:na] at com.google.common.cache.LocalCache$Segment.lockedGetOrLoad(LocalCache.java:2282) ~[guava-18.0.jar:na] at com.google.common.cache.LocalCache$Segment.get(LocalCache.java:2197) ~[guava-18.0.jar:na] at com.google.common.cache.LocalCache.get(LocalCache.java:3937) ~[guava-18.0.jar:na] at com.google.common.cache.LocalCache.getOrLoad(LocalCache.java:3941) ~[guava-18.0.jar:na] at com.google.common.cache.LocalCache$LocalLoadingCache.get(LocalCache.java:4824) ~[guava-18.0.jar:na] at com.google.common.cache.LocalCache$LocalLoadingCache.getUnchecked(LocalCache.java:4830) ~[guava-18.0.jar:na] at com.alibaba.otter.canal.parse.inbound.mysql.dbsync.TableMetaCache.getTableMeta(TableMetaCache.java:195) ~[canal.parse-1.1.4.jar:na] at com.alibaba.otter.canal.parse.inbound.mysql.dbsync.LogEventConvert.getTableMeta(LogEventConvert.java:950) ~[canal.parse-1.1.4.jar:na] at com.alibaba.otter.canal.parse.inbound.mysql.dbsync.LogEventConvert.parseRowsEventForTableMeta(LogEventConvert.java:479) ~[canal.parse-1.1.4.jar:na] at com.alibaba.otter.canal.parse.inbound.mysql.dbsync.LogEventConvert.parseRowsEvent(LogEventConvert.java:500) ~[canal.parse-1.1.4.jar:na] at com.alibaba.otter.canal.parse.inbound.mysql.dbsync.LogEventConvert.parseRowsEvent(LogEventConvert.java:491) ~[canal.parse-1.1.4.jar:na] at com.alibaba.otter.canal.parse.inbound.mysql.dbsync.LogEventConvert.parse(LogEventConvert.java:125) ~[canal.parse-1.1.4.jar:na] at com.alibaba.otter.canal.parse.inbound.mysql.dbsync.LogEventConvert.parse(LogEventConvert.java:69) ~[canal.parse-1.1.4.jar:na] at com.alibaba.otter.canal.parse.inbound.AbstractEventParser.parseAndProfilingIfNecessary(AbstractEventParser.java:417) ~[canal.parse-1.1.4.jar:na] at com.alibaba.otter.canal.parse.inbound.AbstractEventParser$3$1.sink(AbstractEventParser.java:217) ~[canal.parse-1.1.4.jar:na] at com.alibaba.otter.canal.parse.inbound.mysql.MysqlConnection.dump(MysqlConnection.java:178) [canal.parse-1.1.4.jar:na] at com.alibaba.otter.canal.parse.inbound.AbstractEventParser$3.run(AbstractEventParser.java:279) [canal.parse-1.1.4.jar:na] at java.lang.Thread.run(Thread.java:748) [na:1.8.0_181] 16:07:42.792 [ERROR] [destination = example7 , address = s129/192.168.3.129:3306 , EventParser] MysqlEventParser:301 | dump address s129/192.168.3.129:3306 has an error, retrying. caused by 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.
报错标红的说明了,表需要select权限,于是查到对应的源码:
说明查询表元数据时show create table tableName和desc tableName都需要select权限,于是给对应表赋予select权限,同步正常
说明只要赋予表即select权限就可以了,那为什么本地不授权select也可以呢?
查了网上说和master_info_repository参数有关,两个环境确实不一样,本地master_info_repository=TABLE,mha中master_info_repository=FILE,但不知具体原因
使用命令:
#授权 GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT, SHOW VIEW ON *.* TO `canal`@`%` FLUSH PRIVILEGES; #查询权限 SHOW GRANTS FOR 'canal'@'%'; #删除权限 REVOKE SELECT ON *.* FROM 'canal'@'%'; FLUSH PRIVILEGES;
内容总结
以上是互联网集市为您收集整理的canal mysql select权限粒度全部内容,希望文章能够帮你解决canal mysql select权限粒度所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。