tencent cloud

PrevNext

文档反馈

请输入关键字

Recent Pages

文档
消息队列 CKafka 版
    文档
    Download PDF

    MySQL 订阅消息 canal 格式说明

    最后更新时间：2024-01-09 15:02:47
    下载PDF

      概述

      使用 CKafka 连接器订阅 MySQL 的变更操作时，可选多种消息格式，默认采用 debezium 格式，同时提供了兼容其他消息格式的能力。本文介绍兼容官方自定义格式的消息格式说明。

      官方格式一说明

      官方格式一目前仅支持 DML 消息，DDL 消息格式与 canal 格式一致。
      字段名称
      字段说明
      BINLOG_NAME
      binlog 日志文件名称
      BINLOG_POS
      binlog 日志的 pos 位置
      DATABASE
      数据库名称
      EVENT_SERVER_ID
      暂时默认为 null
      GLOBAL_ID
      如果开启 GTID，则为 GTID 信息
      GROUP_ID
      暂时默认为 null
      NEW_VALUES
      type=U，则为更新后的行信息，格式为 json
      type=D，则为 null
      type=I，则为新插入的行信息，格式为 json
      OLD_VALUES
      type=U，则为更新前的行信息，格式为 json
      type=D，则为删除的行信息，格式为 json
      type =I，则为 null
      TABLE
      表名
      TIME
      日志生成时间
      TYPE
      日志类型。U：update，D：delete，l：insert

      DDL 格式

      create database

      {
          "data": null, 
          "database": "dip_test", 
          "es": 1655812326, 
          "id": 0, 
          "isDdl": true, 
          "mysqlType": null, 
          "old": null, 
          "pkNames": null, 
          "sql": "CREATE DATABASE `dip_test` CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci", 
          "sqlType": null, 
          "table": "", 
          "ts": 1655812326, 
          "type": "QUERY"
      }

      drop database

      {
          "data": null, 
          "database": "dip_test", 
          "es": 1655812326, 
          "id": 0, 
          "isDdl": true, 
          "mysqlType": null, 
          "old": null, 
          "pkNames": null, 
          "sql": "DROP DATABASE IF EXISTS `dip_test`", 
          "sqlType": null, 
          "table": "", 
          "ts": 1655812326, 
          "type": "QUERY"
      }

      create table

      {
          "data": null, 
          "database": "dip_test", 
          "es": 1655812326, 
          "id": 0, 
          "isDdl": true, 
          "mysqlType": null, 
          "old": null, 
          "pkNames": null, 
          "sql": "CREATE TABLE `customers` (
        `id` int NOT NULL AUTO_INCREMENT,
        `first_name` varchar(255) NOT NULL,
        `last_name` varchar(255) NOT NULL,
        `email` varchar(255) NOT NULL,
        PRIMARY KEY (`id`),
        UNIQUE KEY `email` (`email`),
        KEY `ix_id` (`id`)
      ) ENGINE=InnoDB AUTO_INCREMENT=1041 DEFAULT CHARSET=utf8", 
          "sqlType": null, 
          "table": "customers", 
          "ts": 1655812326, 
          "type": "CREATE"
      }

      alter table

      {
          "data": null, 
          "database": "test", 
          "es": 1655782153, 
          "id": 0, 
          "isDdl": true, 
          "mysqlType": null, 
          "old": null, 
          "pkNames": null, 
          "sql": "ALTER TABLE `user` ADD COLUMN `createtime` datetime NULL DEFAULT CURRENT_TIMESTAMP", 
          "sqlType": null, 
          "table": "user", 
          "ts": 1655782153, 
          "type": "ALTER"
      }

      drop table

      {
          "data": null, 
          "database": "dip_test", 
          "es": 1655812326, 
          "id": 0, 
          "isDdl": true, 
          "mysqlType": null, 
          "old": null, 
          "pkNames": null, 
          "sql": "DROP TABLE IF EXISTS `dip_test`.`customers`", 
          "sqlType": null, 
          "table": "customers", 
          "ts": 1655812326, 
          "type": "ERASE"
      }

      rename table

      {
          "data": null, 
          "database": "testDB", 
          "es": 1656300979748, 
          "id": 0, 
          "isDdl": true, 
          "mysqlType": null, 
          "old": null, 
          "pkNames": null, 
          "sql": "rename table test to t_test", 
          "sqlType": null, 
          "table": "t_test", 
          "ts": 1656300979748, 
          "type": "RENAME"
      }

      DML 格式

      insert

      {
          "BINLOG_NAME": "mysql-bin.000003", 
          "BINLOG_POS": 154, 
          "DATABASE": "inventory", 
          "EVENT_SERVER_ID": null, 
          "GLOBAL_ID": null, 
          "GROUP_ID": null, 
          "NEW_VALUES": {
              "last_name": "Kretchmar", 
              "id": "1004", 
              "first_name": "Anne", 
              "email": "annek@noanswer.org"
          }, 
          "OLD_VALUES": null, 
          "TABLE": "customers", 
          "TIME": "19700101080000", 
          "TYPE": "I"
      }

      update

      {
          "BINLOG_NAME": "mysql-bin.000003", 
          "BINLOG_POS": 484, 
          "DATABASE": "inventory", 
          "EVENT_SERVER_ID": null, 
          "GLOBAL_ID": null, 
          "GROUP_ID": null, 
          "NEW_VALUES": {
              "last_name": "Kretchmar", 
              "id": "1004", 
              "first_name": "Anne Marie", 
              "email": "annek@noanswer.org"
          }, 
          "OLD_VALUES": {
              "last_name": "Kretchmar", 
              "id": "1004", 
              "first_name": "Anne", 
              "email": "annek@noanswer.org"
          }, 
          "TABLE": "customers", 
          "TIME": "20160611015029", 
          "TYPE": "U"
      }

      delete

      {
          "BINLOG_NAME": "mysql-bin.000003", 
          "BINLOG_POS": 805, 
          "DATABASE": "inventory", 
          "EVENT_SERVER_ID": null, 
          "GLOBAL_ID": null, 
          "GROUP_ID": null, 
          "NEW_VALUES": null, 
          "OLD_VALUES": {
              "last_name": "Kretchmar", 
              "id": "1004", 
              "first_name": "Anne Marie", 
              "email": "annek@noanswer.org"
          }, 
          "TABLE": "customers", 
          "TIME": "20160611020502", 
          "TYPE": "D"
      }
      
      联系我们

      联系我们，为您的业务提供专属服务。

      联系我们
      技术支持

      如果你想寻求进一步的帮助，通过工单与我们进行联络。我们提供7x24的工单服务。

      提交工单
      7x24 电话支持
      Copyright © 2013-2024 Tencent Cloud. All Rights Reserved.
      隐私条款服务条款缓存条款