Add MySQL Database CDC source to an eventstream (preview)

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.

Important

Enhanced capabilities of Fabric event streams are currently in preview.

Note

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

Prerequisites

  • Access to the Fabric premium workspace 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.

Note

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

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.

    A screenshot of the binlog settings for replication under Server parameters.

Add Azure MySQL DB (CDC) as a source

  1. In Fabric Real-Time Intelligence, select Eventstream to create a new eventstream. Make sure the Enhanced Capabilities (preview) option is enabled.

    A screenshot of creating a new eventstream.

  2. On the next screen, select Add external source.

    A screenshot of selecting Add external source.

Configure and connect to Azure MySQL DB (CDC)

  1. On the Select a data source screen, select Azure MySQL DB (CDC) as the data source.

    A screenshot of selecting Azure MySQL DB (CDC).

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

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

    A screenshot of the connection settings for Azure MySQL DB (CDC).

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

    A screenshot of selecting Tables, Server ID, and Port for the Azure MySQL DB (CDC) connection.

    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.

  5. After reviewing the summary for Azure MySQL DB CDC source, select Add to complete the configuration.

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

A screenshot of the added Azure MySQL DB CDC source in Edit mode with the Publish button highlighted.

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

A screenshot of the published eventstream with Azure MySQL DB CDC source in Live View.

Other connectors: