canal mysql select許可權粒度

七星6609發表於2021-03-06
MySql

今天產品問了一個問題，問懵了

產品：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;

 

相關文章