The data tracking feature of Alibaba Cloud's Data Management (DMS) can help you find data changes made in a specific period of time and export rollback scripts in batches as needed. Then, you need to submit a data change ticket to run the rollback scripts to restore data in a database. The feature is applied only when the amount of data that was deleted or modified accidentally is less than 100,000 pieces.

Prerequisites

  • The binary logging feature is enabled for the database. For more information, see Enable binary logging.
  • If the control mode of your cluster is flexible management or stable change in the DMS console, you need to log on to the cluster in the console before you can restore data.

Step 1: Find the data change records to be rolled back and export rollback scripts

First, you need to submit a data tracking ticket in the DMS console. After the ticket is approved, you need to select the data change records that need to be rolled back based on the parsed logs, and generate rollback scripts.

  1. Log on to the new DMS console.
  2. In the top navigation bar, click Database Development. In the left-side navigation pane, choose Data Tracking > Data Tracking Ticket.
  3. In the upper-right corner of the Data Tracking tab, click +Data Tracking.
  4. On the Data Track Ticket Application page, set the parameters that are described in the following table.
    Track data
    ParameterDescription
    Task NameRequired. The name of the task. This helps you find the ticket in subsequent operations and allows approvers to know the purpose of the ticket with ease.
    Database NameThe name of the database in which data was deleted or modified accidentally.
    Table NameThe name of the table in which data was deleted or modified accidentally.
    Track TypeThe type of data operation that you want to track. You can select one or more types as needed.
    Time RangeThe time range in which you want to track data operations. The default time range is the last 2 hours. You can track data operations over the past 6 hours at most in a single ticket. If the time range that you specify exceeds 6 hours, submit multiple tickets to equal the desired time range. The earliest start time of the time range that you can specify is the point in time at which binary logs were first stored in your instance.
  5. Click Submit. DMS automatically retrieves binary logs.
    After the binary logs are obtained, the ticket enters the Approval step.
    Note If the binary logging feature is disabled or you have not logged on to the database, DMS cannot obtain the binary logs.
  6. Wait for approval.
    Note By default, a data tracking ticket is approved by the corresponding database administrator (DBA).
  7. After the ticket is approved, DMS downloads and parses the binary logs.
  8. After the binary logs are parsed, select the change records that you want to roll back and click Export Rollback Script.

Step 2: Run rollback scripts

After the rollback scripts are exported, you can submit a Normal Data Modify ticket. You can upload the exported scripts as the SQL statements for data change, run the scripts, and then apply changes to the database where you want to perform changes.

  1. In the top navigation bar, click Database Development. In the left-side navigation pane, choose Data Change > Normal Data Modify.
  2. On the Data Change Ticket Application page, set the parameters in the Apply step as required and click Submit. The following table describes the parameters.
    Change data
    Table 1. Parameters for a data change
    ParameterDescription
    DatabaseThe name of the database that you want to restore.
    Affected RowsRequired. The estimated number of data rows that may be affected by the data clear operation. To obtain the actual number of affected rows, you can use the COUNT function in SQL statements and execute the SQL statements on the SQLConsole tab.
    Change SQLSelect Attachments and upload the scripts generated in Step 1 to the attachment.
  3. After you submit the ticket, wait until DMS verifies that your configurations are valid.
  4. Submit the ticket for approval. After the ticket is approved, a task is generated.
    Note By default, data change tickets are approved by the corresponding DBAs.
  5. Click Execute Change.