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 periodically archive the data of large tables to Object Storage Service (OSS) buckets or ApsaraDB for Lindorm instances. 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.
  • You are a DMS administrator, database administrator (DBA), or regular user.
    Note If you are a regular user, make sure that you have the export permissions on the database. For more information about permissions in DMS, see Overview.
  • DBS is activated. For more information, see How do I activate DBS?.

Billing

  • DMS creates a backup schedule in DBS to periodically archive data to OSS. You are charged for data backup in DBS based on the amount of data that is backed up. In addition, you are charged storage fees for archiving the backup data to OSS buckets that are connected to DBS. For more information, see Billing overview in DBS documentation.
    Note By default, a backup schedule of the xlarge type is created in DBS for data archiving.
  • If you want to view archived data, you must activate Data Lake Analytics (DLA). For more information about the billing of DLA, see Billing overview.

Procedure

  1. Log on to the DMS console V5.0.
  2. In the top navigation bar, click Solution. In the left-side navigation pane, click Data archiving.
    Note If you are using the previous version of the DMS console, move the pointer over the More icon in the top navigation bar and choose Data Plans > Export > Data archiving.
  3. In the upper-right corner of the Data archivingTickets page, click Data archiving. On the Ticket Application page, set the parameters for creating a data archiving ticket.
    Parameter Description
    Task Name The name of the data archiving task. We recommend that you specify a descriptive 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 for and select a database from the drop-down list.
    Archive configuration
    1. Specify one or more tables whose data you want to archive.
    2. Optional:Specify one or more conditions for filtering data 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 set the parameters in the Archive configuration section.
    Variable configuration Optional. The one or more variables that you want to use for data archiving. For more information about how to set time variables, see Configure time variables.
    Operation mode 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 cycle that you specify. For more information, see Cyclic scheduling.
  4. Click Submit.
    After the data archiving ticket is approved, DMS automatically runs 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.
  5. Optional:View archived data.
    1. Create a data lake in DBS for backup sets. For more information, see Create a data lake.
    2. Optional:Register the data lake 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.
  6. Optional:After the data of the source tables is archived, 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, create a regular data change ticket. For more information, see Change regular data.
    2. To optimize the storage usage of the source tables, create a lock-free change ticket. For more information, see Perform lock-free DDL operations.

Cyclic scheduling

Table 1. Configuration items for cyclic scheduling
Configuration item Description
Scheduling Cycle The cycle based on which DMS runs the data archiving task.
  • Hour: DMS runs the data archiving task within the hours that you select. In this case, you must also set the Timed Scheduling parameter.
  • Day: DMS runs the data archiving task at the specified point in time every day.
  • Week: DMS runs the data archiving task at the specified point in time on the days that you select every week.
  • Month: DMS runs the data archiving task at the specified point in time on the days that you select every month.
Timed Scheduling The method for scheduling the data archiving task to run. DMS provides the following scheduling methods:
  • Scheduling at a specified interval:
    • Starting Time: the beginning of the time range within which DMS runs the data archiving task.
    • Intervals: the interval at which DMS runs the data archiving task within the specified time range. Unit: hours.
    • End Time: the end of the time range within which DMS runs 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 runs the data archiving task at 00:00, 06:00, 12:00, and 18:00.
  • Scheduling at the Specified Time: You can select the hours at which DMS runs the data archiving task.

    For example, if you select 0Hour and 5Hour, DMS runs 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 runs 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 runs the data archiving task on the days that you select every month.
Specific Point in Time The point in time at which DMS runs the data archiving task.

For example, if you set this parameter to 02:55, DMS runs the data archiving task at 02:55 on the specified days.

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.