Data Management:Archive data to an RDS MySQL instance

Prerequisites

Before you begin, ensure that you have:

• A source database of one of the supported 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 MySQL database account granted the REPLICATION CLIENT permission

• An ApsaraDB RDS for MySQL instance as the archiving destination. For instructions, see Create an ApsaraDB RDS for MySQL instance

• Source tables with a primary key or a unique key

• (Recommended) A timestamp column in each source table to use as a filter condition for time-based archiving

Billing

You are charged for the ApsaraDB RDS for MySQL destination instance. For details, see Billable items.

Usage notes

• Storage space: If you configure the job to delete source data after archiving, DMS first writes the rows to temporary backup tables in the source database before deleting them. Make sure the source database has enough storage space to hold the temporary backup tables. Insufficient space can cause the source instance to become unavailable.

• Scheduling mode and control mode: DMS runs an archiving job on a recurring schedule only when both the source and destination databases are managed in Security Collaboration mode. For a one-time job, the databases can be in any control mode. To change the control mode, see Change the control mode of an instance.

How it works

When an archiving job runs, DMS reads rows from the specified source tables that match the filter conditions and writes them to the destination ApsaraDB RDS for MySQL instance. DMS automatically creates a database and tables in the destination instance using the same names as the source database and tables. Four metadata columns are appended to each archived table: archiving information (ticket number and time), source database name, source table name, and source instance ID. Existing data columns are not affected.

If the Post-behavior option is set to delete source data, DMS moves the matched rows into temporary backup tables in the source database before running the DELETE statement. After you verify that the archived data is correct, submit a regular data change ticket to drop the temporary backup tables.

Archive data

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

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

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

3. In the upper-right corner of the Archive DataTickets page, click Archive Data.

4. On the Ticket Application page, configure the parameters described in the following table.

   Parameter	Required	Description
   Task Name	Yes	A descriptive name for the archiving task. A clear name reduces the need for follow-up communication.
   Archiving Destination	Yes	The destination type. Select RDS MySQL.
   ApsaraDB RDS Instance	Yes	The destination ApsaraDB RDS for MySQL instance.
   Source Database	Yes	The source database from which data is archived.
   Archive configuration	Yes	One or more source tables to archive from. Optionally, specify filter conditions to select specific rows. To archive data from a time range (for example, data older than six months), configure time variables in the Variable Configuration section first, then reference them in the filter conditions. Click Add to include additional source tables.
   Archive Table Mapping	No	Custom settings for the destination tables. Click Edit in the Actions column to specify the destination table name, columns, shard keys, and partition keys.
   Variable Configuration	No	Time variables for use in filter conditions. For example, a variable named 6_month_ago with format yyyy-MM-dd and offset -6 Month evaluates to 2021-02-11 when the current date is August 12, 2021. Reference the variable in filter conditions as ${6_month_ago}. For configuration steps, see the Configure time variables section of the "Variables" topic.
   Post-behavior	No	Whether to delete the archived rows from the source table after archiving. If you select Clean up the archived data of the original table (delete-No Lock), DMS deletes the rows using a lock-free DELETE statement. Temporary backup tables are created in the source database during this process. After you verify the archived data, submit a regular data change ticket to drop the temporary backup tables. If you leave this option unselected, archived rows remain in the source table and you must delete them and optimize storage manually: create a regular data change ticket to delete the data, and a lock-free DDL ticket to optimize storage usage.
   Running Mode	Yes	When to run the archiving job. Single execution runs the job once after the ticket is approved. Cyclic scheduling runs the job repeatedly on the schedule you specify—both databases must be in Security Collaboration mode. For scheduling options, see the Periodic scheduling section of the "Archive data to a Lindorm instance" topic.

5. Click Submit.

After the ticket is approved, DMS automatically runs the archiving job. The archived data is stored as tables in the destination instance.

If the job fails, click Details in the Actions column of the Execute step to view the task logs and identify the cause. If the failure was caused by a network or database connection issue, click Retry Breakpoint to resume the job from where it stopped.

Query archived data

After the archiving job completes, follow these steps to view the archived data:

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

2. On the Table tab on the left, find and double-click the table you want to view, then click Execute to load the data.

Each archived table contains four additional metadata columns: archiving information (ticket number and time), source database name, source table name, and source instance ID. These columns do not affect the original data.

What to do next

• To configure recurring archiving schedules, see the Periodic scheduling section of the "Archive data to a Lindorm instance" topic.