MySQL-CDC

    Supported Version

    Dependencies

    In order to set up the MySQL Extract Node, the following table provides dependency information for both projects using a build automation tool (such as Maven or SBT) and SQL Client with Sort Connectors JAR bundles.

    The MySQL driver dependency is also required to connect to MySQL database. Please download mysql-connector-java-8.0.21.jar and put it into .

    You have to define a MySQL user with appropriate permissions on all databases that the Debezium MySQL connector monitors.

    1. Create the MySQL user:
      1. Grant the required permissions to the user:
      1. Finalize the user’s permissions:
      1. mysql> FLUSH PRIVILEGES;

      See more about the .

      Notes

      Set a different SERVER ID for each reader

      Every MySQL database client for reading binlog should have an unique id, called server id. MySQL server will use this id to maintain network connection and the binlog position. Therefore, if different jobs share a same server id, it may result to read from wrong binlog position. Thus, it is recommended to set different server id for each reader via the SQL Hints, e.g. assuming the source parallelism is 4, then we can use SELECT * FROM source_table /*+ OPTIONS('server-id'='5401-5404') */ ; to assign unique server id for each of the 4 source readers.

      When an initial consistent snapshot is made for large databases, your established connection could timeout while the tables are being read. You can prevent this behavior by configuring interactive_timeout and wait_timeout in your MySQL configuration file.

      • interactive_timeout: The number of seconds the server waits for activity on an interactive connection before closing it. See .
      • wait_timeout: The number of seconds the server waits for activity on a noninteractive connection before closing it. See MySQL documentations.

      How to create a MySQL Extract Node

      Usage for SQL API

      • Choose the BINLOG Data Source

      • Configure the MySQL Source MySQL SOURCE

      Usage for InLong Manager Client

      TODO: It will be supported in the future.

      Available Metadata

      The extended CREATE TABLE example demonstrates the syntax for exposing these metadata fields:

      1. CREATE TABLE `mysql_extract_node` (
      2.       `id` INT,
      3.       `name` STRING,
      4.       `database_name` string METADATA FROM 'meta.database_name',
      5.       `table_name`    string METADATA FROM 'meta.table_name',
      6.       `op_ts`         timestamp(3) METADATA FROM 'meta.op_ts',
      7.       `op_type` string METADATA FROM 'meta.op_type',
      8.       `batch_id` bigint METADATA FROM 'meta.batch_id',
      9.       `is_ddl` boolean METADATA FROM 'meta.is_ddl',
      10.       `mysql_type` MAP<STRING, STRING> METADATA FROM 'meta.mysql_type',
      11.       `pk_names` ARRAY<STRING> METADATA FROM 'meta.pk_names',
      13.       `sql_type` MAP<STRING, INT> METADATA FROM 'meta.sql_type',
      14.       `ingestion_ts` TIMESTAMP(3) METADATA FROM 'meta.ts',
      15.       PRIMARY KEY (`id`) NOT ENFORCED 
      16. ) WITH (
      17.       'connector' = 'mysql-cdc-inlong', 
      18.       'hostname' = 'YourHostname',
      19.       'migrate-all' = 'true',
      20.       'port' = '3306',                
      21.       'username' = 'YourUsername',
      22.       'password' = 'YourPassword',
      23.       'database-name' = 'YourDatabase',
      24.       'table-name' = 'YourTable'

      Data Type Mapping