mysql canal cdc_debezium关于cdc的使用(上)

最新推荐文章于 2023-07-24 11:44:22 发布
最新推荐文章于 2023-07-24 11:44:22 发布
阅读量254 收藏
点赞数
文章标签： mysql canal cdc
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_32073375/article/details/113289259
版权

简介

debezium是一个为了捕获数据变更(cdc)的开源的分布式平台。启动并指向数据库，当其他应用对此数据库执行inserts、updates、delete操作时，此应用快速得到响应。debezium是持久化和快速响应的，因此你的应用可以快速响应且不会丢失任意一条事件。debezium记录是数据库表的行级别的变更事件。同时debezium是构建在kafka之上的，同时与kafka深度耦合，所以提供kafka connector来使用，debezium sink。支持的数据库有mysql、MongoDB、PostgreSQL、Oracle、SQL server。本篇以mysql作为数据源来实现功能，监听msyql的binlog，还需要修改。当前版本是0.9.5.Final，0.10版本正在开发中。

配置

本篇文章主要使用Embedding形式监听事件，并同步更新到数据库。

下篇主要使用kafka connector来同步更新到数据库。

mysql需要如下开启binlog。但是如果使用的是debezium/mysql镜像，自动已经配置好了。

log-bin=mysql-bin #添加这一行就ok

binlog-format=ROW #选择row模式

server_id=1 #配置mysql replaction需要定义，不能和canal的slaveId重复

Tutorial

先来一个效果，主要是配置kafka connector来获取debezium事件记录。需要3个服务，zookeeper、kakfa和debezium connector。这里使用docker来启动的，所以需要先按照docker。

启动zookeeper

docker run -it --rm --name zookeeper -p 2181:2181 -p 2888:2888 -p 3888:3888 debezium/zookeeper:0.9

启动kafka

docker run -it --rm --name kafka -p 9092:9092 --link zookeeper:zookeeper debezium/kafka:0.9

启动mysql

docker run -it --rm --name mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=debezium -e MYSQL_USER=mysqluser -e MYSQL_PASSWORD=mysqlpw debezium/example-mysql:0.9

启动kafka connect

docker run -it --rm --name connect -p 8083:8083 -e GROUP_ID=1 -e CONFIG_STORAGE_TOPIC=my_connect_configs -e OFFSET_STORAGE_TOPIC=my_connect_offsets -e STATUS_STORAGE_TOPIC=my_connect_statuses --link zookeeper:zookeeper --link kafka:kafka --link mysql:mysql debezium/connect:0.9

通过connect的http请求创建debezium connector

curl -i -X POST -H "Accept:application/json" -H "Content-Type:application/json" localhost:8083/connectors/ -d '{ "name": "inventory-connector", "config": { "connector.class": "io.debezium.connector.mysql.MySqlConnector", "tasks.max": "1", "database.hostname": "mysql", "database.port": "3306", "database.user": "debezium", "database.password": "dbz", "database.server.id": "184054", "database.server.name": "dbserver1", "database.whitelist": "inventory", "database.history.kafka.bootstrap.servers": "kafka:9092", "database.history.kafka.topic": "dbhistory.inventory" } }'

mysql客户端操作

通过invertory数据库了的任一表的数据

创建监听可以查看debezium事件记录

docker run -it --name watcher --rm --link zookeeper:zookeeper --link kafka:kafka debezium/kafka:0.9 watch-topic -a -k dbserver1.inventory.customers

内嵌式

这里主要使用内嵌式的方式获取cdc事件而不需要使用kafka，直接消费debezium事件流。场景是在某一个mysql数据库里的table发生变更，把变更同步到另一mysql数据库。本次使用的是监听inventory数据库并将数据同步到inventory_back。

debezium配置

connector.class=io.debezium.connector.mysql.MySqlConnector

offset.storage=org.apache.kafka.connect.storage.FileOffsetBackingStore

offset.storage.file.filename=offset.dat

offset.flush.interval.ms=60000

name=debezium-kafka-source

database.hostname=localhost

database.port=3306

database.user=debezium

database.password=dbz

#database.dbname=inventory

database.whitelist=inventory

#database.whitelist=inventory,inventory_back

server.id=184054

database.server.name=dbserver1

#transforms=unwrap

#transforms.unwrap.type=io.debezium.transforms.UnwrapFromEnvelope

#transforms.unwrap.drop.tombstones=false

database.history=io.debezium.relational.history.FileDatabaseHistory

database.history.file.filename=dbhistory.dat

属性和convert配置

@Slf4j

@Configuration

public class DebeziumEmbeddedAutoConfiguration {

@Bean

public Properties embeddedProperties() {

Properties propConfig = new Properties();

try(InputStream propsInputStream = getClass().getClassLoader().getResourceAsStream("config.properties")) {

propConfig.load(propsInputStream);

} catch (IOException e) {

log.error("Couldn't load properties", e);

}

PropertyLoader.loadEnvironmentValues(propConfig);

return propConfig;

}

@Bean

public io.debezium.config.Configuration embeddedConfig(Properties embeddedProperties) {

return io.debezium.config.Configuration.from(embeddedProperties);

}

@Bean

public JsonConverter keyConverter(io.debezium.config.Configuration embeddedConfig) {

JsonConverter converter = new JsonConverter();

converter.configure(embeddedConfig.asMap(), true);

return converter;

}

@Bean

public JsonConverter valueConverter(io.debezium.config.Configuration embeddedConfig) {

JsonConverter converter = new JsonConverter();

converter.configure(embeddedConfig.asMap(), false);

return converter;

}

}

同步DDL和DML

这里主要是利用CommandLineRunner特性，启动debezium的EmbeddedEngine引擎，获取到cdc事件后由handleRecord处理DDL和DML，需要去解析cdc的事件SourceRecord的key和value。

@Slf4j

@Order(2)

@Component

public class DebeziumEmbeddedRunner implements CommandLineRunner {

@Autowired

private io.debezium.config.Configuration embeddedConfig;

@Autowired

private JdbcTemplate jdbcTemplate;

@Autowired

private NamedParameterJdbcTemplate namedTemplate;

@Autowired

private JsonConverter keyConverter;

@Autowired

private JsonConverter valueConverter;

@Override

public void run(String... args) throws Exception {

EmbeddedEngine engine = EmbeddedEngine.create()

.using(embeddedConfig)

.using(this.getClass().getClassLoader())

.using(Clock.SYSTEM)

.notifying(this::handleRecord)

.build();

ExecutorService executor = Executors.newSingleThreadExecutor();

executor.execute(engine);

shutdownHook(engine);

awaitTermination(executor);

}

/**

* For every record this method will be invoked.

*/

private void handleRecord(SourceRecord record) {

logRecord(record);

Struct payload = (Struct) record.value();

if (Objects.isNull(payload)) {

return;

}

String table = Optional.ofNullable(DebeziumRecordUtils.getRecordStructValue(payload, "source"))

.map(s->s.getString("table")).orElse(null);

// // 处理数据DML

Envelope.Operation operation = DebeziumRecordUtils.getOperation(payload);

if (Objects.nonNull(operation)) {

Struct key = (Struct) record.key();

handleDML(key, payload, table, operation);

return;

}

//

// // 处理结构DDL

String ddl = getDDL(payload);

if (StringUtils.isNotBlank(ddl)) {

handleDDL(ddl);

}

}

private String getDDL(Struct payload) {

String ddl = DebeziumRecordUtils.getDDL(payload);

if (StringUtils.isBlank(ddl)) {

return null;

}

String db = DebeziumRecordUtils.getDatabaseName(payload);

if (StringUtils.isBlank(db)) {

db = embeddedConfig.getString(MySqlConnectorConfig.DATABASE_WHITELIST);

}

ddl = ddl.replace(db + ".", "");

ddl = ddl.replace("`" + db + "`.", "");

return ddl;

}

/**

* 执行数据库ddl语句

*

* @param ddl

*/

private void handleDDL(String ddl) {

log.info("ddl语句 : {}", ddl);

try {

jdbcTemplate.execute(ddl);

} catch (Exception e) {

log.error("数据库操作DDL语句失败，", e);

}

}

/**

* 处理insert,update,delete等DML语句

*

* @param key 表主键修改事件结构

* @param payload 表正文响应

* @param table 表名

* @param operation DML操作类型

*/

private void handleDML(Struct key, Struct payload, String table, Envelope.Operation operation) {

AbstractDebeziumSqlProvider provider = DebeziumSqlProviderFactory.getProvider(operation);

if (Objects.isNull(provider)) {

log.error("没有找到sql处理器提供者.");

return;

}

String sql = provider.getSql(key, payload, table);

if (StringUtils.isBlank(sql)) {

log.error("找不到sql.");

return;

}

try {

log.info("dml语句 : {}", sql);

namedTemplate.update(sql, provider.getSqlParameterMap());

} catch (Exception e) {

log.error("数据库DML操作失败，", e);

}

}

/**

* 打印消息

*

* @param record

*/

private void logRecord(SourceRecord record) {

final byte[] payload = valueConverter.fromConnectData("dummy", record.valueSchema(), record.value());

final byte[] key = keyConverter.fromConnectData("dummy", record.keySchema(), record.key());

log.info("Publishing Topic --> {}", record.topic());

log.info("Key --> {}", new String(key));

log.info("Payload --> {}", new String(payload));

}

private void shutdownHook(EmbeddedEngine engine) {

Runtime.getRuntime().addShutdownHook(new Thread(() -> {

log.info("Requesting embedded engine to shut down");

engine.stop();

}));

}

private void awaitTermination(ExecutorService executor) {

try {

while (!executor.awaitTermination(10L, TimeUnit.SECONDS)) {

log.info("Waiting another 10 seconds for the embedded engine to shut down");

}

} catch (InterruptedException e) {

Thread.interrupted();

}

}

}

provider和table字段解析器太多，这里就不在一一列出来了，如下图所示，支持mysql大部分字段类型。如果有需要的可以关注微信公众号或者邮件以及评论回复。

661030e0336bfee92bc721e116ce6917.png

测试表结构

CREATE TABLE `demo` (

`id` int(10) NOT NULL AUTO_INCREMENT,

`bigint_id` bigint(20) NOT NULL,

`var_name` varchar(255) NOT NULL,

`ex_tinyint` tinyint(4) DEFAULT NULL,

`ex_char` char(255) DEFAULT NULL,

`ex_json` json DEFAULT NULL COMMENT '水电费',

`ex_text` text,

`ex_year` year(4) DEFAULT NULL,

`ex_time` time DEFAULT NULL,

`ex_date` date DEFAULT NULL,

`ex_datetime` datetime DEFAULT NULL,

`ex_timestamp` timestamp NULL DEFAULT NULL,

`ex_blob` blob,

`ex_tinyblob` tinyblob,

`ex_binary` binary(255) DEFAULT NULL,

`ex_double` double(10,4) DEFAULT NULL,

`ex_float` float(10,2) DEFAULT NULL,

`ex_decimal` decimal(10,2) DEFAULT NULL,

`ex_numeric` decimal(10,4) DEFAULT NULL,

`ex_real` double(10,4) DEFAULT NULL,

`ex_bit` bit(1) DEFAULT NULL,

`ex_enum` enum('123','@@','22','水电费') DEFAULT '123',

`ex_set` set('a','b','c','d') DEFAULT NULL,

`ex_geometry` geometry DEFAULT NULL,

`ex_point` point DEFAULT NULL,

`ex_linestring` linestring DEFAULT NULL,

`ex_polygon` polygon DEFAULT NULL,

`ex_geometrycollection` geometrycollection DEFAULT NULL,

`ex_multipoint` multipoint DEFAULT NULL,

PRIMARY KEY (`id`,`bigint_id`,`var_name`) USING BTREE

) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

结果

DDL事件

可以看出将数据库表的bigint_id字段长度改为21，监听到事件后：执行了ddl语句，inventory_back库中的demo表的bigint_id字段长度改为21了。

Publishing Topic --> dbserver1

2019-06-24 16:22:21.230 INFO 14995 --- [pool-1-thread-1] c.e.embedded.DebeziumEmbeddedRunner : Key --> {"schema":{"type":"struct","fields":[{"type":"string","optional":false,"field":"databaseName"}],"optional":false,"name":"io.debezium.connector.mysql.SchemaChangeKey"},"payload":{"databaseName":"inventory"}}

2019-06-24 16:22:21.230 INFO 14995 --- [pool-1-thread-1] c.e.embedded.DebeziumEmbeddedRunner : Payload --> {"schema":{"type":"struct","fields":[{"type":"struct","fields":[{"type":"string","optional":true,"field":"version"},{"type":"string","optional":true,"field":"connector"},{"type":"string","optional":false,"field":"name"},{"type":"int64","optional":false,"field":"server_id"},{"type":"int64","optional":false,"field":"ts_sec"},{"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":"boolean","optional":true,"default":false,"field":"snapshot"},{"type":"int64","optional":true,"field":"thread"},{"type":"string","optional":true,"field":"db"},{"type":"string","optional":true,"field":"table"},{"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":"0.9.3.Final","connector":"mysql","name":"dbserver1","server_id":223344,"ts_sec":1561364540,"gtid":null,"file":"mysql-bin.000006","pos":22530,"row":0,"snapshot":false,"thread":null,"db":null,"table":null,"query":null},"databaseName":"inventory","ddl":"ALTER TABLE `inventory`.`demo` \nMODIFY COLUMN `bigint_id` bigint(21) NOT NULL AFTER `id`"}}

2019-06-24 16:22:21.230 ERROR 14995 --- [pool-1-thread-1] c.example.embedded.DebeziumRecordUtils : not find op field.

2019-06-24 16:22:21.231 INFO 14995 --- [pool-1-thread-1] c.e.embedded.DebeziumEmbeddedRunner : ddl语句 : ALTER TABLE `demo`

MODIFY COLUMN `bigint_id` bigint(21) NOT NULL AFTER `id`

3c8b4b71d0bfbbc4df4169df9e4e1d56.gif

DML的insert事件

在inventory库中的demo新增一条记录后有如下日志记录，能查看到topic，key，payload以及dml的insert语句。结果会把数据同步到inventory_back库中的demo。

2019-06-24 16:27:14.735 INFO 14995 --- [pool-1-thread-1] i.debezium.connector.mysql.BinlogReader : 1 records sent during previous 00:04:53.506, last recorded offset: {ts_sec=1561364834, file=mysql-bin.000006, pos=23002, row=1, server_id=223344, event=2}

2019-06-24 16:27:14.737 INFO 14995 --- [pool-1-thread-1] c.e.embedded.DebeziumEmbeddedRunner : Publishing Topic --> dbserver1.inventory.demo

2019-06-24 16:27:14.737 INFO 14995 --- [pool-1-thread-1] c.e.embedded.DebeziumEmbeddedRunner : Key --> {"schema":{"type":"struct","fields":[{"type":"int32","optional":false,"field":"id"},{"type":"int64","optional":false,"field":"bigint_id"},{"type":"string","optional":false,"field":"var_name"}],"optional":false,"name":"dbserver1.inventory.demo.Key"},"payload":{"id":2,"bigint_id":1,"var_name":"老王"}}

2019-06-24 16:27:14.738 INFO 14995 --- [pool-1-thread-1] c.e.embedded.DebeziumEmbeddedRunner : Payload --> {"schema":{"type":"struct","fields":[{"type":"struct","fields":[{"type":"int32","optional":false,"field":"id"},{"type":"int64","optional":false,"field":"bigint_id"},{"type":"string","optional":false,"field":"var_name"},{"type":"int16","optional":true,"field":"ex_tinyint"},{"type":"string","optional":true,"field":"ex_char"},{"type":"string","optional":true,"name":"io.debezium.data.Json","version":1,"field":"ex_json"},{"type":"string","optional":true,"field":"ex_text"},{"type":"int32","optional":true,"name":"io.debezium.time.Year","version":1,"field":"ex_year"},{"type":"int64","optional":true,"name":"io.debezium.time.MicroTime","version":1,"field":"ex_time"},{"type":"int32","optional":true,"name":"io.debezium.time.Date","version":1,"field":"ex_date"},{"type":"int64","optional":true,"name":"io.debezium.time.Timestamp","version":1,"field":"ex_datetime"},{"type":"string","optional":true,"name":"io.debezium.time.ZonedTimestamp","version":1,"field":"ex_timestamp"},{"type":"bytes","optional":true,"field":"ex_blob"},{"type":"bytes","optional":true,"field":"ex_tinyblob"},{"type":"bytes","optional":true,"field":"ex_binary"},{"type":"double","optional":true,"field":"ex_double"},{"type":"double","optional":true,"field":"ex_float"},{"type":"bytes","optional":true,"name":"org.apache.kafka.connect.data.Decimal","version":1,"parameters":{"scale":"2","connect.decimal.precision":"10"},"field":"ex_decimal"},{"type":"bytes","optional":true,"name":"org.apache.kafka.connect.data.Decimal","version":1,"parameters":{"scale":"4","connect.decimal.precision":"10"},"field":"ex_numeric"},{"type":"double","optional":true,"field":"ex_real"},{"type":"boolean","optional":true,"field":"ex_bit"},{"type":"string","optional":true,"name":"io.debezium.data.Enum","version":1,"parameters":{"allowed":"123,22"},"default":"123","field":"ex_enum"},{"type":"string","optional":true,"name":"io.debezium.data.EnumSet","version":1,"parameters":{"allowed":"a,b,c,d"},"field":"ex_set"},{"type":"struct","fields":[{"type":"bytes","optional":false,"field":"wkb"},{"type":"int32","optional":true,"field":"srid"}],"optional":true,"name":"io.debezium.data.geometry.Geometry","version":1,"doc":"Geometry","field":"ex_geometry"},{"type":"struct","fields":[{"type":"double","optional":false,"field":"x"},{"type":"double","optional":false,"field":"y"},{"type":"bytes","optional":true,"field":"wkb"},{"type":"int32","optional":true,"field":"srid"}],"optional":true,"name":"io.debezium.data.geometry.Point","version":1,"doc":"Geometry (POINT)","field":"ex_point"},{"type":"struct","fields":[{"type":"bytes","optional":false,"field":"wkb"},{"type":"int32","optional":true,"field":"srid"}],"optional":true,"name":"io.debezium.data.geometry.Geometry","version":1,"doc":"Geometry","field":"ex_linestring"},{"type":"struct","fields":[{"type":"bytes","optional":false,"field":"wkb"},{"type":"int32","optional":true,"field":"srid"}],"optional":true,"name":"io.debezium.data.geometry.Geometry","version":1,"doc":"Geometry","field":"ex_polygon"},{"type":"struct","fields":[{"type":"bytes","optional":false,"field":"wkb"},{"type":"int32","optional":true,"field":"srid"}],"optional":true,"name":"io.debezium.data.geometry.Geometry","version":1,"doc":"Geometry","field":"ex_geometrycollection"},{"type":"struct","fields":[{"type":"bytes","optional":false,"field":"wkb"},{"type":"int32","optional":true,"field":"srid"}],"optional":true,"name":"io.debezium.data.geometry.Geometry","version":1,"doc":"Geometry","field":"ex_multipoint"}],"optional":true,"name":"dbserver1.inventory.demo.Value","field":"before"},{"type":"struct","fields":[{"type":"int32","optional":false,"field":"id"},{"type":"int64","optional":false,"field":"bigint_id"},{"type":"string","optional":false,"field":"var_name"},{"type":"int16","optional":true,"field":"ex_tinyint"},{"type":"string","optional":true,"field":"ex_char"},{"type":"string","optional":true,"name":"io.debezium.data.Json","version":1,"field":"ex_json"},{"type":"string","optional":true,"field":"ex_text"},{"type":"int32","optional":true,"name":"io.debezium.time.Year","version":1,"field":"ex_year"},{"type":"int64","optional":true,"name":"io.debezium.time.MicroTime","version":1,"field":"ex_time"},{"type":"int32","optional":true,"name":"io.debezium.time.Date","version":1,"field":"ex_date"},{"type":"int64","optional":true,"name":"io.debezium.time.Timestamp","version":1,"field":"ex_datetime"},{"type":"string","optional":true,"name":"io.debezium.time.ZonedTimestamp","version":1,"field":"ex_timestamp"},{"type":"bytes","optional":true,"field":"ex_blob"},{"type":"bytes","optional":true,"field":"ex_tinyblob"},{"type":"bytes","optional":true,"field":"ex_binary"},{"type":"double","optional":true,"field":"ex_double"},{"type":"double","optional":true,"field":"ex_float"},{"type":"bytes","optional":true,"name":"org.apache.kafka.connect.data.Decimal","version":1,"parameters":{"scale":"2","connect.decimal.precision":"10"},"field":"ex_decimal"},{"type":"bytes","optional":true,"name":"org.apache.kafka.connect.data.Decimal","version":1,"parameters":{"scale":"4","connect.decimal.precision":"10"},"field":"ex_numeric"},{"type":"double","optional":true,"field":"ex_real"},{"type":"boolean","optional":true,"field":"ex_bit"},{"type":"string","optional":true,"name":"io.debezium.data.Enum","version":1,"parameters":{"allowed":"123,22"},"default":"123","field":"ex_enum"},{"type":"string","optional":true,"name":"io.debezium.data.EnumSet","version":1,"parameters":{"allowed":"a,b,c,d"},"field":"ex_set"},{"type":"struct","fields":[{"type":"bytes","optional":false,"field":"wkb"},{"type":"int32","optional":true,"field":"srid"}],"optional":true,"name":"io.debezium.data.geometry.Geometry","version":1,"doc":"Geometry","field":"ex_geometry"},{"type":"struct","fields":[{"type":"double","optional":false,"field":"x"},{"type":"double","optional":false,"field":"y"},{"type":"bytes","optional":true,"field":"wkb"},{"type":"int32","optional":true,"field":"srid"}],"optional":true,"name":"io.debezium.data.geometry.Point","version":1,"doc":"Geometry (POINT)","field":"ex_point"},{"type":"struct","fields":[{"type":"bytes","optional":false,"field":"wkb"},{"type":"int32","optional":true,"field":"srid"}],"optional":true,"name":"io.debezium.data.geometry.Geometry","version":1,"doc":"Geometry","field":"ex_linestring"},{"type":"struct","fields":[{"type":"bytes","optional":false,"field":"wkb"},{"type":"int32","optional":true,"field":"srid"}],"optional":true,"name":"io.debezium.data.geometry.Geometry","version":1,"doc":"Geometry","field":"ex_polygon"},{"type":"struct","fields":[{"type":"bytes","optional":false,"field":"wkb"},{"type":"int32","optional":true,"field":"srid"}],"optional":true,"name":"io.debezium.data.geometry.Geometry","version":1,"doc":"Geometry","field":"ex_geometrycollection"},{"type":"struct","fields":[{"type":"bytes","optional":false,"field":"wkb"},{"type":"int32","optional":true,"field":"srid"}],"optional":true,"name":"io.debezium.data.geometry.Geometry","version":1,"doc":"Geometry","field":"ex_multipoint"}],"optional":true,"name":"dbserver1.inventory.demo.Value","field":"after"},{"type":"struct","fields":[{"type":"string","optional":true,"field":"version"},{"type":"string","optional":true,"field":"connector"},{"type":"string","optional":false,"field":"name"},{"type":"int64","optional":false,"field":"server_id"},{"type":"int64","optional":false,"field":"ts_sec"},{"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":"boolean","optional":true,"default":false,"field":"snapshot"},{"type":"int64","optional":true,"field":"thread"},{"type":"string","optional":true,"field":"db"},{"type":"string","optional":true,"field":"table"},{"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"}],"optional":false,"name":"dbserver1.inventory.demo.Envelope"},"payload":{"before":null,"after":{"id":2,"bigint_id":1,"var_name":"老王","ex_tinyint":1,"ex_char":"a","ex_json":"{\"abc\":123}","ex_text":"ert","ex_year":2019,"ex_time":59224000000,"ex_date":null,"ex_datetime":null,"ex_timestamp":null,"ex_blob":null,"ex_tinyblob":null,"ex_binary":null,"ex_double":null,"ex_float":null,"ex_decimal":null,"ex_numeric":null,"ex_real":null,"ex_bit":null,"ex_enum":"123","ex_set":null,"ex_geometry":null,"ex_point":null,"ex_linestring":null,"ex_polygon":null,"ex_geometrycollection":null,"ex_multipoint":null},"source":{"version":"0.9.3.Final","connector":"mysql","name":"dbserver1","server_id":223344,"ts_sec":1561364834,"gtid":null,"file":"mysql-bin.000006","pos":23194,"row":0,"snapshot":false,"thread":9,"db":"inventory","table":"demo","query":null},"op":"c","ts_ms":1561364834477}}

2019-06-24 16:27:14.738 INFO 14995 --- [pool-1-thread-1] c.e.embedded.DebeziumEmbeddedRunner : dml语句 : insert into demo values (:id,:bigint_id,:var_name,:ex_tinyint,:ex_char,:ex_json,:ex_text,:ex_year,:ex_time,:ex_date,:ex_datetime,:ex_timestamp,:ex_blob,:ex_tinyblob,:ex_binary,:ex_double,:ex_float,:ex_decimal,:ex_numeric,:ex_real,:ex_bit,:ex_enum,:ex_set,:ex_geometry,:ex_point,:ex_linestring,:ex_polygon,:ex_geometrycollection,:ex_multipoint)

2e455ba8d632c90a86ca7452cdb983f2.png

DML的update事件

在inventory库中的demo修改刚刚新增的记录后有如下日志记录，能查看到topic，key，payload以及先delete再insert语句。结果会把数据同步到inventory_back库中的demo。

3e46968f3213ab2d97ad246e19316bbc.png

e027d543bfc2c1ebfd5fa77be9d5568d.png

DML的delete事件

在inventory库中的demo修改刚刚修改的记录给删除掉后有如下日志记录，能查看到topic，key，payload以及先delete语句。结果会把数据同步到inventory_back库中的demo将其删掉。这里有2个事件，第二条事件是一种标致，这里不处理。

日志：

c98996e1162b2806eb64ca09ec60ef49.png

59cb10b0af2c425b1518b87c6b80ccc5.png

参考

欢迎

关注了解最新动态更新

028afb5c7146c441ec3d187d9cfed106.png

又是一个小学生
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
debezium+kafka实现mysql数据库的双向数据增量同步
lxs的博客
04-16 4764
一.部署前需要准备的东西 jdk：1.8.0_181 confluent：5.4.1 debezium：1.1.0.Final 修改数据库配置文件my.cnf 或 my.inf server-id=11 log-bin=mysql-bin #添加这一行就ok binlog-format=ROW #选择row模式 binlog_r...
基于Kafka Debezium Confluent实现MySql实时同步到ES
zj007ok的专栏
04-27 1889
实现目标 基于MySql的Binlog实现Mysql表实时同步到ES。 实现方案 1、总体技术方案基于Kafka的Connect技术。具体技术内容，不做介绍，网上有相关文章，本文章主要解决如何配置，因为网上大部分配置都是只能简单运行，只能插入，不能实现数据按照主键进行更新和删除同步的。 2、基于debezium的MySqlConnecto...
数据同步工具 debezium
whohow20094702的博客
05-29 2328
## 简介 ```aidl Debezium是用于捕获变更数据的开源分布式平台。可以响应数据库的所有插入，更新和删除操作。Debezium依赖于kafka上，所以在安装Debezium时需要提前 安装好Zookeeper，Kafka，以及Kakfa Connect。 与canal、maxwell区别 1.canal、maxwell需要在my.cnf配置文件注册需要同步的库，devezium无需声明，在注册同步链路是，下发对谁同步，就抓取谁的binlog,只需要权限到位即可工作； 2.canal 元数据是.
debezium 最佳参数实践v1
一名数据库爱好者的专栏
07-24 217
【代码】debezium 最佳参数实践。
debezium-datetime-converter:Debezium自定义转换器用于处理mysql datetime类型的问题
03-15
debezium-datetime-converter Debezium自定义转换器用于处理mysql datetime类型的问题 MySQL的 Binlog连接器 德比兹 德比兹图式 日期（2021-01-28） 本地日期（2021-01-28） 整数（18655） 碘化氢时间日期 时间（17:29:04） 期间（PT17H29M4S） 长（62944000000） （（持续时间）数据）.toNanos（）/ 1_000 时间戳记（2021-01-28 17:29:04） ZonedDateTime （2021-01-28T09：29：04Z） 细绳（2021-01-28T09：29：04Z） io.debezium.ZoneedTimestamp 约会时间（2021-01-28 17:29:04） LocalDateTime （2021-01-28T17：29
MySQL-CDC的相关学习文档.zip
最新发布
02-22
2. **CDC工具**：有许多开源和商业工具支持MySQL CDC，例如Debezium、Oracle GoldenGate、Maxwell和Canal。这些工具可以监听MySQL Binlog，解析日志事件，并将其转换为可消费的格式。 3. **数据流处理**：一旦捕获...
canal.deployer_1.1.2.zip
02-24
Canal Deployer是一款由阿里巴巴开源的高效、稳定、轻量级的MySQL数据库变更数据捕获（CDC，Change Data Capture）工具。它主要用于实时地从MySQL数据库中抽取并同步数据，是构建数据中间件系统的重要组件。在本篇中...
CanalSharp：阿里巴巴MySQL数据库Binlog订阅和使用者组件Canal的.NET客户端
02-04
在数据库中，更改数据捕获（ CDC ）是一组软件设计模式，用于确定和跟踪已更改的数据，骑士可以使用已更改的数据来采取措施，Canal便是mysql数据库的一种cdc组件。 快速入门 安装 Install-Package CanalSharp 初始化...
Pulsar集群 监听mysql8的binlog java代码
04-04
综上所述，使用Java和Debezium监听MySQL8的binlog，并将变更事件推送到Pulsar集群，是一项涉及多个技术栈的复杂任务。正确实施可以实现高效、实时的数据流动，有助于构建高性能的数据驱动应用。
canal技术调研2.0.docx
06-02
Canal 是阿里巴巴开源的一款高效的数据同步工具，它主要专注于MySQL数据库的变更数据捕获（CDC, Change Data Capture）并提供数据同步的能力。在IT领域，Canal 被广泛应用于异构数据库同步、多级索引更新以及业务...
Debezium引擎
cnhome的专栏
06-20 2512
Debezium引擎 通常，通过将Debezium连接器部署到Kafka Connect服务并配置一个或多个连接器来监视上游数据库并为它们在上游数据库中看到的所有更改生成数据更改事件来进行操作。这些数据更改事件被写入Kafka，在那里它们可以被许多不同的应用程序独立使用。Kafka Connect具有出色的容错性和可扩展性，因为它作为分布式服务运行，并确保所有已注册和配置的连接器始终处于运行状态。例如，即使集群中的一个Kafka Connect端点发生故障，其余的Kafka Connect端点也将重新启动以
Java/scala使用内置嵌入式embedded debezium全量和增量同步Mysql binlog数据
Bulut0907
07-25 3982
目录1. 背景2. 添加依赖3. 同步代码 1. 背景 Debezium通常基于Kafka启动一个Kafka Connect服务，之后可以向Debezium提交Mysql、PostgresSQL等connector任务进行同步数据，数据被保存到Kafka中。分布式的Kafka Connect服务能提供容错性和可拓展性 但是我们有时不想部署一套Kafka集群和Debezium的Kafka Connect服务。而是通过一种更轻量级的方式，将Debezium的Mysql、PostgresSQL等connector
使用kafka connect结合debezium的方式采集oracle数据，日期类型时区问题处理
雾岛与鲸的博客
04-26 4591
环境配置: 1、kafka 2.8.0 2、debezium 1.9.0 3、oracle 11g 使用kafka connect结合debezium的方式采集oracle数据, 参考我的上篇文章 https://blog.csdn.net/qq_36039236/article/details/124236918?spm=1001.2014.3001.5501 本篇主要解决使用 debezium 采集数据过程中，日期类型的数据比正常时间多8个小时的问题，废话少说，直接上干货。 我的解决办法是直接调整 de
数仓实时数据同步debezium实践踩坑
qq_39860344的博客
09-01 1522
数仓实时数据同步debezium实践踩坑 在mysql的测试过程中 日期解析报错。因为mysql的日期时间可以随便写，而java必须是1970-01-01 00:00:00及以后的。生产上很多默认写的0000-01-01 01:01:01。需要修改源码匹配无效时间全部改为1970-01-01 00:00:00。 时区差异，内部转换写死使用的utc，无法更改。并且datetime被选为timestamp数值,timestamp被转为timestamp_with_zone字符串,time被转为微秒数，date
datetime 时间类型处理
mywpython的博客
07-05 166
Debezium-Flink-Hudi：实时流式CDC
ZacksTang的博客
05-25 1880
目录 1. 什么是Debezium 2. Debezium常规使用架构 3. 部署Debezium 3.1. AWS EKS部署Kafka Connector 4. Flink 消费Debezium 类型消息 5. 写入Hudi表 5.1. 依赖包问题 5.2. Flink 版本问题 6. Flink消费Debezium与写入Hudi测试 7. 验证hudi表 8. 总结 References 1. 什么是Debezium Debezium是一个开源的分布式平台，用于捕捉变化数据
Flink CDC 原理及生产实践
微信搜：import_bigdata，大数据领域硬核原创作者
12-29 481
点击上方蓝色字体，选择“设为星标”回复”资源“获取更多资源大数据技术与架构点击右侧关注，大数据开发领域最强公众号！大数据真好玩点击右侧关注，大数据真好玩！MySQL CDC连接器允许从M...
debezium mysql_Debezium初试
weixin_29281941的博客
03-03 683
向导地址1.启动kafka connector$ docker run -it --rm --name connect -p 8083:8083 -e GROUP_ID=1 -e CONFIG_STORAGE_TOPIC=my_connect_configs -e OFFSET_STORAGE_TOPIC=my_connect_offsets -e STATUS_STORAGE_TOPIC=my_...
mysql cdc增量同步数据_CDC 实现数据同步，增量更新
05-17
在MySQL中，可以使用MySQL自带的binlog日志来实现CDC。 binlog日志是MySQL记录数据库操作的日志，包括插入、更新、删除等操作。通过解析binlog日志，可以获取到数据库的增量更新数据，从而实现数据同步。 以下是...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值