项目场景：
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.
第二个错误
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
原因分析：
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;
- 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;
|