Debezium同步MySQL变更到kafka
前期准备: 完成 Kafka Connect Source和MySQL集成
官网: https://debezium.io/documentation/reference/1.5/install.html
mysql官网教程:https://debezium.io/documentation/reference/1.5/connectors/mysql.html
Kafka Connect教程api
首先我们要知道rest服务提供了一些API去操作connector,如下表:
使用浏览器访问http://{ip}:8083/connectors,可以查看所有的connector,此时返回的是一个空数组,说明没有任何的connector:
安装配置
如果您已经安装了Zookeeper、Kafka和Kafka Connect,那么使用 Debezium 的连接器之一很容易。只需下载一个或多个连接器插件档案(见下文),将它们的文件解压缩到您的 Kafka Connect 环境中,然后将解压缩的插件的父目录添加到 Kafka Connect 的插件路径中。如果还不是这种情况,请使用plugin.path配置属性在您的工作器配置中指定插件路径(例如connect-distributed.properties)。例如,假设您已下载 Debezium MySQL 连接器存档并将其内容提取到/kafka/connect/debezium-connector-mysql. 然后你会在工作配置中指定以下内容:
plugin.path=/kafka/connect
重新启动 Kafka Connect 进程以获取新的 JAR。
下载地址:https://debezium.io/documentation/reference/1.5/install.html
使用 Debezium 连接器
要使用连接器为特定源服务器/集群生成更改事件,只需为MySQL 连接器、 Postgres 连接器、 MongoDB 连接器、 SQL Server 连接器、 Oracle 连接器、 Db2 连接器、 Cassandra 连接器或Vitess 连接器创建一个配置文件 ,然后使用Kafka Connect REST API将该连接器配置添加到您的 Kafka Connect 集群。当连接器启动时,它将连接到源并为每个插入、更新和删除的行或文档生成事件。
MySQL 连接器配置示例
使用postamn发送 post请求 http://10.90.81.121:8085/connectors 即可创建 MySQL 连接器
{
"name":"connector_sit",
"config":{
"connector.class":"io.debezium.connector.mysql.MySqlConnector",
"tasks.max":"1",
"database.hostname": "10.90.81.121",
"database.port": "3306",
"database.user": "wys",
"database.password": "wys",
"database.server.id": "184051",
"database.server.name":"uat",
"database.history.kafka.bootstrap.servers": "10.90.81.121:9094",
"database.history.kafka.topic":"debezium_test",
"include.schema.changes":"true"
}
}
// 最终生成的某张表的kafka topic为:(database.server.name).数据库名.表名
{
"name":"debezium-test-5017", // 连接器名称
"config":{ // 连接器配置
"connector.class":"io.debezium.connector.mysql.MySqlConnector",
"database.hostname":"4.4.4.4", //MySQL数据库主机
"database.port":"5017", // MySQL数据库端口
"database.user":"test", // MySQL数据库使用用户
"database.password":"123", // 用户密码
"database.server.id":"316545017",
"database.server.name":"debezium", // 唯一的服务器逻辑名称。该名称将用作所有Kafka topic的前缀。
"database.history.kafka.bootstrap.servers":"1.1.1.1:7091,2.2.2.2:7091,3.3.3.3:7091",
"database.history.kafka.topic":"debezium_test"
}
}
-- 其余常用配置
"database.include.list": "zztest" // 仅监控哪个库的更改,默认监控所有数据库,以下均可配置为正则表达式
"database.exclude.list": "zztest" // 不监控哪个库的更改,不能和database.include.list同时使用
"table.include.list": "t1" // 监控哪些表的更改
"table.exclude.list": "t1" // 不监控哪些表的更改,不能和table.include.list同时使用
"column.include.list": "col1" // 监控哪些字段的更改
"column.exclude.list": "col1" // 不监控哪些字段的更改,不能和column.include.list同时使用
创建成功后 Debezium 会为我们的 数据库 每个表生成一个 对应的 topic ,如果这时候新增一个表,Debezium也会给这个表新增一个对应的topic
创建表 对应的 topic 数据格式如下
{
"schema":{
"type":"struct",
"fields":[
{
"type":"struct",
"fields":[
{
"type":"string",
"optional":false,
"field":"version"
},
{
"type":"string",
"optional":false,
"field":"connector"
},
{
"type":"string",
"optional":false,
"field":"name"
},
{
"type":"int64",
"optional":false,
"field":"ts_ms"
},
{
"type":"string",
"optional":true,
"name":"io.debezium.data.Enum",
"version":1,
"parameters":{
"allowed":"true,last,false"
},
"default":"false",
"field":"snapshot"
},
{
"type":"string",
"optional":false,
"field":"db"
},
{
"type":"string",
"optional":true,
"field":"table"
},
{
"type":"int64",
"optional":false,
"field":"server_id"
},
{
"type":"string",
"optional":true,
"field":"gtid"
},
{
"type":"string",
"optional":false,
"field":"file"
},
{
"type":"int64",
"optional":false,
"field":"pos"
},
{
"type":"int32",
"optional":false,
"field":"row"
},
{
"type":"int64",
"optional":true,
"field":"thread"
},
{
"type":"string",
"optional":true,
"field":"query"
}
],
"optional":false,
"name":"io.debezium.connector.mysql.Source",
"field":"source"
},
{
"type":"string",
"optional":false,
"field":"databaseName"
},
{
"type":"string",
"optional":false,
"field":"ddl"
}
],
"optional":false,
"name":"io.debezium.connector.mysql.SchemaChangeValue"
},
"payload":{
"source":{
"version":"1.3.1.Final",
"connector":"mysql",
"name":"uat",
"ts_ms":1626772791000,
"snapshot":"false",
"db":"canal",
"table":"topic_test1",
"server_id":1,
"gtid":"2de6be90-dfb7-11eb-b359-0050568c1ec5:207",
"file":"mysql-bin.000006",
"pos":54376,
"row":0,
"thread":null,
"query":null
},
"databaseName":"canal",
"ddl":"CREATE TABLE canal.topic_test1 (\r\n\tid INT auto_increment NOT NULL,\r\n\tname varchar(100) NULL,\r\n\tCONSTRAINT topic_test1_PK PRIMARY KEY (id)\r\n)\nENGINE=InnoDB\nDEFAULT CHARSET=utf8mb4\nCOLLATE=utf8mb4_general_ci;"
}
}
删除表 对应的 topic 数据格式如下
{
"schema":{
"type":"struct",
"fields":[
{
"type":"struct",
"fields":[
{
"type":"string",
"optional":false,
"field":"version"
},
{
"type":"string",
"optional":false,
"field":"connector"
},
{
"type":"string",
"optional":false,
"field":"name"
},
{
"type":"int64",
"optional":false,
"field":"ts_ms"
},
{
"type":"string",
"optional":true,
"name":"io.debezium.data.Enum",
"version":1,
"parameters":{
"allowed":"true,last,false"
},
"default":"false",
"field":"snapshot"
},
{
"type":"string",
"optional":false,
"field":"db"
},
{
"type":"string",
"optional":true,
"field":"table"
},
{
"type":"int64",
"optional":false,
"field":"server_id"
},
{
"type":"string",
"optional":true,
"field":"gtid"
},
{
"type":"string",
"optional":false,
"field":"file"
},
{
"type":"int64",
"optional":false,
"field":"pos"
},
{
"type":"int32",
"optional":false,
"field":"row"
},
{
"type":"int64",
"optional":true,
"field":"thread"
},
{
"type":"string",
"optional":true,
"field":"query"
}
],
"optional":false,
"name":"io.debezium.connector.mysql.Source",
"field":"source"
},
{
"type":"string",
"optional":false,
"field":"databaseName"
},
{
"type":"string",
"optional":false,
"field":"ddl"
}
],
"optional":false,
"name":"io.debezium.connector.mysql.SchemaChangeValue"
},
"payload":{
"source":{
"version":"1.3.1.Final",
"connector":"mysql",
"name":"uat",
"ts_ms":1626772566000,
"snapshot":"false",
"db":"canal",
"table":"topic_test",
"server_id":1,
"gtid":"2de6be90-dfb7-11eb-b359-0050568c1ec5:205",
"file":"mysql-bin.000006",
"pos":53894,
"row":0,
"thread":null,
"query":null
},
"databaseName":"canal",
"ddl":"DROP TABLE topic_test;"
}
}
新增一条记录 对应的 topic 数据格式如下
{
"schema":{
"type":"struct",
"fields":[
{
"type":"struct",
"fields":[
{
"type":"int32",
"optional":false,
"field":"id"
},
{
"type":"string",
"optional":true,
"field":"Cname"
}
],
"optional":true,
"name":"uat.canal.topic_test.Value",
"field":"before"
},
{
"type":"struct",
"fields":[
{
"type":"int32",
"optional":false,
"field":"id"
},
{
"type":"string",
"optional":true,
"field":"Cname"
}
],
"optional":true,
"name":"uat.canal.topic_test.Value",
"field":"after"
},
{
"type":"struct",
"fields":[
{
"type":"string",
"optional":false,
"field":"version"
},
{
"type":"string",
"optional":false,
"field":"connector"
},
{
"type":"string",
"optional":false,
"field":"name"
},
{
"type":"int64",
"optional":false,
"field":"ts_ms"
},
{
"type":"string",
"optional":true,
"name":"io.debezium.data.Enum",
"version":1,
"parameters":{
"allowed":"true,last,false"
},
"default":"false",
"field":"snapshot"
},
{
"type":"string",
"optional":false,
"field":"db"
},
{
"type":"string",
"optional":true,
"field":"table"
},
{
"type":"int64",
"optional":false,
"field":"server_id"
},
{
"type":"string",
"optional":true,
"field":"gtid"
},
{
"type":"string",
"optional":false,
"field":"file"
},
{
"type":"int64",
"optional":false,
"field":"pos"
},
{
"type":"int32",
"optional":false,
"field":"row"
},
{
"type":"int64",
"optional":true,
"field":"thread"
},
{
"type":"string",
"optional":true,
"field":"query"
}
],
"optional":false,
"name":"io.debezium.connector.mysql.Source",
"field":"source"
},
{
"type":"string",
"optional":false,
"field":"op"
},
{
"type":"int64",
"optional":true,
"field":"ts_ms"
},
{
"type":"struct",
"fields":[
{
"type":"string",
"optional":false,
"field":"id"
},
{
"type":"int64",
"optional":false,
"field":"total_order"
},
{
"type":"int64",
"optional":false,
"field":"data_collection_order"
}
],
"optional":true,
"field":"transaction"
}
],
"optional":false,
"name":"uat.canal.topic_test.Envelope"
},
"payload":{
"before":null,
"after":{
"id":1,
"Cname":"name"
},
"source":{
"version":"1.3.1.Final",
"connector":"mysql",
"name":"uat",
"ts_ms":1626771878000,
"snapshot":"false",
"db":"canal",
"table":"topic_test",
"server_id":1,
"gtid":"2de6be90-dfb7-11eb-b359-0050568c1ec5:202",
"file":"mysql-bin.000006",
"pos":52974,
"row":0,
"thread":22,
"query":null
},
"op":"c",
"ts_ms":1626771878949,
"transaction":null
}
}
更新一条记录 对应的 topic 数据格式如下
{
"schema":{
"type":"struct",
"fields":[
{
"type":"struct",
"fields":[
{
"type":"int32",
"optional":false,
"field":"id"
},
{
"type":"string",
"optional":true,
"field":"Cname"
}
],
"optional":true,
"name":"uat.canal.topic_test.Value",
"field":"before"
},
{
"type":"struct",
"fields":[
{
"type":"int32",
"optional":false,
"field":"id"
},
{
"type":"string",
"optional":true,
"field":"Cname"
}
],
"optional":true,
"name":"uat.canal.topic_test.Value",
"field":"after"
},
{
"type":"struct",
"fields":[
{
"type":"string",
"optional":false,
"field":"version"
},
{
"type":"string",
"optional":false,
"field":"connector"
},
{
"type":"string",
"optional":false,
"field":"name"
},
{
"type":"int64",
"optional":false,
"field":"ts_ms"
},
{
"type":"string",
"optional":true,
"name":"io.debezium.data.Enum",
"version":1,
"parameters":{
"allowed":"true,last,false"
},
"default":"false",
"field":"snapshot"
},
{
"type":"string",
"optional":false,
"field":"db"
},
{
"type":"string",
"optional":true,
"field":"table"
},
{
"type":"int64",
"optional":false,
"field":"server_id"
},
{
"type":"string",
"optional":true,
"field":"gtid"
},
{
"type":"string",
"optional":false,
"field":"file"
},
{
"type":"int64",
"optional":false,
"field":"pos"
},
{
"type":"int32",
"optional":false,
"field":"row"
},
{
"type":"int64",
"optional":true,
"field":"thread"
},
{
"type":"string",
"optional":true,
"field":"query"
}
],
"optional":false,
"name":"io.debezium.connector.mysql.Source",
"field":"source"
},
{
"type":"string",
"optional":false,
"field":"op"
},
{
"type":"int64",
"optional":true,
"field":"ts_ms"
},
{
"type":"struct",
"fields":[
{
"type":"string",
"optional":false,
"field":"id"
},
{
"type":"int64",
"optional":false,
"field":"total_order"
},
{
"type":"int64",
"optional":false,
"field":"data_collection_order"
}
],
"optional":true,
"field":"transaction"
}
],
"optional":false,
"name":"uat.canal.topic_test.Envelope"
},
"payload":{
"before":{
"id":1,
"Cname":"name"
},
"after":{
"id":1,
"Cname":"name2"
},
"source":{
"version":"1.3.1.Final",
"connector":"mysql",
"name":"uat",
"ts_ms":1626772119000,
"snapshot":"false",
"db":"canal",
"table":"topic_test",
"server_id":1,
"gtid":"2de6be90-dfb7-11eb-b359-0050568c1ec5:203",
"file":"mysql-bin.000006",
"pos":53363,
"row":0,
"thread":22,
"query":null
},
"op":"u",
"ts_ms":1626772119532,
"transaction":null
}
}
删除一条记录 对应的 topic 数据格式如下
{
"schema":{
"type":"struct",
"fields":[
{
"type":"struct",
"fields":[
{
"type":"int32",
"optional":false,
"field":"id"
},
{
"type":"string",
"optional":true,
"field":"Cname"
}
],
"optional":true,
"name":"uat.canal.topic_test.Value",
"field":"before"
},
{
"type":"struct",
"fields":[
{
"type":"int32",
"optional":false,
"field":"id"
},
{
"type":"string",
"optional":true,
"field":"Cname"
}
],
"optional":true,
"name":"uat.canal.topic_test.Value",
"field":"after"
},
{
"type":"struct",
"fields":[
{
"type":"string",
"optional":false,
"field":"version"
},
{
"type":"string",
"optional":false,
"field":"connector"
},
{
"type":"string",
"optional":false,
"field":"name"
},
{
"type":"int64",
"optional":false,
"field":"ts_ms"
},
{
"type":"string",
"optional":true,
"name":"io.debezium.data.Enum",
"version":1,
"parameters":{
"allowed":"true,last,false"
},
"default":"false",
"field":"snapshot"
},
{
"type":"string",
"optional":false,
"field":"db"
},
{
"type":"string",
"optional":true,
"field":"table"
},
{
"type":"int64",
"optional":false,
"field":"server_id"
},
{
"type":"string",
"optional":true,
"field":"gtid"
},
{
"type":"string",
"optional":false,
"field":"file"
},
{
"type":"int64",
"optional":false,
"field":"pos"
},
{
"type":"int32",
"optional":false,
"field":"row"
},
{
"type":"int64",
"optional":true,
"field":"thread"
},
{
"type":"string",
"optional":true,
"field":"query"
}
],
"optional":false,
"name":"io.debezium.connector.mysql.Source",
"field":"source"
},
{
"type":"string",
"optional":false,
"field":"op"
},
{
"type":"int64",
"optional":true,
"field":"ts_ms"
},
{
"type":"struct",
"fields":[
{
"type":"string",
"optional":false,
"field":"id"
},
{
"type":"int64",
"optional":false,
"field":"total_order"
},
{
"type":"int64",
"optional":false,
"field":"data_collection_order"
}
],
"optional":true,
"field":"transaction"
}
],
"optional":false,
"name":"uat.canal.topic_test.Envelope"
},
"payload":{
"before":{
"id":1,
"Cname":"name2"
},
"after":null,
"source":{
"version":"1.3.1.Final",
"connector":"mysql",
"name":"uat",
"ts_ms":1626772247000,
"snapshot":"false",
"db":"canal",
"table":"topic_test",
"server_id":1,
"gtid":"2de6be90-dfb7-11eb-b359-0050568c1ec5:204",
"file":"mysql-bin.000006",
"pos":53751,
"row":0,
"thread":22,
"query":null
},
"op":"d",
"ts_ms":1626772247638,
"transaction":null
}
}