CREATE SOURCE: MySQL (New Syntax)

Creates a new source from MySQL. Materialize supports creating sources from MySQL version 8.0.1+. Once a new source is created, you can CREATE TABLE FROM SOURCE to create the corresponding tables in Materialize and start the data ingestion process.

Prerequisites

To create a source from MySQL(8.0.1+), you must first:

• Configure upstream MySQL instance
  • Enable GTID-based binary log(binlog) replication. You must set binlog_row_metadata=FULL to use the new CREATE SOURCE syntax.
  • Create a replication user and password for Materialize to use to connect.
• Configure network security
  • Ensure Materialize can connect to your MySQL instance.
• Create a connection to MySQL in Materialize
  • The connection setup depends on the network security configuration.

Syntax

To create a source from an external MySQL database:

CREATE SOURCE [IF NOT EXISTS] <source_name>
[IN CLUSTER <cluster_name>]
FROM MYSQL CONNECTION <connection_name>
[WITH ( <with_option> [, ...] )]
;

Ingesting data

After a source is created, you can create tables from the source referencing upstream MySQL tables that have GTID-based binlog replication enabled (Note: binlog_row_metadata=FULL is required to use the new syntax). You can create multiple tables that reference the same upstream table. See CREATE TABLE FROM SOURCE for details.

Handling table schema changes

The use of CREATE SOURCE with the new CREATE TABLE FROM SOURCE allows for the handling of certain upstream schema changes, specifically adding or dropping columns in the upstream tables, without downtime.

See Guide: Handle upstream schema changes for details.

See also Handling upstream operations for additional upstream operation considerations.

Supported types

With the new syntax, after a MySQL source is created, you CREATE TABLE FROM SOURCE to create a corresponding table in Materialize and start ingesting data.

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").

For more information, including strategies for handling unsupported types, see CREATE TABLE FROM SOURCE.

Change data capture

The source uses MySQL’s binlog replication protocol to continually ingest changes resulting from INSERT, UPDATE and DELETE operations in the upstream database. This process is known as change data capture.

The replication method used is based on global transaction identifiers (GTIDs), and guarantees transactional consistency — any operation inside a MySQL transaction is assigned the same timestamp in Materialize, which means that the source will never show partial results based on partially replicated transactions.

Before creating a source in Materialize, you must configure the upstream MySQL database for GTID-based binlog replication:

If you’re running MySQL using a managed service, additional configuration changes might be required. To enable GTID-based binlog replication for your MySQL service, see the integration guides.

Binlog retention

By default, MySQL retains binlog files for 30 days (i.e., 2592000 seconds) before automatically removing them. This is configurable via the binlog_expire_logs_seconds system variable. We recommend using the default value for this configuration in order to not compromise Materialize’s ability to resume replication in case of failures or restarts.

In some MySQL managed services, binlog expiration can be overridden by a service-specific configuration parameter. It’s important that you double-check if such a configuration exists, and ensure it’s set to the maximum interval available.

As an example, Amazon RDS for MySQL has its own configuration parameter for binlog retention (binlog retention hours) that overrides binlog_expire_logs_seconds and is set to NULL by default.

Monitoring source progress

By default, MySQL sources expose progress metadata as a subsource that you can use to monitor source ingestion progress. The name of the progress subsource can be specified when creating a source using the EXPOSE PROGRESS AS clause; otherwise, it will be named <src_name>_progress.

The following metadata is available for each source as a progress subsource:

And can be queried using:

SELECT transaction_id
FROM <src_name>_progress;

Progress metadata is represented as a GTID set of future possible GTIDs, which is similar to the gtid_executed system variable on a MySQL replica. The reported transaction_id should increase as Materialize consumes new binlog records from the upstream MySQL database. For more information, see Troubleshooting.

Handling upstream operations

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.

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.

Example

Prerequisites

To create a source from MySQL(8.0.1+), you must first:

• Configure upstream MySQL instance
  • Enable GTID-based binary log(binlog) replication. You must set binlog_row_metadata=FULL to use the new CREATE SOURCE syntax.
  • Create a replication user and password for Materialize to use to connect.
• Configure network security
  • Ensure Materialize can connect to your MySQL instance.
• Create a connection to MySQL in Materialize
  • The connection setup depends on the network security configuration.

For details, see the MySQL integration guides.

Create a source

Once you have configured the upstream MySQL, network security, and created the connection to MySQL, you can create the source. In this example, assume the connection you created is named mysql_connection.

CREATE SOURCE mysql_source
FROM MYSQL CONNECTION mysql_connection;

After a source is created, you can create a table from the source, referencing specific upstream table(s). Use a DDL transaction block to create multiple tables from the same source.

BEGIN;
CREATE TABLE items
FROM SOURCE mysql_source (REFERENCE mydb.items);

CREATE TABLE orders
FROM SOURCE mysql_source (REFERENCE mydb.orders);
COMMIT;

Related pages

• CREATE TABLE
• CREATE SECRET
• CREATE CONNECTION
• CREATE SOURCE
• MySQL integration guides