MySQL Binary Log

You can configure the origin to start reading the binary log file from the beginning of the file or from an initial offset in the file.

The initial offset is the point in the binary log file where you want to start processing. When you start the pipeline, the MySQL Binary Log origin starts processing from the initial offset and continues until you stop the pipeline.

The format that you use to configure the initial offset depends on whether MySQL server is enabled for Global Transaction Identifiers (GTID):

GTID enabled

When the database is enabled for GTID, the MySQL Binary Log origin expects the initial offset to contain the range of transaction IDs to exclude from being processed. Use the following format to configure the offset:

<source ID>:1-<last transaction ID to exclude>

For example, to begin reading the binary log file at the GTID 8720bebf-bb38-11e6-97b1-0242ac110004:7066, enter 8720bebf-bb38-11e6-97b1-0242ac110004:1-7065 for the offset.

Note: If you enter a single transaction ID for the offset, the origin skips that transaction ID only, reading all other transaction IDs. For example, if you enter 8720bebf-bb38-11e6-97b1-0242ac110004:7065 for the offset, the origin reads transaction IDs 1 through 7064, skips 7065, then begins reading again at 7066.

GTID disabled

When the database is disabled for GTID, the MySQL Binary Log origin expects the initial offset to contain both the binary log file name and a position within the file. Use the following format to configure the offset:

<binary log file name>:<position>

For example, the following offset configures the origin to start reading the mysql-bin.000004 binary log file at position 587:

mysql-bin.000004:587

When a pipeline stops, the MySQL Binary Log origin notes the offset where it stops reading. When the pipeline starts again, the origin continues processing from the last saved offset. You can reset the origin to process all requested objects.

Each generated record includes the following information:

CRUD operation type

The CRUD operation type is stored in the Type record field and also in the sdc.operation.type record header attribute.

The Type field contains one of the following string values: INSERT, DELETE, or UPDATE.

The sdc.operation.type record header attribute contains one of the following numeric values:

• 1 for insert data
• 2 for delete data
• 3 for update data

You can use either the Type record field or the sdc.operation.type record header attribute to write records to the destination system. CRUD-enabled destinations such as JDBC Producer and Kudu use the sdc.operation.type header attribute. For an overview of Data Collector CDC and a list of CRUD-enabled destinations, see Processing Changed Data.

Primary key information

When a table contains a primary key, the origin includes the following record header attribute:

• jdbc.primaryKeySpecification - Provides a JSON-formatted string that lists the columns that form the primary key in the table and the metadata for those columns.
  For example, a table with a composite primary key contains the following attribute:

  jdbc.primaryKeySpecification = 
       {{"<primary key column 1 name>":
           {"type": <type>, 
            "datatype": "<data type>", 
            "size": <size>, 
            "precision": <precision>, 
            "scale": <scale>, 
            "signed": <Boolean>,  
            "currency": <Boolean> }},
            ...,
       {"<primary key column N name>":
           {"type": <type>, 
            "datatype": "<data type>", 
            "size": <size>, 
            "precision": <precision>, 
            "scale": <scale>, 
            "signed": <Boolean>,  
            "currency": <Boolean> } } }

  A table without a primary key contains the attribute with an empty value:

  jdbc.primaryKeySpecification = {} 

For an update operation on a table with a primary key, the origin includes the following record header attributes:

• jdbc.primaryKey.before.<primary key column name> - Provides the old value for the specified primary key column.
• jdbc.primaryKey.after.<primary key column name> - Provides the new value for the specified primary key column.

Note: The origin provides the new and old values of the primary key columns regardless of whether the value changes.

Change data capture information

The change data capture information is stored in the following record fields:

• BinLogFilename - when GTID is disabled
• BinLogPosition- when GTID is disabled
• SeqNo - when GTID is enabled
• GTID - when GTID is enabled
• Table
• ServerId
• Database
• Timestamp
• Offset

New data

Fields that contain new data to be inserted or updated appear in a Data map field.

Changed data

Fields that contain old data to be updated, or fields in a delete record appear in an OldData map field.

For example, the origin generates the following record when it reads a binary log file for a MySQL server with GTID enabled for a table without a primary key:

Due to the structure of the MySQL binary log records, you might want to use processors to restructure the record and remove any unnecessary fields. Of the CRUD-enabled destinations, only the Kudu and JDBC Producer destinations include a Change Log Format property that enables processing MySQL Binary Log records as generated.

For example, MySQL binary log records include CDC data in record fields. If you are using a destination other than Kudu and all you need is record data, you can use a Field Remover to remove the unwanted fields.

Similarly, update records include the previous data in an OldData map field, and the updated data in a Data map field. If all you need is the updated data, you can use the Field Remover to drop the OldData field from the record. And you could use a Field Flattener processor to flatten the fields in the Data field.

The binary log file captures all changes made to the MySQL database. If you want the MySQL Binary Log origin to capture changes from a subset of tables, you can configure the origin to include changes from specific tables or to ignore changes from specific tables.

<database name>.<table name>

sales.orders,sales.customers

If you enter the same value in the Ignore Tables property, the origin captures changes from all tables in the log file, except for the orders and customers tables in the sales database.

The Ignore Tables property takes precedence. If you include a table in both properties, the origin ignores the table.

The MySQL Binary Log origin converts MySQL data types into Data Collector data types.