Debezium + MySQL + CloudSQL

[chr...@wepay.com]

Hi, 

I am looking at using Debezium for MySQL -> Kafka replication. I had a few questions:

1. It looks to me like Debezium does NOT require running on the actual MySQL DB instance. If this is the case, what are the host/part that we should configure to connect to the MySQL instance? Note: we are using CloudSQL for some DBs (https://cloud.google.com/sql/docs/replication), so we don't have direct access to the boxes. Cloud SQL does support external slave replicas, though.

2. How do we determine the bin log configuration (database.binlog) to set?

Thanks!

Chris

[aksh...@wepay.com]

Hi,

Also it would be great, if you can provide us with some examples for config file which needs to used for Debezium mysql connector.

Regards,

Akshath

[Randall Hauch]

On March 1, 2016 at 3:48:56 PM, chr...@wepay.com (chr...@wepay.com) wrote:

Hi, 

I am looking at using Debezium for MySQL -> Kafka replication. I had a few questions:

1. It looks to me like Debezium does NOT require running on the actual MySQL DB instance. If this is the case, what are the host/part that we should configure to connect to the MySQL instance? Note: we are using CloudSQL for some DBs (https://cloud.google.com/sql/docs/replication), so we don't have direct access to the boxes. Cloud SQL does support external slave replicas, though.

The MySQL connector does not have to run on the same host. Rather, it uses the MySQL replication feature, so you have to create a replication DB user with proper privilege; see https://dev.mysql.com/doc/refman/5.7/en/replication-howto-repuser.html and https://github.com/debezium/debezium/blob/master/debezium-connector-mysql/src/test/docker/init/connector-test-db.sql for an example. You then configure the connector with the same hostname and port that a normal client uses (e.g., a JDBC client) and the replication user’s username and password; see https://github.com/debezium/debezium/blob/master/debezium-connector-mysql/src/test/java/io/debezium/connector/mysql/MySqlConnectorIT.java#L73. The “SERVER_ID” property is important, since the MySQL connector joins the MySQL database cluster using this ID, which must be unique among all replication clients.

2. How do we determine the bin log configuration (database.binlog) to set?

The MySQL configuration has to turn on the binlog configuration, and you define the root name of all binlog files there; see https://github.com/debezium/debezium/blob/master/debezium-connector-mysql/src/test/docker/server/mysql.cnf#L37. Also, be sure that you use the “row” format for the binlog. Then decide the name of the existing binlog file that you want to consume, and set the name of that using the connector's “database.binlog” property.

Yes, this is all fairly complicated to get right, which is why I’m currently working on a working example with Docker images all set up and ready to go, and documentation to make the recipe even easier.

Best regards,

Randall

[Randall Hauch]

On March 1, 2016 at 4:50:14 PM, aksh...@wepay.com (aksh...@wepay.com) wrote:

Hi,

Also it would be great, if you can provide us with some examples for config file which needs to used for Debezium mysql connector.

I completely agree. The code has been done for a bit now, but I’m working on an example (using Docker so that it will be easy for everyone to run with almost no setup) and some good documentation (at least the start of it).

As it stands, there are examples in the integration tests, which use a real MySQL server running in a local Docker container. See the last few lines of https://github.com/debezium/debezium/blob/master/debezium-connector-mysql/src/test/docker/server/mysql.cnf for the MySQL configuration, https://github.com/debezium/debezium/blob/master/debezium-connector-mysql/src/test/docker/init/connector-test-db.sql for a sample SQL script that set up the replication user w/ privileges and some initial content; and https://github.com/debezium/debezium/blob/master/debezium-connector-mysql/src/test/java/io/debezium/connector/mysql/MySqlConnectorIT.java#L73 for the connector configuration.

I understand that knowing where these examples live in the codebase is not straightforward, and that’s why I’m working on a better example and documentation.

Best regards,

Randall

[chr...@wepay.com]

Thanks! We'll have a look.

[aksh...@wepay.com]

Thanks Randall  for quick response.

Will wait for examples on how to configure and run debezium. 

I Just tried around with jdbc debezium connector, Got into some errors.

java.lang.ExceptionInInitializerError

at io.debezium.connector.mysql.MySqlConnector.version(MySqlConnector.java:35)

at org.apache.kafka.connect.runtime.Worker.addConnector(Worker.java:183)

at org.apache.kafka.connect.runtime.standalone.StandaloneHerder.startConnector(StandaloneHerder.java:197)

at org.apache.kafka.connect.runtime.standalone.StandaloneHerder.putConnectorConfig(StandaloneHerder.java:145)

at org.apache.kafka.connect.cli.ConnectStandalone.main(ConnectStandalone.java:85)

Caused by: java.lang.NullPointerException

at java.util.Properties$LineReader.readLine(Properties.java:434)

at java.util.Properties.load0(Properties.java:353)

at java.util.Properties.load(Properties.java:341)

at io.debezium.util.IoUtil.loadProperties(IoUtil.java:444)

at io.debezium.util.IoUtil.loadProperties(IoUtil.java:473)

at io.debezium.connector.mysql.Module.<clinit>(Module.java:19)

... 5 more

[Randall Hauch]

On March 1, 2016 at 7:03:28 PM, aksh...@wepay.com (aksh...@wepay.com) wrote:

Thanks Randall  for quick response.

Will wait for examples on how to configure and run debezium. 

I Just tried around with jdbc debezium connector, Got into some errors.

Can you explain this? The “debezium-connector-jdbc” and “debezium-connector-postgres” are placeholder projects at the moment, so don’t try to use them. The only connector that is complete is “debezium-connector-mysql”.

java.lang.ExceptionInInitializerError

at io.debezium.connector.mysql.MySqlConnector.version(MySqlConnector.java:35)

at org.apache.kafka.connect.runtime.Worker.addConnector(Worker.java:183)

at org.apache.kafka.connect.runtime.standalone.StandaloneHerder.startConnector(StandaloneHerder.java:197)

at org.apache.kafka.connect.runtime.standalone.StandaloneHerder.putConnectorConfig(StandaloneHerder.java:145)

at org.apache.kafka.connect.cli.ConnectStandalone.main(ConnectStandalone.java:85)

Caused by: java.lang.NullPointerException

at java.util.Properties$LineReader.readLine(Properties.java:434)

at java.util.Properties.load0(Properties.java:353)

at java.util.Properties.load(Properties.java:341)

at io.debezium.util.IoUtil.loadProperties(IoUtil.java:444)

at io.debezium.util.IoUtil.loadProperties(IoUtil.java:473)

at io.debezium.connector.mysql.Module.<clinit>(Module.java:19)

... 5 more

It looks like you’re trying to use the MySQL connector, and based upon your stack trace that you’re trying use the code but don’t have the "io/debezium/connector/mysql/build.properties” file (in “debezium-connector-mysql/src/main/resources”) on the classpath. 

If you want to use a JAR, then you need to use “mvn clean install” (https://github.com/debezium/debezium/blob/master/CONTRIBUTE.md) to build all of the code; this will properly create the necessary JAR for the MySQL connector and include in this JAR the class files and the resource file(s). Don’t use the JAR created by your IDE.

The build will also create a “plugin” archive for the MySQL connector (“io.debezium:debezium-connector-mysql-0.1-SNAPSHOT-plugin.zip” or "debezium-connector-mysql-0.1-SNAPSHOT-plugin.tar.gz”) and install that into Maven as well. These archive files have all of the libraries needed by the MySQL connector *except* those provided by Kafka, so the intention is that you can use these archives to install the MySQL connector-specific JARs into a Kafka Connect installation.

Best regards,

Randall

[chr...@wepay.com]

Do you mean build.version? That's the only file that I see in that directory. It seems to me that the hard-coded string is pointing to a path that doesn't exist.

[chr...@wepay.com]

Another random question: shouldn't the build stuff be in META-INF, not as a random file?

[chr...@wepay.com]

Note: your instructions are slightly off. Should be:

mvn clean install -P assembly -DskipITs

Without the assembly profile (which is defaulted to off), we were not getting any zip/tar.gz files. With assembly on, we get them.

Note: even with this, I still see now build.properties file in the resource path of the JAR:

META-INF/
META-INF/MANIFEST.MF
io/
io/debezium/
io/debezium/connector/
io/debezium/connector/mysql/
io/debezium/connector/mysql/build.version
io/debezium/connector/mysql/Module.class
io/debezium/connector/mysql/MySqlConnector.class
io/debezium/connector/mysql/MySqlConnectorConfig.class
io/debezium/connector/mysql/MySqlConnectorTask$1.class
io/debezium/connector/mysql/MySqlConnectorTask$EventHandler.class
io/debezium/connector/mysql/MySqlConnectorTask.class
io/debezium/connector/mysql/MySqlDdlParser.class
io/debezium/connector/mysql/SourceInfo.class
io/debezium/connector/mysql/StopEventData.class
io/debezium/connector/mysql/StopEventDataDeserializer.class
io/debezium/connector/mysql/TableConverters$1.class
io/debezium/connector/mysql/TableConverters$Converter.class
io/debezium/connector/mysql/TableConverters.class
io/debezium/connector/mysql/TopicSelector$1.class
io/debezium/connector/mysql/TopicSelector.class
META-INF/maven/
META-INF/maven/io.debezium/
META-INF/maven/io.debezium/debezium-connector-mysql/
META-INF/maven/io.debezium/debezium-connector-mysql/pom.xml
META-INF/maven/io.debezium/debezium-connector-mysql/pom.properties
META-INF/INDEX.LIST

[chr...@wepay.com]

Sent a PR:

https://github.com/debezium/debezium/pull/23

This appears to get rid of the missing build.properties NPE that we were seeing.