Which user permissions needed for NiFi to access mysql cdc

[#1]

Hello All,

I am using the mysql CDC processor in Nifi, to get incremental changes into DWH, in this processor fo the username field, which permissions should be granted to the user so that it can access cdc files ?

should be the one with highest privileges/ root user ?

because it is not mentioned anywhere in the help section (https://nifi.apache.org/docs/nifi-docs/components/org.apache.nifi/nifi-cdc-mysql-nar/1.5.0/org.apach... )

And when i provide a user credentials who has GRANT ALL PRIVILEGES ON *.*  then it is able to access CDC bin logs

But getting permission denied when trying to access with a user who has only read permissions on *.*

Since the processor has nothing to do with write, i thought only read permissions were enough, but looks like its not the case.. 

Because I need to create new users in the source to access these CDC files.. and I dont want to give them Grant ALL, as it seems riskier.. Please help with your suggestions and thoughts..

Thanks

[#2]

@SandyClouds ,

The CaptureChangeMySQL processor acts as a MySQL replication slave. As such, I believe it requires the REPLICATION_SLAVE privilege in MySQL.

Cheers,

André

[#3]

Hi @araujo , Thank you very much for the answer and info..

I am running a single master server and i don't have/need any replication server for my usecase.

But I just created a user based on your suggestion like below:

mysql> create user 'replication'@'localhost' IDENTIFIED BY 'PASSWORD';
Query OK, 0 rows affected (0.03 sec)

mysql> create user 'replication'@'%' IDENTIFIED BY 'PASSWORD';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE on *.* TO 'replication'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE on *.* TO 'replication'@'localhost';
Query OK, 0 rows affected (0.01 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

Now I tried to connect to the db using a mysql client- it says permission denied for user replication@localhost

And also tried to use the same user in Nifi processor (capture change Mysql)

and it also errored out as expected - "could not connect binlog client to any of specified hosts"

Can you please help me where I am doing it wrong..

[#4 · Accepted solution]

@SandyClouds ,

You don't need a MySQL replication server. What I meant is that the CaptureChangeMySQL act as a replication client, and needs the same privileges as one.

I tested the exact same commands that you specified above and I was able to connect to the database from the command line:

[root@cdp ~]# mysql -u replication -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.26 Source distribution

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

I was also able to configure the CaptureChangeMySQL processor to capture changes without any other privileges being granted:

Which version of MySQL are you using?

There seems to be some issue with your user creation. The connection from the command line should work. What was the command you used to connect to the db using the replication user?

Could you please provide the configuration of your processor?

Cheers,

André

[#5]

Hi @araujo , Much thankful to your answer..

Yes it worked for me after fixing user.

I had to enable native password for the user..

so, I ran this command "ALTER USER 'replication'@'localhost' IDENTIFIED WITH mysql_native_password BY 'somepassword' " to fix user

(One other observation while connecting through SQL client is, if I just leave the database name empty then it connects with replication user, if any db name is provided, it will throws error.) 

[#6]

@SandyClouds, Has the reply helped resolve your issue? If so, please mark the appropriate reply as the solution, as it will make it easier for others to find the answer in the future.