CDC变化数据捕获——Debezium-Embedded
0. 前言
- CDC(Change Data Capture)是变化数据捕获的意思,可以捕获数据库数据的增加、更新、删除等记录,RedHat的 Debezium 正是这样一款产品。
- 对于数据库数据变化的监听,国内常用的一般是阿里的 Canal ,可惜目前仅支持MySQL。而相对来说,Debezium支持的数据库就非常丰富了,包括:MySQL、PostgreSQL、MongoDB、Oracle、SQL Server、Db2、Cassandra、Vitess。
- 使用Debezium比较麻烦的是,它通常需要用到 Apache Kafka 来保证数据的高容错性和高可靠性。但是,很多应用不需要这样的特性,或者不想连接Kafka,因此Debezium提供了
debezium-embedded
。利用它,可以在Java应用中连接到数据库,直接获取到数据库的CDC事件信息,不用额外进行其他操作。 debezium-embedded
使用起来更为便捷、灵巧,目前国内已有阿里的Flink-CDC在使用它了(查看示例)。可惜的是Debezium在国内相对来说文档不多,其嵌入式使用方式的示例更是难找(官方英文文档有坑- -!!),本文的目的正是要写出此处的示例(MySQL),方便大家参阅。- 官方文档地址: Debezium Engine
1. 配置MySQL主从同步
- 停止MySQL服务
- 修改配置文件,在
[mysqld]
处添加内容如下# 一般情况下,Window修改mysql目录下的my.ini
# 一般情况下,Linux修改/etc/mysql下的my.cnf
[mysqld]
# 添加的部分,server-id随便填
server-id = 12345
log-bin = mysql-bin
# 必须为ROW
binlog_format = ROW
# 必须为FULL,MySQL-5.7后才有该参数
binlog_row_image = FULL
expire_logs_days = 10
- 启动MySQL服务
- root 登入MySQL,查看binlog相关变量配置
SHOW VARIABLES LIKE '%binlog%';
- root 登入MySQL,新增用于同步数据的用户
CREATE USER 'cdc_user' IDENTIFIED BY 'cdc_password';
GRANT SELECT, RELOAD, SHOW DATABASES, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'cdc_user';
- 新用户
cdc_user
登入MySQL,查看主从同步情况SHOW MASTER STATUS;
SHOW SLAVE STATUS;
SHOW BINARY LOGS;
- 建议再建个测试用的库和表(带主键),方便调试,例如Flink-CDC中的
2. Debezium-Embedded 代码开发
2.1 Maven导包
<properties>
<maven.compiler.source>8</maven.compiler.source>
<maven.compiler.target>8</maven.compiler.target>
<version.debezium>1.4.2.Final</version.debezium>
</properties>
<dependencies>
<dependency>
<groupId>io.debezium</groupId>
<artifactId>debezium-api</artifactId>
<version>${version.debezium}</version>
</dependency>
<dependency>
<groupId>io.debezium</groupId>
<artifactId>debezium-embedded</artifactId>
<version>${version.debezium}</version>
</dependency>
<dependency>
<groupId>io.debezium</groupId>
<artifactId>debezium-connector-mysql</artifactId>
<version>${version.debezium}</version>
</dependency>
</dependencies>
2.2 代码-简单示例
import io.debezium.connector.mysql.MySqlConnector;
import io.debezium.engine.ChangeEvent;
import io.debezium.engine.DebeziumEngine;
import io.debezium.engine.format.Json;
import io.debezium.relational.history.FileDatabaseHistory;
import org.apache.kafka.connect.storage.FileOffsetBackingStore;
import java.util.Properties;
import java.util.UUID;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
public class Demo01 {
public static void main(String[] args) {
Properties props = genProps();
DebeziumEngine<ChangeEvent<String, String>> engine = DebeziumEngine.create(Json.class)
.using(props)
.notifying(record -> {
System.out.println("record.key() = " + record.key());
System.out.println("record.value() = " + record.value());
})
.using((success, message, error) -> {
if (!success && error != null) {
System.out.println("----------error------");
System.out.println(message);
}
}).build();
ExecutorService executor = Executors.newSingleThreadExecutor();
executor.execute(engine);
}
private static Properties genProps() {
Properties props = new Properties();
props.setProperty("connector.class", MySqlConnector.class.getCanonicalName());
props.setProperty("database.server.name", "my_server_01");
props.setProperty("database.hostname", "localhost");
props.setProperty("database.port", String.valueOf(3306));
props.setProperty("database.user", "cdc_user");
props.setProperty("database.password", "cdc_password");
props.setProperty("database.serverTimezone", "UTC");
props.setProperty("table.whitelist", "db_inventory_cdc.tb_products_cdc");
props.setProperty("name", "engine");
props.setProperty("key.converter.schemas.enable", "false");
props.setProperty("value.converter.schemas.enable", "false");
props.setProperty("include.schema.changes", "false");
props.setProperty("tombstones.on.delete", "false");
props.setProperty("database.history", FileDatabaseHistory.class.getCanonicalName());
props.setProperty("database.history.store.only.monitored.tables.ddl", "true");
props.setProperty("database.history.file.filename", "./storage/dbhistory.dat");
props.setProperty("database.history.instance.name", UUID.randomUUID().toString());
props.setProperty("database.history.skip.unparseable.ddl", "true");
props.setProperty("offset.storage", FileOffsetBackingStore.class.getCanonicalName());
props.setProperty("offset.storage.file.filename", "./tmp/offsets.dat");
props.setProperty("offset.flush.interval.ms", "6000");
return props;
}
}
2.3 代码-使用Connect.class创建引擎
import io.debezium.embedded.Connect;
……
DebeziumEngine<ChangeEvent<SourceRecord, SourceRecord>> engine = DebeziumEngine.create(Connect.class)
.using(props)
.notifying(record -> {
System.out.println("record.key = " + record.key());
System.out.println("record.value = " + record.value());
System.out.println("record.value.sourcePartition = " + record.value().sourcePartition());
System.out.println("record.value.sourceOffset = " + record.value().sourceOffset());
System.out.println("record.value.key = " + record.value().key());
System.out.println("record.value.value = " + record.value().value());
System.out.println("record.destination = " + record.destination());
})
.using((success, message, error) -> {
}).build();
2.4 代码-批量处理CDC事件
DebeziumEngine<ChangeEvent<String, String>> engine = DebeziumEngine.create(Json.class)
.using(props)
.notifying((records, committer) -> {
for (ChangeEvent<String, String> record : records) {
System.out.println("record.key() = " + record.key());
System.out.println("record.value() = " + record.value());
}
committer.markBatchFinished();
})
.using((success, message, error) -> {
}).build();
2.5 启动代码运行即可
record.key() = {"id":1}
record.value() = {"before":null,"after":{"id":1,"name":"xiaowang","age":34,"address":"beijing"},"source":{"version":"1.4.2.Final","connector":"mysql","name":"my_server_01","ts_ms":0,"snapshot":"true","db":"y1","table":"tb_test_from","server_id":0,"gtid":null,"file":"binlog.000030","pos":115750444,"row":0,"thread":null,"query":null},"op":"r","ts_ms":1679554873026,"transaction":null}
record.key() = {"id":2}
record.value() = {"before":null,"after":{"id":2,"name":"lilei","age":12,"address":"chongqing"},"source":{"version":"1.4.2.Final","connector":"mysql","name":"my_server_01","ts_ms":0,"snapshot":"true","db":"y1","table":"tb_test_from","server_id":0,"gtid":null,"file":"binlog.000030","pos":115750444,"row":0,"thread":null,"query":null},"op":"r","ts_ms":1679554873026,"transaction":null}
record.key() = {"id":3}
record.value() = {"before":null,"after":{"id":3,"name":"meimei","age":19,"address":"chengdu"},"source":{"version":"1.4.2.Final","connector":"mysql","name":"my_server_01","ts_ms":0,"snapshot":"true","db":"y1","table":"tb_test_from","server_id":0,"gtid":null,"file":"binlog.000030","pos":115750444,"row":0,"thread":null,"query":null},"op":"r","ts_ms":1679554873026,"transaction":null}
record.key() = {"id":4}
record.value() = {"before":null,"after":{"id":4,"name":"chunfang","age":18,"address":"chongqing"},"source":{"version":"1.4.2.Final","connector":"mysql","name":"my_server_01","ts_ms":0,"snapshot":"true","db":"y1","table":"tb_test_from","server_id":0,"gtid":null,"file":"binlog.000030","pos":115750444,"row":0,"thread":null,"query":null},"op":"r","ts_ms":1679554873026,"transaction":null}
record.key() = {"id":6}
record.value() = {"before":null,"after":{"id":6,"name":"lili","age":15,"address":"chongqing"},"source":{"version":"1.4.2.Final","connector":"mysql","name":"my_server_01","ts_ms":0,"snapshot":"true","db":"y1","table":"tb_test_from","server_id":0,"gtid":null,"file":"binlog.000030","pos":115750444,"row":0,"thread":null,"query":null},"op":"r","ts_ms":1679554873026,"transaction":null}
record.key() = {"id":7}
record.value() = {"before":null,"after":{"id":7,"name":"xiaoming","age":21,"address":"beijing"},"source":{"version":"1.4.2.Final","connector":"mysql","name":"my_server_01","ts_ms":0,"snapshot":"true","db":"y1","table":"tb_test_from","server_id":0,"gtid":null,"file":"binlog.000030","pos":115750444,"row":0,"thread":null,"query":null},"op":"r","ts_ms":1679554873026,"transaction":null}
record.key() = {"id":8}
record.value() = {"before":null,"after":{"id":8,"name":"wangwu","age":24,"address":"hunan"},"source":{"version":"1.4.2.Final","connector":"mysql","name":"my_server_01","ts_ms":0,"snapshot":"true","db":"y1","table":"tb_test_from","server_id":0,"gtid":null,"file":"binlog.000030","pos":115750444,"row":0,"thread":null,"query":null},"op":"r","ts_ms":1679554873026,"transaction":null}
record.key() = {"id":9}
record.value() = {"before":null,"after":{"id":9,"name":"tom","age":12,"address":"beijing"},"source":{"version":"1.4.2.Final","connector":"mysql","name":"my_server_01","ts_ms":0,"snapshot":"true","db":"y1","table":"tb_test_from","server_id":0,"gtid":null,"file":"binlog.000030","pos":115750444,"row":0,"thread":null,"query":null},"op":"r","ts_ms":1679554873026,"transaction":null}
record.key() = {"id":10}
record.value() = {"before":null,"after":{"id":10,"name":"whitee","age":55,"address":"hebei"},"source":{"version":"1.4.2.Final","connector":"mysql","name":"my_server_01","ts_ms":0,"snapshot":"true","db":"y1","table":"tb_test_from","server_id":0,"gtid":null,"file":"binlog.000030","pos":115750444,"row":0,"thread":null,"query":null},"op":"r","ts_ms":1679554873026,"transaction":null}
record.key() = {"id":11}
record.value() = {"before":null,"after":{"id":11,"name":"meimei","age":31,"address":"hebei"},"source":{"version":"1.4.2.Final","connector":"mysql","name":"my_server_01","ts_ms":0,"snapshot":"true","db":"y1","table":"tb_test_from","server_id":0,"gtid":null,"file":"binlog.000030","pos":115750444,"row":0,"thread":null,"query":null},"op":"r","ts_ms":1679554873026,"transaction":null}
record.key() = {"id":12}
record.value() = {"before":null,"after":{"id":12,"name":"hehe","age":13,"address":"nanjing"},"source":{"version":"1.4.2.Final","connector":"mysql","name":"my_server_01","ts_ms":0,"snapshot":"true","db":"y1","table":"tb_test_from","server_id":0,"gtid":null,"file":"binlog.000030","pos":115750444,"row":0,"thread":null,"query":null},"op":"r","ts_ms":1679554873026,"transaction":null}
record.key() = {"id":13}
record.value() = {"before":null,"after":{"id":13,"name":"liyang","age":16,"address":"beijing"},"source":{"version":"1.4.2.Final","connector":"mysql","name":"my_server_01","ts_ms":0,"snapshot":"true","db":"y1","table":"tb_test_from","server_id":0,"gtid":null,"file":"binlog.000030","pos":115750444,"row":0,"thread":null,"query":null},"op":"r","ts_ms":1679554873026,"transaction":null}
record.key() = {"id":14}
record.value() = {"before":null,"after":{"id":14,"name":"huahua","age":21,"address":"shandong"},"source":{"version":"1.4.2.Final","connector":"mysql","name":"my_server_01","ts_ms":0,"snapshot":"true","db":"y1","table":"tb_test_from","server_id":0,"gtid":null,"file":"binlog.000030","pos":115750444,"row":0,"thread":null,"query":null},"op":"r","ts_ms":1679554873026,"transaction":null}
record.key() = {"id":15}
record.value() = {"before":null,"after":{"id":15,"name":"zhangsan","age":25,"address":"hubei"},"source":{"version":"1.4.2.Final","connector":"mysql","name":"my_server_01","ts_ms":0,"snapshot":"last","db":"y1","table":"tb_test_from","server_id":0,"gtid":null,"file":"binlog.000030","pos":115750444,"row":0,"thread":null,"query":null},"op":"r","ts_ms":1679554873026,"transaction":null}
// 删除了一条带IP的日志
record.key() = {"id":14}
record.value() = {"before":{"id":14,"name":"huahua","age":21,"address":"shandong"},"after":{"id":14,"name":"huahua","age":10,"address":"shandong"},"source":{"version":"1.4.2.Final","connector":"mysql","name":"my_server_01","ts_ms":1679554893000,"snapshot":"false","db":"y1","table":"tb_test_from","server_id":1,"gtid":null,"file":"binlog.000030","pos":115750677,"row":0,"thread":26773,"query":null},"op":"u","ts_ms":1679554893186,"transaction":null}
record.key() = {"id":16}
record.value() = {"before":null,"after":{"id":16,"name":"alice","age":18,"address":"dalian"},"source":{"version":"1.4.2.Final","connector":"mysql","name":"my_server_01","ts_ms":1679554929000,"snapshot":"false","db":"y1","table":"tb_test_from","server_id":1,"gtid":null,"file":"binlog.000030","pos":115752466,"row":0,"thread":26773,"query":null},"op":"c","ts_ms":1679554929396,"transaction":null}
record.key() = {"id":6}
record.value() = {"before":{"id":6,"name":"lili","age":15,"address":"chongqing"},"after":null,"source":{"version":"1.4.2.Final","connector":"mysql","name":"my_server_01","ts_ms":1679554936000,"snapshot":"false","db":"y1","table":"tb_test_from","server_id":1,"gtid":null,"file":"binlog.000030","pos":115752780,"row":0,"thread":26773,"query":null},"op":"d","ts_ms":1679554936366,"transaction":null}