question Re: Need help to connect MySQL with CaptureChangeMySQL in Support Questions

Need help to connect MySQL with CaptureChangeMySQL

raymond_cui2015 — Thu, 19 Dec 2019 04:28:34 GMT

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:

The settings of the NiFi processors are:

CDC MapCache:

CaptureChangeMySQL:

Ubuntu 16.04:

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

Re: Need help to connect MySQL with CaptureChangeMySQL

DennisJaheruddi — Tue, 24 Dec 2019 12:00:00 GMT

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?

Re: Need help to connect MySQL with CaptureChangeMySQL

stevenmatison — Tue, 24 Dec 2019 12:31:07 GMT

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.

Re: Need help to connect MySQL with CaptureChangeMySQL

raymond_cui2015 — Wed, 25 Dec 2019 09:23:33 GMT

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