flink cdc 没有Replication client ，Replication slave权限，报错，处理

项目场景：

fiink cdc 的表用户，需要有Replication client ，Replication slave权限。
授权命令如下：
grant Replication client on . to ods_base@‘%’;
grant replication slave on . to ods_base@‘%’;
flush privileges;

---

问题描述

flink cdc读取mysql数据，没有往外写数据，页面的状态显示正常，但日志报错。

Caused by: org.apache.flink.util.FlinkRuntimeException: Cannot read the binlog filename and position via 'SHOW MASTER STATUS'. Make sure your server is correctly configured
	at com.ververica.cdc.connectors.mysql.debezium.DebeziumUtils.currentBinlogOffset(DebeziumUtils.java:122) ~[flink-sql-connector-mysql-cdc-2.2-SNAPSHOT.jar:2.2-SNAPSHOT]
	at com.ververica.cdc.connectors.mysql.debezium.task.MySqlSnapshotSplitReadTask.doExecute(MySqlSnapshotSplitReadTask.java:139) ~[flink-sql-connector-mysql-cdc-2.2-SNAPSHOT.jar:2.2-SNAPSHOT]
	at com.ververica.cdc.connectors.mysql.debezium.task.MySqlSnapshotSplitReadTask.execute(MySqlSnapshotSplitReadTask.java:115) ~[flink-sql-connector-mysql-cdc-2.2-SNAPSHOT.jar:2.2-SNAPSHOT]
	... 6 more
Caused by: java.sql.SQLSyntaxErrorException: Access denied; you need (at least one of) the SUPER, REPLICATION CLIENT privilege(s) for this operation
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120) ~[flink-sql-connector-mysql-cdc-2.2-SNAPSHOT.jar:2.2-SNAPSHOT]
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) ~[flink-sql-connector-mysql-cdc-2.2-SNAPSHOT.jar:2.2-SNAPSHOT]
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[flink-sql-connector-mysql-cdc-2.2-SNAPSHOT.jar:2.2-SNAPSHOT]
	at com.mysql.cj.jdbc.StatementImpl.executeQuery(StatementImpl.java:1200) ~[flink-sql-connector-mysql-cdc-2.2-SNAPSHOT.jar:2.2-SNAPSHOT]
	at io.debezium.jdbc.JdbcConnection.queryAndMap(JdbcConnection.java:643) ~[flink-sql-connector-mysql-cdc-2.2-SNAPSHOT.jar:2.2-SNAPSHOT]
	at io.debezium.jdbc.JdbcConnection.queryAndMap(JdbcConnection.java:512) ~[flink-sql-connector-mysql-cdc-2.2-SNAPSHOT.jar:2.2-SNAPSHOT]
	at com.ververica.cdc.connectors.mysql.debezium.DebeziumUtils.currentBinlogOffset(DebeziumUtils.java:104) ~[flink-sql-connector-mysql-cdc-2.2-SNAPSHOT.jar:2.2-SNAPSHOT]
	at com.ververica.cdc.connectors.mysql.debezium.task.MySqlSnapshotSplitReadTask.doExecute(MySqlSnapshotSplitReadTask.java:139) ~[flink-sql-connector-mysql-cdc-2.2-SNAPSHOT.jar:2.2-SNAPSHOT]
	at com.ververica.cdc.connectors.mysql.debezium.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

第二个错误

Caused by: com.ververica.cdc.connectors.shaded.org.apache.kafka.connect.errors.ConnectException: An exception occurred in the change event producer. This connector will be stopped.
	at io.debezium.pipeline.ErrorHandler.setProducerThrowable(ErrorHandler.java:42) ~[flink-sql-connector-mysql-cdc-2.2-SNAPSHOT.jar:2.2-SNAPSHOT]
	at com.ververica.cdc.connectors.mysql.debezium.task.context.MySqlErrorHandler.setProducerThrowable(MySqlErrorHandler.java:72) ~[flink-sql-connector-mysql-cdc-2.2-SNAPSHOT.jar:2.2-SNAPSHOT]
	at io.debezium.connector.mysql.MySqlStreamingChangeEventSource$ReaderThreadLifecycleListener.onCommunicationFailure(MySqlStreamingChangeEventSource.java:1185) ~[flink-sql-connector-mysql-cdc-2.2-SNAPSHOT.jar:2.2-SNAPSHOT]
	at com.github.shyiko.mysql.binlog.BinaryLogClient.listenForEventPackets(BinaryLogClient.java:973) ~[flink-sql-connector-mysql-cdc-2.2-SNAPSHOT.jar:2.2-SNAPSHOT]
	at com.github.shyiko.mysql.binlog.BinaryLogClient.connect(BinaryLogClient.java:606) ~[flink-sql-connector-mysql-cdc-2.2-SNAPSHOT.jar:2.2-SNAPSHOT]
	at com.github.shyiko.mysql.binlog.BinaryLogClient$7.run(BinaryLogClient.java:850) ~[flink-sql-connector-mysql-cdc-2.2-SNAPSHOT.jar:2.2-SNAPSHOT]
	... 1 more
Caused by: io.debezium.DebeziumException: Access denied; you need (at least one of) the REPLICATION SLAVE privilege(s) for this operation Error code: 1227; SQLSTATE: 42000.
	at io.debezium.connector.mysql.MySqlStreamingChangeEventSource.wrap(MySqlStreamingChangeEventSource.java:1146) ~[flink-sql-connector-mysql-cdc-2.2-SNAPSHOT.jar:2.2-SNAPSHOT]
	at io.debezium.connector.mysql.MySqlStreamingChangeEventSource$ReaderThreadLifecycleListener.onCommunicationFailure(MySqlStreamingChangeEventSource.java:1185) ~[flink-sql-connector-mysql-cdc-2.2-SNAPSHOT.jar:2.2-SNAPSHOT]
	at com.github.shyiko.mysql.binlog.BinaryLogClient.listenForEventPackets(BinaryLogClient.java:973) ~[flink-sql-connector-mysql-cdc-2.2-SNAPSHOT.jar:2.2-SNAPSHOT]
	at com.github.shyiko.mysql.binlog.BinaryLogClient.connect(BinaryLogClient.java:606) ~[flink-sql-connector-mysql-cdc-2.2-SNAPSHOT.jar:2.2-SNAPSHOT]
	at com.github.shyiko.mysql.binlog.BinaryLogClient$7.run(BinaryLogClient.java:850) ~[flink-sql-connector-mysql-cdc-2.2-SNAPSHOT.jar:2.2-SNAPSHOT]
	... 1 more
Caused by: com.github.shyiko.mysql.binlog.network.ServerException: Access denied; you need (at least one of) the REPLICATION SLAVE privilege(s) for this operation
	at com.github.shyiko.mysql.binlog.BinaryLogClient.listenForEventPackets(BinaryLogClient.java:937) ~[flink-sql-connector-mysql-cdc-2.2-SNAPSHOT.jar:2.2-SNAPSHOT]
	at com.github.shyiko.mysql.binlog.BinaryLogClient.connect(BinaryLogClient.java:606) ~[flink-sql-connector-mysql-cdc-2.2-SNAPSHOT.jar:2.2-SNAPSHOT]
	at com.github.shyiko.mysql.binlog.BinaryLogClient$7.run(BinaryLogClient.java:850) ~[flink-sql-connector-mysql-cdc-2.2-SNAPSHOT.jar:2.2-SNAPSHOT]
	... 1 more
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

---

原因分析：

flink 客户端，这个用户没有权限同步mysql的的数据。赋予相应的权限就行。

---

解决方案：

授权

replication client
拥有此权限可以查询master server、slave server状态。
mysql> show master status;
ERROR 1227 (42000): Access denied; you need the SUPER,REPLICATION CLIENT privilege for this operation
mysql> grant Replication client on . to p1@localhost;
或：mysql> grant super on . to p1@localhost;
mysql> show master status;
+——————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.000006 | 2111 | | |
+——————+———-+————–+——————+
mysql> show slave status;

22. replication slave
    拥有此权限可以查看从服务器，从主服务器读取二进制日志。
    mysql> show slave hosts;
    ERROR 1227 (42000): Access denied; you need the REPLICATION SLAVE privilege for this operation
    mysql> show binlog events;
    ERROR 1227 (42000): Access denied; you need the REPLICATION SLAVE privilege for this operation
    mysql> grant replication slave on . to p1@localhost;
    mysql> show slave hosts;
    Empty set (0.00 sec)
    mysql>show binlog events;
    +—————+——-+—————-+———–+————-+————–+
    | Log_name | Pos | Event_type | Server_id| End_log_pos|Info |
    +—————+——-+————–+———–+————-+—————+
    | mysql-bin.000005 | 4 | Format_desc | 1 | 98 | Server ver: 5.0.77-log, Binlog ver: 4 |
    |mysql-bin.000005|98|Query|1|197|use mysql; create table a1(i int)engine=myisam|

赋予如下权限就ok

 grant Replication client on *.* to ods_base@'%';
  grant replication slave on *.* to ods_base@'%';
flush privileges;
1
2
3