Use Zero-ETL to synchronize PolarDB for MySQL data in real time - AnalyticDB

Key concepts

Term	Definition
Zero-ETL task	A managed synchronization job that continuously replicates DML and DDL changes from a PolarDB for MySQL source cluster to an AnalyticDB for MySQL destination cluster.
Source cluster	The PolarDB for MySQL cluster where transactional data originates.
Destination cluster	The AnalyticDB for MySQL cluster where replicated data is available for analytics.
ACU	AnalyticDB compute unit. The resource unit used to calculate how many zero-ETL tasks a cluster can run concurrently.

Supported sources

Zero-ETL supports the following data synchronization paths:

PolarDB for MySQL to AnalyticDB for MySQL (this guide)
ApsaraDB RDS for MySQL to AnalyticDB for MySQL. For details, see Use zero-ETL to synchronize data.

Benefits

No pipeline setup: Select the source and destination clusters and synchronization starts automatically. No ETL code or third-party tools required.
No extra cost: Zero-ETL tasks are free. Analyzing the synchronized data in AnalyticDB for MySQL incurs no additional charges.
Multi-source aggregation: Synchronize data from multiple PolarDB for MySQL clusters into a single AnalyticDB for MySQL cluster for unified analytics.

Supported regions

Zero-ETL is available in the following regions: China (Beijing), China (Hangzhou), China (Shanghai), China (Shenzhen), China (Zhangjiakou), China (Qingdao), China (Guangzhou), China (Hong Kong), Singapore, US (Silicon Valley), and US (Virginia).

Task limits

The maximum number of zero-ETL tasks per AnalyticDB for MySQL cluster depends on the cluster's total reserved resources:

Total reserved ACUs	Maximum zero-ETL tasks
Less than 24 ACUs	1
24 ACUs or more	`3 + 3 × [(Total ACUs − 24) ÷ 50]` (rounded down)

Example: A cluster with 48 ACUs gives 3 + 3 × [(48 − 24) ÷ 50] = 3 + 3 × 0 = 3 tasks.

How ACUs are counted varies by cluster edition:

Data Lakehouse Edition: ACUs = reserved computing resource ACUs + reserved storage resource ACUs.

Data Warehouse Edition (elastic mode): ACUs = computing resource cores + elastic I/O resource cores.

Data Warehouse Edition (reserved mode): ACUs = computing resource cores.

If a cluster reaches its task limit, delete unused zero-ETL tasks in the AnalyticDB for MySQL console, or create additional tasks directly in the Data Transmission Service (DTS) console.

Prerequisites

Before you begin, ensure that you have:

An AnalyticDB for MySQL cluster and a PolarDB for MySQL cluster in the same region. To create a PolarDB for MySQL cluster, see Purchase clusters
Database accounts for both clusters:
- AnalyticDB for MySQL: Create a database account
- PolarDB for MySQL: Create and manage a database account
Binary logging enabled for the PolarDB for MySQL cluster. See Enable binary logging

Set up permissions

Create a service-linked role for AnalyticDB for MySQL

Log on to the Resource Access Management (RAM) console. In the left-side navigation pane, choose Identities > Roles.
Check whether a service-linked role named AliyunServiceRoleForAnalyticDBForMySQL exists. If it does not exist, create it:
1. Click Create Role.
2. In the Select role type step, select Alibaba Cloud Service and click Next.
3. In the Configure role step, set Role type to Service linked role, then select AnalyticDB for MySQL from the Select service drop-down list.
4. Click OK and verify the role appears in the list.

Grant a RAM user permission to manage zero-ETL tasks

A RAM user needs two sets of permissions to create and manage zero-ETL tasks.

Step 1: Attach the AnalyticDB for MySQL full-access policy

Attach the AliyunADBFullAccess policy to the RAM user. For instructions, see Grant permissions to a RAM user.

Step 2: Create and attach a custom policy for zero-ETL

Create a custom policy that grants DTS permissions on the source and destination clusters, then attach it to the RAM user. For instructions on creating custom policies, see Create custom policies.

Use one of the following policy templates:

Grant permissions for all PolarDB for MySQL instances and AnalyticDB for MySQL clusters

{
    "Version": "1",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": "dts:*",
            "Resource": [
                "acs:adb:*:*:*",
                "acs:polardb:*:*:*"
            ]
        },
        {
            "Effect": "Allow",
            "Action": [
                "dts:DescribeRegions",
                "dts:DescribeConfigRelations",
                "dts:DescribeSrcLinkConfig",
                "dts:DescribeDestLinkConfig",
                "dts:DescribeLinkConfig"
            ],
            "Resource": [
                "acs:dts:*:*:*"
            ]
        }
    ]
}

Grant permissions for specific PolarDB for MySQL instances and AnalyticDB for MySQL clusters

{
    "Version": "1",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": "dts:*",
            "Resource": [
                "acs:adb:*:*:dbcluster/am-2zeod8ax4b9a****",
                "acs:polardb:*:*:dbcluster/pc-bp13jqn4a26b0****"
            ]
        },
        {
            "Effect": "Allow",
            "Action": [
                "dts:DescribeRegions",
                "dts:DescribeConfigRelations",
                "dts:DescribeSrcLinkConfig",
                "dts:DescribeDestLinkConfig",
                "dts:DescribeLinkConfig"
            ],
            "Resource": "acs:dts:*:*:*"
        }
    ]
}

Replace am-2zeod8ax4b9a**` with your AnalyticDB for MySQL cluster ID and `pc-bp13jqn4a26b0** with your PolarDB for MySQL cluster ID.

Create a zero-ETL task

Log on to the AnalyticDB for MySQL console. In the upper-left corner, select your region. In the left-side navigation pane, click Clusters. On the Clusters page, click the edition tab for your cluster, find the cluster, and click its ID.
Navigate to the zero-ETL configuration page:
- Enterprise Edition, Basic Edition, or Data Lakehouse Edition: In the left-side navigation pane, choose Data Ingestion > Database Data Synchronization.
- Data Warehouse Edition: In the left-side navigation pane, choose Data Transmission Service > Zero-ETL.

Click Create Zero-ETL Task. On the Create Zero-ETL Task page, configure the source cluster parameters: Configure the destination cluster parameters:

If you select SSL-encrypted, enable SSL encryption for the PolarDB for MySQL cluster first. See Configure SSL encryption.

Parameter	Description
Task name	A name for the zero-ETL task.
Database type	The source database engine. Select PolarDB for MySQL.
Access method	Automatically set to Alibaba Cloud Instance.
Instance region	The region where the PolarDB for MySQL cluster resides.
PolarDB cluster ID	The ID of the PolarDB for MySQL cluster.
Database account	The database account for the PolarDB for MySQL cluster.
Database password	The password for the database account.
Encryption	The connection encryption method. Select Non-encrypted or SSL-encrypted.

Parameter	Description
Database type	Automatically set to AnalyticDB for MySQL 3.0.
Access method	Automatically set to Alibaba Cloud Instance.
Instance region	The region where the AnalyticDB for MySQL cluster resides.
Instance ID	The ID of the AnalyticDB for MySQL cluster.
Database account	The database account for the AnalyticDB for MySQL cluster.
Database password	The password for the database account.

Click Test Connectivity and Proceed. Configure synchronization settings:

Parameter	Description
DDL and DML operations to be synchronized	The DML operations (INSERT, UPDATE, DELETE) and DDL operations (CREATE, ALTER, DROP, TRUNCATE) to synchronize. All operations are selected by default.
Source objects and selected objects	The databases and tables to synchronize.
Advanced settings (optional)	Retry intervals for connection failures and other errors on the source and destination clusters.

Click Next: Configure Database and Table Fields. Configure the table mapping parameters:

Parameter	Description
Database name	The name of the selected database.
Table name	The name of the selected table.
Primary key column	The primary key column of the table.
Distribution key	The distribution key column of the table.
Type	The table type: partitioned table or replicated table.
Definition status	The configuration status of the table. Changes from Undefined to Defined after you configure the table fields.

Click Next: Save Task Settings and Precheck.
After the precheck completes successfully, click Start.

If the precheck fails, review the error details and verify that all prerequisites are met—binary logging is enabled on the PolarDB for MySQL cluster, database accounts exist on both clusters, and the RAM user has the required permissions.

The zero-ETL task appears on the zero-ETL configuration page, where you can view the task Name, Source/Destination, and Status.

Monitor zero-ETL tasks and set alert rules

After starting a zero-ETL task, configure monitoring and alerting in CloudMonitor.

View monitoring data:

Log on to the CloudMonitor console.
In the left-side navigation pane, choose Cloud Service Monitoring > Cloud Service Monitoring.
Hover over the AnalyticDB for MySQL card and click AnalyticDB for mysql 3.0 - ZeroETL Latency.
Click the cluster ID to view monitoring details for your zero-ETL tasks.

Create an alert rule:

In the left-side navigation pane, choose Alerts > Alert Rules.
Click Create Alert Rule and configure the alert parameters. Set Product to AnalyticDB for mysql 3.0 - ZeroETL Latency. For details, see Create an alert rule.

Create a subscription policy:

In the left-side navigation pane, choose Event Center > Event Subscription.
Click Create Subscription Policy and configure the subscription parameters: For details, see Manage event subscription policies (recommended).
- Products: Select AnalyticDB for MySQL V3.0.
- Event type: Select Abnormal or Restore.
- Event name: Select zero_etl_job_abnormal or zero_etl_job_restore.

What's next

To synchronize data from ApsaraDB RDS for MySQL to AnalyticDB for MySQL, see Use zero-ETL to synchronize data.