Articles in this section

FAQ - MySQL listener for change data capture (CDC)

Michelle Welch
Updated

This FAQ answers common questions about the MySQL listener, which streams row changes from MySQL into a flow using change data capture (CDC). It's for builders and database administrators (DBAs) who have created or plan to create a listener as described in Listen for real-time changes from MySQL. The questions cover how the listener uses the binary log (binlog), how snapshot.mode and the stored cursor behave, what server settings and privileges you need, and how to resolve the errors the listener reports.

Prerequisites

The MySQL listener requires:

How does a listener relate to the binary log?

Each MySQL listener runs its own Debezium consumer that reads the server's binlog — a single, server-wide record of every data change. Unlike PostgreSQL, MySQL has no per-consumer object such as a replication slot or publication on the server.

  • One consumer per listener — Each listener tracks its own position in the binlog. That position (the cursor) is stored by integrator.io, not on your MySQL server.
  • Binlog retention — Don't leave a listener disabled for longer than the server's binlog retention period. MySQL purges binlog files after the period set by binlog_expire_logs_seconds (or your hosting service's equivalent, such as the backup retention setting on Amazon RDS). If a listener is disabled or falls behind for longer than that period, the binlog position it stored no longer exists on the server. With snapshot.mode = when_needed, the listener reads the captured tables again and continues from the current binlog position. With no_data, the listener can't resume and reports DEBEZIUM_ENGINE_ERROR until you reset its cursor; changes made while it was stopped aren't recovered.
  • Scale — One listener can watch every table, but a listener that receives a high volume of changes across many tables can fall behind. Split high-volume tables across separate listeners, each scoped with Tables or the include and exclude lists in Additional properties.

A disabled MySQL listener doesn't consume storage on your server the way a paused PostgreSQL replication slot does, but set your binlog retention long enough to cover planned downtime.

What's the difference between no_data and when_needed?

snapshot.mode in Additional properties controls what the listener does with rows that already exist when it starts.

  • no_data (default) — The listener doesn't read existing rows. It captures only changes written to the binlog after it starts.
  • when_needed — The listener reads the existing rows in the captured tables and delivers them with op = r when it has no stored cursor (the first run, or after a cursor reset) or when its stored binlog position is no longer available on the server. Otherwise it resumes streaming from the cursor without reading the tables again.

Which snapshot.mode values can I use?

The snapshot.mode list offers no_data and when_needed. These are the two values integrator.io validates and tests.

Why do events arrive in batches rather than one at a time?

The listener groups events into pages before sending them to the next step. A page is sent as soon as it reaches Page size (default 250 records) or when Max wait time (default 300 seconds, minimum 30) elapses, whichever comes first. During quiet periods a single change can wait up to Max wait time before it reaches your flow. To reduce the delay, lower Max wait time in the listener's advanced settings.

How do I load existing rows the first time I capture a table?

Set snapshot.mode = when_needed on a new listener scoped to that table.

  1. Create a listener and select the tables you're onboarding in Tables.
  2. In Additional properties, set snapshot.mode to when_needed.
  3. Turn on the flow. The listener reads the existing rows once, then stores a cursor and streams new changes.
  4. Leave the listener running as the flow's ongoing source.

Use no_data when you never want the listener to read existing rows — for example, when you've already loaded historical data with a scheduled export.

Why didn't a table I added get its existing rows?

Because the listener already had a stored cursor. when_needed reads existing rows only when no cursor exists or the stored binlog position has been purged. Once a cursor is stored, adding a table to Tables doesn't trigger another read; the listener captures only new changes for the added table from the current binlog position. To load the table's existing rows, see How do I load existing rows for a newly added table?

How do I load existing rows for a newly added table?

When a listener already has a cursor, choose one of two approaches to load history for a table you've added.

Option A – Reset the cursor (recommended)

Resetting the cursor makes the listener behave as if it were starting for the first time. With when_needed, the listener reads the existing rows in every captured table — not only the added one — so plan for duplicate reads in your downstream import, for example by using an upsert.

  1. In Additional properties, confirm snapshot.mode is when_needed.
  2. Expand Cursor management. This section appears only when a cursor exists, typically after several minutes of active streaming.
  3. Select Reset.
  4. Save the listener.

On save, the stored cursor is cleared and the listener restarts, reads the existing rows, and then streams new changes.

Option B – Load history with a separate listener

Keep your main listener's cursor untouched and load history separately.

  1. Create a second listener scoped in Tables to only the added table.
  2. In Additional properties, set snapshot.mode to when_needed.
  3. Turn on its flow and let it read the existing rows once.
  4. Turn off or delete the second listener.
  5. Add the table to your main listener, which captures new changes from that point forward.

Why don't I see the before field in my listener data?

Two conditions must both be true to receive the row state before a change.

The server must log full row images

binlog_row_image controls how much of each row MySQL writes to the binlog. With FULL, both the before and after images of every changed row are logged. With MINIMAL or NOBLOB, the before image contains only some columns, so there's nothing complete to deliver. This is a server-wide setting; unlike PostgreSQL's per-table REPLICA IDENTITY, you don't change it table by table. To check it, run:

SHOW VARIABLES LIKE 'binlog_row_image';

If the value isn't FULL, ask your DBA to change the server configuration.

You must select before in Fields to include

Fields to include filters what integrator.io keeps from each event. Even when the server logs full row images, integrator.io drops the before image unless before is selected.

How do deletes appear in my listener data?

A delete event has an empty after field and, when before is selected in Fields to include, a before field containing the deleted row. The op field is d. To act on deletes downstream — for example, to remove the matching record in the destination — select both before and op so your flow can identify the deleted row and the operation type.

What happens when I change the tables a listener captures?

The stored cursor is a binlog position, and changing the listener's scope doesn't move it.

  • Adding tables — The listener continues from its cursor and captures the added tables from that position forward, with none of their existing rows. See Why didn't a table I added get its existing rows?
  • Removing tables — Changes from the removed tables stop flowing. Nothing is reprocessed.

What privileges does the connection user need?

The database user in your MySQL connection needs, at minimum:

  • REPLICATION SLAVE — Reads the binlog stream.
  • REPLICATION CLIENT — Queries binlog status and positions.
  • SELECT on the captured tables — Reads existing rows when snapshot.mode is when_needed.

REPLICATION SLAVE and REPLICATION CLIENT are global privileges. They're granted ON *.* and can't be limited to one database. SELECT can be limited to the databases or tables you capture. A DBA can grant the privileges with statements such as:

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'your_user'@'%';
GRANT SELECT ON your_database.* TO 'your_user'@'%';

Replace '%' with the host or address range you allow the connection to come from, if your security policy requires it.

How do I verify my server is configured for CDC?

Run these statements and compare the results:

SHOW VARIABLES LIKE 'log_bin';          -- must be ON
SHOW VARIABLES LIKE 'binlog_format';    -- must be ROW
SHOW VARIABLES LIKE 'binlog_row_image'; -- must be FULL

If any value differs, ask your DBA to change it.

  • Self-managed MySQL — Set log_bin, binlog_format, and binlog_row_image in the server configuration file. Turning on log_bin requires a server restart, so plan a maintenance window for a production server.
  • Amazon RDS for MySQL and Aurora MySQL — Set binlog_format and binlog_row_image in the instance's parameter group. On RDS for MySQL, binary logging is on only when automated backups are turned on (backup retention greater than zero).

Why does my listener fail to start with DEBEZIUM_ENGINE_ERROR?

The listener reports DEBEZIUM_ENGINE_ERROR when the Debezium consumer can't initialize or stops unexpectedly. The most common cause is the server's binlog format.

Symptom — The flow shows an error with Code: DEBEZIUM_ENGINE_ERROR and a message beginning Unable to initialize and start connector's task class 'io.debezium.connector.mysql.MySqlConnectorTask'. No events arrive.

Cause — binlog_format on the MySQL server is MIXED or STATEMENT. Debezium requires ROW and rejects the other formats when the connector starts. Missing replication privileges on the connection user, or a stored binlog position that the server has purged (see How does a listener relate to the binary log?), produce the same error code.

Resolution

  1. Run SHOW VARIABLES LIKE 'binlog_format'; and confirm the value is ROW. If not, ask your DBA to change it in the server configuration or the RDS parameter group.
  2. Confirm the connection user has REPLICATION SLAVE and REPLICATION CLIENT. See What privileges does the connection user need?
  3. If the error message says the binlog position is no longer available, reset the listener's cursor in Cursor management.
  4. Turn the flow off and on again to restart the listener.

Why did my listener stop during the initial read with a DDL parsing error?

When snapshot.mode is when_needed, the listener reads the table definitions in the captured databases before it reads rows. If it finds a data definition language (DDL) statement it can't parse, the read fails and the listener stops.

Symptom — The flow shows DEBEZIUM_ENGINE_ERROR with a message that includes DDL statement couldn't be parsed and quotes the statement — for example, a DROP TABLE or CREATE TABLE for a table whose name contains a backtick.

Cause — A table in one of the captured databases has a name or definition Debezium's MySQL parser doesn't support. The failure stops the whole listener, not only capture from that table.

Resolution

  1. Note the table named in the error message.
  2. Exclude it from capture by adding table.exclude.list in Additional properties with a regular expression matching its database.table name, or ask your DBA whether the table can be renamed.
  3. Turn the flow off and on again to restart the listener.

Which properties from other CDC listeners don't apply to MySQL?

If you've used the PostgreSQL, MongoDB, or Microsoft SQL listener, these don't exist for MySQL and aren't on the MySQL listener form:

  • Slot name and Publication name — PostgreSQL replication concepts. MySQL CDC reads the binlog directly, with no server-side consumer object to manage.
  • capture.mode, Aggregation pipeline, collection.exclude.list, and field.exclude.list — MongoDB change-stream concepts and naming. Use table.exclude.list and column.exclude.list for MySQL.
  • schema.include.list and schema.exclude.list — MySQL has no schema layer separate from the database. Table names are two-part database.table values; use database.include.list and database.exclude.list instead.

Does the listener capture schema changes?

No. The MySQL listener delivers row-level inserts, updates, and deletes only. DDL statements such as ALTER TABLE aren't delivered as events, although the listener tracks table structure internally so it can continue to parse row changes after a column is added or removed.