The data tracking feature of Alibaba Cloud's Data Management Service (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, move the pointer over the More icon and choose Data Plans > Data Tracking.
  3. In the upper-right corner of the Data Tracking tab, click +Data Tracking.
  4. In the Data Track Ticket Application dialog box, set the parameters that are described in the following table.
    Data Track Ticket Application
    Parameter Description
    Task Name 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 Name The name of the database in which data was deleted or modified accidentally.
    Table Name The name of the table in which data was deleted or modified accidentally.
    Track Type The type of data operation that you want to track. You can select one or more types as needed.
    Time Range The 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, then DMS automatically retrieves binary logs.
    After the binary logs are retrieved, 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 data change records that you want to roll back and click Export Rollback Script.
    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, move the pointer over the More icon and choose Data Plans > Normal Data Modify.
  2. In the Data Change Ticket Application dialog box, configure the parameters in the Apply step as required and click Submit. The following table describes the parameters.
    Data changes
    Table 1. Parameters for data changes
    Parameter Description
    Database The name of the database that you want to restore.
    Affected Rows The estimated number of data rows to be affected by this data change. To obtain the actual number of affected rows, you can include the COUNT function in SQL statements and execute the SQL statements in the SQLConsole tab.
    SQL Statements for Change Select 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. After the verification is completed, 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.