# Ingest data from Azure DB How to stream data from Azure DB for MySQL to Materialize This page shows you how to stream data from [Azure DB for MySQL](https://azure.microsoft.com/en-us/products/MySQL) 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 8.0.1+ with support for [GTID-based binary log (binlog) replication](#1-enable-gtid-based-binlog-replication). - 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 Azure DB ### 1. Enable GTID-based binlog replication > **Note:** GTID-based replication is supported for Azure DB for MySQL [flexible server](https://learn.microsoft.com/en-us/azure/mysql/flexible-server/overview-single). > It is **not supported** for single server databases. Before creating a source in Materialize, you **must** configure Azure DB for 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_row_image FULL
binlog_row_metadata FULL
binlog_format ROW Deprecated as of MySQL 8.0.34. Newer versions of MySQL default to row-based logging.
gtid_mode ON
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 Azure DB, see the [Azure documentation](https://learn.microsoft.com/en-us/azure/mysql/flexible-server/how-to-data-in-replication?tabs=shell%2Ccommand-line#configure-the-source-mysql-server). ### 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 Azure DB 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 firewall 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. **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. Update your [Azure DB firewall rules](https://learn.microsoft.com/en-us/azure/azure-sql/database/firewall-configure?view=azuresql) to allow traffic from each IP address from the previous step. **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. 1. [Launch an Azure VM with a static public IP address](https://learn.microsoft.com/en-us/azure/virtual-network/ip-services/virtual-network-deploy-static-pip-arm-portal?toc=%2Fazure%2Fvirtual-machines%2Ftoc.json) to serve as your SSH bastion host. - Make sure the VM is publicly accessible and in the same VPC as your database. - Add a key pair and note the username. You'll use this username when connecting Materialize to your bastion host. - Make sure the VM has a static public IP address. You'll use this IP address when connecting Materialize 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. Update your SSH bastion host's [firewall rules](https://learn.microsoft.com/en-us/azure/virtual-network/tutorial-filter-network-traffic?toc=%2Fazure%2Fvirtual-machines%2Ftoc.json) to allow traffic from each IP address from the previous step. 1. Update your [Azure DB firewall rules](https://learn.microsoft.com/en-us/azure/azure-sql/database/firewall-configure?view=azuresql) to allow traffic from the SSH bastion host. **Self-Managed:** Configure your network to allow Materialize to connect to your database. For example, you can: - **Allow Materialize IPs:** Configure your database's security group to allow connections from Materialize. - **Use an SSH tunnel:** Use an SSH tunnel to connect Materialize to the database. > **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. Update your [Azure DB firewall rules](https://learn.microsoft.com/en-us/azure/azure-sql/database/firewall-configure?view=azuresql) to allow traffic from Materialize IPs. **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. 1. [Launch an Azure VM with a static public IP address](https://learn.microsoft.com/en-us/azure/virtual-network/ip-services/virtual-network-deploy-static-pip-arm-portal?toc=%2Fazure%2Fvirtual-machines%2Ftoc.json) to serve as your SSH bastion host. - Make sure the VM is publicly accessible and in the same VPC as your database. - Add a key pair and note the username. You'll use this username when connecting Materialize to your bastion host. - Make sure the VM has a static public IP address. You'll use this IP address when connecting Materialize to your bastion host. 1. Configure the SSH bastion host to allow traffic only from Materialize. 1. Update your [Azure DB firewall rules](https://learn.microsoft.com/en-us/azure/azure-sql/database/firewall-configure?view=azuresql) to allow traffic from the SSH bastion host. ## 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](/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/#available-sizes) `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 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 {{< tabs level=4 >}} {{< tab "New Syntax" >}} {{% include-example file="examples/ingest_data/mysql/create_source_cloud" example="create-source" %}} {{% include-example file="examples/ingest_data/mysql/create_source_cloud" example="schema-changes" %}} {{< /tab >}} {{< tab "Legacy Syntax" >}} {{% include-example file="examples/ingest_data/mysql/create_source_cloud" example="create-source-legacy" %}} {{% include-example file="examples/ingest_data/mysql/create_source_cloud" example="create-source-options-legacy" %}} {{% include-example file="examples/ingest_data/mysql/create_source_cloud" example="schema-changes" %}} {{< /tab >}} {{< /tabs >}} ### 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 ### 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](/sql/types/)**, you can: - Use [`TEXT COLUMNS` option](/sql/create-source/mysql/#handling-unsupported-types) 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`](/sql/create-source/mysql/#excluding-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`](/sql/create-source/mysql/#handling-unsupported-types) 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"`). ### 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. ## 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`](/sql/create-source/mysql-v2/) syntax, create a new table from the source. See [Handle upstream column addition](/ingest-data/mysql/source-versioning/#handle-upstream-column-addition). - If using the legacy [`CREATE SOURCE ... FOR ...`](/sql/create-source/mysql/) syntax that creates subsources, use [`DROP SOURCE`](/sql/drop-source/) to drop the affected subsource, and then add the table back to the source using [`ALTER SOURCE ... ADD SUBSOURCE`](/sql/alter-source/). 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`](/sql/create-source/mysql-v2/) syntax, you can safely drop a column by first ignoring it in Materialize. See [Handle upstream column drop](/ingest-data/mysql/source-versioning/#handle-upstream-column-drop). - If using legacy [`CREATE SOURCE ... FOR ...`](/sql/create-source/mysql/) syntax, use [`DROP SOURCE`](/sql/drop-source/) to drop the affected subsource, and then add the table back to the source using [`ALTER SOURCE ... ADD SUBSOURCE`](/sql/alter-source/). ### 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.