Debezium同步MySQL变更到kafka集群及REST API使用方法汇总

Debezium安装及REST API使用方法

参考文档：
Debeziun官方文档：https://debezium.io/documentation/reference/1.3/
https://blog.csdn.net/ylejun/article/details/107272923

一、Kafka-distributed模式安装启动Debezium

1、前期准备

​ 前期准备需安装kafka、zookeeper集群及MySQL测试数据库

1. kafka集群信息
1.1.1.1:7091
2.2.2.2:7091
3.3.3.3:7091

2. zookeeper集群信息
1.1.1.1:2181
2.2.2.2:2181
3.3.3.3:2181

3. MySQL数据库信息
host: 4.4.4.4
port: 5017
user: test
pass: 123

2、安装配置

1. 下载官方提供的debezium-mysql插件，官方还提供了其他种类数据库的插件，这里目前仅测试Debezium对MySQL数据库变动的支持
wget https://repo1.maven.org/maven2/io/debezium/debezium-connector-mysql/1.3.1.Final/debezium-connector-mysql-1.3.1.Final-plugin.tar.gz

2. 在三个kafka所在机器上将解压后的jar插件放入对应的plugin目录下
tar zxf debezium-connector-mysql-1.3.1.Final-plugin.tar.gz
cp debezium-connector-mysql/*.jar /work/kafka/libs/

3. 在kafka集群上创建topic
./bin/kafka-topics.sh --create --zookeeper 1.1.1.1:2181,2.2.2.2:2181,3.3.3.3:2181 --topic cdc-connect-configs --replication-factor 3 --partitions 1 --config cleanup.policy=compact
./bin/kafka-topics.sh --create --zookeeper 1.1.1.1:2181,2.2.2.2:2181,3.3.3.3:2181 --topic cdc-connect-offsets --replication-factor 3 --partitions 50 --config cleanup.policy=compact
./bin/kafka-topics.sh --create --zookeeper 1.1.1.1:2181,2.2.2.2:2181,3.3.3.3:2181 --topic cdc-connect-status --replication-factor 3 --partitions 10 --config cleanup.policy=compact

- config.storage.topic(默认connect-configs，这里设置为：cdc-connect-configs)：用于存储连接器和任务配置的topic；应为单个分区、多副本
- offset.storage.topic(默认connect-offsets，这里设置为：cdc-connect-offsets)：用于存储偏移量的topic，应该为多分区，多副本，并配置为压缩
- status.storage.topic(默认connect-status，这里设置为cdc-connect-status)：用于存储状态的topic，可设置为多分区、多副本和压缩

4. 修改connect-distributed配置
cd /work/kafka
cp ./config/connect-distributed.properties ./config/cdc-connect-distributed.properties
## 配置文件以1.1.1.1为例，需设置如下
cat ./config/cdc-connect-distributed.properties
bootstrap.servers=1.1.1.1:7091
### kafka-group.id
group.id=cdc-connect-cluster
### topic cdc-connect-offsets设置
offset.storage.topic=cdc-connect-offsets
offset.storage.replication.factor=3
offset.storage.partitions=50
### topic cdc-connect-configs设置
config.storage.topic=cdc-connect-configs
config.storage.replication.factor=3
config.storage.partitions=1
### topic cdc-connect-status设置
status.storage.topic=cdc-connect-status
status.storage.replication.factor=3
status.storage.partitions=10
### debezium插件所在目录
plugin.path=/work/kafka/libs/

3、启动

cd /work/kafka
./bin/connect-distributed.sh ./config/cdc-connect-distributed.properties

二、Debezium-MySQL连接器注册

curl -i -X POST -H "Accept:application/json" -H "Content-Type:application/json" http://1.1.1.1:8083/connectors/ -d '{ "name" : "debezium-test-5017", "config": { "connector.class":"io.debezium.connector.mysql.MySqlConnector", "database.hostname":"4.4.4.4", "database.port":"5017", "database.user":"test", "database.password":"123", "database.server.id":"316545017", "database.server.name" : "debezium_mysql_5017","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" } }'

1. json详情

// 最终生成的某张表的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_mysql_5017",       // 唯一的服务器逻辑名称。该名称将用作所有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同时使用

三、REST API调用

1. DDL操作

1. kafka消费
bin/kafka-console-consumer.sh --bootstrap-server 1.1.1.1:7091,2.2.2.2:7091,3.3.3.3:7091 --topic debezium_mysql_5017

2. 数据库执行DDL建表操作
SQL：create table t2 like t1;

结果值

{
    "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":"debezium_mysql_5017",
            "ts_ms":1609415299000,
            "snapshot":"false",
            "db":"cdc",
            "table":"t2",
            "server_id":316545017,
            "gtid":"0cb0a492-2ada-11eb-bdb5-08688d61c0e5:37",
            "file":"mysql-bin.000001",
            "pos":10245,
            "row":0,
            "thread":null,
            "query":null
        },
        "databaseName":"cdc",
        "ddl":"create table t2 like t1"
    }
}

2. DML操作

对cdc.t2表进行操作
kafka消费：
bin/kafka-console-consumer.sh --bootstrap-server 1.1.1.1:7091,2.2.2.2:7091,3.3.3.3:7091 --topic debezium_mysql_5017.cdc.t2

## t2表结构为
CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL DEFAULT 'cdc' COMMENT '姓名',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8

## 初始化数据为：
select * from t2;
+----+------+
| id | name |
+----+------+
|  1 | aaa  |
|  2 | bbb  |
|  3 | aaa  |
|  4 | bbb  |
+----+------+

2.1 Insert

数据库操作

insert into t2 (name) values ('ccc');

结果

// 结果schema结构和DDL类似，仅最终payload不同
    {"payload":{
        "before":null,
        "after":{
            "id":5,
            "name":"ccc"
        },
        "source":{
            "version":"1.3.1.Final",
            "connector":"mysql",
            "name":"debezium_mysql_5017",
            "ts_ms":1609415836000,
            "snapshot":"false",
            "db":"cdc",
            "table":"t2",
            "server_id":316545017,
            "gtid":"0cb0a492-2ada-11eb-bdb5-08688d61c0e5:42",
            "file":"mysql-bin.000001",
            "pos":11408,
            "row":0,
            "thread":3219,
            "query":null
        },
        "op":"c",
        "ts_ms":1609415836113,
        "transaction":null
    }
}

2.2 Delete

数据库操作

delete from t2 where name = "bbb";

结果

// 按照示例存在两条结果，结果schema结构类似，仅最终payload不同
// payload1
    {"payload":{
        "before":{
            "id":2,
            "name":"bbb"
        },
        "after":null,
        "source":{
            "version":"1.3.1.Final",
            "connector":"mysql",
            "name":"debezium_mysql_5017",
            "ts_ms":1609415906000,
            "snapshot":"false",
            "db":"cdc",
            "table":"t2",
            "server_id":316545017,
            "gtid":"0cb0a492-2ada-11eb-bdb5-08688d61c0e5:43",
            "file":"mysql-bin.000001",
            "pos":11666,
            "row":0,
            "thread":3219,
            "query":null
        },
        "op":"d",
        "ts_ms":1609415906931,
        "transaction":null
    }
}

// payload2
{
    "payload":{
        "before":{
            "id":4,
            "name":"bbb"
        },
        "after":null,
        "source":{
            "version":"1.3.1.Final",
            "connector":"mysql",
            "name":"debezium_mysql_5017",
            "ts_ms":1609415906000,
            "snapshot":"false",
            "db":"cdc",
            "table":"t2",
            "server_id":316545017,
            "gtid":"0cb0a492-2ada-11eb-bdb5-08688d61c0e5:43",
            "file":"mysql-bin.000001",
            "pos":11666,
            "row":1,
            "thread":3219,
            "query":null
        },
        "op":"d",
        "ts_ms":1609415906931,
        "transaction":null
    }
}

2.3 Update

数据库操作

update t2 set name = "test" where id > 3;

结果

{
    "payload":{
        "before":{
            "id":5,
            "name":"ccc"
        },
        "after":{
            "id":5,
            "name":"test"
        },
        "source":{
            "version":"1.3.1.Final",
            "connector":"mysql",
            "name":"debezium_mysql_5017",
            "ts_ms":1609415991000,
            "snapshot":"false",
            "db":"cdc",
            "table":"t2",
            "server_id":316545017,
            "gtid":"0cb0a492-2ada-11eb-bdb5-08688d61c0e5:44",
            "file":"mysql-bin.000001",
            "pos":11933,
            "row":0,
            "thread":3219,
            "query":null
        },
        "op":"u",
        "ts_ms":1609415991124,
        "transaction":null
    }
}

四、REST API常用方法

GET /Connectors：返回活跃的 Connector 列表

POST /Connectors：创建一个新的 Connector；请求的主体是一个包含字符串name字段和对象 config 字段的 JSON 对象。

GET /Connectors/{name}：获取指定 Connector 的信息

GET /Connectors/{name}/config：获取指定 Connector 的配置参数

PUT /Connectors/{name}/config：更新指定 Connector 的配置参数

GET /Connectors/{name}/status：获取 Connector 的当前状态，包括它是否正在运行，失败，暂停等。

GET /Connectors/{name}/tasks：获取当前正在运行的 Connector 的任务列表。

GET /Connectors/{name}/tasks/{taskid}/status：获取任务的当前状态，包括是否是运行中的，失败的，暂停的等，

PUT /Connectors/{name}/pause：暂停连接器和它的任务，停止消息处理，直到 Connector 恢复。

PUT /Connectors/{name}/resume：恢复暂停的 Connector

POST /Connectors/{name}/restart：重启 Connector

POST /Connectors/{name}/tasks/{taskId}/restart：重启单个任务

DELETE /Connectors/{name}：删除 Connector, 停止所有的任务并删除其配置

1、返回连接器列表

1.1 request

curl -i -X GET -H "Accept:application/json" -H "Content-Type:application/json" http://1.1.1.1:8083/connectors/

1.2 reponse

HTTP/1.1 200 OK
Date: Thu, 31 Dec 2020 11:42:04 GMT
Content-Type: application/json
Content-Length: 92
Server: Jetty(9.2.22.v20170606)

["debezium-test-5011","debezium-5011","debezium-mysql-test-5016","debezium-mysql-test-5015"]

2、创建Connector

2.1 request

curl -i -X POST -H "Accept:application/json" -H "Content-Type:application/json" http://1.1.1.1:8083/connectors/ -d '{ "name" : "debezium-test-5017", "config": { "connector.class":"io.debezium.connector.mysql.MySqlConnector", "database.hostname":"4.4.4.4", "database.port":"5017", "database.user":"test", "database.password":"123", "database.server.id":"316545017", "database.server.name" : "debezium_mysql_5017","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" } }'

2.2 reponse

HTTP/1.1 201 Created
Date: Thu, 31 Dec 2020 10:46:00 GMT
Location: http://1.1.1.1:8083/connectors/debezium-5011
Content-Type: application/json
Content-Length: 546
Server: Jetty(9.2.22.v20170606)

{"name":"debezium-test-5017","config":{ "connector.class":"io.debezium.connector.mysql.MySqlConnector", "database.hostname":"4.4.4.4", "database.port":"5017", "database.user":"test", "database.password":"123", "database.server.id":"316545017", "database.server.name" : "debezium_mysql_5017","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" },"tasks":[],"type":null}

3、获取指定connector信息

3.1 request

curl -i -X GET -H "Accept:application/json" -H "Content-Type:application/json" http://1.1.1.1:8083/connectors/debezium-test-5017/

3.2 reponse

HTTP/1.1 200 OK
Date: Thu, 31 Dec 2020 13:35:45 GMT
Content-Type: application/json
Content-Length: 533
Server: Jetty(9.2.22.v20170606)

{"name":"debezium-test-5017","config":{"connector.class":"io.debezium.connector.mysql.MySqlConnector","database.user":"test","database.server.id":"316545017","database.hostname":"4.4.4.4","database.password":"123","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","name":"debezium-test-5017","database.server.name":"debezium_mysql_5017","database.port":"5017"},"tasks":[{"connector":"debezium-test-5017","task":0}],"type":"source"}

4、获取 Connector 的状态

4.1 request

curl -i -X GET -H "Accept:application/json" -H "Content-Type:application/json" http://1.1.1.1:8083/connectors/debezium-test-5017/status

4.2 reponse

HTTP/1.1 200 OK
Date: Thu, 31 Dec 2020 13:35:26 GMT
Content-Type: application/json
Content-Length: 178
Server: Jetty(9.2.22.v20170606)

{"name":"debezium-test-5017","connector":{"state":"RUNNING","worker_id":"3.3.3.3:8083"},"tasks":[{"state":"RUNNING","id":0,"worker_id":"1.1.1.1:8083"}],"type":"source"}

5、获取 Connector 的任务列表

5.1 request

curl -i -X GET -H "Accept:application/json" -H "Content-Type:application/json" http://1.1.1.1:8083/connectors/debezium-test-5017/tasks

5.2 reponse

HTTP/1.1 200 OK
Date: Thu, 31 Dec 2020 13:36:17 GMT
Content-Type: application/json
Content-Length: 548
Server: Jetty(9.2.22.v20170606)

[{"id":{"connector":"debezium-test-5017","task":0},"config":{"connector.class":"io.debezium.connector.mysql.MySqlConnector","database.user":"test","task.class":"io.debezium.connector.mysql.MySqlConnectorTask","database.server.id":"316545017","database.hostname":"4.4.4.4","database.password":"123","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","name":"debezium-test-5017","database.server.name":"debezium_mysql_5017","database.port":"5017"}}]

6、获取任务状态

6.1 request

curl -i -X GET -H "Accept:application/json" -H "Content-Type:application/json" http://1.1.1.1:8083/connectors/debezium-test-5017/tasks/0/status

6.2 reponse

HTTP/1.1 200 OK
Date: Thu, 31 Dec 2020 13:36:42 GMT
Content-Type: application/json
Content-Length: 58
Server: Jetty(9.2.22.v20170606)

{"state":"RUNNING","id":0,"worker_id":"1.1.1.1:8083"}

7、暂停连接器和它的任务

7.1 request

curl -i -X PUT -H "Accept:application/json" -H "Content-Type:application/json" http://1.1.1.1:8083/connectors/debezium-test-5017/pause

7.2 reponse

ebezium-test-5017/pause
HTTP/1.1 202 Accepted
Date: Thu, 31 Dec 2020 13:37:24 GMT
Content-Length: 0
Server: Jetty(9.2.22.v20170606)

-- 再次查看状态
 curl -i -X GET -H "Accept:application/json" -H "Content-Type:application/json" http://1.1.1.1:8083/connectors/debezium-test-5017/status
 
HTTP/1.1 200 OK
Date: Thu, 31 Dec 2020 13:37:28 GMT
Content-Type: application/json
Content-Length: 176
Server: Jetty(9.2.22.v20170606)

{"name":"debezium-test-5017","connector":{"state":"PAUSED","worker_id":"3.3.3.3:8083"},"tasks":[{"state":"PAUSED","id":0,"worker_id":"1.1.1.1:8083"}],"type":"source"}

8、更新connector配置(返回500,还需测试)

8.1 request

curl -i -X PUT -H "Accept:application/json" -H "Content-Type:application/json" http://1.1.1.1:8083/connectors/debezium-test-5017/config -d '{ "name" : "debezium-test-5017", "config": { "connector.class":"io.debezium.connector.mysql.MySqlConnector", "database.hostname":"4.4.4.4", "database.port":"5017", "database.user":"test", "database.password":"123", "database.server.id":"316545017", "database.server.name" : "debezium_mysql_5017", "database.include.list":"zztest", "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" } }'

8.2 reponse

HTTP/1.1 500 Request failed.
Date: Thu, 31 Dec 2020 13:52:37 GMT
Cache-Control: must-revalidate,no-cache,no-store
Content-Type: text/html; charset=ISO-8859-1
Content-Length: 320
Server: Jetty(9.2.22.v20170606)

<html>
<head>
<meta http-equiv="Content-Type" content="text/html;charset=ISO-8859-1"/>
<title>Error 500 </title>
</head>
<body>
<h2>HTTP ERROR: 500</h2>
<p>Problem accessing /connectors/debezium-test-5017/config. Reason:
<pre>    Request failed.</pre></p>
<hr /><i><small>Powered by Jetty://</small></i>
</body>
</html>

9、恢复暂停的 Connector

9.1 request

curl -i PUT -H "Accept:application/json" -H "Content-Type:application/json" http://1.1.1.1:8083/connectorebezium-test-5017/resume

9.2 reponse

HTTP/1.1 202 Accepted
Date: Thu, 31 Dec 2020 13:38:23 GMT
Content-Length: 0
Server: Jetty(9.2.22.v20170606)

-- 再次查看状态
curl -i -X GET -H "Accept:application/json" -H "Content-Type:application/json" http://1.1.1.1:8083/connectors/debezium-test-5017/status
HTTP/1.1 200 OK
Date: Thu, 31 Dec 2020 13:38:47 GMT
Content-Type: application/json
Content-Length: 178
Server: Jetty(9.2.22.v20170606)

{"name":"debezium-test-5017","connector":{"state":"RUNNING","worker_id":"3.3.3.3:8083"},"tasks":[{"state":"RUNNING","id":0,"worker_id":"1.1.1.1:8083"}],"type":"source"}

10、重启 Connector

10.1 request

curl -i -X POST -H "Accept:application/json" -H "Content-Type:application/json" http://1.1.1.1:8083/connectors/debezium-test-5017/restart

10.2 reponse

HTTP/1.1 204 No Content
Date: Thu, 31 Dec 2020 13:39:18 GMT
Server: Jetty(9.2.22.v20170606)

11、重启单个任务

11.1 request

 curl -i -X POST -H "Accept:application/json" -H "Content-Type:application/json" http://1.1.1.1:8083/connectors/debezium-test-5017/tasks/0/restart

11.2 reponse

HTTP/1.1 204 No Content
Date: Thu, 31 Dec 2020 13:40:09 GMT
Server: Jetty(9.2.22.v20170606)

12、删除 Connector

12.1 request

-- 获取删除前connector列表
curl -i -X GET -H "Accept:application/json" -H "Content-Type:application/json" http://1.1.1.1:8083/connectors
HTTP/1.1 200 OK
Date: Thu, 31 Dec 2020 13:40:34 GMT
Content-Type: application/json
Content-Length: 113
Server: Jetty(9.2.22.v20170606)

["debezium-test-5011","debezium-5011","debezium-mysql-test-5016","debezium-test-5017","debezium-mysql-test-5015"]

-- 删除某个connector
curl -i -X DELETE -H "Accept:application/json" -H "Content-Type:application/json" http://1.1.1.1:8083/connectors/debezium-test-5011

12.2 reponse

HTTP/1.1 204 No Content
Date: Thu, 31 Dec 2020 13:41:24 GMT
Server: Jetty(9.2.22.v20170606)

-- 再次查看connector列表
 curl -i -X GET -H "Accept:application/json" -H "Content-Type:application/json" http://1.1.1.1:8083/connectors
HTTP/1.1 200 OK
Date: Thu, 31 Dec 2020 13:41:26 GMT
Content-Type: application/json
Content-Length: 92
Server: Jetty(9.2.22.v20170606)

["debezium-5011","debezium-mysql-test-5016","debezium-test-5017","debezium-mysql-test-5015"]