Data Management (DMS) provides the data tracking feature. You can use this feature to find data changes that are performed in a specific time period and generate SQL statements for rolling back the data changes. This topic describes the data tracking feature and shows you how to use this feature.

Background information

When you manage data in databases, the data may fail to meet your expectations due to misoperation, such as accidental update, delete, or write operations. In this case, you must restore the data to the normal state. A common practice is to clone databases, or restore instances, databases, or tables. To restore databases and tables, database and table backups must be prepared and managed in a precise manner. This asks for high costs and in-advance backup schedules.

In view of this, the data tracking feature is provided. You can use this feature to find data changes that are performed in a specific time period and generate SQL statements for rolling back the data changes as needed. After the data changes are found, a script is generated for rolling back multiple data changes at a time. Then, you submit a data change ticket to run the rollback script to restore the data in a database.

Prerequisites

  • A MySQL database is used.
    Note The MySQL database may be an ApsaraDB RDS for MySQL database, a PolarDB for MySQL database, or a self-managed database on an Elastic Compute Service (ECS) instance or in a data center. The MySQL database may also be a cloud database from other cloud service providers, or a self-managed database on a cloud server of other cloud service providers.
  • The binary logging feature is enabled for the database.

Procedure

  1. Log on to the DMS console.
  2. In the top navigation bar, move the pointer over the All functions 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.
    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 a specific database in a specific database instance. You must have permissions to manage the database in DMS. Enter the prefix of a database name in the field and select the database from the matched results.
    Table Name The one or more tables where you want to track data operations. Enter the prefix of a table name in the field and select the table from the matched results.
    Track Type The type of data operation that you want to track. You can select one or more types as needed. Valid values:
    • Insert: DELETE statements will be generated to roll back INSERT operations that are tracked.
    • Update: UPDATE statements will be generated to roll back UPDATE operations that are tracked.
    • Delete: INSERT statements will be generated to roll back DELETE operations that are tracked.
    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 in the last 6 hours at most in a single ticket. If the time range that you specify exceeds 6 hours, split the time range and submit multiple tickets. The earliest start time of the time range that you can specify is the point in time at which binary logs were first stored on the database server.
    Change Stakeholder The stakeholders of the ticket. Only users who are relevant to the ticket, including those who participate in the approval process of the ticket, can view ticket details.
  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 a database administrator (DBA) of a database. For more information about the security rules for approving data tracking tickets, see Data Tracking.
  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.
    Note You can also perform the following operations:
    • Select multiple change records and export the rollback statements for these records at a time. By default, each page displays 10 records. A maximum of 200 records can be displayed on each page. You can specify the number of records to be displayed per page as needed.
    • To view the details of a record and copy the rollback statement, click View Details to the right of the record name.
    • Filter INSERT, UPDATE, and DELETE records by setting the Track Type parameter.

What to do next

After the rollback script is exported, you can submit a Normal Data Modify ticket. You can upload the exported script as the SQL statements for data change, run the script, and then apply changes to the database where you want to perform changes. For more information, see Change common data.