Debezium SQL server 2017同步到kafka

最新推荐文章于 2025-03-26 10:50:39 发布

vkingnew

最新推荐文章于 2025-03-26 10:50:39 发布

阅读量2.5k

点赞数

分类专栏： Kafka

本文链接：https://blog.csdn.net/vkingnew/article/details/89149564

版权

Kafka 专栏收录该内容

18 篇文章

订阅专栏

本文介绍如何使用Kafka Connect与Debezium连接器实现SQL Server变更数据捕获(CDC)，包括配置教程、启动流程及操作示例，如DML与DDL操作验证。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

--启动kafka：
kafka-server-start.sh  /usr/local/kafka/config/server.properties  &
配置文件：
# vim mssql.properties
name=mssql
connector.class=io.debezium.connector.sqlserver.SqlServerConnector
tasks.max=1
database.hostname=10.19.166.249
database.port=1433
database.user=sa
database.password=Yijiupi123
database.dbname=TestDB
database.server.name=MS
database.history.kafka.topic=history.ms
database.history.kafka.bootstrap.servers=10.19.166.249:9092
table.whitelist=dbo.orders,dbo.users
#table.blacklist=
#column.blacklist=
#column.propagate.source.type=

#advanced:
#options: initial（default）initial_schema_only
snapshot.mode=initial
snapshot.locking.mode=none
poll.interval.ms=1000
max.queue.size=8192
max.batch.size=2048
heartbeat.interval.ms=0
#format:<heartbeat.topics.prefix>.<server.name>
heartbeat.topics.prefix=__debezium-heartbeat

--启动：
#connect-standalone.sh connect-standalone.properties mssql.properties &
--查看topic：
 kafka-topics.sh --list --zookeeper 10.19.166.249:2181
--在SQL的操作
DML：

EXEC sys.sp_cdc_enable_table @source_schema = 'dbo', @source_name = 'orders', @role_name = NULL, @supports_net_changes = 1;
insert into orders(id,orderno,product_name,product_id,product_num,order_amount,lastmodifytime)values(9,'LL001','productL',900,99.9,99.55,'2019-04-09 17:09:09');
update orders set orderno='L' where id=9;
delete from orders where id=9;

--查看JSON数据：


DDL操作验证：
新增字段：
alter table orders add isa bit not null default 1;
EXEC sys.sp_cdc_enable_table @source_schema = 'dbo', @source_name = 'orders', @role_name = NULL, @supports_net_changes = 0, @capture_instance = 'dbo_orders_v2';
GO

insert into orders(id,orderno,product_name,product_id,product_num,order_amount,lastmodifytime)values(10,'MM010','productM',990,10.9,10.10,'2019-04-10 19:10:10');

--查看JSON格式的数据：


参考信息：
http://www.tracefact.net/tech/087.html
https://github.com/VenuMeda/kafka-connect-cdc-mssql
https://docs.confluent.io/current/connect/kafka-connect-cdc-mssql/index.html
https://github.com/debezium/debezium-examples/tree/master/tutorial#using-sql-server

https://blog.csdn.net/singgel/article/details/86162990