Help Center > > User Guide> MySQL Database Management (Standard Edition)> Data Plan> Data Tracking and Rollback

Data Tracking and Rollback

Updated at:May 14, 2020 GMT+08:00

Scenarios

  • Audit core data changes, collect statistics on the number of changes, and view sensitive change information.

For example: the revenue and expense information about a bank, statistics on new orders, and the key configuration change history of the configuration table.

  • Roll back the data that is deleted by mistake and restore the data to the status before the change.

For example: If the WHERE condition is not added when DBA updates the configuration table, the configuration data may be deleted by mistake and a large amount of dirty data may be generated.

Prerequisites

You have logged in to the DAS console.

Procedure

  1. On the top menu bar, choose Data Scheme > Data Tracking.

    • Search for tasks by setting the time range or database name.
    • You can delete tasks as required. The deletion operation is synchronized to DAS.
    • In the task list, click the task ID to view the task details.
    • In the task list, locate the target task and open it. Then, you can view task details, search for logs and log tracking and rollback details.

    The created task will expire in 15 days. The system automatically retrieves and deletes the changes in DAS.

  2. On the task list, click Create Tracking Task. On the displayed page, set the required parameters.

    • Users who create the tracking task for the first time need to check and agree to the agreement.
    • The time range for task tracking and rollback cannot exceed six hours.

  3. In the displayed dialog box, click Precheck to obtain the operation details at a point in time.

    • During the pre-check, the binlog list is filtered based on the time range. The RDS DB instance with the backup function enabled periodically backs up the binlog file to the OBS bucket. The backup delay is no more than 5 minutes.
    • When you create a tracking task, there may be no changes in the latest five minutes. In this case, you can create a task again later.

  4. On the displayed dialog box, click Read Log to obtain log details.

    When reading logs, the system initiates binlog parsing and stores log changes based on task information for filtering and displaying data.

    • You can search for logs only after all logs are successfully read.
    • If the last tracking task is not completely read and a new task is started, the log start time may be later than the log end time. This is a normal phenomenon.

      Example

      When you create a tracing task, the task reads logs from binlog. If the data read in this task is the data not completely read in the last task, that is, the log end time is the time corresponding to the log time in the last task, the log end time will be earlier than the log start time.

  5. On the displayed dialog box, click Search Log to obtain details about the changes of events.

    • You can search for files by time range, file type, table name, and other criteria.
    • In the Operation column of the search list, click View Data to view the data of the current event.
    • Check the rollback SQL statements of the current event. Tables without the primary key do not support the generation of rollback statements.

  6. Create a rollback task if you need to roll back multiple events. In the displayed dialog box for creating a rollback task, specify the task ID and rollback scope, store the rollback SQL in the OBS bucket, set advanced settings as required, and click OK.

    • The start event ID and end event ID are the IDs in the task list and must be entered in ascending order.
    • Combing changes

      If a record (primary key) is changed for three times (for example, 1->2->3->4), only the change of the last takes effect.

    • Statement type

      Generate an event-based rollback SQL statement: Generates SQL statements with reverse changes based on the images before and after data changes.

      Obtain the original data before changes: Generates rollback tables and insert statements by mirroring before data change.

  7. In the rollback task list, view the current rollback task information or create a rollback task.

    • In the task list, click the task ID to view the task details.
    • Click View Details in the Operation column of the task list to view details about the rollback task.
    • In the Operation column, click Download to download the compressed data package of the task.

      Changes (such as insert->delete, delete->insert, update->update) on the same record will be combined or canceled. Therefore, the generated file may have no rollback SQL statement or original data.

Did you find this page helpful?

Submit successfully!

Thank you for your feedback. Your feedback helps make our documentation better.

Failed to submit the feedback. Please try again later.

Which of the following issues have you encountered?







Please complete at least one feedback item.

Content most length 200 character

Content is empty.

OK Cancel