Archive data to a PolarDB for MySQL cluster

Prerequisites

Before you begin, make sure 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

• The REPLICATION CLIENT permission granted to the MySQL database account

• A PolarDB for MySQL cluster purchased. For details, see Purchase procedures.

• Source tables that have a primary key or unique key

• (Recommended) A timestamp column in source tables to use as a filter condition

Risks before you start

Review the following risks before configuring the archiving task.

Storage space for source cleanup

If you enable Post-behavior to delete archived rows from the source, DMS creates temporary backup tables in the source database before deletion. Make sure the source database has enough storage space for these tables. Insufficient space can cause the archiving job to fail or the source instance to become unavailable.

Control mode requirement for recurring tasks

For a recurring archiving task, both the source and destination databases must be managed in Security Collaboration or Stable Change mode. A one-time task has no control mode requirement.

If an instance is in Stable Change mode and you need recurring runs, enable security hosting for that instance or change its control mode to Security Collaboration. Without this, the task may fail when the instance login expires. See Enable security hosting and Change the control mode of an instance.

Billing

You are charged for the PolarDB for MySQL cluster. For details, see Billable items.

Archive data to a PolarDB for MySQL cluster

The following example archives orders older than 6 months to a PolarDB for MySQL cluster on a recurring monthly schedule.

1. Log in to the DMS console V5.0.

2. In the top navigation bar, choose Solution > Data Archiving.

   In simple mode, hover over the icon in the upper-left corner and choose All functions > Solution > Data Archiving.

3. In the upper-right corner of the Data Archiving Tickets page, click Data Archiving.

4. On the Ticket Application page, configure the following parameters.

   Parameter	Required	Description
   Task Name	Yes	A descriptive name for the archiving task.
   Archiving Destination	Yes	Select PolarDB MySQL.
   PolarDB Cluster	Yes	The destination PolarDB for MySQL cluster.
   Source Database	Yes	The source database from which to archive data.
   Archive Configuration	Yes	The source tables and optional filter conditions. To filter by date (for example, rows older than 6 months), define a time variable in Variable Configuration first, then reference it here as ${variable_name}. Click Add to include more source tables.
   Archive Table Mapping	No	Destination table settings. Click Edit in the Actions column to specify the table name, columns, shard keys, and partition keys in the destination database.
   Variable Configuration	No	Time variables for filter conditions. For example, a variable named 6_month_ago in yyyy-MM-dd format with an offset of -6 months resolves to 2021-02-11 when the current date is August 12, 2021. For configuration details, see Configure time variables.
   Post-behavior	No	Whether to delete archived rows from the source table after archiving succeeds: <br>- Clean up the archived data of the original table (delete-No Lock): DMS deletes rows from the source table after writing them to the destination. DMS creates temporary backup tables in the source database before deletion. After verifying the archived data, submit a regular data change ticket to drop those backup tables. <br>- Not selected: Rows remain in the source table. Delete them manually with a regular data change ticket, then reclaim space with a lock-free DDL ticket.
   Operation Mode	Yes	Single execution: DMS runs the task once after the ticket is approved. <br>Cyclic scheduling: DMS runs the task on a recurring schedule. For scheduling options, see the Periodic scheduling section in the Lindorm archiving topic.

5. Click Submit.

After the ticket is approved, DMS runs the archiving task automatically. Wait until the data archiving task is complete.

If the task fails, click Details in the Actions column of the Execute step to view the task logs. For failures caused by a network or database connection issue, click Retry Breakpoint to resume from where the task stopped.

DMS automatically creates the destination database and tables using the same names as the source database and tables.

Query the archived data

After the archiving task completes:

1. On the Ticket Details page, go to the Basic Information section and click View next to Target Database to open the SQL Console.

2. On the left-side Table tab of the SQL Console, double-click the table name, then click Execute to view the archived rows.

Each archived table includes four additional metadata columns that do not affect the original data:

What's next

• To drop the temporary backup tables created during source cleanup, submit a regular data change ticket.

• To reclaim storage space in the source tables after manual deletion, submit a lock-free DDL ticket.