Support Questions
Find answers, ask questions, and share your expertise

Need help to connect MySQL with CaptureChangeMySQL

Solved Go to solution

Need help to connect MySQL with CaptureChangeMySQL

New Contributor

I am trying to repeat the work of Change Data Capture (CDC) with Apache NiFi.

My testing environment is : Ubuntu 16.04;  MySQL and NiFi are installed in the OS system without using Docker, can access to MySQL via the terminal from another Windows PC in the same local network.

However, when I just use the processor CaptureChangeMySQL and connect to the MySQL, it shows me the error: 

mysql.png

 

The settings of the NiFi processors are:

CDC MapCache:

mysql.png

CaptureChangeMySQL:

20484-3-capturechangemysql-properties.png

 

Ubuntu 16.04: 

20484-3-capturechangemysql-properties.png

Could anyone help to address the issues? Thank you very much.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

Re: Need help to connect MySQL with CaptureChangeMySQL

Couple things I notice.  First per documentation, your hostname should be an FQDN and mapped to the correct IP.  Not mapping a proper hostname to the main server IP will cause confusion between short hostname, long hostname, localhost, localhost IP, etc.   Second, after fixing that, you need to create permissions from your nifi FQDN hostname to your mysql server with a user that has the proper privelages.  A simple test to check network connectivity from NiFi to mysql host would be using telnet to port 3306.  If the telnet connects, then you know for sure you need to create the user@'host' permission grants as follows:

 

CREATE USER 'user'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'user'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

 

Depending on your setup the % (wildcard) may or may not work. If required replace the wildcard with the NiFI FQDN hostname.

 

Last suggestion:   tail -f /var/log/nifi/nifi-app.log while testing.  You will see much more info about errors than you see in the NiFi UI.  You can also set each processor error level to get more information in the UI.

 

 

If this answer helps solve your issue, please accept it as the solution.

View solution in original post

3 REPLIES 3

Re: Need help to connect MySQL with CaptureChangeMySQL

Super Collaborator

Not sure if this helps, but it looks like you are hitting a general connectivity error. Possibly not even related to MySQL. Are you able to do anything via nifi on that remote node?


- Dennis Jaheruddin

If this answer helped, please mark it as 'solved' and/or if it is valuable for future readers please apply 'kudos'. Also check out my techincal portfolio at https://portfolio.jaheruddin.nl

Re: Need help to connect MySQL with CaptureChangeMySQL

New Contributor

Hi Dennis,

 

Thank you for the reply. I could use the ListFile and FetchFile processors to get the files of the target host (such as: MySQL log files).  

Re: Need help to connect MySQL with CaptureChangeMySQL

Couple things I notice.  First per documentation, your hostname should be an FQDN and mapped to the correct IP.  Not mapping a proper hostname to the main server IP will cause confusion between short hostname, long hostname, localhost, localhost IP, etc.   Second, after fixing that, you need to create permissions from your nifi FQDN hostname to your mysql server with a user that has the proper privelages.  A simple test to check network connectivity from NiFi to mysql host would be using telnet to port 3306.  If the telnet connects, then you know for sure you need to create the user@'host' permission grants as follows:

 

CREATE USER 'user'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'user'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

 

Depending on your setup the % (wildcard) may or may not work. If required replace the wildcard with the NiFI FQDN hostname.

 

Last suggestion:   tail -f /var/log/nifi/nifi-app.log while testing.  You will see much more info about errors than you see in the NiFi UI.  You can also set each processor error level to get more information in the UI.

 

 

If this answer helps solve your issue, please accept it as the solution.

View solution in original post