MySQL

Ingest from MySQL via change data capture

Materialize supports MySQL (8.0.1+) as a real-time data source. The MySQL source uses MySQL’s binlog replication protocol to continually ingest changes resulting from CRUD operations in the upstream database. The native support for MySQL Change Data Capture (CDC) in Materialize gives you the following benefits:

• No additional infrastructure: Ingest MySQL change data into Materialize in real-time with no architectural changes or additional operational overhead. In particular, you do not need to deploy Kafka and Debezium for MySQL CDC.

• Transactional consistency: The MySQL source ensures that transactions in the upstream MySQL database are respected downstream. Materialize will never show partial results based on partially replicated transactions.

• Incrementally updated materialized views: Materialized views are not supported in MySQL, so you can use Materialize as a read-replica to build views on top of your MySQL data that are efficiently maintained and always up-to-date.

Supported versions and services

The MySQL source requires MySQL 8.0.1+ and is compatible with most common MySQL hosted services.

Integration guides

To help you get started, the following integration guides are available:

• Amazon Aurora for MySQL
• Amazon RDS for MySQL
• Azure DB for MySQL
• Google Cloud SQL for MySQL
• Self-hosted MySQL

Supported data types

Supported types

Materialize natively supports the following MySQL types:

• bigint
• binary
• bit
• blob
• boolean
• char
• date
• datetime
• decimal
• double
• float
• int
• json
• longblob
• longtext
• mediumblob
• mediumint
• mediumtext
• numeric
• real
• smallint
• text
• time
• timestamp
• tinyblob
• tinyint
• tinytext
• varbinary
• varchar

When replicating tables that contain the unsupported data types, you can:

• Use TEXT COLUMNS option for the following unsupported MySQL types:

  • enum
  • year

  The specified columns will be treated as text and will not offer the expected MySQL type features.

• Use the EXCLUDE COLUMNS option to exclude any columns that contain unsupported data types.

Zero values for date, datetime, and timestamp

MySQL allows the special “zero” values 0000-00-00, 0000-00-00 00:00:00 in date, datetime, and timestamp columns when the server sql_mode does not include NO_ZERO_DATE or NO_ZERO_IN_DATE. These values are not representable in Materialize’s corresponding native types, so they will cause ingestion to fail for the affected column.

To ingest columns that contain zero values, use TEXT COLUMNS to decode the affected columns as text. The zero values for date, datetime, timestamp, and year are preserved verbatim as strings (e.g. "0000-00-00 00:00:00", "0000").

How ingestion from MySQL works

Snapshot parallelism

When a source is created, Materialize parallelizes the initial snapshot across the cluster’s workers, and can split the read of a large table across workers when the table meets certain requirements. See Snapshot parallelism.

Adding a table to an existing source

When you add a new subsource to an existing source (ALTER SOURCE ... ADD SUBSOURCE ...), Materialize starts the snapshotting process for the new subsource. During this snapshotting, the data ingestion for the existing subsources for the same source is temporarily blocked. As such, if possible, you can resize the cluster to speed up the snapshotting process and once the process finishes, resize the cluster for steady-state.

Supported schema and table changes

The following table summarizes how Materialize handles changes to an upstream table it is ingesting. See the details below the table for recovery commands.

This section describes how changes to upstream tables that Materialize ingests affect the corresponding Materialize tables.

Adding a column

When you add a new column to your upstream table, Materialize continues to ingest only the existing columns.

To incorporate the new column:

• If using the new CREATE SOURCE and CREATE TABLE FROM SOURCE syntax, create a new table from the source. See Handle upstream column addition.

• If using the legacy CREATE SOURCE ... FOR ... syntax that creates subsources, use DROP SOURCE to drop the affected subsource, and then add the table back to the source using ALTER SOURCE ... ADD SUBSOURCE. The re-added subsource includes the new column.

Dropping a column

Dropping columns that Materialize does not ingest (for example, columns added after the source was created, or columns that are excluded) is supported. As these columns were never ingested, you can drop them without issue.

If your Materialize source ingests a column, dropping that column from your upstream table puts the affected table into an error state.

• If using the new CREATE SOURCE and CREATE TABLE FROM SOURCE syntax, you can safely drop a column by first ignoring it in Materialize. See Handle upstream column drop.

• If using legacy CREATE SOURCE ... FOR ... syntax, use DROP SOURCE to drop the affected subsource, and then add the table back to the source using ALTER SOURCE ... ADD SUBSOURCE.

Changing constraints

Materialize ignores the following constraint changes: foreign key and CHECK. As such, you can add or drop them without affecting ingestion.

Materialize also ignores NOT NULL, UNIQUE, and PRIMARY KEY constraints that are added after the Materialize table is created (that is, the table was created without them). Adding such a constraint, and later dropping it, does not affect ingestion.

Dropping a NOT NULL, UNIQUE, or PRIMARY KEY constraint that existed when the table was created puts the affected table into an error state.

If using the new CREATE SOURCE and CREATE TABLE FROM SOURCE syntax, you can safely drop such a constraint by first excluding it in Materialize. See Handle upstream constraint drop.

Changing a column’s data type

Changing an ingested column’s data type upstream so that it maps to a different Materialize type than before puts the affected Materialize table into an error state. Ingestion for that table stops, and you must drop and recreate the table in Materialize to resume ingestion.

Changing an ingested column’s upstream data type so that it continues to map to the same Materialize type does not interrupt ingestion. For example, changing tinyint to smallint, changing within the text/tinytext/mediumtext/longtext family, and adjusting bit(n) precision are all safe.

Appending new values to the end of an existing enum does not put the table into an error state. However, the newly-added values are not recognized, so rows that use them fail to decode until you drop and recreate the table. Existing enum values remain recognized, and rows that use them continue to decode successfully.

Any other enum change puts the affected Materialize table into an error state, including inserting a value before the end, reordering or renaming values, and removing values.

Renaming a column

Renaming a column that Materialize ingests puts the affected table into an error state. Ingestion for that table stops, and you must drop and recreate the table in Materialize to resume ingestion.

Table-level operations

The following upstream operations put the affected table into an error state. Ingestion for that table stops, and you must drop and recreate the affected table in Materialize to resume:

• Dropping a table (DROP TABLE).
• Renaming a table or moving it to a different schema.
• Truncating a table (TRUNCATE). To clear a table without putting it into an error state, use an unqualified DELETE FROM t; instead.

Supported database operations

The following table summarizes how Materialize handles operational events on the upstream MySQL database. See the details below the table for the error text and any required configuration.

Operations that do not require re-creating the source

Materialize tracks its position in the upstream binary log as a set of global transaction identifiers (GTIDs), which it persists alongside the ingested data. A GTID identifies a transaction across the whole replication topology, not a byte offset in a particular binlog file, so it survives routine operational events: after an interruption the source reconnects, asks the server for the transactions after its last committed GTID, and catches up.

The source recovers on its own. It briefly reports a stalled status while the condition persists, then returns to running and catches up for all of the following:

• Restarting or patching MySQL (including OS-level restarts).
• Restarting Materialize. The source resumes from its tracked GTID set and does not re-snapshot already-ingested data.
• Transient network interruptions between Materialize and MySQL.
• The upstream server running out of disk space, until space is reclaimed.
• A long-running upstream transaction blocking the initial snapshot. The source stalls until that transaction commits or rolls back.
• Failing over to a replica, with the checks described below.

Operations that require re-creating the source

A smaller set of events breaks GTID continuity or makes the binlog stream unreadable. Materialize cannot then guarantee a correct, gap-free view of your data, so it puts the entire source into an error state that requires re-creating the source. Upstream changes to an individual table’s schema are handled separately, and do not error the entire source.

In each case, the remediation is to drop the source and create it again with the statements you originally used, which triggers a fresh snapshot:

DROP SOURCE mz_source CASCADE;

Once the source is back, it is healthy when status is running and error is NULL:

SELECT status, error FROM mz_internal.mz_source_statuses WHERE name = 'mz_source';

Binlog files removed before the resume point

MySQL expires binlog files on a retention schedule, and PURGE BINARY LOGS removes them on demand. If Materialize is disconnected or lagging long enough that the files holding its resume point are removed, the source fails with one of:

mysql server does not have the binlog available at the requested gtid set

mysql server binlog frontier at <frontier> is beyond required frontier <frontier>

To avoid this, keep planned outages shorter than the retention window and monitor source lag against it. For how retention is configured, including the service-specific parameters that override it, see Binlog retention.

Resetting the binary log

RESET BINARY LOGS AND GTIDS (RESET MASTER before MySQL 8.4) discards the binlog files and the server’s GTID history, so the source’s resume point no longer exists. The source fails with:

mysql server does not have the binlog available at the requested gtid set

or, if the server then reissues GTIDs the source has already seen, with an out-of-order GTID error.

Changing a required replication setting

Materialize re-validates the upstream replication settings each time it (re-)establishes the replication stream. If one no longer holds its required value, the source fails with:

mysql server configuration: invalid mysql system setting '<setting>'. Expected '<expected>'. Got '<actual>'.

The validated settings are log_bin, binlog_format, binlog_row_image, gtid_mode, enforce_gtid_consistency, gtid_next, and, when replica_parallel_workers is greater than 1, replica_preserve_commit_order (slave_preserve_commit_order on servers older than MySQL 8.0). For the required values, see Change data capture. Restore the setting upstream, then re-create the source.

Restoring the upstream database

Materialize has no dedicated check for a restore of the upstream database. Depending on how the restore handles GTIDs, the source either fails with one of the errors above or with:

received a gtid set from the server that violates our requirements: <detail>

or, if the restore reuses GTIDs the source has already ingested, it keeps replicating against a history that no longer matches its state. Materialize cannot detect that case, so re-create the source after any restore of the upstream database, including a disaster-recovery restore onto a new server, rather than relying on an error.

Out-of-order GTIDs

If Materialize observes GTIDs in an order it cannot reconcile, the source fails with:

received out of order gtids for source <source_id> at transaction-id <transaction_id>

This is most common when Materialize replicates from a MySQL replica that applies transactions with multiple threads. See Troubleshooting: Received out of order GTIDs for the diagnosis steps and the upstream settings that make it less likely.

Operations that require re-creating only the affected tables

Lowering binlog_row_metadata

A table that Materialize began ingesting while binlog_row_metadata was FULL can no longer be decoded once the setting is lowered, and enters an error state with:

unable to decode: Table <table> was created with binlog_row_metadata=FULL but
binlog_row_metadata has since been set to a different value, meaning we cannot
reliably decode the columns

Restoring binlog_row_metadata=FULL does not clear the error. Set it back to FULL and re-create the affected tables. Tables that were created while the setting was lower keep replicating.

Failovers

Because Materialize replicates by GTID rather than by binlog file and position, it can follow a failover to a replica that was replicating from the failed server with GTIDs enabled. Point the connection at the new primary with ALTER CONNECTION, or at an endpoint that always resolves to the current primary.

On the new primary, confirm that:

• The binlog files covering the source’s resume point are present. A replica configured with a shorter retention than the old primary can leave the source with no way to resume. See Binlog files removed before the resume point.
• The required replication settings hold, including replica_preserve_commit_order while the server is still applying replication from another server. Multi-threaded apply is the main source of out-of-order GTIDs; preserving commit order reduces but does not eliminate the risk, so keep replica_parallel_workers at 0 or 1 where you can.