Skip to main content
SandyClouds
SandyClouds
Gold ⭐️
July 29, 2022
Solved

Which user permissions needed for NiFi to access mysql cdc

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

araujo
araujoAnswer
Platinum ⭐️⭐️⭐️
August 3, 2022

@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:

araujo_0-1659502129903.png

 

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é

 

-- Was your question answered? Please take some time to click on "Accept as Solution" below this post. If you find a reply useful, say thanks by clicking on the thumbs up button.
View in thread

5 replies

araujo
Platinum ⭐️⭐️⭐️
araujo
Platinum ⭐️⭐️⭐️
August 1, 2022

@SandyClouds ,

 

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

 

Cheers,

André

 

Translate
-- Was your question answered? Please take some time to click on "Accept as Solution" below this post. If you find a reply useful, say thanks by clicking on the thumbs up button.
SandyClouds
SandyCloudsAuthor
Gold ⭐️
August 2, 2022

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..

Translate
araujo
Platinum ⭐️⭐️⭐️
araujoAnswer
Platinum ⭐️⭐️⭐️
August 3, 2022

@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:

araujo_0-1659502129903.png

 

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é

 

Translate
-- Was your question answered? Please take some time to click on "Accept as Solution" below this post. If you find a reply useful, say thanks by clicking on the thumbs up button.
    SandyClouds
    SandyCloudsAuthor
    Gold ⭐️
    August 8, 2022

    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.) 

     

     

    Translate
    VidyaSargur
    Community Manager
    VidyaSargur
    Community Manager
    August 8, 2022

    @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.

    Translate
    Regards, Vidya Sargur, Community Manager Was your question answered? Make sure to mark the answer as the accepted solution. If you find a reply useful, say thanks by clicking on the thumbs up button.
    Terms & ConditionsAccessibility statement