All Products
Search
Document Center

Data tracking

Last Updated: May 11, 2020
  • During routine database access, you may run some wrong UPDATE, DELETE, or INSERT statements, resulting in non-conforming data. At this time, you need to quickly restore the data to the normal state.
    • Conventional solutions include building clone instances and restoring database instances, databases, and tables. The prerequisite for database and table restoration is that you have completed fine-grained backup of databases and tables. The cost of such solutions is high and you need to formulate a backup solution in advance.
    • DMS Enterprise provides the data tracing feature to quickly trace historical data changes during a specified time period and generate corresponding rollback statements for all traced data. You can run the rollback statements to quickly restore data to the normal state.

Preparations

  • Run the UPDATE, DELETE, and INSERT statements in the SQLConsole to simulate data changes.
    • In actual scenarios, data changes to be traced may be performed by users in the SQLConsole or may be performed by users after a data change ticket is approved. In addition, non-conforming data may be generated after users use their business-specific application code to perform some data changes.
    • Run the UPDATE statement. update
    • Run the DELETE statement.
    • Run the INSERT statement.

Operation path

entrance

Create a data tracing ticket

  • This ticket is used to reversely parse the binlogs of the target database, compare data before and after the change, and generate a rollback statement for restoring data. When creating a data tracing ticket, fill in the following information:
    • [Required] The name of the task. You can use this information to easily query the ticket. Approvers can also use this information to clearly understand your operation intention.
    • [Required] The tracing time span. By default, data within the past 2 hours of the current time is traced. The maximum tracing time span for a single ticket is 6 hours. If the time span of data to be traced exceeds 6 hours, you can create multiple tickets to divide the time span into several time periods. The earliest traceable time point is the earliest time when the first binlog file was generated on the target database server.
    • [Required] The name of the target database on the database instance. Make sure that you have the permission to operate the database in the DMS Enterprise console. You can enter the prefix of the database name and select the database from the auto-completion list.
    • [Optional] The name of the target table. If you do not specify a table, operations performed on all tables in the target database are traced. If you specify multiple target tables, operations performed on these tables are traced. You can enter the prefix of the table name and select the table from the auto-completion list.
    • [Required] The type of operations to be traced. Valid values: Insert, Update, and Delete. Select an option as required. In this example, we have simulated all the three types of operations, and therefore all the three options are selected.
    • [Optional] The stakeholder of the ticket. Select stakeholders as required. Only the owner, stakeholders, and approvers can view the ticket details.new-order

Approve the ticket and parse logs

  • Currently, the approval process specified by the security rule for a data tracing ticket is fixed as follows: The ticket needs to be approved by the data owner of the target database and DBA in turn.
  • When the ticket is approved, the system automatically downloads and parses logs. The page shown in the following figure appears after logs are parsed.result

Trace different types of operations

INSERT operation

  • Set Track Type to Insert and click Search.
    • If multiple change records need to be rolled back, select them and click Export Rollback Script.
    • You can click View Details corresponding to a change record to view the change details and copy the DELETE statement used for rollback.
    • A DELETE statement is used to roll back INSERT operations.
    • You can trace operations on fields in a table.

UPDATE operation

  • Set Track Type to Update and click Search.
    • If multiple change records need to be rolled back, select them and click Export Rollback Script. Ten records are listed on each page by default. You can set the number of records to return on each page as required. A maximum of 200 records can be listed on each page.
    • You can click View Details corresponding to a change record to view the change details. Fields with data changes are highlighted. You can click Previous or Next to quickly view other fields with data changes.
    • An UPDATE statement is used to roll back UPDATE operations.
    • You can upload the downloaded rollback script to submit a data change ticket and run the script in the database to restore data.

DELETE operation

  • Set Track Type to Delete and click Search.
    • For more information about operations that you can perform on the returned records, see the UPDATE operation section.
    • An INSERT statement is used to roll back DELETE operations.

Summary

  • Currently, DMS Enterprise allows you to trace data in MySQL databases, including RDS-MySQL databases, ECS-hosted MySQL databases, and MySQL databases that are registered with DMS Enterprise from other environments.
  • The maximum tracing time span for a single ticket is 6 hours. If the time span of data to be traced exceeds 6 hours, you can create multiple tickets to divide the time span into several time periods. The earliest traceable time point is the earliest time when the first binlog file was generated on the target database server.
  • After data tracing is completed, you can generate a rollback script for all traced data. Then you can upload the script to submit a data change ticket and run the script in the database to restore data.