Ingest data from Amazon RDS for MySQL

This page shows you how to stream data from Amazon RDS for MySQL to Materialize using the MySQL source.

Before you begin

• Make sure you are running MySQL 5.7 or higher. Materialize uses GTID-based binary log (binlog) replication, which is not available in older versions of MySQL.

• Ensure you have access to your MySQL instance via the mysql client, or your preferred SQL client.

Step 1. Enable GTID-based binlog replication

Before creating a source in Materialize, you must configure Amazon RDS for GTID-based binlog replication. For guidance on enabling GTID-based binlog replication in RDS, see the Amazon RDS for MySQL documentation.

1. Enable automated backups in your RDS instance by setting the backup retention period to a value greater than 0 to enable binary logging.

2. Create a custom RDS parameter group.

   • Set Parameter group family to your MySQL version.
   • Set Type to DB Parameter Group.

3. Edit the new parameter group to set the configuration parameters to the following values:

   Configuration parameter	Value	Details
   log_bin_use_v1_row_events	ON	AWS Management Console equivalent to MySQL’s log_bin configuration parameter.
   binlog_format	ROW	This configuration is deprecated as of MySQL 8.0.34. Newer versions of MySQL default to row-based logging.
   binlog_row_image	FULL
   gtid-mode	ON	AWS Management Console equivalent to MySQL’s gtid_mode configuration parameter.
   enforce_gtid_consistency	ON
   replica_preserve_commit_order	ON	Only required when connecting Materialize to a read-replica for replication, rather than the primary server.

4. Associate the RDS parameter group to your database.

   Use the Apply Immediately option. The database must be rebooted in order for the parameter group association to take effect. Keep in mind that rebooting the RDS instance can affect database performance.

   Do not move on to the next step until the database Status is Available in the RDS Console.

5. In addition to the step above, you must also ensure that binlog retention is set to a reasonable value. To check the current value of the binlog retention hours configuration parameter, connect to your RDS instance and run:

   CALL mysql.rds_show_configuration;
   

   If the value returned is NULL, or less than 168 (i.e. 7 days), run:

   CALL mysql.rds_set_configuration('binlog retention hours', 168);
   

   Although 7 days is a reasonable retention period, we recommend using the default MySQL retention period (30 days) in order to not compromise Materialize’s ability to resume replication in case of failures or restarts.

6. To validate that all configuration parameters are set to the expected values after the above configuration changes, run:

   -- Validate "binlog retention hours" configuration parameter
   CALL mysql.rds_show_configuration;
   

   -- Validate parameter group configuration parameters
   SHOW VARIABLES WHERE variable_name IN (
     'log_bin',
     'binlog_format',
     'binlog_row_image',
     'gtid_mode',
     'enforce_gtid_consistency',
     'replica_preserve_commit_order'
   );
   

Step 2. Create a user for replication

Once GTID-based binlog replication is enabled, we recommend creating a dedicated user for Materialize with sufficient privileges to manage replication.

1. As a superuser, use mysql (or your preferred SQL client) to connect to your database.

2. Create a dedicated user for Materialize, if you don’t already have one:

   CREATE USER 'materialize'@'%' IDENTIFIED BY '<password>';
   
   ALTER USER 'materialize'@'%' REQUIRE SSL;
   

3. Grant the user permission to manage replication:

   GRANT SELECT, RELOAD, SHOW DATABASES, REPLICATION SLAVE, REPLICATION CLIENT, LOCK TABLES ON *.* TO 'materialize'@'%';
   

   Once connected to your database, Materialize will take an initial snapshot of the tables in your MySQL server. SELECT privileges are required for this initial snapshot.

4. Apply the changes:

   FLUSH PRIVILEGES;
   

Step 3. Configure network security

There are various ways to configure your database’s network to allow Materialize to connect:

• Allow Materialize IPs: If your database is publicly accessible, you can configure your database’s security group to allow connections from a set of static Materialize IP addresses.

• Use an SSH tunnel: If your database is running in a private network, you can use an SSH tunnel to connect Materialize to the database.

Select the option that works best for you.

Step 4. (Optional) Create a cluster

In Materialize, a cluster is an isolated environment, similar to a virtual warehouse in Snowflake. When you create a cluster, you choose the size of its compute resource allocation based on the work you need the cluster to do, whether ingesting data from a source, computing always-up-to-date query results, serving results to clients, or a combination.

In this case, you’ll create a dedicated cluster for ingesting source data from your MySQL database.

1. In the SQL Shell, or your preferred SQL client connected to Materialize, use the CREATE CLUSTER command to create the new cluster:

   CREATE CLUSTER ingest_mysql (SIZE = '200cc');
   
   SET CLUSTER = ingest_mysql;
   

   A cluster of size 200cc should be enough to process the initial snapshot of the tables in your MySQL database. For very large snapshots, consider using a larger size to speed up processing. Once the snapshot is finished, you can readjust the size of the cluster to fit the volume of changes being replicated from your upstream MySQL database.

Step 5. Start ingesting data

Now that you’ve configured your database network and created an ingestion cluster, you can connect Materialize to your MySQL database and start ingesting data. The exact steps depend on your networking configuration, so start by selecting the relevant option.

Step 6. Check the ingestion status

Before it starts consuming the replication stream, Materialize takes a snapshot of the relevant tables in your publication. Until this snapshot is complete, Materialize won’t have the same view of your data as your PostgreSQL database.

In this step, you’ll first verify that the source is running and then check the status of the snapshotting process.

1. Back in the SQL client connected to Materialize, use the mz_source_statuses table to check the overall status of your source:

   WITH
     source_ids AS
     (SELECT id FROM mz_sources WHERE name = 'mz_source')
   SELECT *
   FROM
     mz_internal.mz_source_statuses
       JOIN
         (
           SELECT referenced_object_id
           FROM mz_internal.mz_object_dependencies
           WHERE
             object_id IN (SELECT id FROM source_ids)
           UNION SELECT id FROM source_ids
         )
         AS sources
       ON mz_source_statuses.id = sources.referenced_object_id;
   

   For each subsource, make sure the status is running. If you see stalled or failed, there’s likely a configuration issue for you to fix. Check the error field for details and fix the issue before moving on. Also, if the status of any subsource is starting for more than a few minutes, contact our team.

2. Once the source is running, use the mz_source_statistics table to check the status of the initial snapshot:

   WITH
     source_ids AS
     (SELECT id FROM mz_sources WHERE name = 'mz_source')
   SELECT sources.referenced_object_id AS id, mz_sources.name, snapshot_committed
   FROM
     mz_internal.mz_source_statistics
       JOIN
         (
           SELECT object_id, referenced_object_id
           FROM mz_internal.mz_object_dependencies
           WHERE
             object_id IN (SELECT id FROM source_ids)
           UNION SELECT id, id FROM source_ids
         )
         AS sources
       ON mz_source_statistics.id = sources.referenced_object_id
       JOIN mz_sources ON mz_sources.id = sources.referenced_object_id;
   

   object_id | snapshot_committed
   ----------|------------------
    u144     | t
   (1 row)
   

   Once snapshot_commited is t, move on to the next step. Snapshotting can take between a few minutes to several hours, depending on the size of your dataset and the size of the cluster the source is running in.

Step 7. Right-size the cluster

After the snapshotting phase, Materialize starts ingesting change events from the PostgreSQL replication stream. For this work, Materialize generally performs well with an 100cc replica, so you can resize the cluster accordingly.

1. Still in a SQL client connected to Materialize, use the ALTER CLUSTER command to downsize the cluster to 100cc:

   ALTER CLUSTER ingest_postgres SET (SIZE '100cc');
   

   Behind the scenes, this command adds a new 100cc replica and removes the 200cc replica.

2. Use the SHOW CLUSTER REPLICAS command to check the status of the new replica:

   SHOW CLUSTER REPLICAS WHERE cluster = 'ingest_postgres';
   

        cluster     | replica |  size  | ready
   -----------------+---------+--------+-------
    ingest_postgres | r1      | 100cc  | t
   (1 row)
   

3. Going forward, you can verify that your new cluster size is sufficient as follows:

   1. In Materialize, get the replication slot name associated with your PostgreSQL source from the mz_internal.mz_postgres_sources table:

      SELECT
          d.name AS database_name,
          n.name AS schema_name,
          s.name AS source_name,
          pgs.replication_slot
      FROM
          mz_sources AS s
          JOIN mz_internal.mz_postgres_sources AS pgs ON s.id = pgs.id
          JOIN mz_schemas AS n ON n.id = s.schema_id
          JOIN mz_databases AS d ON d.id = n.database_id;
      

   2. In PostgreSQL, check the replication slot lag, using the replication slot name from the previous step:

      SELECT
          pg_size_pretty(pg_current_wal_lsn() - confirmed_flush_lsn)
          AS replication_lag_bytes
      FROM pg_replication_slots
      WHERE slot_name = '<slot_name>';
      

      The result of this query is the amount of data your PostgreSQL cluster must retain in its replication log because of this replication slot. Typically, this means Materialize has not yet communicated back to PostgreSQL that it has committed this data. A high value can indicate that the source has fallen behind and that you might need to scale up your ingestion cluster.

Next steps

With Materialize ingesting your PostgreSQL data into durable storage, you can start exploring the data, computing real-time results that stay up-to-date as new data arrives, and serving results efficiently.

• Explore your data with SHOW SOURCES and SELECT.

• Compute real-time results in memory with CREATE VIEW and CREATE INDEX or in durable storage with CREATE MATERIALIZED VIEW.

• Serve results to a PostgreSQL-compatible SQL client or driver with SELECT or SUBSCRIBE or to an external message broker with CREATE SINK.

• Check out the tools and integrations supported by Materialize.