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

If the data of an ApsaraDB RDS for MySQL instance does not meet your expectations due to accidental operations, such as accidental update, delete, and write operations, you can use the data tracking feature of Data Management (DMS) to restore the data of the RDS instance. This feature provides a method for you to restore data in a more efficient manner compared with other alternative restoration methods. For more information about the alternative restoration methods, see Restore full data of an ApsaraDB RDS for MySQL instance and Restore individual databases and tables of an ApsaraDB RDS for MySQL instance.

Differences between the data tracking feature and other alternative restoration methods

Restoration method Workflow Billing Speed Restorable time range
Data tracking Use the data tracking feature of DMS to identify all updates that are made over the specified time range, generate statements that are used to roll the updates back, and aggregate the generated statements into a script. Then, submit a ticket to run the script in the RDS instance. For more information, see Change regular data.
  • If the control mode of an RDS instance is Flexible Management, no fees are generated.
  • If the control mode of an RDS instance is Stable Change or Secure Collaboration, fees are generated. For more information about the price, see Pricing.
Fast The restorable time range varies based on the control mode and the binary log retention period.
  • If the control mode of an RDS instance is Flexible Management, the restorable time range spans up to 1 hour.
  • If the control mode of an RDS instance is Stable Change or Secure Collaboration, the restorable time range varies based on the following factors:
Restore full data of an ApsaraDB RDS for MySQL instance Restore all data of the original RDS instance to a new RDS instance, verify the data on the new RDS instance, and then migrate the data from the new RDS instance back to the original RDS instance, an existing RDS instance, or an on-premises database instance.
  • You are charged for the new RDS instance. For more information about the price, visit the ApsaraDB RDS buy page.
  • You are charged for your backup storage usage that exceeds the provided free quota. For more information, see Backup storage pricing of an ApsaraDB RDS for MySQL instance.
  • You are charged for the traffic that is consumed to migrate the data of the RDS instance over the Internet. For more information, see Pricing.
Slow The restorable time range varies based on the log backup retention period and the data backup retention period. The restorable time range spans up to 730 days. For more information about how to specify these retention periods, see Enable the automatic backup feature for an ApsaraDB RDS for MySQL instance.
Restore individual databases and tables of an ApsaraDB RDS for MySQL instance Enable the Restore Individual Database/Table feature. Then, restore the data of the specified individual databases and tables to a new RDS instance or an existing RDS instance. If you restore the data to an existing RDS instance, a primary/secondary switchover is triggered.
  • If you restore the data to a new RDS instance, you are charged for the new instance. For more information about the price, visit the ApsaraDB RDS buy page.
  • You are charged for your backup storage usage that exceeds the provided free quota. For more information, see Backup storage pricing of an ApsaraDB RDS for MySQL instance.
Slow The restorable time range varies based on the log backup retention period and the point in time at which the Restore Individual Database/Table feature is enabled. The log backup retention period spans up to 730 days. For more information, see Back up the individual databases and tables of an ApsaraDB RDS for MySQL instance.

Prerequisites

  • The RDS instance runs MySQL 5.6 or later.
  • If the control mode of the RDS instance is Flexible Management or Stable Change, you have logged on to the RDS instance. If the control mode of the RDS instance is Security Collaboration, you do not need to log on to the RDS instance, but you must have obtained the permissions on the databases whose data you want to restore. For more information, see Control modes.

Procedure

  1. Log on to the DMS console V5.0.
  2. In the top navigation bar, click Database Development. In the left-side navigation pane, choose Data Tracking Ticket > Data Tracking.
  3. In the upper-right corner of the Data TrackingTickets page, click Data Tracking.
  4. On the Data Track Ticket Application page, 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 table for which you want to track data operations. Enter a keyword in the field and select a table whose name contains the keyword from the matched results. You can specify multiple tables.
    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 the INSERT operations that are tracked.
    • Update: UPDATE statements will be generated to roll back the UPDATE operations that are tracked.
    • Delete: INSERT statements will be generated to roll back the DELETE operations that are tracked.
    Time Range The time range in which you want to track data operations.
    • If the database instance to which the database belongs is managed in Flexible Management mode, you can track data operations only in the latest 1 hour.
    • If the database instance to which the database belongs is managed in Stable Change or Security Collaboration mode, you can track data operations in all time ranges. However, the time range is limited to a maximum of 48 hours in a single ticket. If the time range that you specify exceeds 48 hours, split the time range and submit multiple tickets.
    Change Stakeholder The stakeholders involved in 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, data tracking tickets are approved by database administrators (DBAs). For more information about the approval rules for 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.
    • Select one or more options from the Track Type drop-down list to filter INSERT, UPDATE, and DELETE records.

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 on which you want to perform changes. For more information, see Change regular data.