As your business grows, the amount of data in your online databases increases. As a result, table queries slow down, and your business operations are affected. To resolve this problem, Data Management (DMS) provides the data archiving feature. You can use this feature to archive the data of large tables to Object Storage Service (OSS) buckets or ApsaraDB for Lindorm instances. You can also use this feature to delete archived data from large tables and recycle the storage space that is occupied by large tables. This way, you can improve query performance and reduce the costs of online storage. This topic describes how to archive the data of large tables to OSS buckets that are connected to Database Backup (DBS).

Prerequisites

  • A MySQL database, such as an ApsaraDB RDS for MySQL database or a PolarDB for MySQL database, is used.
  • DBS is activated. For more information, see How do I activate DBS?.
  • Data Lake Analytics (DLA) is activated. For information about how to activate DLA, see Activate DLA.
  • You are a DMS administrator, database administrator (DBA), or regular user.
    Note A regular user must have the export permissions on the database. For more information about how to apply for the permissions, see Manage permissions.

Billing

DMS creates a backup schedule in DBS to periodically archive data to OSS. You are charged for data backup or storage in DBS. For more information, see Billing overview in DBS documentation.
Note By default, an xlarge backup schedule is created in DBS.

Procedure

  1. Log on to the DMS console V5.0.
    Note To switch to the previous version of the DMS console, click the 5租户头像 icon in the lower-right corner of the page. For more information, see Switch to the previous version of the DMS console.
  2. On the New Data Archive Work Order page, set the parameters described in the following table.
    Parameter Description
    Task Name The name of the data archiving task. We recommend that you specify an informative name that helps you reduce unnecessary communication.
    Archiving Destination The destination to which data is archived. Select OSS Buckets Connected to DBS.
    Database The database to be archived. You can search and select a database from the drop-down list.
    Archive configuration
    1. The configurations for data archiving, including the one or more tables to be archived and the filter conditions.
    2. Optional: Enter a WHERE clause without the WHERE keyword in the Filter Condition field.
      Note If you want to use time variables in scenarios such as archiving data generated six months ago, you can configure the variables in the Variable configuration section before you configure the parameters in the Archive configuration section.
    Variable configuration Optional. The variable that you want to use for data archiving. You can configure one or more variables. For more information about how to set time variables, see Configure time variables.
    Operation mode The method to be used for running the task. Valid values:
    • Single execution: After the data archiving ticket is approved, DMS executes the data archiving task only once.
    • Cyclic scheduling: After the data archiving ticket is approved, DMS executes the data archiving task based on the cycle that you specify. For more information, see Cyclic scheduling.
  3. Click Submit.
    After the data archiving ticket is approved, DMS automatically executes the data archiving task.
    Note The data archiving feature implements data archiving and post-behavior based on the UTC+0 time zone regardless of the time zone of the database to be archived.
  4. Optional: View archived data.
    1. Create a data lake in DBS for backup sets. For more information, see Create a data lake.
    2. Register a DLA instance with DMS. After the registration, you can query data on the SQL Console tab and perform data analysis and development by using the task orchestration feature.
      Note For more information, see Register an ApsaraDB instance.
  5. Optional: After the data of the source tables is archived, you can delete the archived data from the source tables and optimize the storage usage of the source tables.
    1. To delete the archived data from the source tables, you can create a data change ticket. For more information, see Change regular data.
    2. To optimize the storage usage of the source tables, you can create a lock-free change ticket. For more information, see DDL-based lockless change.

Cyclic scheduling

Table 1. Configuration items for cyclic scheduling
Configuration item Description
Scheduling Cycle Select the cycle based on which DMS executes the data archiving task.
  • Hour: DMS executes the data archiving task within the hours that you select. You must also configure the Timed Scheduling parameter.
  • Day: DMS executes the data archiving task at the specified point in time every day.
  • Week: DMS executes the data archiving task at the specified point in time on the days that you select every week.
  • Month: DMS executes the data archiving task at the specified point in time on the days that you select every month.
Timed Scheduling Specify one of the following scheduling methods:
  • Scheduling at a specified interval:
    • Starting Time: the beginning of the time range within which DMS executes the data archiving task.
    • Intervals: the interval at which DMS executes the data archiving task within the specified time range. Unit: hours.
    • End Time: the end of the time range within which DMS executes the data archiving task.
    For example, if you set the Starting Time parameter to 00:00, the Intervals parameter to 6, and the End Time parameter to 20:59, DMS executes the data archiving task at 00:00, 06:00, 12:00, and 18:00.
  • Scheduling at the Specified Time: You can specify the hours at which DMS executes the data archiving task.

    For example, if you select 0Hour and 5Hour, DMS executes the data archiving task at 00:00 and 05:00.

Specified Time
  • If you set the Scheduling Cycle parameter to Week, select one or more days of the week. DMS executes the data archiving task on the days that you select every week.
  • If you set the Scheduling Cycle parameter to Month, select one or more days of the month. DMS executes the data archiving task on the days that you select every month.
Specific Point in Time Specify the point in time at which DMS executes the data archiving task.

For example, if you select 02:55, DMS executes the data archiving task at 02:55 on the days that you select.

Cron Expression You do not need to configure this parameter. DMS automatically generates a cron expression based on the values that you specify for the preceding parameters.