tencent cloud

PrevNext

Feedback

search by keyword

Recent Pages

Documentation
TDMQ for CKafka
    Documentation
    Download PDF

    Canal Format of MySQL Subscription Message

    Last updated: 2024-01-09 15:02:47
    Download PDF

      Overview

      When using CKafka Connector to subscribe to change operations in MySQL, you can select multiple message formats, and the default format is Debezium. In addition, the system provides compatibility with other message formats. This document describes the message formats compatible with the official custom format.

      Official Format 1

      Official format 1 currently is supported only for DML messages, while the DDL message format is the same as the Canal format.
      Field
      Description
      BINLOG_NAME
      Binlog filename
      BINLOG_POS
      Binlog position
      DATABASE
      Database name
      EVENT_SERVER_ID
      It is null currently
      GLOBAL_ID
      GTID information if GTID is enabled
      GROUP_ID
      It is null currently
      NEW_VALUES
      If type is U, it is the row information after the update in JSON format.
      If type is D, it is null.
      If type is I, it is the inserted row information in JSON format.
      OLD_VALUES
      If type is U, it is the row information before the update in JSON format.
      If type is D, it is the deleted row information in JSON format.
      If type is I, it is null.
      TABLE
      Table name
      TIME
      Log generation time
      TYPE
      Log type. Valid values: U (UPDATE); D (DELETE); I (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