Data Management:Archive data to ApsaraDB RDS for MySQL

Supported databases for archiving to ApsaraDB RDS for MySQL

• MySQL databases: ApsaraDB RDS for MySQL, PolarDB for MySQL, AnalyticDB for MySQL 3.0, and PolarDB-X 2.0.

  Note

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

• PostgreSQL databases: ApsaraDB RDS for PostgreSQL and PolarDB for PostgreSQL.

Usage notes

• Each source table must have a primary or unique key.

  Note

  Your source tables should contain a timestamp field that indicates when the data was last modified. You can use this field as a filter condition for archiving.

• To automatically clean up source tables after archiving, ensure your source database has sufficient tablespace to prevent archiving failures.

• DMS runs periodic archiving tasks only when both the source and destination databases are managed in Security Collaboration or Stable Change mode. Single-execution tasks work in any control mode.

  Note

  For periodic tasks with databases in Stable Change mode, enable security hosting or change the control mode to Security Collaboration. Otherwise, the task may fail because of instance logon expiration. For more information, see Enable security hosting and Change the control mode of an instance.

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

Billing

The Data Management (DMS) data archiving feature is free of charge. For information about the billable items of DMS, see Billable items (DMS).

Sample table data

This topic uses the orders_test01 table as an example. The table contains the gmt_modified field, which indicates the last modification time of the business data.

/*Create a table*/
CREATE TABLE `orders_test01` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary key',
  `gmt_create` datetime NOT NULL COMMENT 'Creation time',
  `gmt_modified` datetime NOT NULL COMMENT 'Modification time',
  `buyer_name` varchar(128) NOT NULL COMMENT 'Buyer',
  `seller_name` varchar(128) NOT NULL COMMENT 'Seller',
  `item_id` varchar(128) NOT NULL COMMENT 'Item ID',
  `city` varchar(128) NOT NULL COMMENT 'City',
  `zone` varchar(128) DEFAULT NULL COMMENT 'Purchase region',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8mb4 COMMENT='Test order table';
/*Insert data*/
INSERT INTO orders_test01(id,gmt_create,gmt_modified,buyer_name,seller_name,item_id,city,zone) VALUES 
(1,'2022-08-11 13:41:11','2022-08-11 13:41:12','xiaoming','Group A','IPhone9','Hangzhou','Zhejiang'),
(2,'2022-08-11 13:41:12','2023-08-11 13:41:13','xiaohong','Group B','Xiaomi 8','Hangzhou','Zhejiang'),
(3,'2022-08-11 13:41:13','2023-08-11 13:41:14','xiaohei','Group C','Samsung G1','Hangzhou','Zhejiang'),
(4,'2022-08-11 13:41:14','2022-08-11 13:41:15','xiaolan','Group D','Oppo V3','Hangzhou','Zhejiang'),
(5,'2022-10-11 13:41:11','2024-07-12 13:41:12','xiaoming','Group A','IPhone9','Hangzhou','Zhejiang'),
(6,'2022-11-11 13:41:12','2025-01-11 13:41:13','xiaohong','Group B','Xiaomi 8','Hangzhou','Zhejiang'),
(7,'2022-12-01 13:41:13','2023-02-11 13:41:14','xiaohei','Group C','Samsung G1','Hangzhou','Zhejiang'),
(8,'2022-12-01 13:41:13','2022-08-11 13:41:14','xiaohei','Group C','Samsung G1','Hangzhou','Zhejiang'),
(9,'2022-12-01 13:41:13','2023-08-11 13:41:14','xiaohei','Group C','Samsung G1','Hangzhou','Zhejiang'),
(10,'2022-12-01 13:41:13','2024-08-11 13:41:14','xiaohei','Group C','Samsung G1','Hangzhou','Zhejiang'),
(11,'2022-12-01 13:41:13','2024-06-11 13:41:14','xiaohei','Group C','Samsung G1','Hangzhou','Zhejiang'),
(12,'2022-12-01 13:41:13','2024-08-11 13:41:14','xiaohei','Group C','Samsung G1','Hangzhou','Zhejiang'),
(13,'2022-12-01 13:41:13','2023-08-11 13:41:14','xiaohei','Group C','Samsung G1','Hangzhou','Zhejiang'),
(14,'2022-12-11 13:41:14','2024-01-11 13:41:15','xiaolan','Group D','Oppo V3','Hangzhou','Zhejiang');

Data archiving

1. Log in to DMS 5.0.

2. Move the pointer over the icon in the upper-left corner and choose All Features > Solution > Data Archiving.

   Note

   If you use the DMS console in normal mode, choose Solution > Data Archiving in the top navigation bar.

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

4. On the new data archiving ticket page, configure the ticket parameters and then click Submit.

   This example archives data from one ApsaraDB RDS for MySQL instance to another. Data older than six months from the source table orders_test01 is archived to a database in the destination instance.

   Parameter	Required	Description
   Task name	Yes	Use a clear and descriptive name for the task.
   Archive destination	Yes	Select ApsaraDB RDS for MySQL.
   RDS instance	Yes	Search for and select the destination ApsaraDB RDS for MySQL instance.
   Source database	Yes	Search for and select the source database.
   Archive configuration	Yes	Select the source tables to be archived. You can click Add to add multiple source tables. If you need to archive only specific data, configure filter conditions. For example, to archive data older than six months, first configure a time variable named 6_month_ago, and then set the filter condition to gmt_modified <= '${6_month_ago}'. When configuring the variable, set Time Format to yyyy-MM-dd. For Time Operation, select -, enter 6, and then select Month.
   Archive table mapping	No	Maps the source table to a destination table. By default, the destination table has the same name as the source table. You can click Edit to modify the table name, column information, sharding key, and partition key for the destination table.
   Variable configuration	No	You can use configured variables in the filter conditions for archiving. For example, if you create a time variable named 6_month_ago with the yyyy-MM-dd format and set the offset to -6 months, the variable ${6_month_ago} resolves to 2021-02-11 if the current date is August 12, 2021. For more information about how to configure time variables, see Configure time variables.
   Post-actions	No	Clean up the archived data of the original table (delete-No Lock): DMS automatically deletes the archived data from the source tables by using the DELETE statement. Temporary backup tables are created in the source database to store the archived data. Make sure the source database has enough storage space. After verifying that the archived data is correct, create a Standard data change ticket to clear the temporary backup tables. If you do not select the cleanup option, the archived data remains in the source tables. To delete the data manually: Create a regular data change ticket to delete the archived data. For more information, see Standard data change. Create a lock-free change ticket to optimize storage usage. For more information, see Perform lockless schema changes using lockless change tickets.
   Execution mode	Yes	Select an execution mode. The following options are supported: One-time execution: The archiving task runs only once after the ticket is approved. Periodic scheduling: After the ticket is approved, the task runs periodically as configured. For more information, see Periodic scheduling.

5. Approve the ticket.

   After the ticket is approved, the system automatically runs the data archiving task. Wait for the task to complete. If the task fails, you can click Details in the Actions column of the execution step to view the task logs. If the task fails due to a transient issue, such as a network or database connection error, you can click Retry Breakpoint to retry it.

   Note

   The archived data is stored in the database as tables.

6. Optional: Query the archived data.

Query the archived data

1. After the data archive task runs successfully, on the current Ticket Details page, in the Basic Information section, click View on the right side of Destination Database.

2. After you enter the SQL Console page, in the table list on the left, select and double-click the destination table name, and then click Execute to view the archived data.

   Note

   The system automatically creates databases and tables in the archive instance based on the database and table names in the source database. Therefore, the destination database name is the same as the source database name.

   Four new columns are added to the archived table without affecting the use of the original data:

   • Archive information (ticket ID and archive time)

   • Database name

   • Table name

   • Instance ID (the ID assigned when the instance is added to DMS, corresponding to the actual instance ID)

FAQ

Q: If I do not specify a destination database when configuring the ticket, where will the source data be archived?

A: DMS automatically creates a destination database with the same name as the source database to store the archived data.