MySQL CDC - Materialize

Materialize can ingest data from MySQL (5.7+) databases using MySQL’s GTID-based binlog replication protocol for Change Data Capture (CDC). This allows you to continuously stream INSERT, UPDATE, and DELETE operations from your MySQL database into Materialize.

Prerequisites

Before creating a MySQL source, you must:

Enable GTID-based binlog replication in your MySQL database
Create a dedicated replication user with appropriate permissions
Configure network access to allow Materialize to connect
Ensure sufficient binlog retention (recommended: 30 days)

GTID-based binlog replication must be enabled before you can create a MySQL source. This may require a database restart depending on your configuration.

Step 1: Configure MySQL

Enable GTID-Based Binlog Replication

Verify and configure the following MySQL server variables:

-- In MySQL: Check current configuration
SHOW VARIABLES WHERE variable_name IN (
    'log_bin',
    'binlog_format',
    'binlog_row_image',
    'gtid_mode',
    'enforce_gtid_consistency',
    'binlog_expire_logs_seconds'
);

Required settings:

# In my.cnf or my.ini
log_bin = ON
binlog_format = ROW
binlog_row_image = FULL
gtid_mode = ON
enforce_gtid_consistency = ON
binlog_expire_logs_seconds = 2592000  # 30 days

For managed MySQL services (RDS, Aurora, Cloud SQL), these settings may need to be configured through parameter groups or service-specific mechanisms.

Create a Replication User

Create a dedicated MySQL user with replication privileges:

-- In MySQL
CREATE USER 'materialize'@'%' IDENTIFIED BY '<password>';

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'materialize'@'%';

GRANT SELECT ON <database>.* TO 'materialize'@'%';

FLUSH PRIVILEGES;

Configure Binlog Retention

If Materialize tries to resume replication and finds GTID gaps due to missing binlog files, the source will enter an errored state and must be recreated.

For self-hosted MySQL:

-- Set binlog retention to 30 days (2592000 seconds)
SET PERSIST binlog_expire_logs_seconds = 2592000;

For Amazon RDS/Aurora:

-- RDS uses a separate parameter
CALL mysql.rds_set_configuration('binlog retention hours', 720);  -- 30 days

Step 2: Configure Network Security

Choose a network security option based on your deployment:

AWS PrivateLink
SSH Tunnel
Static IP Allowlist

AWS PrivateLink provides secure, private connectivity between Materialize and your MySQL database in AWS.1. Create a target group for your MySQL instance:

Target type: IP address
Protocol: TCP
Port: 3306 (or your custom port)

2. Create a Network Load Balancer in the same VPC as your MySQL instance3. Create a TCP listener that forwards to your target group4. Create a VPC endpoint service and associate it with the NLB5. In Materialize, create the PrivateLink connection:

CREATE CONNECTION privatelink_svc TO AWS PRIVATELINK (
    SERVICE NAME 'com.amazonaws.vpce.us-east-1.vpce-svc-0e123abc123198abc',
    AVAILABILITY ZONES ('use1-az1', 'use1-az4')
);

6. Retrieve and configure the AWS principal (see PostgreSQL guide for details)

Use an SSH tunnel to connect to MySQL through a bastion host.1. Get Materialize egress IPs:

SELECT * FROM mz_egress_ips;

2. Configure your bastion host to allow SSH connections from those IPs3. Create the SSH tunnel connection:

CREATE CONNECTION ssh_connection TO SSH TUNNEL (
    HOST 'bastion-host.example.com',
    PORT 22,
    USER 'materialize'
);

4. Add Materialize’s public keys to the bastion host’s authorized_keys

Allow connections from Materialize’s static egress IP addresses.1. Get Materialize egress IPs:

SELECT * FROM mz_egress_ips;

2. Update your MySQL firewall/security group to allow connections from those IP addresses on port 3306

Step 3: Create a Connection in Materialize

Direct Connection
With AWS PrivateLink
With SSH Tunnel

CREATE SECRET mysqlpass AS '<MYSQL_PASSWORD>';

CREATE CONNECTION mysql_connection TO MYSQL (
    HOST 'instance.foo000.us-west-1.rds.amazonaws.com',
    PORT 3306,
    USER 'materialize',
    PASSWORD SECRET mysqlpass
);

CREATE SECRET mysqlpass AS '<MYSQL_PASSWORD>';

CREATE CONNECTION mysql_connection TO MYSQL (
    HOST 'instance.foo000.us-west-1.rds.amazonaws.com',
    PORT 3306,
    USER 'materialize',
    PASSWORD SECRET mysqlpass,
    AWS PRIVATELINK privatelink_svc
);

CREATE SECRET mysqlpass AS '<MYSQL_PASSWORD>';

CREATE CONNECTION mysql_connection TO MYSQL (
    HOST 'internal-mysql.vpc.local',
    PORT 3306,
    USER 'materialize',
    PASSWORD SECRET mysqlpass,
    SSH TUNNEL ssh_connection
);

Step 4: Create a Source

All Tables
Specific Schemas
Specific Tables

Create subsources for all tables across all schemas:

CREATE SOURCE mysql_source
    FROM MYSQL CONNECTION mysql_connection
    FOR ALL TABLES;

Create subsources for all tables from specific schemas:

CREATE SOURCE mysql_source
    FROM MYSQL CONNECTION mysql_connection
    FOR SCHEMAS (mydb, analytics);

Create subsources for specific tables only:

CREATE SOURCE mysql_source
    FROM MYSQL CONNECTION mysql_connection
    FOR TABLES (
        mydb.users,
        mydb.orders,
        analytics.metrics AS reporting_metrics
    );

Handling Unsupported Types

If your tables contain unsupported data types, use the TEXT COLUMNS option:

CREATE SOURCE mysql_source
    FROM MYSQL CONNECTION mysql_connection (
        TEXT COLUMNS (mydb.table_name.column_with_unsupported_type)
    )
    FOR ALL TABLES;

Excluding Columns

MySQL doesn’t provide column-level filtering in binlog replication, but you can exclude columns in Materialize:

CREATE SOURCE mysql_source
    FROM MYSQL CONNECTION mysql_connection (
        EXCLUDE COLUMNS (mydb.users.password_hash, mydb.users.ssn)
    )
    FOR ALL TABLES;

How It Works

GTID-Based Replication

Materialize uses MySQL’s Global Transaction Identifiers (GTIDs) to track replication progress. GTIDs provide:

Transactional consistency: Operations in a MySQL transaction get the same timestamp in Materialize
Reliable resumption: Replication can resume from any point without data loss
No duplicate events: Each transaction is uniquely identified

Subsources

Materialize automatically creates a subsource for each table:

SHOW SOURCES;

         name         |   type
----------------------+-----------
 mysql_source          | mysql
 mysql_source_progress | progress
 users                 | subsource
 orders                | subsource
 products              | subsource

Progress Tracking

Monitor ingestion progress using the progress subsource:

SELECT transaction_id
FROM mysql_source_progress;

The transaction_id represents the next possible GTID and should increase as Materialize consumes binlog records.

Monitoring

Check Source Status

SELECT *
FROM mz_internal.mz_source_statuses
WHERE name = 'mysql_source';

Monitor Snapshotting Progress

SELECT
    name,
    snapshot_committed,
    bytes_received,
    messages_received
FROM mz_internal.mz_source_statistics s
JOIN mz_sources src ON s.id = src.id
WHERE src.name = 'mysql_source';

Monitor Replication Lag

In MySQL, check GTID execution status:

-- In MySQL
SHOW VARIABLES LIKE 'gtid_executed';

-- Compare with Materialize's progress
SELECT * FROM mysql_source_progress;

Schema Changes

Compatible Changes

Materialize automatically handles these schema changes:

Adding a column with a DEFAULT value
Adding a nullable column
Dropping a column
Changing column default values

Incompatible Changes

These changes require manual intervention:

Changing a column’s data type
Adding a NOT NULL constraint
Renaming columns or tables
Dropping and recreating tables

To handle incompatible changes:

-- 1. Drop the affected subsource
DROP SOURCE users;

-- 2. Add it back to the source
ALTER SOURCE mysql_source ADD SUBSOURCE users;

Handling Errors

Source in Error State

Check the error message:

SELECT error
FROM mz_internal.mz_source_statuses
WHERE name = 'mysql_source';

Common issues:

GTID gaps detected: Binlog files were purged; recreate the source
Connection refused: Check network security settings
Authentication failed: Verify credentials and permissions
Binlog not enabled: Enable binlog replication in MySQL

Recovering from GTID Gaps

If binlog files are purged while Materialize is offline, you must drop and recreate the source. There is no way to recover.

-- Drop the source and all subsources
DROP SOURCE mysql_source CASCADE;

-- Recreate with the same configuration
CREATE SOURCE mysql_source
    FROM MYSQL CONNECTION mysql_connection
    FOR ALL TABLES;

Best Practices

Resource Management

Create a dedicated cluster for MySQL sources:

CREATE CLUSTER mysql_ingest SIZE = '100cc';

SET CLUSTER = mysql_ingest;

CREATE SOURCE mysql_source FROM ...

For large initial snapshots, use a larger cluster temporarily:

-- Before creating the source
ALTER CLUSTER mysql_ingest SET (SIZE = '200cc');

-- After snapshotting completes
ALTER CLUSTER mysql_ingest SET (SIZE = '100cc');

Binlog Management

Always configure binlog retention for at least 30 days to handle extended outages or maintenance windows.

Monitor binlog disk usage:

-- In MySQL
SHOW BINARY LOGS;

SELECT
    ROUND(SUM(file_size)/1024/1024/1024, 2) AS binlog_size_gb
FROM information_schema.binary_logs;

Limit Data Volume

Only replicate schemas and tables you need
Use EXCLUDE COLUMNS for sensitive or unnecessary columns
Consider separate sources for high-volume tables

Performance Optimization

Use schema-specific sources instead of all-tables sources when possible
Monitor cluster resource usage and right-size for steady-state
Avoid running source creation during peak database hours

Troubleshooting

Source Not Progressing

Check MySQL replication status:

-- In MySQL
SHOW MASTER STATUS;
SHOW BINARY LOGS;

Verify GTID configuration:

-- In MySQL
SELECT @@gtid_mode, @@enforce_gtid_consistency;

High Memory Usage

MySQL sources with many tables or high update rates can consume significant memory:

Increase cluster size to handle the workload
Use separate sources for high-volume tables
Limit the number of tables being replicated
Monitor using mz_cluster_replica_metrics

Snapshotting Takes Too Long

Scale up the cluster temporarily during snapshotting
Reduce data volume by limiting tables
Create sources for subsets of tables rather than all tables
Run during off-peak hours to minimize database impact

Connection Issues

Test connectivity:

VALIDATE CONNECTION mysql_connection;

Common solutions:

Verify firewall rules allow connections on port 3306
Check that the MySQL user has proper permissions
Ensure GTID mode is enabled
Verify binlog format is set to ROW
Check that the database is accessible from Materialize IPs

Binlog Purged Error

If you see errors about missing binlog files:

Check binlog retention settings in MySQL
Increase retention to prevent future purges:

-- In MySQL
SET PERSIST binlog_expire_logs_seconds = 2592000;  -- 30 days

-- For RDS
CALL mysql.rds_set_configuration('binlog retention hours', 720);

Recreate the source to resnapshot from current state

Supported MySQL Services

Materialize supports MySQL CDC from:

Amazon RDS for MySQL
Amazon Aurora MySQL
Google Cloud SQL for MySQL
Azure Database for MySQL
Self-hosted MySQL
MariaDB (with some limitations)

Some managed MySQL services require additional configuration steps. Consult your provider’s documentation for enabling GTID-based binlog replication.

Get Started

Core Concepts

Ingesting Data

Transform Data

Serve Results

Operations

Integrations

​Prerequisites

​Step 1: Configure MySQL

​Enable GTID-Based Binlog Replication

​Create a Replication User

​Configure Binlog Retention

​Step 2: Configure Network Security

​Step 3: Create a Connection in Materialize

​Step 4: Create a Source

​Handling Unsupported Types

​Excluding Columns

​How It Works

​GTID-Based Replication

​Subsources

​Progress Tracking

​Monitoring

​Check Source Status

​Monitor Snapshotting Progress

​Monitor Replication Lag

​Schema Changes

​Compatible Changes

​Incompatible Changes

​Handling Errors

​Source in Error State

​Recovering from GTID Gaps

​Best Practices

​Resource Management

​Binlog Management

​Limit Data Volume

​Performance Optimization

​Troubleshooting

​Source Not Progressing

​High Memory Usage

​Snapshotting Takes Too Long

​Connection Issues

​Binlog Purged Error

​Supported MySQL Services

​Known Limitations

​Unsupported Features

​Data Type Considerations

​Next Steps

Transform Data

PostgreSQL CDC

Build docs developers (and LLMs) love

Prerequisites

Step 1: Configure MySQL

Enable GTID-Based Binlog Replication

Create a Replication User

Configure Binlog Retention

Step 2: Configure Network Security

Step 3: Create a Connection in Materialize

Step 4: Create a Source

Handling Unsupported Types

Excluding Columns

How It Works

GTID-Based Replication

Subsources

Progress Tracking

Monitoring

Check Source Status

Monitor Snapshotting Progress

Monitor Replication Lag

Schema Changes

Compatible Changes

Incompatible Changes

Handling Errors

Source in Error State

Recovering from GTID Gaps

Best Practices

Resource Management

Binlog Management

Limit Data Volume

Performance Optimization

Troubleshooting

Source Not Progressing

High Memory Usage

Snapshotting Takes Too Long

Connection Issues

Binlog Purged Error

Supported MySQL Services

Known Limitations

Unsupported Features

Data Type Considerations

Next Steps