Data Management:Archive data to an AnalyticDB for MySQL V3.0 cluster

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) periodically runs a data archiving task only if both the source and destination databases are managed in Security Collaboration or Stable Change mode. If you need to run a data archiving task only once, the source and destination databases can be managed in any mode.

  Note

  If you want to configure a periodic data archiving task and the source and destination database instances are managed in Stable Change mode, we recommend that you enable security hosting for the database instances or change the control mode of the database instances to Security Collaboration. Otherwise, the data archiving task may fail due to instance logon expiration. For more information, see the Enable security hosting section of the "Security hosting" topic and Change the control mode of an instance.

• If you archive data to an AnalyticDB for MySQL Data Lakehouse Edition V3.0 cluster, make sure that the cluster has reserved computing resources. Otherwise, the data archiving task fails. For more information about scaling up or down a Data Lakehouse Edition cluster, see Scale a Data Lakehouse Edition cluster.

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

Billing

You are charged for the AnalyticDB for MySQL V3.0 cluster that you purchase. For more information, see Pricing for Data Warehouse Edition (V3.0) and Pricing for Data Lakehouse Edition (V3.0).

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 Data ArchivingTickets page, click Data Archiving.

4. On the Ticket Application page, configure 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 AnalyticDB-MySQL 3.0.
   AnalyticDB Instance	Yes	The destination AnalyticDB for MySQL V3.0 cluster to which you want to archive data.
   Source Database	Yes	The source database from which you want to archive data.
   Archiving Configuration	Yes	Optional. Specify one or more filter conditions for querying data to be archived in the specified tables. Example: gmt_modified<='${6_month_ago}'. 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.
   Operation 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.

6. 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.

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

7. Query the archived data. For more information, see the Query the archived data section of this topic.

Query the archived data

Method 1: Use DMS to 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

Method 2: Use AnalyticDB for MySQL to query the archived data

For more information, see Import and query data.