FlinkCDC 连接 MySQL 问题记录_遗梦孤魂

大大的周 02-07 7726

前提

1、User does not have the 'LOCK TABLES' privilege required to obtain a consistent snapshot by preventing concurrent writes to tables.

2、Access denied; you need (at least one of) the SUPER, REPLICATION CLIENT privilege(s) for this operation Error code: 1227; SQLSTATE: 42000.

3、Unknown command Error code: 1047; SQLSTATE: HY000.

前提 ?Mysql5.7flink1.12.0flink-cdc1.2.0

使用mysql的root账号没有任何问题。使用普通账号就出现了下面一系列问题。

终极解决是

GRANT SELECT, RELOAD, SHOW DATABASES, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'user' IDENTIFIED BY 'password'; FLUSH PRIVILEGES;

1、User does not have the 'LOCK TABLES' privilege required to obtain a consistent snapshot by preventing concurrent writes to tables.

mysql的账号没有锁表的权限；

GRANT LOCK TABLES ON `database`.* TO 'user'@'host'; FLUSH PRIVILEGES; Caused by: org.apache.kafka.connect.errors.ConnectException: User does not have the 'LOCK TABLES' privilege required to obtain a consistent snapshot by preventing concurrent writes to tables. at io.debezium.connector.mysql.AbstractReader.wrap(AbstractReader.java:241) at io.debezium.connector.mysql.AbstractReader.failed(AbstractReader.java:218) at io.debezium.connector.mysql.SnapshotReader.execute(SnapshotReader.java:857) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at java.lang.Thread.run(Thread.java:748) Caused by: org.apache.kafka.connect.errors.ConnectException: User does not have the 'LOCK TABLES' privilege required to obtain a consistent snapshot by preventing concurrent writes to tables. at io.debezium.connector.mysql.SnapshotReader.execute(SnapshotReader.java:471) ... 3 more 2、Access denied; you need (at least one of) the SUPER, REPLICATION CLIENT privilege(s) for this operation Error code: 1227; SQLSTATE: 42000.

mysql的账号没有super 或者 replication client 权限：那就开一个权限呗！

GRANT REPLICATION CLIENT ON *.* TO 'user'@'host'; FLUSH PRIVILEGES; Caused by: org.apache.kafka.connect.errors.ConnectException: Access denied; you need (at least one of) the SUPER, REPLICATION CLIENT privilege(s) for this operation Error code: 1227; SQLSTATE: 42000. at io.debezium.connector.mysql.AbstractReader.wrap(AbstractReader.java:241) at io.debezium.connector.mysql.AbstractReader.failed(AbstractReader.java:218) at io.debezium.connector.mysql.SnapshotReader.execute(SnapshotReader.java:857) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at java.lang.Thread.run(Thread.java:748) 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) at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) at com.mysql.cj.jdbc.StatementImpl.executeQuery(StatementImpl.java:1200) at io.debezium.jdbc.JdbcConnection.query(JdbcConnection.java:554) at io.debezium.jdbc.JdbcConnection.query(JdbcConnection.java:497) at io.debezium.connector.mysql.SnapshotReader.readBinlogPosition(SnapshotReader.java:920) at io.debezium.connector.mysql.SnapshotReader.execute(SnapshotReader.java:492) ... 3 more 3、Unknown command Error code: 1047; SQLSTATE: HY000.

这个真不知道是啥问题：也没查出来是啥

最后看到一个官网的账号权限是：

GRANT SELECT, RELOAD, SHOW DATABASES, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'user' IDENTIFIED BY 'password'; FLUSH PRIVILEGES;

?猜测是mysql的代理导致的，所以不要用代理的节点IP，改用mysql的集群的主节点就可以了。

十二月 20, 2021 1:43:02 下午 com.github.shyiko.mysql.binlog.BinaryLogClient connect 信息: Connected to 10.96.3.239:3306 at 8ca1174d-b39b-11eb-8386-fa163e8e348c:1-41121,909193bf-b39b-11eb-ab34-fa163e89374a:1-769843152 (sid:5597, cid:25180920) Exception in thread "main" org.apache.flink.runtime.client.JobExecutionException: Caused by: org.apache.kafka.connect.errors.ConnectException: Unknown command Error code: 1047; SQLSTATE: HY000. at io.debezium.connector.mysql.AbstractReader.wrap(AbstractReader.java:241) at io.debezium.connector.mysql.AbstractReader.failed(AbstractReader.java:207) at io.debezium.connector.mysql.BinlogReader$ReaderThreadLifecycleListener.onCommunicationFailure(BinlogReader.java:1142) at com.github.shyiko.mysql.binlog.BinaryLogClient.listenForEventPackets(BinaryLogClient.java:962) at com.github.shyiko.mysql.binlog.BinaryLogClient.connect(BinaryLogClient.java:595) at com.github.shyiko.mysql.binlog.BinaryLogClient$7.run(BinaryLogClient.java:839) at java.lang.Thread.run(Thread.java:748) Caused by: com.github.shyiko.mysql.binlog.network.ServerException: Unknown command at com.github.shyiko.mysql.binlog.BinaryLogClient.listenForEventPackets(BinaryLogClient.java:926) ... 3 more