MySQL CDC

Use the MySQL CDC (Change Data Capture) Connector to get data from MySQL into Decodable. The MySQL CDC Connector is powered by Debezium and able to extract and send change events (INSERTS, UPDATES, and DELETES) through Decodable.

Overview

Connector name

mysql-cdc

Type

source

Delivery guarantee

exactly once

Prerequisites

Before you can get data from MySQL, the following requirements must be met:

  • Your MySQL database must be accessible from the Decodable network. Contact us for more information about enabling network access to your infrastructure.

  • Your MySQL instance must be configured to send change records. See Setting up MySQL from the Debezium documentation.

    • If you are using Amazon RDS, Automated Backups must be enabled. Otherwise, binlog will be disabled regardless of any other settings. See How To: Set up a MySQL CDC connection on Amazon RDS for detailed instructions on how to configure MySQL on Amazon RDS.

  • Your MySQL user must have sufficient permissions for CDC replication.

Steps: Create a connection with the MySQL Connector using Decodable Web

Follow these steps to get data from MySQL into Decodable. These steps assume that you are using the Decodable web interface. However, if you want to use the Decodable CLI to create the connection, you can refer to the Property Name column for information about what the underlying property names are.

  1. From the Connections page, select MySQL CDC and complete the following fields.

    UI Field Property Name in the Decodable CLI Description

    Connection Type

    N/A

    Select Source to use this connector to get data into Decodable.

    Host

    hostname

    The host name for your MySQL database. For example, mysql-server.

    Port

    port

    Optional. The port number to use when connecting to the host.

    Defaults to 3306.

    Database

    database-name

    The name of the database containing the tables that you want to ingest data from. If empty, this connection connects to all existing databases.

    Username

    username

    The username to use to authenticate to MySQL.

    Password

    password

    The password associated with the username. This must be provided as a secret resource. If you are using the Decodable CLI, run decodable secret list to view available secrets or decodable secret --help for help with creating a new secret.

    Note: For security purposes, Decodable will never display secret values in plaintext. You can manage which users have permissions to create, delete, or modify secrets in the Access Control management view. See Roles, groups, and permissions for more information.

    Scan Startup Mode

    scan.startup.mode

    Optional. Specifies where in the collection to start reading data when the connection is first started or when it’s restarted. Must be one of the following:

    • initial: At startup, takes an initial snapshot of monitored database tables, then continuously reads the latest binlog entries thereafter.

    • latest-offset: Avoids taking an initial snapshot of monitored database tables upon startup. Instead, reads changes from the end of the binlog, capturing only the modifications made since the connector was initiated or restarted.

    • timestamp: Bypasses the snapshot phase and initiates reading binlog events directly from a specified timestamp.

    Defaults to initial.

    Starting Timestamp

    scan.startup.timestamp-millis

    The timestamp to start reading data from, specified in milliseconds since Epoch time.

    Only applicable if scan.startup.mode is timestamp.

    N/A

    ssl-mode

    Optional. Specifies how the connector should handle secure connection to the database server. Must be one of the following:

    • DISABLED: Establish unencrypted connections.

    • PREFERRED: Establish encrypted connections if the server enabled them, otherwise fall back to unencrypted connections.

    • REQUIRED: Establish secure connections if the server enabled them, fail otherwise;

    • VERIFY_CA: Like REQUIRED but additionally verify the server TLS certificate against the configured Certificate Authority (CA) certificates.

    • VERIFY_IDENTITY: Like VERIFY_CA, but additionally verify that the server certificate matches the host to which the connection is attempted.

    Defaults to PREFERRED.

    This configuration is currently available only through the CLI.
  2. Select Next. The next screen shows a list of tables that Decodable has detected from the MySQL host.

  3. Select the tables that you want to ingest data from. Decodable automatically creates a stream for each table selected. Each stream’s schema is automatically translated from MySQL, including the field names, the data types of those fields, and which field(s) to use as the primary key. See Data Types Mapping for how MySQL types map to Decodable types.

    1. By default, Decodable uses the same name for the stream as the input table. If a stream already exists with that name and the schema of the stream matches the schema of the input table, Decodable uses that stream instead.

  4. (Optional) If you want to rename the stream created, you can override the generated stream name.

    1. For example, if you want to limit access to certain streams, you might choose to prefix specific streams and use role-based access control to grant access to certain prefixes. See Roles, groups, and permissions.

  5. Select Next.

  6. Give the newly created connection a Name and Description. Then, select Save.

Data Types Mapping

The following table shows the Decodable data types that are generated from the corresponding MySQL data types.

If you are creating this connection with Decodable Web, then you will see a preview of how the MySQL data types are translated to Decodable data types before your connection is created. This preview is not shown when you are using the Decodable CLI, so use this table as a reference for what your MySQL data types will look like in Decodable.

MySQL Type Decodable Type

TINYINT

TINYINT(>1)

TINYINT

SMALLINT

TINYINT

UNSIGNED

SMALLINT

INT

MEDIUMINT

SMALLINT

UNSIGNED

INT

BIGINT

INT

UNSIGNED

BIGINT

BIGINT

UNSIGNED

DECIMAL(20,0)

FLOAT

FLOAT

DOUBLE

DOUBLE

DECIMAL(p, s)

NUMERIC(p, s)

DEC(p, s)

FIXED(p, s)

DECIMAL(p,s)

BOOLEAN

TINYINT(1)

BIT(1)

BOOLEAN

BIT(>1)

BYTES

YEAR

INT

DATE

DATE

TIME[(p)]

TIME[(p)]

TIMESTAMP [(p)]

DATETIME [(p)]

TIMESTAMP[(p)]

CHAR(n)

CHAR(n)

VARCHAR(n)

VARCHAR(n)

BINARY(n)

BINARY(n)

VARBINARY(n)

VARBINARY(n)

TINYTEXT

TEXT

MEDIUMTEXT

LONGTEXT

STRING

TINYBLOB

BLOB

MEDIUMBLOB

LONGBLOB

BYTES

JSON

ENUM

SET

STRING

GEOMETRY

LINESTRING

POLYGON

MULTIPOINT

MULTILINESTRING

MULTIPOLYGON

GEOMETRYCOLLECTION

Not supported