数据库CDC技术实时数据变更捕获的利器

一、什么是CDC？它解决了什么问题？

CDC（Change Data Capture）定义

CDC是一种数据库技术，用于捕获和跟踪数据变更，并将这些变更以结构化的方式提供给其他系统使用。简单说，CDC就是"数据库的监控摄像头"，实时记录谁在什么时候改变了什么。

传统数据变更记录的问题

— 传统方式：应用层记录
— 需要在每个业务操作中手动记录
UPDATE orders SET status = 'SHIPPED' WHERE id = 1001;

— 然后手动记录日志
INSERT INTO change_log (record_id, operation, old_value, new_value)
VALUES (1001, 'UPDATE', 'PENDING', 'SHIPPED');

传统方式痛点：

• 代码入侵：每个业务操作都要写日志代码
• 容易遗漏：开发人员可能忘记记录
• 事务一致性问题：主操作成功，日志记录失败怎么办？
• 性能影响：同步记录日志影响主业务性能

二、CDC的工作原理：数据库层的"监控系统"

CDC的三种实现方式

1. 基于触发器的CDC

— MySQL示例：创建触发器自动记录变更
CREATE TRIGGER orders_audit_trigger
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
INSERT INTO orders_audit (
order_id,
old_status,
new_status,
change_time,
change_user
) VALUES (
NEW.id,
OLD.status,
NEW.status,
NOW(),
CURRENT_USER()
);
END;

优点：

• 实现简单
• 实时性强
• 能捕获所有变更

缺点：

• 性能影响大（每个操作都要执行触发器）
• 增加数据库负载
• 维护复杂（表结构变更需要同步修改触发器）

2. 基于日志的CDC（最常用）

#mermaid-svg-d4xjsbWTExOC9WEL{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-svg-d4xjsbWTExOC9WEL .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-svg-d4xjsbWTExOC9WEL .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-svg-d4xjsbWTExOC9WEL .error-icon{fill:#552222;}#mermaid-svg-d4xjsbWTExOC9WEL .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-d4xjsbWTExOC9WEL .edge-thickness-normal{stroke-width:1px;}#mermaid-svg-d4xjsbWTExOC9WEL .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-d4xjsbWTExOC9WEL .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-d4xjsbWTExOC9WEL .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-svg-d4xjsbWTExOC9WEL .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-d4xjsbWTExOC9WEL .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-d4xjsbWTExOC9WEL .marker{fill:#333333;stroke:#333333;}#mermaid-svg-d4xjsbWTExOC9WEL .marker.cross{stroke:#333333;}#mermaid-svg-d4xjsbWTExOC9WEL svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-d4xjsbWTExOC9WEL p{margin:0;}#mermaid-svg-d4xjsbWTExOC9WEL .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#mermaid-svg-d4xjsbWTExOC9WEL .cluster-label text{fill:#333;}#mermaid-svg-d4xjsbWTExOC9WEL .cluster-label span{color:#333;}#mermaid-svg-d4xjsbWTExOC9WEL .cluster-label span p{background-color:transparent;}#mermaid-svg-d4xjsbWTExOC9WEL .label text,#mermaid-svg-d4xjsbWTExOC9WEL span{fill:#333;color:#333;}#mermaid-svg-d4xjsbWTExOC9WEL .node rect,#mermaid-svg-d4xjsbWTExOC9WEL .node circle,#mermaid-svg-d4xjsbWTExOC9WEL .node ellipse,#mermaid-svg-d4xjsbWTExOC9WEL .node polygon,#mermaid-svg-d4xjsbWTExOC9WEL .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-d4xjsbWTExOC9WEL .rough-node .label text,#mermaid-svg-d4xjsbWTExOC9WEL .node .label text,#mermaid-svg-d4xjsbWTExOC9WEL .image-shape .label,#mermaid-svg-d4xjsbWTExOC9WEL .icon-shape .label{text-anchor:middle;}#mermaid-svg-d4xjsbWTExOC9WEL .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#mermaid-svg-d4xjsbWTExOC9WEL .rough-node .label,#mermaid-svg-d4xjsbWTExOC9WEL .node .label,#mermaid-svg-d4xjsbWTExOC9WEL .image-shape .label,#mermaid-svg-d4xjsbWTExOC9WEL .icon-shape .label{text-align:center;}#mermaid-svg-d4xjsbWTExOC9WEL .node.clickable{cursor:pointer;}#mermaid-svg-d4xjsbWTExOC9WEL .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#mermaid-svg-d4xjsbWTExOC9WEL .arrowheadPath{fill:#333333;}#mermaid-svg-d4xjsbWTExOC9WEL .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-d4xjsbWTExOC9WEL .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-d4xjsbWTExOC9WEL .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-d4xjsbWTExOC9WEL .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#mermaid-svg-d4xjsbWTExOC9WEL .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-d4xjsbWTExOC9WEL .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#mermaid-svg-d4xjsbWTExOC9WEL .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-d4xjsbWTExOC9WEL .cluster text{fill:#333;}#mermaid-svg-d4xjsbWTExOC9WEL .cluster span{color:#333;}#mermaid-svg-d4xjsbWTExOC9WEL div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-d4xjsbWTExOC9WEL .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#mermaid-svg-d4xjsbWTExOC9WEL rect.text{fill:none;stroke-width:0;}#mermaid-svg-d4xjsbWTExOC9WEL .icon-shape,#mermaid-svg-d4xjsbWTExOC9WEL .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-d4xjsbWTExOC9WEL .icon-shape p,#mermaid-svg-d4xjsbWTExOC9WEL .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#mermaid-svg-d4xjsbWTExOC9WEL .icon-shape rect,#mermaid-svg-d4xjsbWTExOC9WEL .image-shape rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-d4xjsbWTExOC9WEL .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#mermaid-svg-d4xjsbWTExOC9WEL .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#mermaid-svg-d4xjsbWTExOC9WEL :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}

业务操作

数据库执行

写入事务日志如MySQL的binlog

CDC工具读取日志

解析和格式化

输出变更事件

常见工具：

• Debezium：开源CDC平台，支持多种数据库
• Canal：阿里巴巴的MySQL binlog增量订阅组件
• Maxwell：MySQL binlog读取工具

3. 基于时间戳/版本的CDC

— 表中增加版本字段
CREATE TABLE products (
id INT PRIMARY KEY,
name VARCHAR(100),
price DECIMAL(10,2),
— CDC相关字段
version INT DEFAULT 1,
last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

— 通过查询变更时间获取变更数据
SELECT * FROM products
WHERE last_updated > '2024-01-15 10:00:00'
ORDER BY last_updated;

三、主流数据库的CDC实现

1. MySQL CDC

# 使用Debezium连接MySQL的配置示例
debezium:
connector.class: io.debezium.connector.mysql.MySqlConnector
database.hostname: localhost
database.port: 3306
database.user: cdc_user
database.password: password
database.server.id: 184054
database.server.name: myserver
database.whitelist: production_db
table.whitelist: production_db.orders,production_db.products
database.history.kafka.bootstrap.servers: kafka:9092
database.history.kafka.topic: dbhistory.myserver

2. PostgreSQL CDC

— PostgreSQL使用逻辑复制
— 1. 修改postgresql.conf
wal_level = logical
max_replication_slots = 10

— 2. 创建复制槽
SELECT pg_create_logical_replication_slot('debezium_slot', 'pgoutput');

— 3. 创建发布
CREATE PUBLICATION debezium_publication FOR TABLE orders, products;

— 4. Debezium配置
debezium.connector.class=io.debezium.connector.postgresql.PostgresConnector
debezium.plugin.name=pgoutput
debezium.slot.name=debezium_slot
debezium.publication.name=debezium_publication

3. SQL Server CDC

— SQL Server内置CDC功能
— 1. 启用数据库CDC
EXEC sys.sp_cdc_enable_db;

— 2. 启用表级CDC
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'orders',
@role_name = NULL;

— 3. 查询变更数据
SELECT * FROM cdc.dbo_orders_CT
WHERE __$operation IN (1,2,3,4); — 1=删除, 2=插入, 3=更新前, 4=更新后

4. Oracle CDC

— Oracle使用LogMiner
— 1. 启用补充日志
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

— 2. 创建LogMiner表
EXEC DBMS_LOGMNR_D.BUILD('logminer.ora', '/oracle/logs/');

— 3. 添加日志文件
EXEC DBMS_LOGMNR.ADD_LOGFILE('/oracle/logs/redo01.log');

— 4. 开始LogMiner
EXEC DBMS_LOGMNR.START_LOGMNR;

— 5. 查询变更
SELECT sql_redo, sql_undo FROM v$logmnr_contents
WHERE table_name = 'ORDERS';

四、CDC在信息化管理系统的应用场景

场景1：实时数据同步

# 生产数据实时同步到数据仓库
source: MES生产数据库
↓ CDC捕获变更
Kafka: 变更事件流
↓ Flink实时处理
target:
– 数据仓库(ClickHouse)
– 实时监控大屏
– 质量分析系统

场景2：微服务数据变更通知

// 订单服务更新状态，通过CDC通知其他服务
@Component
public class OrderChangeConsumer {

@KafkaListener(topics = "mysql.mesdb.orders")
public void handleOrderChange(String message) {
OrderChangeEvent event = parseEvent(message);

switch(event.getOperation()) {
case "UPDATE":
if (event.getField("status").isChanged()) {
// 通知物流服务
notifyLogistics(event);
// 通知财务服务
notifyFinance(event);
// 通知客户服务
notifyCustomer(event);
}
break;
}
}
}

场景3：数据审计与合规

— 使用CDC自动构建审计表
CREATE TABLE production_audit AS
SELECT
op,
ts,
before,
after,
source.ts_ms as db_change_time,
CURRENT_TIMESTAMP as audit_time
FROM debezium_stream
WHERE table = 'production_records';

五、CDC vs 应用层日志：详细对比

六、CDC在生产系统的实战案例

案例：数据实时监控

架构设计

#mermaid-svg-tRGqwY3QSUxT8YGn{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-svg-tRGqwY3QSUxT8YGn .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-svg-tRGqwY3QSUxT8YGn .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-svg-tRGqwY3QSUxT8YGn .error-icon{fill:#552222;}#mermaid-svg-tRGqwY3QSUxT8YGn .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-tRGqwY3QSUxT8YGn .edge-thickness-normal{stroke-width:1px;}#mermaid-svg-tRGqwY3QSUxT8YGn .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-tRGqwY3QSUxT8YGn .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-tRGqwY3QSUxT8YGn .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-svg-tRGqwY3QSUxT8YGn .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-tRGqwY3QSUxT8YGn .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-tRGqwY3QSUxT8YGn .marker{fill:#333333;stroke:#333333;}#mermaid-svg-tRGqwY3QSUxT8YGn .marker.cross{stroke:#333333;}#mermaid-svg-tRGqwY3QSUxT8YGn svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-tRGqwY3QSUxT8YGn p{margin:0;}#mermaid-svg-tRGqwY3QSUxT8YGn .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#mermaid-svg-tRGqwY3QSUxT8YGn .cluster-label text{fill:#333;}#mermaid-svg-tRGqwY3QSUxT8YGn .cluster-label span{color:#333;}#mermaid-svg-tRGqwY3QSUxT8YGn .cluster-label span p{background-color:transparent;}#mermaid-svg-tRGqwY3QSUxT8YGn .label text,#mermaid-svg-tRGqwY3QSUxT8YGn span{fill:#333;color:#333;}#mermaid-svg-tRGqwY3QSUxT8YGn .node rect,#mermaid-svg-tRGqwY3QSUxT8YGn .node circle,#mermaid-svg-tRGqwY3QSUxT8YGn .node ellipse,#mermaid-svg-tRGqwY3QSUxT8YGn .node polygon,#mermaid-svg-tRGqwY3QSUxT8YGn .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-tRGqwY3QSUxT8YGn .rough-node .label text,#mermaid-svg-tRGqwY3QSUxT8YGn .node .label text,#mermaid-svg-tRGqwY3QSUxT8YGn .image-shape .label,#mermaid-svg-tRGqwY3QSUxT8YGn .icon-shape .label{text-anchor:middle;}#mermaid-svg-tRGqwY3QSUxT8YGn .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#mermaid-svg-tRGqwY3QSUxT8YGn .rough-node .label,#mermaid-svg-tRGqwY3QSUxT8YGn .node .label,#mermaid-svg-tRGqwY3QSUxT8YGn .image-shape .label,#mermaid-svg-tRGqwY3QSUxT8YGn .icon-shape .label{text-align:center;}#mermaid-svg-tRGqwY3QSUxT8YGn .node.clickable{cursor:pointer;}#mermaid-svg-tRGqwY3QSUxT8YGn .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#mermaid-svg-tRGqwY3QSUxT8YGn .arrowheadPath{fill:#333333;}#mermaid-svg-tRGqwY3QSUxT8YGn .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-tRGqwY3QSUxT8YGn .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-tRGqwY3QSUxT8YGn .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-tRGqwY3QSUxT8YGn .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#mermaid-svg-tRGqwY3QSUxT8YGn .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-tRGqwY3QSUxT8YGn .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#mermaid-svg-tRGqwY3QSUxT8YGn .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-tRGqwY3QSUxT8YGn .cluster text{fill:#333;}#mermaid-svg-tRGqwY3QSUxT8YGn .cluster span{color:#333;}#mermaid-svg-tRGqwY3QSUxT8YGn div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-tRGqwY3QSUxT8YGn .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#mermaid-svg-tRGqwY3QSUxT8YGn rect.text{fill:none;stroke-width:0;}#mermaid-svg-tRGqwY3QSUxT8YGn .icon-shape,#mermaid-svg-tRGqwY3QSUxT8YGn .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-tRGqwY3QSUxT8YGn .icon-shape p,#mermaid-svg-tRGqwY3QSUxT8YGn .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#mermaid-svg-tRGqwY3QSUxT8YGn .icon-shape rect,#mermaid-svg-tRGqwY3QSUxT8YGn .image-shape rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-tRGqwY3QSUxT8YGn .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#mermaid-svg-tRGqwY3QSUxT8YGn .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#mermaid-svg-tRGqwY3QSUxT8YGn :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}

应用层

处理层

CDC层

生产数据库

工单表

报工表

质检表

Debezium连接器

Kafka集群

Flink实时处理

业务规则引擎

实时监控大屏

异常告警系统

数据分析平台

配置实现

# debezium-connector-mes.yaml
{
"name": "mes-production-connector",
"config": {
"connector.class": "io.debezium.connector.mysql.MySqlConnector",
"database.hostname": "mes-db.production.com",
"database.port": "3306",
"database.user": "cdc_user",
"database.password": "${secure_password}",
"database.server.id": "5401",
"database.server.name": "mes-prod",
"database.include.list": "mes_production",
"table.include.list": "mes_production.work_orders,mes_production.production_records,mes_production.quality_checks",
"database.history.kafka.bootstrap.servers": "kafka-broker1:9092,kafka-broker2:9092",
"database.history.kafka.topic": "schema-changes.mes-prod",
"include.schema.changes": "true",
"transforms": "unwrap",
"transforms.unwrap.type": "io.debezium.transforms.ExtractNewRecordState",
"transforms.unwrap.drop.tombstones": "false",
"transforms.unwrap.delete.handling.mode": "drop",
"key.converter": "org.apache.kafka.connect.json.JsonConverter",
"value.converter": "org.apache.kafka.connect.json.JsonConverter"
}
}

实时处理逻辑

public class ProductionDataProcessor {

public void processChangeEvent(SourceRecord record) {
Struct value = (Struct) record.value();
String operation = value.getString("op"); // c=创建, u=更新, d=删除

if ("u".equals(operation)) {
Struct before = value.getStruct("before");
Struct after = value.getStruct("after");

String table = value.getStruct("source").getString("table");

switch (table) {
case "production_records":
handleProductionRecordChange(before, after);
break;
case "quality_checks":
handleQualityCheckChange(before, after);
break;
case "work_orders":
handleWorkOrderChange(before, after);
break;
}
}
}

private void handleProductionRecordChange(Struct before, Struct after) {
Integer workOrderId = after.getInt32("work_order_id");
String oldStatus = before.getString("status");
String newStatus = after.getString("status");
Integer oldQty = before.getInt32("actual_quantity");
Integer newQty = after.getInt32("actual_quantity");

// 1. 触发实时监控更新
realtimeDashboard.updateProductionStatus(workOrderId, newStatus);

// 2. 检查生产异常
if ("quality_issue".equals(newStatus)) {
alertSystem.sendQualityAlert(workOrderId);
}

// 3. 更新生产进度
if (oldQty != null && newQty != null && !oldQty.equals(newQty)) {
productionProgressService.updateProgress(workOrderId, newQty);
}

// 4. 记录到审计日志（包含业务上下文）
auditService.logProductionChange(
workOrderId,
oldStatus, newStatus,
oldQty, newQty,
"SYSTEM_AUTO", // CDC无法知道操作人，用SYSTEM_AUTO标记
"CDC自动捕获的变更"
);
}
}

CDC事件示例

{
"before": {
"id": 1001,
"work_order_no": "WO-20240115-001",
"actual_quantity": 95,
"status": "in_progress",
"quality_level": "A",
"last_updated": "2024-01-15T14:30:00Z"
},
"after": {
"id": 1001,
"work_order_no": "WO-20240115-001",
"actual_quantity": 100,
"status": "completed",
"quality_level": "A",
"last_updated": "2024-01-15T15:30:00Z"
},
"source": {
"version": "1.9.7.Final",
"connector": "mysql",
"name": "mes-prod",
"ts_ms": 1705332600000,
"snapshot": "false",
"db": "mes_production",
"table": "production_records",
"server_id": 223344,
"gtid": null,
"file": "binlog.000008",
"pos": 45789,
"row": 0,
"thread": 7,
"query": null
},
"op": "u",
"ts_ms": 1705332600123
}

七、CDC的局限性及解决方案

局限性1：无法获取业务语义

// CDC只能捕获数据变化，不知道"为什么"变化
// 解决方案：业务系统额外提供上下文
class HybridApproach {
// 业务操作时记录业务上下文
public void updateProductionRecord(UpdateRequest request) {
// 1. 业务操作（CDC会捕获这个变更）
productionRepo.update(request.getData());

// 2. 记录业务上下文到独立表
businessContextRepo.save(BusinessContext.of(
request.getRecordId(),
request.getChangeReason(), // CDC无法获取这个
request.getOperator(),
request.getBusinessCase()
));

// 3. 处理层通过事务ID关联
// CDC事件 + 业务上下文 = 完整审计记录
}
}

局限性2：无法记录删除原因

— CDC能知道记录被删除，但不知道为什么删除
— 解决方案：软删除 + 删除原因字段
ALTER TABLE production_records
ADD COLUMN deleted_at DATETIME,
ADD COLUMN delete_reason VARCHAR(200),
ADD COLUMN deleted_by INT;

— 删除时更新而不是真正删除
UPDATE production_records
SET
deleted_at = NOW(),
delete_reason = '设备故障导致数据无效',
deleted_by = 1001
WHERE id = 1234;

— CDC会捕获这个更新，包含删除原因

局限性3：无法获取操作人信息

# 解决方案1：应用层补充用户上下文
debezium.transforms: AddUserContext
transforms.AddUserContext.type: com.example.AddUserContextTransform
transforms.AddUserContext.thread.local.user.id: ${currentUser.id}
transforms.AddUserContext.thread.local.user.name: ${currentUser.name}

# 解决方案2：数据库会话变量
SET @cdc_user_id = 1001;
UPDATE orders SET status = 'SHIPPED' WHERE id = 1234;

# 在CDC中通过解析SQL或使用特定字段

八、CDC工具选型指南

推荐组合：生产管理系统

对于MES/ERP系统，推荐：
1. 主要方案：Debezium + Kafka
– 成熟稳定，社区活跃
– 支持多种数据库
– 与微服务架构契合

2. 替代方案：Canal + RocketMQ
– 适合阿里云环境
– 国内文档和案例丰富

3. 轻量方案：Maxwell + RabbitMQ
– 部署简单
– 适合中小型系统

九、结论：CDC在信息化系统中的价值

适合使用CDC的场景

不适合CDC的场景

CDC技术为信息化管理系统提供了强大的实时数据变更捕获能力，但它不是银弹。合理结合CDC、应用层日志和传统ETL，才能构建出既满足实时性需求，又具备完整审计能力的生产数据管理体系。