When you perform data change operations in the SQL Console of a database in Data Management (DMS), data changes may fail to meet your expectations due to accidental operations such as accidental update, delete, or write operations. In this case, you can use the data tracking feature of DMS to efficiently restore data. This feature helps you track data operations that are performed within the retention period of binary logs and generate SQL statements for rolling back data changes.
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 and Restore individual databases and tables.
For more information about data restoration methods, see Overview of data restoration methods.
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 Normal Data Modify. |
| Fast | The restorable time range varies based on the control mode and the binary log retention period.
|
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. |
| 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. | |
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. |
| 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
A database of MySQL 5.6 or later is used.
The binary logging feature is enabled for the database.
You have logged on to the database in DMS.
NoteIf the database instance to which the database belongs is managed in Flexible Management or Stable Change mode, you must log on to the database. If the database instance to which the database belongs is managed in Security Collaboration mode, you do not need to log on to the database.
Usage notes
In Flexible Management mode: You can access the binary logs for DML operations performed within the last 30 minutes. You cannot export, roll back, and rebuild the scripts.
In Stable Change mode: No time limits are imposed when you access the binary logs for DML operations. You can download scripts in batches.
Procedure
- Log on to the DMS console V5.0.
In the top navigation bar, click .
NoteIf you use the DMS console in simple mode, move the pointer over the
icon in the upper-left corner of the console and choose . In the upper-right corner of the Data Tracking Ticket page, click Data Tracking.
On the Data Tracking Tickets page, configure the parameters that are described in the following table.
Parameter
Description
Task Name
Enter a name that facilitates future searches and helps approvers understand the purpose of the ticket.
Database Name
Select a database in a specific database instance. You must have the permissions to manage the database in DMS. You can enter the prefix of a database name in the field and select the database from the matched results.
Table Name
Select one or more tables for which you want to track data operations.
Track Type
Select one or more types of operations that you want to track.
Insert:
INSERTstatements are generated to roll back INSERT operations.Update:
UPDATEstatements are generated to roll back UPDATE operations.Delete:
DELETEstatements are generated to roll back DELETE operations.
Time Range
Specify a 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 only data operations that are performed within the previous 30 minutes.
If the database instance to which the database belongs is managed in Stable Change or Security Collaboration mode, you can track data operations that are performed within the retention period of binary logs. However, the time range is limited to a maximum of 48 hours in a single ticket. If you want to track data operations in a time range that exceeds 48 hours, split the time range and submit multiple tickets.
Change Stakeholder
Select the stakeholders involved in the ticket. Only the selected stakeholders and ticket approvers can view ticket details.
Click Submit. DMS automatically obtains the binary logs of the database.
After the binary logs are obtained, the ticket enters the Approval step.
Wait until the ticket is approved.
NoteBy default, a data tracking ticket for a database is approved by the database administrator (DBA) of the database. For more information about the approval rules for data tracking tickets, see Data tracking.
After the ticket is approved, wait for DMS to download and parse the binary logs.
After the binary logs are downloaded and parsed, you can specify filter conditions such as Track Type, Table Name, and Column Name to filter data changes that you want to roll back. Select data records and click Export Rollback Script. A rollback script is downloaded to your computer.
NoteYou can find a data record and click View Details to view the details of the data record and copy rollback statements.
You can track the following types of operations: INSERT, UPDATE, and DELETE.
What to do next
After the rollback script is exported, you can estimate the number of data rows to be affected by rollback statements and select one of the following methods to execute rollback statements:
If a small number of data rows are to be affected, you can execute rollback statements in the SQL Console. For more information, see Manage a database on the SQLConsole tab.
If a large number of data rows are to be affected, you can submit a Normal Data Modify ticket and upload the exported rollback script as the SQL statements for data change. This way, the SQL statements can be applied to the selected database. For more information, see Normal Data Modify.
Call the following API operations to track data operations: