Data Management:Archive data to an ApsaraDB RDS for MySQL instance

Prerequisites

• The source database from which you want to archive data is of one of the following types:

  • MySQL: ApsaraDB RDS for MySQL, PolarDB for MySQL, and AnalyticDB for MySQL V3.0

  • PostgreSQL: ApsaraDB RDS for PostgreSQL and PolarDB for PostgreSQL

  • PolarDB-X

  Note

  The account of a MySQL database must have the REPLICATION CLIENT permission.

• An ApsaraDB RDS for MySQL instance is purchased. For more information, see Create an ApsaraDB RDS for MySQL instance.

• The source tables whose data you want to archive have a primary key or a unique key.

  Note

  We recommend that you provide a field that indicates the time of each data modification operation in the source tables. You can use this field as a filter condition when you archive data from the source tables.

Usage notes

• If you set the Post-behavior parameter to Clean up the archived data of the original table (delete-No Lock) when you configure a data archiving ticket, make sure that the source database has sufficient storage space. This prevents exceptions caused by insufficient storage space during data archiving.

• Data Management (DMS) runs a data archiving task periodically only if the source and destination databases are managed in Security Collaboration mode. If you need to run a data archiving task only once, the source and destination databases can be managed in any mode. For more information about how to change the control mode of a database instance, see Change the control mode of an instance.

• The data archiving feature is supported only in the Singapore and Indonesia (Jakarta) regions.

Billing

You are charged for the ApsaraDB RDS for MySQL instance that you purchase. For more information, see Billable items.

Procedure

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

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

   Note

   If you use the DMS console 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 to create a data archiving ticket. The following table describes the parameters.

   Parameter	Required	Description
   Task Name	Yes	The name of the data archiving task. We recommend that you specify a descriptive name that makes it easy to identify the task. This helps you reduce unnecessary communication.
   Archiving Destination	Yes	The destination to which you want to archive data. Select RDS MySQL.
   ApsaraDB RDS Instance	Yes	The destination ApsaraDB RDS for MySQL instance to which you want to archive data.
   Source Database	Yes	The source database from which you want to archive data.
   Archive configuration	Yes	Specify one or more source tables whose data you want to archive. Optional. Specify one or more filter conditions for querying data to be archived in the specified tables. Note If you want to use time variables in scenarios such as archiving data generated six months ago, you can set the variables in the Variable Configuration section before you configure the parameters in the Archive configuration section. Optional. Click Add to add more source tables.
   Archive Table Mapping	No	The table settings to be used in the destination database. You can click Edit in the Actions column of a source table and specify the name, columns, database shard keys, and partition keys of the corresponding archive table in the destination database.
   Variable Configuration	No	The variables that are used when you configure conditions for filtering archived data. For example, you have created a time variable named 6_month_ago in the yyyy-MM-dd format and set the offset to - 6 Month. In this case, if the current date is August 12, 2021, the value of the ${6_month_ago} variable is 2021-02-11, which indicates February 11, 2021. For more information about how to configure time variables, see the Configure time variables section of the "Variables" topic.
   Post-behavior	No	Specify whether to delete the archived data from the source tables. If you select Clean up the archived data of the original table (delete-No Lock), the archived data is automatically deleted from the source tables. You can execute the DELETE statement to delete temporary backup tables. The temporary backup tables are generated in the source database to store the archived data when the source tables are deleted. Make sure that the source database has sufficient storage space to prevent instance unavailability caused by insufficient storage space. After the data is archived and you verify that the archived data is correct, you can create a regular data change ticket to clear the temporary backup tables from the source database. If you do not select Clean up the archived data of the original table (delete-No Lock), the archived data is not deleted from the source tables. In this case, you need to manually delete data from the source tables and optimize the storage usage. To delete the archived data from the source tables, create a regular data change ticket. For more information, see Perform regular data change. To optimize the storage usage of the source tables, create a lock-free change ticket. For more information, see Perform lock-free DDL operations.
   Running Mode	Yes	The method to be used for running the data archiving task. Valid values: Single execution: After the data archiving ticket is approved, DMS runs the data archiving task only once. Cyclic scheduling: After the data archiving ticket is approved, DMS runs the data archiving task based on the scheduling cycle that you specify. For more information, see the Periodic scheduling section of the "Archive data to a Lindorm instance" topic.

5. Click Submit.

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

   If the data archiving task fails to be executed, you can click Details in the Actions column in the Execute step to view the logs of the data archiving task and identify the cause of the task failure. If an issue occurs due to a network or database connection failure, you can click Retry Breakpoint to restart the task.

   Note

   The archived data is stored in the database in the form of tables.

6. Optional. Query the archived data.

Query the archived data

1. After the data archiving task is complete, perform the following steps to view the archived data: In the Basic Information section of the Ticket Details page, click View next to Target Database to go to the SQL Console tab.

2. On the left-side Table tab of the SQL Console tab, find the table that you want to manage, double-click the table name, and then click Execute to view the archived data.

   Note

   DMS automatically creates a database and tables in the destination instance based on the names of the source database and tables. Therefore, the name of the destination database is the same as that of the source database.

   The following four columns of data are added to the archived table. This does not affect the use of the original archived data in the table.

   • Data archiving information, including the ticket number and time when data is archived

   • Database name

   • Table name

   • Instance ID, which is the ID that is specified when you register the instance with DMS and corresponds to the real ID of the instance