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

最新推荐文章于 2024-12-14 21:21:11 发布

*星星之火*

最新推荐文章于 2024-12-14 21:21:11 发布

阅读量3.8k

点赞数 1

分类专栏： flink 文章标签： flink mysql 数据库

本文链接：https://blog.youkuaiyun.com/spark_dev/article/details/126783658

版权

flink 专栏收录该内容

38 篇文章

订阅专栏

博客内容讲述了在使用Flink CDC从MySQL读取数据时遇到的权限问题，用户缺少Replicationclient和Replicationslave权限导致无法正常同步。通过授权命令`grant Replicationclient on *.* to ods_base@'%';`和`grant Replicationslave on *.* to ods_base@'%';`并执行`flush privileges;`来解决这个问题。错误信息显示了SQLSyntaxErrorException和ConnectException，提示需要SUPER或REPLICATIONCLIENT, REPLICATIONSLAVE权限。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

项目场景：

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;