All Products
Search
Document Center

Data Management:Data tracking

Last Updated:May 06, 2024

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.

Prerequisites

  • A database of MySQL 5.6 or later is used.

    Note

    The data tracking feature is supported for MySQL databases that are connected to DMS, such as ApsaraDB RDS for MySQL databases, PolarDB for MySQL databases, self-managed MySQL databases that are hosted on Elastic Compute Service (ECS) instances, self-managed MySQL databases that are hosted in data centers, and MySQL databases from other cloud service providers.

  • The binary logging feature is enabled for the database.

  • You have logged on to the database in DMS.

    Note

    If 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

  • For a database instance managed in Flexible Management mode, you can track DML operations that are performed within the previous 30 minutes but you cannot export rollback or rebuild scripts.

  • For a database instance managed in Stable Change or Security Collaboration mode, you can track data operations that are performed within the retention period of binary logs and export rollback or rebuild scripts.

  • The data operations that you can track are limited by the retention period of binary logs in a database. If data operations are performed beyond the retention period of binary logs, DMS cannot retrieve data for such operations.

  • If the binary logging feature is disabled for a database or you have not logged on to the database, DMS cannot obtain the binary logs of the database.

  • The data tracking feature allows you to track only DML operations. You cannot track DDL operations by using this feature.

Procedure

  1. Log on to the DMS console V5.0.
    1. In the top navigation bar, click Database Development > Data Tracking > Data Tracking Ticket.

    Note

    If you use the DMS console in simple mode, move the pointer over the 2023-01-28_15-57-17.png icon in the upper-left corner of the console and choose All Features > Database Development > Data Tracking > Data Tracking Ticket.

  2. In the upper-right corner of the Data Tracking Ticket page, click Data Tracking.

  3. 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: INSERT statements are generated to roll back INSERT operations.

    • Update: UPDATE statements are generated to roll back UPDATE operations.

    • Delete: DELETE statements 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.

  4. Click Submit. DMS automatically obtains the binary logs of the database.

    After the binary logs are obtained, the ticket enters the Approval step.

  5. Wait until the ticket is approved.

    Note

    By 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.

  6. After the ticket is approved, wait for DMS to download and parse the binary logs.

  7. 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.

    Note
    • You 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