Archive data to an AnalyticDB for MySQL V3.0 cluster

How it works

When you submit a data archiving ticket, DMS runs the following workflow:

Reads data from the source tables based on the filter conditions you specify.
Writes the data to the destination AnalyticDB for MySQL V3.0 cluster.
If you enabled source data cleanup, creates temporary backup tables in the source database, then deletes the archived rows from the source tables.

DMS appends four metadata columns to each archived table: archiving ticket number and timestamp, source database name, source table name, and source instance ID. These columns do not modify the original data.

Prerequisites

Make sure that you have:

A source database of one of the following types:
- MySQL: ApsaraDB RDS for MySQL, PolarDB for MySQL, or AnalyticDB for MySQL V3.0
- PostgreSQL: ApsaraDB RDS for PostgreSQL or PolarDB for PostgreSQL
- PolarDB-X
An AnalyticDB for MySQL V3.0 cluster. For more information, see Create a cluster
Source tables with a primary key or unique key
(Recommended) A timestamp field in source tables that records the last modification time, for use as a filter condition

The MySQL database account must have the REPLICATION CLIENT permission.

Usage notes

Regional availability: The data archiving feature is available only in the Singapore and Indonesia (Jakarta) regions.
Storage space for source data cleanup: If you set Post-behavior to Clean up the archived data of the original table (delete-No Lock), make sure that the source database has sufficient storage space. This prevents exceptions caused by insufficient storage space during data archiving. For example, archiving 390 GB of data requires at least 390 GB of free storage in the source database.
Control mode for periodic tasks: DMS runs a periodic data archiving task only when both the source and destination databases are managed in Security Collaboration or Stable Change mode. For a one-time task, any control mode works.

For periodic tasks with databases in Stable Change mode, enable security hosting or switch to Security Collaboration mode to prevent task failures caused by logon expiration. For more information, see Security hosting and Change the control mode of an instance.
Data Lakehouse Edition clusters: If you archive data to a Data Lakehouse Edition V3.0 cluster, make sure that the cluster has reserved computing resources. Otherwise, the task fails. For more information, see Scale a Data Lakehouse Edition cluster.

Billing

You are charged for the AnalyticDB for MySQL V3.0 cluster. For pricing details, see Pricing for Data Warehouse Edition and Pricing for Data Lakehouse Edition.

Create a data archiving task

Step 1: Open the data archiving page

Log on to the DMS console V5.0.
Click the icon in the upper-left corner, click All Features, and then click Data Archiving under the Solution section.

Step 2: Configure the archiving ticket

In the upper-right corner of the Data Archiving Tickets page, click Data Archiving.
On the Ticket Application page, configure the following parameters.

Parameter	Required	Description
Task Name	Yes	The name of the data archiving task. Specify a descriptive name that makes it easy to identify the task.
Archiving Destination	Yes	Select AnalyticDB-MySQL 3.0.
Analytics Engine	Yes	The destination AnalyticDB for MySQL V3.0 cluster.
Source Database	Yes	The source database from which to archive data.
Archiving Configuration	Yes	Specify filter conditions for the data to archive. Example: `gmt_modified<='${6_month_ago}'`. To archive multiple tables, click Add to add more source tables.
Archive Table Mapping	No	Customize the destination table settings. Click Edit in the Actions column to change the table name, columns, database shard keys, and partition keys.
Variable Configuration	No	Define time variables for filter conditions. For example, create a variable named `6_month_ago` with format `yyyy-MM-dd` and offset `-6 Month`. If today is August 12, 2021, `${6_month_ago}` resolves to `2021-02-11`. For more information, see the "Configure time variables" section of the Variables topic.
Post-behavior	No	Controls whether to delete archived data from the source tables. See Configure post-behavior for details.
Operation Mode	Yes	Single execution: DMS runs the task once after approval. Cyclic scheduling: DMS runs the task on a recurring schedule. For scheduling options, see the Periodic scheduling section of the "Archive data to a Lindorm instance" topic.

To use time variables in filter conditions, define the variables in Variable Configuration before setting up the Archiving Configuration.

Step 3: Submit and run the task

Click Submit.
After the ticket is approved, DMS runs the archiving task automatically. Wait for the task to complete.

Verify the result

After the task completes, verify the archived data by using one of the methods described in Query archived data.

If the task fails, click Details in the Actions column to view the task logs. For failures caused by network or database connection issues, click Retry to restart the task.

Configure post-behavior

Post-behavior controls whether DMS deletes archived data from the source tables.

Option 1: Delete archived data from source tables

Select Clean up the archived data of the original table (delete-No Lock).

DMS automatically deletes the archived rows from the source tables and creates temporary backup tables in the source database. After you verify that the archived data is correct, create a regular data change ticket to drop the temporary backup tables.

Important

Make sure that the source database has sufficient storage space. Insufficient storage can cause the source database to become unavailable.

Option 2: Keep archived data in source tables

If you do not select the cleanup option, the archived data remains in the source tables. To free up space manually:

Create a regular data change ticket to delete the archived data from the source tables.
Create a lock-free change ticket to optimize the storage usage of the source tables.

Query archived data

Method 1: Query in DMS

On the Ticket Details page, find the Basic Information section and click View next to Target Database. The SQL Console tab opens.
On the left-side Table tab, find the archived table, double-click the table name, and click Execute to view the data.

DMS creates the destination database and tables with the same names as the source. Four metadata columns are appended to each archived table: archiving ticket number and timestamp, source database name, source table name, and source instance ID.

Method 2: Query in AnalyticDB for MySQL

Connect to the AnalyticDB for MySQL V3.0 cluster directly and run queries against the archived tables. For more information, see Import and query data.