Archive data

This topic describes how to archive data by submitting a ticket.

Background information

When the amount of data in an online database increases, the query performance and business operations may be affected. OceanBase Developer Center (ODC) allows you to periodically archive table data from one database to another to address this issue.

Technical mechanism

Prerequisites

The table to be archived has a primary key.

Considerations

• Pay attention to the following rules:

  • You must connect to an OceanBase data source by using OceanBase Database Proxy (ODP). At present, you cannot archive data in a directly connected OceanBase data source.

  • At present, an OceanBase data source must be a cluster instance. Therefore, you must configure the cluster instance name when you create an OceanBase data source.

  • If the data sources of the source and destination are of different types, the destination table cannot be automatically created.

  • Make sure that the fields in the source table are compatible with those at the destination. The data archiving service does not handle field compatibility issues.

  • CPU and memory exhaustion prevention is not supported for a MySQL data source.

• The following archive links are supported:

  • Links between MySQL tenants of OceanBase Database

  • Links between MySQL databases

  • Links from a MySQL database to a MySQL tenant of OceanBase Database

  • Links from a MySQL tenant of OceanBase Database to a MySQL database

• Data archiving is not supported in the following cases:

  • The source table does not have a primary key.

  • The source table contains fields of the bit, enum, set, xml, or geometry data type.

  • The archiving condition contains a LIMIT clause.

  • The source table contains a foreign key.

Create a data archiving task

Example: Archive the student table in the odc_test database to the test database.

1. On the Ticket tab in the left-side navigation pane of the homepage, click Data Archiving and then click Create New.

   

2. In the Create New panel, configure the following parameters.

   

   Parameter	Description
   Source Database	The database to which the table belongs. Note In ODC V4.2.2 and later, you can archive data from a MySQL database to OceanBase Database.
   Destination Database	The database to which the table is to be archived. Note In ODC V4.2.2 and later, you can archive data from OceanBase Database to a MySQL database.
   Archiving Scope	Partial Archiving: specifies to archive only tables that meet filtering conditions in the source database. You can configure filtering conditions by using constants or referencing variables defined in Variable Configuration. For example, in create<'${create_time}', create_time is the name of a variable configured in Variable Configuration and create is a field in the table to be archived. Database Archiving: specifies to archive all tables in the source database.
   Custom Variable	Optional. You can define variables and set time offsets to filter rows to be archived.
   Execution Mode	The execution mode of the task. Valid values: Execute Now, Scheduled Execution, and Periodic Execution.
   Task Settings	Insert Policy: Choose whether to ignore or update duplicate data during data archiving. Set Limit: Specify Row Limit and Data Size Limit.
   Remarks	Optional. Additional information about the task, which cannot exceed 200 characters in length.

3. Click Create.

4. After the task is generated, choose Ticket > Data Archiving to view the task.

   

View a data archiving task

Task information

1. In the data archiving task list on the Ticket tab, click View in the Actions column of a task.

2. In the Task Details panel, click the Task Information tab and view information such as the task type, source database, destination database, variable configurations, archiving scope, and execution mode.

Task process

In the Task Details panel, click the Task Process tab and view information such as the task initiation status, approval status, execution status, and execution result.

Execution records

In the Task Details panel, click the Execution Records tab and view task status and perform operations such as task retry and rollback.

Task logs

In the Task Details panel, click the Operation Records tab and view the approval status and records of the task.

References

• Clean up data