tencent cloud

All product documents
TDMQ for CKafka
DocumentationTDMQ for CKafkaGeneral ReferencesConnectorDatabase Change SubscriptionCanal Format of MySQL Subscription Message
Canal Format of MySQL Subscription Message
Download PDF
Last updated: 2024-01-09 15:02:47
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"
}

Was this page helpful?
You can also Contact Sales or Submit a Ticket for help.
Yes
No

Feedback

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
Hong Kong, China
+852 800 906 020 (Toll Free)
United States
+1 844 606 0804 (Toll Free)
United Kingdom
+44 808 196 4551 (Toll Free)
Canada
+1 888 605 7930 (Toll Free)
Australia
+61 1300 986 386 (Toll Free)
EdgeOne hotline
+852 300 80699
More local hotlines coming soon