Skip to main content
raymond_cui2015
raymond_cui2015
Silver ⭐️⭐️
December 19, 2019
Solved

Need help to connect MySQL with CaptureChangeMySQL

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.

 

 

stevenmatison
stevenmatisonAnswer
Platinum ⭐️⭐️
December 24, 2019

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 in thread

3 replies

DennisJaheruddi
Platinum ⭐️
DennisJaheruddi
Platinum ⭐️
December 24, 2019

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?

Translate
- Dennis Jaheruddin If this answer helped, please mark it as 'solved' and/or if it is valuable for future readers please apply 'kudos'.
raymond_cui2015
raymond_cui2015Author
Silver ⭐️⭐️
December 25, 2019

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

Translate
stevenmatison
stevenmatisonAnswer
Platinum ⭐️⭐️
December 24, 2019

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.

Translate
Terms & ConditionsAccessibility statement