tencent cloud

PrevNext

Feedback

search by keyword

Recent Pages

Documentation
TDMQ for CKafka
    Documentation
    Download PDF

    Official Format Description for MySQL Subscription Messages

    Last updated: 2024-09-09 21:41:37
    Download PDF

      Overview

      When the CKafka connector is used to subscribe to MySQL change operations, various message formats can be selected. By default, the debezium format is used, and it also provides compatibility with other message formats. This document introduces the message format compatible with Custom Official Definition Format.

      Official Format I Description

      Official Format I currently supports only DML messages. The DDL message format is consistent with the canal format.
      Field Name
      Field Description
      BINLOG_NAME
      The binlog file name.
      BINLOG_POS
      The binlog pos position.
      DATABASE
      The database name.
      EVENT_SERVER_ID
      It is temporarily set to null by default.
      GLOBAL_ID
      If GTID is enabled, this field contains GTID information.
      GROUP_ID
      It is temporarily set to null by default.
      NEW_VALUES
      If type = U, this field contains the updated row information in JSON format.
      If type = D, this field is null.
      If type = I, this field contains the newly inserted row information in JSON format.
      OLD_VALUES
      If type = U, this field contains the row information before the update in JSON format.
      If type = D, this field contains the deleted row information in JSON format.
      If type = I, this field is null.
      TABLE
      The table name.
      TIME
      The log generation time.
      TYPE
      The log type:
      U: update
      D: delete
      l: insert

      DDL Format

      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 Format

      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"
      }
      
      
      Contact Us

      Contact our sales team or business advisors to help your business.

      Contact Us
      Technical Support

      Open a ticket if you're looking for further assistance. Our Ticket is 7x24 avaliable.

      Submit a Ticket
      7x24 Phone Support
      Copyright © 2013-2024 Tencent Cloud. All Rights Reserved.
      Privacy PolicyLegalCookie Policy