Ingest data from Amazon Aurora

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

💡 Tip: For help getting started with your own data, you can schedule a free guided trial.

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.

A. Configure Amazon Aurora

1. Enable GTID-based binlog replication

NOTE: GTID-based replication is supported for Amazon Aurora MySQL v2 and v3, as well as Aurora Serverless v2.

Before creating a source in Materialize, you must configure Amazon Aurora MySQL for GTID-based binlog replication. This requires enabling binlog replication and the following additional configuration changes:

Configuration parameter	Value	Details
`log_bin`	`ON`
`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`	In the AWS console, this parameter appears as `gtid-mode`.
`enforce_gtid_consistency`	`ON`
`binlog retention hours`	168
`replica_preserve_commit_order`	`ON`	Only required when connecting Materialize to a read-replica for replication, rather than the primary server.

For guidance on enabling GTID-based binlog replication in Aurora, see the Amazon Aurora MySQL documentation.

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.

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

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

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

ALTER USER 'materialize'@'%' REQUIRE SSL;

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.
Apply the changes:
```
FLUSH PRIVILEGES;
```

B. (Optional) Configure network security

NOTE: If you are prototyping and your Aurora instance is publicly accessible, you can skip this step. For production scenarios, we recommend configuring one of the network security options below.

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.
Use AWS PrivateLink: If your database is running in a private network, you can use AWS PrivateLink to connect Materialize to the database. For details, see AWS PrivateLink.

Select the option that works best for you.

In the SQL Shell, or your preferred SQL client connected to Materialize, find the static egress IP addresses for the Materialize region you are running in:
```
SELECT * FROM mz_egress_ips;
```
Add an inbound rule to your Aurora security group for each IP address from the previous step.

In each rule:
- Set Type to MySQL.
- Set Source to the IP address in CIDR notation.

AWS PrivateLink lets you connect Materialize to your Aurora instance without exposing traffic to the public internet. To use AWS PrivateLink, you create a network load balancer in the same VPC as your Aurora instance and a VPC endpoint service that Materialize connects to. The VPC endpoint service then routes requests from Materialize to Aurora via the network load balancer.

NOTE: Materialize provides a Terraform module that automates the creation and configuration of AWS resources for a PrivateLink connection. For more details, see the Terraform module repository.

Get the IP address of your Aurora instance.

You’ll need this address to register your Aurora instance as the target for the network load balancer in the next step.

To get the IP address of your database instance:
1. In the AWS Management Console, select your database.
2. Find your Aurora endpoint under Connectivity & security.
3. Use the dig or nslooklup command to find the IP address that the endpoint resolves to:
```
dig +short <AURORA_ENDPOINT>
```
Create a dedicated target group for your Aurora instance.
- Choose the IP addresses type.
- Set the protocol and port to TCP and 5432.
- Choose the same VPC as your RDS instance.
- Use the IP address from the previous step to register your Aurora instance as the target.
Warning: The IP address of your Aurora instance can change without notice. For this reason, it’s best to set up automation to regularly check the IP of the instance and update your target group accordingly. You can use a lambda function to automate this process - see Materialize’s Terraform module for AWS PrivateLink for an example. Another approach is to configure an EC2 instance as an RDS router for your network load balancer.
Create a network load balancer.
- For Network mapping, choose the same VPC as your RDS instance and select all of the availability zones and subnets that you RDS instance is in.
- For Listeners and routing, set the protocol and port to TCP and 5432 and select the target group you created in the previous step.
In the security group of your Aurora instance, allow traffic from the the network load balancer.

If client IP preservation is disabled, the easiest approach is to add an inbound rule with the VPC CIDR of the network load balancer. If you don’t want to grant access to the entire VPC CIDR, you can add inbound rules for the private IP addresses of the load balancer subnets.
- To find the VPC CIDR, go to the network load balancer and look under Network mapping.
- To find the private IP addresses of the load balancer subnets, go to Network Interfaces, search for the name of the network load balancer, and look on the Details tab for each matching network interface.
Create a VPC endpoint service.
- For Load balancer type, choose Network and then select the network load balancer you created in the previous step.
- After creating the VPC endpoint service, note its Service name. You’ll use this service name when connecting Materialize later.
Remarks By disabling Acceptance Required, while still strictly managing who can view your endpoint via IAM, Materialze will be able to seamlessly recreate and migrate endpoints as we work to stabilize this feature.
Go back to the target group you created for the network load balancer and make sure that the health checks are reporting the targets as healthy.

To create an SSH tunnel from Materialize to your database, you launch an instance to serve as an SSH bastion host, configure the bastion host to allow traffic only from Materialize, and then configure your database’s private network to allow traffic from the bastion host.

NOTE: Materialize provides a Terraform module that automates the creation and configuration of resources for an SSH tunnel. For more details, see the Terraform module repository.

Launch an EC2 instance to serve as your SSH bastion host.
- Make sure the instance is publicly accessible and in the same VPC as your Amazon Aurora MySQL instance.
- Add a key pair and note the username. You’ll use this username when connecting Materialize to your bastion host.
Warning: Auto-assigned public IP addresses can change in certain cases. For this reason, it’s best to associate an elastic IP address to your bastion host.
Configure the SSH bastion host to allow traffic only from Materialize.
1. In the SQL Shell, or your preferred SQL client connected to Materialize, get the static egress IP addresses for the Materialize region you are running in:
```
SELECT * FROM mz_egress_ips;
```
2. For each static egress IP, add an inbound rule to your SSH bastion host’s security group.
  
  In each rule:
  - Set Type to MySQL.
  - Set Source to the IP address in CIDR notation.
In the security group of your RDS instance, add an inbound rule to allow traffic from the SSH bastion host.
- Set Type to All TCP.
- Set Source to Custom and select the bastion host’s security group.

C. Ingest data in Materialize

1. (Optional) Create a cluster

NOTE: If you are prototyping and already have a cluster to host your MySQL source (e.g. quickstart), you can skip this step. For production scenarios, we recommend separating your workloads into multiple clusters for resource isolation.

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.

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.

2. Start ingesting data

Now that you’ve configured your database network, 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.

In the SQL Shell, or your preferred SQL client connected to Materialize, use the CREATE SECRET command to securely store the password for the materialize MySQL user you created earlier:
```
CREATE SECRET mysqlpass AS '<PASSWORD>';
```

Use the CREATE CONNECTION command to create a connection object with access and authentication details for Materialize to use:

CREATE CONNECTION mysql_connection TO MYSQL (
    HOST <host>,
    PORT 3306,
    USER 'materialize',
    PASSWORD SECRET mysqlpass,
    SSL MODE REQUIRED
);

Replace <host> with your MySQL endpoint.

Use the CREATE SOURCE command to connect Materialize to your Azure instance and start ingesting data:
```
CREATE SOURCE mz_source
  FROM mysql CONNECTION mysql_connection
  FOR ALL TABLES;
```
- By default, the source will be created in the active cluster; to use a different cluster, use the IN CLUSTER clause.
- To ingest data from specific schemas or tables, use the FOR SCHEMAS (<schema1>,<schema2>) or FOR TABLES (<table1>, <table2>) options instead of FOR ALL TABLES.
- To handle unsupported data types, use the TEXT COLUMNS or IGNORE COLUMNS options. Check out the reference documentation for guidance.
After source creation, you can handle upstream schema changes by dropping and recreating the source.

In the SQL Shell, or your preferred SQL client connected to Materialize, use the CREATE CONNECTION command to create an SSH tunnel connection:
```
CREATE CONNECTION ssh_connection TO SSH TUNNEL (
    HOST '<SSH_BASTION_HOST>',
    PORT <SSH_BASTION_PORT>,
    USER '<SSH_BASTION_USER>'
);
```
- Replace <SSH_BASTION_HOST> and <SSH_BASTION_PORT> with the public IP address and port of the SSH bastion host you created earlier.
- Replace <SSH_BASTION_USER> with the username for the key pair you created for your SSH bastion host.
Get Materialize’s public keys for the SSH tunnel connection:
```
SELECT * FROM mz_ssh_tunnel_connections;
```

# Command for Linux
echo "ssh-ed25519 AAAA...76RH materialize" >> ~/.ssh/authorized_keys
echo "ssh-ed25519 AAAA...hLYV materialize" >> ~/.ssh/authorized_keys

Back in the SQL client connected to Materialize, validate the SSH tunnel connection you created using the VALIDATE CONNECTION command:
```
VALIDATE CONNECTION ssh_connection;
```
If no validation error is returned, move to the next step.
Use the CREATE SECRET command to securely store the password for the materialize MySQL user you created earlier:
```
CREATE SECRET mysqlpass AS '<PASSWORD>';
```
Use the CREATE CONNECTION command to create another connection object, this time with database access and authentication details for Materialize to use:
```
CREATE CONNECTION mysql_connection TO MYSQL (
HOST '<host>',
SSH TUNNEL ssh_connection
);
```
- Replace <host> with your MySQL endpoint.
Use the CREATE SOURCE command to connect Materialize to your Azure instance and start ingesting data:
```
CREATE SOURCE mz_source
  FROM mysql CONNECTION mysql_connection
  FOR ALL TABLES;
```
- By default, the source will be created in the active cluster; to use a different cluster, use the IN CLUSTER clause.
- To ingest data from specific schemas or tables, use the FOR SCHEMAS (<schema1>,<schema2>) or FOR TABLES (<table1>, <table2>) options instead of FOR ALL TABLES.
- To handle unsupported data types, use the TEXT COLUMNS or IGNORE COLUMNS options. Check out the reference documentation for guidance.

3. Monitor the ingestion status

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

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

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.

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.

4. Right-size the cluster

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

Still in a SQL client connected to Materialize, use the ALTER CLUSTER command to downsize the cluster to 100cc:
```
ALTER CLUSTER ingest_mysql SET (SIZE '100cc');
```
Behind the scenes, this command adds a new 100cc replica and removes the 200cc replica.

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

SHOW CLUSTER REPLICAS WHERE cluster = 'ingest_mysql';

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

Next steps

With Materialize ingesting your MySQL 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.