Add MySQL Database CDC source to an eventstream (preview)

Article
05/23/2024

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.

At the mysql command prompt, create the MySQL user:

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

Grant the required privileges to the user:

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

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.

On the Azure portal page for your Azure Database for MySQL account, select Server parameters under Settings in the left navigation.
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

In Fabric Real-Time Intelligence, select Eventstream to create a new eventstream. Make sure the Enhanced Capabilities (preview) option is enabled.
On the next screen, select Add external source.

Configure and connect to Azure MySQL DB (CDC)

On the Select a data source screen, select Azure MySQL DB (CDC) as the data source.
On the Connect screen, under Connection, select New connection to create a cloud connection.
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.
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.
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.
After reviewing the summary for Azure MySQL DB CDC source, select Add to complete the configuration.