Add MySQL Database CDC source to an eventstream

Note

This article contains references to the term SLAVE, a term that Microsoft no longer uses. When the term is removed from the software, we'll remove it from this article.

This article shows you how to add an Azure Database for MySQL Change Data Capture source to an eventstream. The Azure MySQL Database Change Data Capture (CDC) Source connector for Microsoft Fabric event streams allows you to capture a snapshot of the current data in an Azure Database for MySQL database.

You can specify the tables to monitor, and the eventstream records any future row-level changes to the tables. Once the changes are captured in the eventstream, you can process this CDC data in real-time and send it to different destinations in Fabric for further processing or analysis.

Note

This source is not supported in the following regions of your workspace capacity: West US3, Switzerland West.

Prerequisites

• Access to a workspace in the Fabric capacity license mode (or) the Trial license mode with Contributor or higher permissions.
• Access to an instance of Azure Database for MySQL - Flexible Server.
• Your MySQL database must be publicly accessible and not be behind a firewall or secured in a virtual network.
• If you don't have an eventstream, create an eventstream.

Set up MySQL DB

The connector uses the Debezium MySQL connector to capture changes in your Azure Database for MySQL database. You must define a MySQL user with appropriate privileges on all databases where the Messaging Connector can capture the changes from. You can directly use the admin user to connect to the database which normally has the appropriate privileges already as below. or you can follow the below steps to create a new user

Note

The new user or admin account and the corresponding password will be used to connect to database later inside Eventstream.

1. At the mysql command prompt, create the MySQL user:

   mysql> CREATE USER 'user'@'%' IDENTIFIED BY 'password';
   

2. Grant the required privileges to the user:

   mysql> GRANT SELECT, SHOW DATABASES, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'user'@'%';
   

3. Finalize the user's permissions:

   mysql> FLUSH PRIVILEGES;
   

To confirm if the user or admin has the required privileges granted, run the below command and then the required privileges in step#2 above should be shown.

SHOW GRANTS FOR user;

For more information about granting the required permissions to the user, see Debezium connector for MySQL: Debezium Documentation.

Enable the binlog

You must enable binary logging for MySQL replication. The binary logs record transaction updates for replication tools to propagate changes.

1. On the Azure portal page for your Azure Database for MySQL account, select Server parameters under Settings in the left navigation.

2. On the Server parameters page, configure the following properties, and then select Save.

   • For binlog_row_image, select full.

   • For binlog_expire_logs_seconds, set the number of seconds the service waits before the binary log file is purged. Set the value to match the needs of your environment, for example 86400.

   

Add Azure MySQL DB (CDC) as a source

If you haven't added any source to your eventstream yet, select Use external source tile.

If you're adding the source to an already published eventstream, switch to Edit mode, select Add source on the ribbon, and then select External sources.

 

On the Select a data source page, search for and select Connect on the MySQL DB (CDC) tile.

Configure and connect to Azure MySQL DB (CDC)

1. On the Connect screen, under Connection, select New connection to create a cloud connection.

   

2. Enter the following Connection settings and Connection credentials for your Azure MySQL DB, and then select Connect.

   • Server: The server address of your Azure Database for MySQL database, for example my-mysql-server.mysql.database.azure.com.

   • Database: The database name, for example my_database.

   • Connection name: Automatically generated, or you can enter a new name for this connection.

   • Username and Password: Enter the credentials for your Azure Database for MySQL database. Make sure you enter the server admin account or the user account created with required privileges granted.

     

3. Enter the following information to configure the Azure MySQL DB CDC data source, and then select Next.

   • Table(s): Enter a list of table names separated by commas. Each table name must follow the format <database name>.<table name>, for example my_database.users.

   • Server ID: Enter a unique value for each server and replication client in the MySQL cluster. The default value is 1000.

   • Port: Leave the default value unchanged.

     

   You can also edit source name by selecting the Pencil button for Source name in the Stream details section to the right.

   Note

   Set a different Server ID for each reader. Every MySQL database client for reading binlog should have a unique id, called Server ID. MySQL Server uses this ID to maintain the network connection and the binlog position. Different jobs sharing the same Server ID can result in reading from the wrong binlog position. Therefore, it's recommended to set a different Server ID for each reader.

4. On the Review + connect page, after reviewing the summary for Azure MySQL DB CDC source, select Add to complete the configuration.

   

Note

The maximum number of sources and destinations for one eventstream is 11.

View updated eventstream

1. You see the Azure MySQL DB (CDC) source added to your eventstream in Edit mode.

   

2. Select Publish to publish the changes and begin streaming Azure MySQL DB CDC data to the eventstream.

   

Related content

Other connectors:

• Amazon Kinesis Data Streams
• Azure Cosmos DB
• Azure Event Hubs
• Azure IoT Hub
• Azure SQL Database Change Data Capture (CDC)
• Confluent Kafka
• Custom endpoint
• Google Cloud Pub/Sub
• PostgreSQL Database CDC
• Sample data
• Azure Blob Storage events
• Fabric workspace event