kafka debezium mysql 日志订阅

mysql

[mysqld]
server-id         = 1
log_bin           = mysql-bin
default-time-zone = '+8:00'
binlog_format = ROW
binlog_row_image  = FULL 
gtid_mode =ON
enforce_gtid_consistency = ON
log-slave-updates=1 # 5.6 版本需要开启该参数

debezium

下载 debezium 的 mysql插件
https://debezium.io/documentation/reference/1.2/connectors/mysql.html#mysql-connector-configuration-properties_debezium
下的Download the Debezium
MySQL connector plug-in.
解压到\kafka_2.13-2.8.0\plugins

kafka

启动zk kafka

nohup ./zookeeper-server-start.sh ../config/zookeeper.properties >> zook.log &
nohup ./kafka-server-start.sh ../config/server.properties  >> kafka.log &

启动kafka-connnector

nohup ./connect-distributed.sh ../config/connect-distributed.properties  >> distributed.log &

附：删除连接

curl -X DELETE
http://192.168.80.133:8083/connectors/sqlserver-syncdb-connector

查看插件是否生效

http://192.168.80.133:8083/connector-plugins

主题列表

./kafka-topics.sh --list --zookeeper 192.168.80.133:2181

生成者

./kafka-console-producer.sh --bootstrap-server localhost:9092 --topic
oracle-sync-topic

消费者

./kafka-console-consumer.sh --bootstrap-server localhost:9092
–from-beginning --topic mysql80.cdc_demo.customers

连接方式一：简约库表正则

curl -i -X POST -H "Accept:application/json" -H  "Content-Type:application/json" http://localhost:8083/connectors/ -d '{
    "name": "mysql-while-connector",
    "config": {
      "connector.class": "io.debezium.connector.mysql.MySqlConnector",
      "database.hostname": "192.168.80.135",
      "database.port": "3306",
      "database.user": "root",
      "database.password": "123456",
      "database.server.id": "1",
      "database.server.name": "mysql80",
      "database.whitelist": "cdc_demo.*",
      "database.history.kafka.bootstrap.servers": "localhost:9092",
      "database.history.kafka.topic": "mysql.history.sync",
      "include.schema.changes": "true"
    }
 }'

自动生成的主题

[root@hd03 bin]# ./kafka-topics.sh --list --zookeeper  192.168.80.133:2181
__consumer_offsets
connect-configs
connect-offsets
connect-status
mysql.history.sync
mysql80
mysql80.cdc_demo.customers
mysql80.cdc_demo.t_user

对应的增删改日志

{"before":{"id":1,"first_name":"23","last_name":"啊多发点是","email":"1"},"after":{"id":1,"first_name":"23","last_name":"11","email":"1"},"source":{"version":"1.2.5.Final","connector":"mysql","name":"mysql80","ts_ms":1658205507000,"snapshot":"false","db":"cdc_demo","table":"customers","server_id":1,"gtid":"cfda4e73-040a-11ed-a4c4-000c29d36977:6","file":"DESKTOP-5G0JOCT-bin.000010","pos":1689,"row":0,"thread":16,"query":null},"op":"u","ts_ms":1658205507576,"transaction":null}
{"before":null,"after":{"id":2,"first_name":"123","last_name":"123123","email":"123"},"source":{"version":"1.2.5.Final","connector":"mysql","name":"mysql80","ts_ms":1658205525000,"snapshot":"false","db":"cdc_demo","table":"customers","server_id":1,"gtid":"cfda4e73-040a-11ed-a4c4-000c29d36977:7","file":"DESKTOP-5G0JOCT-bin.000010","pos":2030,"row":0,"thread":16,"query":null},"op":"c","ts_ms":1658205525239,"transaction":null}
{"before":{"id":2,"first_name":"123","last_name":"123123","email":"123"},"after":null,"source":{"version":"1.2.5.Final","connector":"mysql","name":"mysql80","ts_ms":1658205529000,"snapshot":"false","db":"cdc_demo","table":"customers","server_id":1,"gtid":"cfda4e73-040a-11ed-a4c4-000c29d36977:8","file":"DESKTOP-5G0JOCT-bin.000010","pos":2348,"row":0,"thread":16,"query":null},"op":"d","ts_ms":1658205529284,"transaction":null}
null

连接方式二：指定库的list

curl -i -X POST -H "Accept:application/json" -H "Content-Type:application/json" http://localhost:8083/connectors/ -d '{
"name": "dbhistory.mysql",
"config": {
"connector.class": "io.debezium.connector.mysql.MySqlConnector",
"database.hostname": "192.168.80.135",
"database.port": "3306",
"database.user": "root",
"database.password": "123456",
"database.server.id": "3306",
"database.server.name": "mysql81",
"database.include.list": "cdc_demo",
"database.history.kafka.bootstrap.servers": "localhost:9092",
"database.history.kafka.topic": "dbhistory.mysql",
"include.schema.changes": "true"
}
}'

命令成功后一大堆的主题生成
[root@hd03 bin]# ./kafka-topics.sh --list --zookeeper  192.168.80.133:2181
__consumer_offsets
connect-configs
connect-offsets
connect-status
dbhistory.mysql
mysql81
mysql81.cdc_demo.customers
mysql81.cdc_demo.t_user
mysql81.sakila.actor
mysql81.sakila.address
mysql81.sakila.category
mysql81.sakila.city
mysql81.sakila.country
mysql81.sakila.customer
mysql81.sakila.film
mysql81.sakila.film_actor
mysql81.sakila.film_category
mysql81.sakila.film_text
mysql81.sakila.inventory
mysql81.sakila.language
mysql81.sakila.payment
mysql81.sakila.rental
mysql81.sakila.staff
mysql81.sakila.store
mysql81.world.city
mysql81.world.country
mysql81.world.countrylanguage

对应主题下的增删改

{"before":{"id":2,"name":"11"},"after":{"id":2,"name":"爱的色放"},"source":{"version":"1.2.5.Final","connector":"mysql","name":"mysql81","ts_ms":1658205137000,"snapshot":"false","db":"cdc_demo","table":"t_user","server_id":1,"gtid":"cfda4e73-040a-11ed-a4c4-000c29d36977:3","file":"DESKTOP-5G0JOCT-bin.000010","pos":764,"row":0,"thread":16,"query":null},"op":"u","ts_ms":1658205137957,"transaction":null}
{"before":null,"after":{"id":223,"name":"123"},"source":{"version":"1.2.5.Final","connector":"mysql","name":"mysql81","ts_ms":1658205159000,"snapshot":"false","db":"cdc_demo","table":"t_user","server_id":1,"gtid":"cfda4e73-040a-11ed-a4c4-000c29d36977:4","file":"DESKTOP-5G0JOCT-bin.000010","pos":1079,"row":0,"thread":16,"query":null},"op":"c","ts_ms":1658205159470,"transaction":null}
{"before":{"id":223,"name":"123"},"after":null,"source":{"version":"1.2.5.Final","connector":"mysql","name":"mysql81","ts_ms":1658205163000,"snapshot":"false","db":"cdc_demo","table":"t_user","server_id":1,"gtid":"cfda4e73-040a-11ed-a4c4-000c29d36977:5","file":"DESKTOP-5G0JOCT-bin.000010","pos":1376,"row":0,"thread":16,"query":null},"op":"d","ts_ms":1658205163561,"transaction":null}
null

附：

1. default-time-zone 需要指定 否则，报错

Unable to connect: The server time zone value '�й���׼ʱ��' is unrecognized or represents more than on
解决
[mysqld] 
default-time-zone = '+8:00'