Articles in this section

Listen for real-time changes from MySQL

Michelle Welch
Updated

A MySQL listener streams inserts, updates, and deletes from your MySQL tables into a flow as they happen, using change data capture (CDC). Instead of a scheduled export that runs a query and polls for changed rows, the listener reads the server's binary log (binlog) through Debezium, the open-source CDC engine integrator.io uses for database listeners, and delivers changes to your flow in pages — as soon as a page fills or Max wait time elapses. This article is for builders who own a MySQL database or work with the database administrator (DBA) who does. You'll confirm the server prerequisites, create the listener, choose which tables and event fields to capture, and manage the stored cursor.

A MySQL listener offers these advantages over a scheduled export:

  • Reduces load on the database by reading the binlog instead of running repeated queries against source tables.
  • Delivers only the rows that changed, with the operation type and (optionally) the row state before the change.
  • Captures deletes, which a query-based export can't detect.
  • Works with transformations, output filters, and pre-save page hooks.

Note: MySQL CDC is binlog-based. It doesn't use a replication slot or publication (PostgreSQL) or change streams (MongoDB), so the server prerequisites and some property names differ from those listeners. See Properties not supported for MySQL in this article.

Prerequisites

Confirm each of the following before you create the listener. If the server isn't configured for row-based binary logging, the listener fails to start and the flow reports a DEBEZIUM_ENGINE_ERROR.

  • A MySQL listener requires a Professional or Enterprise edition. See Celigo platform editions.
  • Your database runs MySQL 8.0 or later. Supported versions are 8.0.x, 8.4.x, 9.0, and 9.1. MySQL 5.7 isn't supported.
  • Your database is hosted on Amazon RDS for MySQL, Amazon Aurora MySQL, or a self-managed MySQL server. Azure Database for MySQL and Google Cloud SQL for MySQL aren't validated for use with the listener.
  • You have a MySQL connection, or you can create one during setup. See Set up a connection to MySQL.
  • The MySQL server is configured for row-based binary logging:
    • log_bin = ON
    • binlog_format = ROW
    • binlog_row_image = FULL
  • The database user in your MySQL connection has, at minimum, the REPLICATION SLAVE and REPLICATION CLIENT privileges and SELECT on the tables you want to capture. On Amazon RDS and Aurora, the listener doesn't take a global read lock, so RELOAD isn't needed there.
  • The server's binlog retention period is longer than any planned downtime for the flow. If a disabled listener falls behind the binlog, it can't resume from where it stopped. See FAQ – MySQL listener for change data capture (CDC) [link after publish].

Ask your DBA to confirm or change these settings. To check them yourself, see FAQ – MySQL listener for change data capture (CDC) [link after publish].

Create a MySQL listener

Create the listener as the source step of a flow, then configure which changes it captures.

  1. Go to Build > Flows > Create flow.
  2. In the flow builder, select Add source.

Configure step and connection details

  1. In Application, search for and select MySQL.

  2. In Flow step type, select Listen for real-time data from source application.

    Option What it opens
    Export records from source application The standard MySQL export form for scheduled, query-based exports. See Export data from MySQL.
    Listen for real-time data from source application The MySQL listener form described in this article.
  3. In Name your step, enter a unique name. Listeners can be reused across flows, so a specific name makes it easier to find in a list.
  4. Optional: In Describe your step, enter a short description of what this listener captures and why.
  5. In Connection, select a MySQL connection. If you don't have one, select Create connection at the end of the list and set up a connection to MySQL.

The listener form doesn't include a preview pane. To generate a payload structure for downstream mapping before real events arrive, use Mock output > Populate empty stub.

[Screenshot: MySQL listener form showing the Tables, Additional properties, and Fields to include fields.]

Configure the listener

Choose which tables the listener watches, how Debezium behaves, and which parts of each change event reach your flow.

  1. Optional: In Tables, select the tables to listen to. The list is loaded from your database and refreshes when you change the connection. Table names are two-part database.table values. MySQL has no schema layer between the database and the table, so there is no three-part database.schema.table form like Microsoft SQL uses. If you leave Tables blank, the listener captures events from every table the connection user can read. To keep everything except a few tables, leave Tables blank and add table.exclude.list in Additional properties.

  2. In Additional properties, add the key-value pairs that control how Debezium captures changes. Select a key from the list, then enter its value. Rows with an empty value are removed when you save. These six keys are available:

    Key Required Description
    snapshot.mode Yes What the listener does with existing rows when it starts. Set to no_data by default, which captures only changes that occur after the listener starts. when_needed reads the existing rows in the captured tables once — when no cursor is stored, or when the stored binlog position is no longer available on the server — and then streams new changes.
    table.exclude.list No Comma-separated regular expressions matching database.table names to exclude from capture.
    database.include.list No Comma-separated regular expressions matching database names to include. When set, only tables in these databases are captured.
    database.exclude.list No Comma-separated regular expressions matching database names to exclude.
    column.include.list No Comma-separated regular expressions matching database.table.column names to include in the event payload.
    column.exclude.list No Comma-separated regular expressions matching database.table.column names to exclude from the event payload.

    Tip: Leave snapshot.mode at no_data unless you need to load existing rows the first time the listener runs. For property details, see the Debezium MySQL connector documentation.

  3. In Fields to include, select the parts of each change event to include in the listener payload. By default, only after is selected. Select only the fields you need to keep payloads small, or select Select all to include every field.

    Field Description
    after The row as it exists after the change. Present for inserts, updates, and snapshot reads; empty for deletes. Selected by default.
    before The row as it existed before the change. Present for updates and deletes. Requires binlog_row_image = FULL on the server. Select this field if your flow needs to identify deleted rows.
    schema The Debezium schema descriptor for the event.
    source Metadata about where the event came from, including the database name, table name, and binlog file and position.
    op The operation type: c (insert), u (update), d (delete), or r (snapshot read).
    transaction Transaction metadata, including the transaction ID and the event's order within the transaction.
    ts_ms When the change was processed, in milliseconds.
    ts_us When the change was processed, in microseconds.
    ts_ns When the change was processed, in nanoseconds.

    Note: MySQL change events don't include an updateDescription structure or a payload.id field, so the updatedFields, removedFields, truncatedArrays, and id options that MongoDB listeners offer aren't available for MySQL.

  4. In Cursor management, view or reset the listener's stored cursor. The cursor is the binlog file and position the listener last saved, and the listener resumes from it after a restart. This section appears only when a cursor exists — typically after several minutes of active streaming — or when a reset is pending. To reset the cursor, expand Cursor management, select Reset, and save the listener. On save, the stored cursor is cleared and the listener restarts. What happens next depends on snapshot.mode: with when_needed, the listener reads the existing rows again before streaming; with no_data, it streams from the current binlog position without reading existing rows.

[Screenshot: Cursor management section expanded, showing the stored cursor value and the Reset action.]

Advanced settings

Advanced settings control paging, error links, retry data, and trace keys for the MySQL listener. Change them only when your scenario requires it.

  • Max wait time — How long, in seconds, the listener waits before sending a page of events downstream. The listener sends a page as soon as it reaches Page size; if fewer events arrive, it sends what it has when the wait time elapses. Default: 300. Minimum: 30. Digits only.
  • Page size — How many records to include in each page of data. Default: 250. Pages are capped automatically at 5 MB. The application you import into is usually the limiting factor on page size.
  • Data URI template — A handlebars template that builds a link back to the original record in the source application. When a record fails in the flow, the error in your dashboard includes this link.
  • Do not store retry data — Turn on if you don't want integrator.io to store retry data for records that fail. Storing retry data can slow the flow when very large numbers of records fail.
  • Override trace key template — A trace key that identifies each record. Reference a single field, such as {{record.field1}}, or a handlebars expression such as {{join "_" record.field1 record.field2}}, which produces a trace key like 123_456. If a transformation is applied to the listener's output, reference the transformed fields without the record. prefix — for example, {{field1}}.

After you save the listener, it appears in the flow builder as the flow's source. Add an import step to start delivering the captured changes to a destination.

What the listener doesn't capture

The MySQL listener delivers row-level data changes only.

  • Schema changes — data definition language (DDL) statements such as ALTER TABLE — aren't delivered as events. The listener tracks table structure internally so it can parse row changes, but you won't receive an event when a column is added or removed.
  • Changes that occur while the listener is stopped and that are purged from the binlog before it restarts can't be recovered from the log. See FAQ – MySQL listener for change data capture (CDC) [link after publish] for how binlog retention affects a disabled listener.

Properties not supported for MySQL

The following fields and properties from the PostgreSQL, MongoDB, and Microsoft SQL listeners don't apply to MySQL and aren't available on the MySQL listener form:

  • Slot name and Publication name — PostgreSQL replication concepts. MySQL CDC reads the binlog directly.
  • Aggregation pipeline and capture.mode — MongoDB change-stream concepts.
  • collection.exclude.list and field.exclude.list — MongoDB naming. Use table.exclude.list and column.exclude.list instead.
  • schema.include.list and schema.exclude.list — MySQL has no schema layer separate from the database. Use database.include.list and database.exclude.list instead.