How to Real-Time Sync MySQL RDS to Analytic DB with Alibaba Cloud Data Transmission

Overview

Alibaba Cloud Data Transmission Service (DTS) provides combined data migration, subscription, and real‑time synchronization for structured storage products. It enables millisecond‑level asynchronous data transfer across public and hybrid cloud environments, allowing changes in an RDS for MySQL instance to be streamed instantly to an analytical database.

Prerequisites

Enable the Data Transmission service in the same Alibaba Cloud account that owns the RDS for MySQL instance.

Download the dts-ads-writer plugin, unzip it on a server that can access the Internet (an Alibaba Cloud ECS instance is recommended for reliability).

Install Java 6 or newer (JRE/JDK) on the server.

Step‑by‑Step Procedure

Create the target table in the analytical database. Set the table’s data update type to real‑time write and ensure column names match those in MySQL.

In the DTS console, create a data subscription channel and note its channel ID (Tunnel ID).

Edit the dts-ads-writer/app.conf file with the parameters shown in the configuration example below. Save the file and restart the writer whenever the file is modified.

Start the writer process: ./bin/startup.sh or sh bin/startup.sh Monitor the writer process and its log files. Errors are logged in the form ErrorCode=XXXX ErrorMessage=XXXX.

Configuration Example (app.conf)

{
  "dtsAccessId": "YOUR_ACCESS_ID",
  "dtsAccessKey": "YOUR_ACCESS_KEY",
  "dtsTunnelId": "YOUR_TUNNEL_ID",
  "adsUserName": "ADS_USERNAME",
  "adsPassword": "ADS_PASSWORD",
  "adsJdbcUrl": "jdbc:mysql://HOST:PORT/DATABASE",
  "tables": [
    {
      "source": {
        "primaryKeys": ["col1", "col2"],
        "db": "rds_db",
        "table": "rds_table",
        "skipColumns": ["col3"]
      },
      "target": {
        "table": "ads_table"
      },
      "columnMapping": {
        "col1": "col1_ads",
        "col2": "col2_ads"
      }
    }
  ]
}

Key Considerations

The primary‑key definitions of the source RDS table and the target analytical table must be identical; otherwise data inconsistency may occur.

Only one analytical database can be specified per writer process via adsJdbcUrl.

A writer process can bind to only one data subscription channel. Changing the channel requires a process restart.

DDL statements executed on the MySQL side are not synchronized to the analytical database.

Changes to app.conf take effect only after the writer is restarted.

If a full resynchronization is required, the maximum replay window is the most recent 24 hours; adjust the consumption offset in the DTS console.

Synchronization throughput is limited by the server’s Internet bandwidth and disk IOPS.