# Ingest data from Amazon Aurora How to stream data from Amazon Aurora for MySQL to Materialize This page shows you how to stream data from [Amazon Aurora MySQL](https://aws.amazon.com/rds/aurora/) to Materialize using the [MySQL source](/sql/create-source/mysql/). > **Tip:** For help getting started with your own data, you can schedule a [free guided > trial](https://materialize.com/demo/?utm_campaign=General&utm_source=documentation). ## Before you begin - Make sure you are running MySQL 5.7 or higher. Materialize uses [GTID-based binary log (binlog) replication](/sql/create-source/mysql/#change-data-capture), which is not available in older versions of MySQL. - Ensure you have access to your MySQL instance via the [`mysql` client](https://dev.mysql.com/doc/refman/8.0/en/mysql.html), 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. 1. Before creating a source in Materialize, you **must** configure Amazon Aurora MySQL for GTID-based binlog replication. Ensure the upstream MySQL database has been configured for GTID-based binlog replication:
MySQL Configuration Value Notes
log_bin ON
binlog_format ROW
binlog_row_image FULL
gtid_mode ON In the AWS console, this parameter appears as gtid-mode.
enforce_gtid_consistency ON
replica_preserve_commit_order ON Only required when connecting Materialize to a read-replica.
For guidance on enabling GTID-based binlog replication in Aurora, see the [Amazon Aurora MySQL documentation](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/mysql-replication-gtid.html). 1. In addition to the step above, you **must** also ensure that [binlog retention](/sql/create-source/mysql/#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: ```mysql CALL mysql.rds_show_configuration; ``` If the value returned is `NULL`, or less than `168` (i.e. 7 days), run: ```mysql 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. 1. To validate that all configuration parameters are set to the expected values after the above configuration changes, run: ```mysql -- Validate "binlog retention hours" configuration parameter CALL mysql.rds_show_configuration; ``` ```mysql -- 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' ); ``` ### 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. 1. Create a dedicated user for Materialize, if you don't already have one: ```mysql CREATE USER 'materialize'@'%' IDENTIFIED BY ''; ALTER USER 'materialize'@'%' REQUIRE SSL; ``` IAM authentication with AWS RDS for MySQL is also supported. See the [Amazon RDS User Guide](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.IAMDBAuth.html) for instructions on enabling IAM database authentication, creating IAM policies, and creating a database account. 1. Grant the user permission to manage replication: ```mysql 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. 1. Apply the changes: ```mysql 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. **Cloud:** 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 AWS PrivateLink**: If your database is running in a private network, you can use [AWS PrivateLink](/ingest-data/network-security/privatelink/) to connect Materialize to the database. For details, see [AWS PrivateLink](/ingest-data/network-security/privatelink/). - **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. **Allow Materialize IPs:** 1. In the [SQL Shell](/console/), or your preferred SQL client connected to Materialize, find the static egress IP addresses for the Materialize region you are running in: ```mzsql SELECT * FROM mz_egress_ips; ``` 1. [Add an inbound rule to your Aurora security group](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Overview.RDSSecurityGroups.html) for each IP address from the previous step. In each rule: - Set **Type** to **MySQL**. - Set **Source** to the IP address in CIDR notation. **Use AWS PrivateLink:** [AWS PrivateLink](https://aws.amazon.com/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](https://github.com/MaterializeInc/terraform-aws-rds-privatelink). 1. 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. 1. Find your Aurora endpoint under **Connectivity & security**. 1. Use the `dig` or `nslooklup` command to find the IP address that the endpoint resolves to: ```sh dig +short ``` 1. [Create a dedicated target group for your Aurora instance](https://docs.aws.amazon.com/elasticloadbalancing/latest/network/create-target-group.html). - Choose the **IP addresses** type. - Set the protocol and port to **TCP** and **3306**. - 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](https://github.com/MaterializeInc/terraform-aws-rds-privatelink/blob/main/lambda_function.py) for an example. Another approach is to [configure an EC2 instance as an RDS router](https://aws.amazon.com/blogs/database/how-to-use-amazon-rds-and-amazon-aurora-with-a-static-ip-address/) for your network load balancer. 1. [Create a network load balancer](https://docs.aws.amazon.com/elasticloadbalancing/latest/network/create-network-load-balancer.html). - 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 **3306** and select the target group you created in the previous step. 1. In the security group of your Aurora instance, [allow traffic from the the network load balancer](https://docs.aws.amazon.com/elasticloadbalancing/latest/network/target-group-register-targets.html). If [client IP preservation](https://docs.aws.amazon.com/elasticloadbalancing/latest/network/load-balancer-target-groups.html#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. 1. [Create a VPC endpoint service](https://docs.aws.amazon.com/vpc/latest/privatelink/create-endpoint-service.html). - 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](https://docs.aws.amazon.com/vpc/latest/privatelink/configure-endpoint-service.html#accept-reject-connection-requests), 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. 1. Go back to the target group you created for the network load balancer and make sure that the [health checks](https://docs.aws.amazon.com/elasticloadbalancing/latest/network/target-group-health-checks.html) are reporting the targets as healthy. **Use an SSH tunnel:** 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](https://github.com/MaterializeInc/terraform-aws-ec2-ssh-bastion). 1. [Launch an EC2 instance](https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/LaunchingAndUsingInstances.html) 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](https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/using-instance-addressing.html#concepts-public-addresses). For this reason, it's best to associate an [elastic IP address](https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/using-instance-addressing.html#ip-addressing-eips) to your bastion host. 1. Configure the SSH bastion host to allow traffic only from Materialize. 1. In the [SQL Shell](/console/), or your preferred SQL client connected to Materialize, get the static egress IP addresses for the Materialize region you are running in: ```mzsql SELECT * FROM mz_egress_ips; ``` 1. For each static egress IP, [add an inbound rule](https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/ec2-security-groups.html) to your SSH bastion host's security group. In each rule: - Set **Type** to **MySQL**. - Set **Source** to the IP address in CIDR notation. 1. In the security group of your RDS instance, [add an inbound rule](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Overview.RDSSecurityGroups.html) 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. **Self-Managed:**

Configure your network to allow Materialize to connect to your database. For example, you can:

NOTE:

The steps to allow Materialize to connect to your database depends on your deployment setup. Refer to your company’s network/security policies and procedures.

**Allow Materialize IPs:** 1. [Add an inbound rule to your Aurora security group](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Overview.RDSSecurityGroups.html) to allow traffic from Materialize IPs. In each rule: - Set **Type** to **MySQL**. - Set **Source** to the IP address in CIDR notation. **Use an SSH tunnel:** 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](https://github.com/MaterializeInc/terraform-aws-ec2-ssh-bastion). 1. [Launch an EC2 instance](https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/LaunchingAndUsingInstances.html) 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](https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/using-instance-addressing.html#concepts-public-addresses). For this reason, it's best to associate an [elastic IP address](https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/using-instance-addressing.html#ip-addressing-eips) to your bastion host. 1. Configure the SSH bastion host to allow traffic only from Materialize. 1. In the security group of your RDS instance, [add an inbound rule](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Overview.RDSSecurityGroups.html) 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 source 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](/sql/create-cluster/#resource-isolation). In Materialize, a [cluster](/concepts/clusters/) 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](/console/), or your preferred SQL client connected to Materialize, use the [`CREATE CLUSTER`](/sql/create-cluster/) command to create the new cluster: ```mzsql CREATE CLUSTER ingest_mysql (SIZE = '200cc'); SET CLUSTER = ingest_mysql; ``` A cluster of [size](/sql/create-cluster/#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. Create a connection Once you have configured your network, create a connection in Materialize per your networking configuration. **Allow Materialize IPs:** 1. In the [SQL Shell](/console/), or your preferred SQL client connected to Materialize, use the [`CREATE SECRET`](/sql/create-secret/) command to securely store the password for the `materialize` MySQL user you created [earlier](#2-create-a-user-for-replication): ```mzsql CREATE SECRET mysqlpass AS ''; ``` For AWS IAM authentication, you must create a connection to AWS. See the [`CREATE CONNECTION`](/sql/create-connection/#aws) command for details. 1. Use the [`CREATE CONNECTION`](/sql/create-connection/) command to create a connection object with access and authentication details for Materialize to use: ```mzsql CREATE CONNECTION mysql_connection TO MYSQL ( HOST , PORT 3306, USER 'materialize', PASSWORD SECRET mysqlpass, SSL MODE REQUIRED ); ``` - Replace `` with your MySQL endpoint. AWS IAM authentication is also available, see the [`CREATE CONNECTION`](/sql/create-connection/#mysql) command for details. **Use AWS PrivateLink (Cloud-only):** 1. In the [SQL Shell](/console/), or your preferred SQL client connected to Materialize, use the [`CREATE CONNECTION`](/sql/create-connection/#aws-privatelink) command to create an AWS PrivateLink connection: ↕️ **In-region connections** To connect to an AWS PrivateLink endpoint service in the **same region** as your Materialize environment: ```mzsql CREATE CONNECTION privatelink_svc TO AWS PRIVATELINK ( SERVICE NAME 'com.amazonaws.vpce..vpce-svc-', AVAILABILITY ZONES ('use1-az1', 'use1-az2', 'use1-az4') ); ``` - Replace the `SERVICE NAME` value with the service name you noted [earlier](#b-optional-configure-network-security). - Replace the `AVAILABILITY ZONES` list with the IDs of the availability zones in your AWS account. For in-region connections the availability zones of the NLB and the consumer VPC **must match**. To find your availability zone IDs, select your database in the RDS Console and click the subnets under **Connectivity & security**. For each subnet, look for **Availability Zone ID** (e.g., `use1-az6`), not **Availability Zone** (e.g., `us-east-1d`). ↔️ **Cross-region connections** To connect to an AWS PrivateLink endpoint service in a **different region** to the one where your Materialize environment is deployed: ```mzsql CREATE CONNECTION privatelink_svc TO AWS PRIVATELINK ( SERVICE NAME 'com.amazonaws.vpce.us-west-1.vpce-svc-', -- For now, the AVAILABILITY ZONES clause **is** required, but will be -- made optional in a future release. AVAILABILITY ZONES () ); ``` - Replace the `SERVICE NAME` value with the service name you noted [earlier](#b-optional-configure-network-security). - The service name region refers to where the endpoint service was created. You **do not need** to specify `AVAILABILITY ZONES` manually — these will be optimally auto-assigned when none are provided. 1. Retrieve the AWS principal for the AWS PrivateLink connection you just created: ```mzsql SELECT principal FROM mz_aws_privatelink_connections plc JOIN mz_connections c ON plc.id = c.id WHERE c.name = 'privatelink_svc'; ```

``` principal --------------------------------------------------------------------------- arn:aws:iam::664411391173:role/mz_20273b7c-2bbe-42b8-8c36-8cc179e9bbc3_u1 ``` 1. Update your VPC endpoint service to [accept connections from the AWS principal](https://docs.aws.amazon.com/vpc/latest/privatelink/add-endpoint-service-permissions.html). 1. If your AWS PrivateLink service is configured to require acceptance of connection requests, [manually approve the connection request from Materialize](https://docs.aws.amazon.com/vpc/latest/privatelink/configure-endpoint-service.html#accept-reject-connection-requests). **Note:** It can take some time for the connection request to show up. Do not move on to the next step until you've approved the connection. 1. Validate the AWS PrivateLink connection you created using the [`VALIDATE CONNECTION`](/sql/validate-connection) command: ```mzsql VALIDATE CONNECTION privatelink_svc; ``` If no validation error is returned, move to the next step. 1. Use the [`CREATE SECRET`](/sql/create-secret/) command to securely store the password for the `materialize` MySQL user you created [earlier](#2-create-a-user-for-replication): ```mzsql CREATE SECRET mysqlpass AS ''; ``` For AWS IAM authentication, you must create a connection to AWS. See the [`CREATE CONNECTION`](/sql/create-connection/#aws) command for details. 1. Use the [`CREATE CONNECTION`](/sql/create-connection/) command to create another connection object, this time with database access and authentication details for Materialize to use: ```mzsql CREATE CONNECTION mysql_connection TO MYSQL ( HOST , PORT 3306, USER 'materialize', PASSWORD SECRET mysqlpass, SSL MODE REQUIRED, AWS PRIVATELINK privatelink_svc ); ``` - Replace `` with your RDS endpoint. To find your RDS endpoint, select your database in the RDS Console, and look under **Connectivity & security**. AWS IAM authentication is also available, see the [`CREATE CONNECTION`](/sql/create-connection/#mysql) command for details. **Use an SSH tunnel:** 1. In the [SQL Shell](/console/), or your preferred SQL client connected to Materialize, use the [`CREATE CONNECTION`](/sql/create-connection/#ssh-tunnel) command to create an SSH tunnel connection: ```mzsql CREATE CONNECTION ssh_connection TO SSH TUNNEL ( HOST '', PORT , USER '' ); ``` - Replace `` and ` with the public IP address and port of the SSH bastion host you created [earlier](#b-optional-configure-network-security). - Replace `` with the username for the key pair you created for your SSH bastion host. 1. Get Materialize's public keys for the SSH tunnel connection: ```mzsql SELECT * FROM mz_ssh_tunnel_connections; ``` 1. Log in to your SSH bastion host and add Materialize's public keys to the `authorized_keys` file, for example: ```sh # Command for Linux echo "ssh-ed25519 AAAA...76RH materialize" >> ~/.ssh/authorized_keys echo "ssh-ed25519 AAAA...hLYV materialize" >> ~/.ssh/authorized_keys ``` 1. Back in the SQL client connected to Materialize, validate the SSH tunnel connection you created using the [`VALIDATE CONNECTION`](/sql/validate-connection) command: ```mzsql VALIDATE CONNECTION ssh_connection; ``` If no validation error is returned, move to the next step. 1. Use the [`CREATE SECRET`](/sql/create-secret/) command to securely store the password for the `materialize` MySQL user you created [earlier](#2-create-a-user-for-replication): ```mzsql CREATE SECRET mysqlpass AS ''; ``` For AWS IAM authentication, you must create a connection to AWS. See the [`CREATE CONNECTION`](/sql/create-connection/#aws) command for details. 1. Use the [`CREATE CONNECTION`](/sql/create-connection/) command to create another connection object, this time with database access and authentication details for Materialize to use: ```mzsql CREATE CONNECTION mysql_connection TO MYSQL ( HOST '', SSH TUNNEL ssh_connection ); ``` - Replace `` with your MySQL endpoint. AWS IAM authentication is also available, see the [`CREATE CONNECTION`](/sql/create-connection/#mysql) command for details. ### 3. Start ingesting data Once you have created the connection, you can use the connection in the [`CREATE SOURCE`](/sql/create-source/) command to connect to your MySQL instance and start ingesting data: ```mzsql 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 (,)` or `FOR TABLES (, )` options instead of `FOR ALL TABLES`. - To handle [unsupported data types](#supported-types), use the `TEXT COLUMNS` or `EXCLUDE COLUMNS` options. After source creation, refer to [schema changes considerations](#schema-changes) for information on handling upstream schema changes. [//]: # "TODO(morsapaes) Replace these Step 6. and 7. with guidance using the new progress metrics in mz_source_statistics + console monitoring, when available (also for PostgreSQL)." ### 4. 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. 1. Back in the SQL client connected to Materialize, use the [`mz_source_statuses`](/reference/system-catalog/mz_internal/#mz_source_statuses) table to check the overall status of your source: ```mzsql 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](/support/). 2. Once the source is running, use the [`mz_source_statistics`](/reference/system-catalog/mz_internal/#mz_source_statistics) table to check the status of the initial snapshot: ```mzsql 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; ```

```nofmt 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. ### 5. 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. 1. Still in a SQL client connected to Materialize, use the [`ALTER CLUSTER`](/sql/alter-cluster/) command to downsize the cluster to `100cc`: ```mzsql ALTER CLUSTER ingest_mysql SET (SIZE '100cc'); ``` Behind the scenes, this command adds a new `100cc` replica and removes the `200cc` replica. 1. Use the [`SHOW CLUSTER REPLICAS`](/sql/show-cluster-replicas/) command to check the status of the new replica: ```mzsql SHOW CLUSTER REPLICAS WHERE cluster = 'ingest_mysql'; ```

```nofmt cluster | replica | size | ready -----------------+---------+--------+------- ingest_mysql | r1 | 100cc | t (1 row) ``` ## D. Explore your data 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`](/sql/show-sources) and [`SELECT`](/sql/select/). - Compute real-time results in memory with [`CREATE VIEW`](/sql/create-view/) and [`CREATE INDEX`](/sql/create-index/) or in durable storage with [`CREATE MATERIALIZED VIEW`](/sql/create-materialized-view/). - Serve results to a PostgreSQL-compatible SQL client or driver with [`SELECT`](/sql/select/) or [`SUBSCRIBE`](/sql/subscribe/) or to an external message broker with [`CREATE SINK`](/sql/create-sink/). - Check out the [tools and integrations](/integrations/) supported by Materialize. ## Considerations ### Schema changes > **Note:** Work to more smoothly support ddl changes to upstream tables is currently in > progress. The work introduces the ability to re-ingest the same upstream table > under a new schema and switch over without downtime. Materialize supports schema changes in the upstream database as follows: #### Compatible schema changes

  • Adding columns to tables. Materialize will not ingest new columns added upstream unless you use DROP SOURCE to first drop the affected subsource, and then add the table back to the source using ALTER SOURCE...ADD SUBSOURCE.

  • Dropping columns that were added after the source was created. These columns are never ingested, so you can drop them without issue.

  • Adding or removing NOT NULL constraints to tables that were nullable when the source was created.

#### Incompatible schema changes

All other schema changes to upstream tables will set the corresponding subsource into an error state, which prevents you from reading from the subsource.

To handle incompatible schema changes, use DROP SOURCE to first drop the affected subsource, and then ALTER SOURCE...ADD SUBSOURCE to add the subsource back to the source. When you add the subsource, it will have the updated schema from the corresponding upstream table.

### 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.

### Truncation

Avoid truncating upstream tables that are being replicated into Materialize. If a replicated upstream table is truncated, the corresponding subsource in Materialize becomes inaccessible and will not produce any data until it is recreated.

Instead of truncating, use an unqualified DELETE to remove all rows from the upstream table:

DELETE FROM t;
### Modifying an existing source When you add a new subsource to an existing source ([`ALTER SOURCE ... ADD SUBSOURCE ...`](/sql/alter-source/)), 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.