DocsICP FilingConsole
官方文档
首页

Archive data to ApsaraDB RDS for MySQL

更新时间：
复制 MD 格式
产品详情
我的收藏

This topic explains how to archive data from MySQL or PostgreSQL databases to an ApsaraDB RDS for MySQL instance.

Note

Data archiving is currently in public preview.

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.

  • To run periodic archiving tasks, the control mode for both the source and destination databases must be Security Collaboration or Stable Change. For one-time tasks, there is no restriction on the database control mode.

    Note

    If the control mode for your instances is Stable Change, enable security protection or change the control mode to Security Collaboration before you run a periodic archiving task. This prevents task failures due to an expired instance logon. For more information, see Enable security protection and Change the control mode of an instance.

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.

Click to view the SQL statements for creating the sample table and inserting 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 2023-01-28_15-57-17.png 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

    • If you select Clean up archived data from source table (delete-lock-free), DMS automatically deletes the archived data from the source table by running a DELETE statement as a lock-free change. However, this process creates a temporary backup table in the source database. Ensure your source database has sufficient space to avoid availability issues.

      To clean up the temporary backup table: After you confirm that the data archiving is complete and the data is correct, you can submit a regular data change ticket to clean up the temporary backup table from the source database.

    • If you do not select Clean up archived data from source table (delete-lock-free), you must manually delete the data from the source table and optimize the table space after successful archiving.

      1. Clean up source table data: Submit a regular data change ticket to clean up the archived data. For more information, see Regular data change.

      2. Optimize source table space: Submit a lock-free change ticket to optimize the space of the source table. For more information, see Perform lock-free schema changes by using a lock-free change ticket.

    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 archiving task is successful, go to the Ticket Details page. In the Basic Information section, click View next to Destination Database.

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

    Note

    The system automatically creates a database and a table in the destination instance with the same names as the source database and table. Therefore, the destination database name is the same as the source database name.

    Four new informational columns are added to the archived table without altering the original data.

    • Archive information (ticket ID and archive time)

    • Database name

    • Table name

    • Instance ID (the ID assigned to the instance by DMS)

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.

Previous：Archive data to an AnalyticDB for PostgreSQL instanceNext：Archive data to a PolarDB for MySQL cluster