Track and roll back database changes

Use Track & Rollback to analyze data or object changes in a database, locate risky operations, and generate rollback SQL for fast recovery.

Background

As database schemas and business data grow, it becomes harder to trace accidental changes. A program bug, manual update, or unexpected SQL task can change data in ways that require investigation and rollback.

Supported scope

NineData tracks change statements that have been executed in the target database. You can filter the parsed records by change type, time range, table, and other conditions.

• Supported change types:

  Change Statement Type	Change Statement
  DML	INSERT, UPDATE, DELETE
  DDL	CREATE, ALTER, DROP, TRUNCATE

• Supported data sources: MySQL 5.6 and later, including self-managed and cloud databases.

Limitations

• After a Track & Rollback task is created, tracking records (Binlog parsing records) are retained for one month and then cleared.
• Even if the database Binlog retains more than one month of records, Track & Rollback can trace only records from the last month up to the current time.
• Rollback SQL generation currently supports only DML statements. DDL rollback SQL is not supported.

Before you begin

• You have already created or joined an organization, and the organization has subscribed to DevOps Enterprise. Please ensure that your annual or monthly subscription is still active. For more information, please refer to Manage Organizations.
• Your current account has been switched to the target organization. For more information, please refer to Switching to an Organization.

• To ensure Track & Rollback works correctly, grant these privileges to the target database account:

  GRANT REPLICATION SLAVE, REPLICATION CLIENT, SELECT ON *.* TO 'binlog_user'@'%';

• The data source has Binlog enabled with these parameters:

  • binlog_format=ROW

  • binlog_row_image=FULL

    tip

    If the data source is a standby database, enable log_slave_updates so NineData can obtain complete Binlog records.

Create a Track & Rollback task

Before you begin

• You have permissions on the target data source, databases, and tables. If not, apply for permissions first. See Permission application and approval.
• You have the "Data Track&Rollback" module permission, which is usually granted by default. If you cannot see this entry, contact your system administrator.
• You have the Track & Rollback permission. To request this permission, see Permission application and approval.

Procedure

1. Log in to the NineData Console.

2. Go to DevOps > Track & Rollback.

3. On the Track & Rollback page, click Create Track & Rollback.

4. On the Create Track & Rollback page, configure the task.

   Parameter	Description
   Name	Enter a task name. Use a meaningful name so the task is easy to find and manage later. Up to 64 characters are supported.
   Datasource	Select the data source for Track & Rollback. Note: If you do not have permission for this data source, apply for permission first. See Permission application and approval.
   Database	Select the database for Track & Rollback. Note: If you do not have permission for this database, apply for permission first. See Permission application and approval.
   Tables	Select one or more tables for Track & Rollback.
   Condition (optional)	If you track a single table, enter a filter condition so NineData tracks only records that match the condition.
   Modify Type	Select the change types to track. Multiple selections are supported. Click Select All to select all change types.
   Time Zone	Select the time zone for Track & Rollback. The default is Beijing time.
   Tracking Time	Select the time range to track. The selectable period is 72 hours, and the tracked records must be within the last month up to the current time.
   Note (optional)	Enter notes for the task.

5. Click Create Track & Rollback. NineData opens the Details page.

6. In the Pre-check node, wait until the task status changes to Pending. In the Pre-check node, you can also:

   • Click the icon to refresh the task status.

   • Click Duplicate to copy a new task with the same configuration.

   • Click Withdraw to withdraw the task or edit and resubmit it.

     tip

     • Pre-check checks the Track & Rollback task, including data source type, account permissions, Binlog configuration, Binlog file status within the specified time range, and Binlog file size. If the check finds an issue, NineData shows details so you can fix the task.
     • If no approval process is configured for the data source, the task skips the Submit Approval node and proceeds to the Parsing Binlog node. After parsing is complete, all matching changes are listed in the Tracking Record area, and the task ends.

7. Click Submit Approval. In the approval dialog, select the workflow approvers and click OK.

   tip

   • The number of approvers depends on the approval process configured for the data source.

   • If the Not Specifying Approvers feature is enabled in the current approval process, this section will display After submission, all approvers can review. Please check the results in the approval process.. You do not need to manually specify an approver; all personnel authorized to approve the current work order will receive approval notifications and be able to approve it.

8. After the task status changes to Pending Approval, you can use these actions before approval:

   • Click the icon to refresh the task status.
   • Click Withdraw to withdraw the task or edit and resubmit it.
   • Click Transfer to change the approver of this task.

9. After the task status changes to Approved, NineData parses the Binlog. During parsing, all matching changes are listed in the Tracking Record area.

Approve a task

Before you begin

• You have created or joined an organization. See Manage organizations.
• The NineData console is in organization mode. To switch from personal mode to organization mode, see Switch to an organization.
• The Track & Rollback task status is Pending Approval.
• You are Administrator or the Approver for the task.

Procedure

1. Log in to the NineData Console.

2. Go to DevOps > Track & Rollback.

3. On the Track & Rollback page, find the target task and click the task ID or Details in the Actions column.

   tip

   You can also hover over the icon at the top of the page and click the target task on the Todo tab.

4. On the Details page, review the approval request and select Transfer, Approved, or Rejected.

   Operation	Description
   Transfer	Transfer the task to another user for approval.
   Approved	Approve the request. After you click Approved, enter Reason if needed and click OK.
   Rejected	Reject the request. After you click Rejected, enter Reason for rejection and click OK.

Terminate a task

Terminate a parsing task if it starts to affect the database.

Before you begin

• You have created or joined an organization. See Manage organizations.
• The NineData console is in organization mode. To switch from personal mode to organization mode, see Switch to an organization.
• The Track & Rollback task status is Parsing.
• You are an Administrator, the task creator, or an Approver for the task.

Procedure

1. Log in to the NineData Console.

2. Go to DevOps > Track & Rollback.
3. On the Track & Rollback page, find the target task and click the task ID or Details in the Actions column.
4. Click Terminate, then click OK in the confirmation dialog.

View tracking records

Before you begin

• You have created or joined an organization. See Manage organizations.

• The NineData console is in organization mode. To switch from personal mode to organization mode, see Switch to an organization.

• The Track & Rollback task status is Finished.

  tip

  You can also view parsed records when the tracking task status is Parsing.

• You are an Administrator, the task creator, or an Approver for the task.

Procedure

1. Log in to the NineData Console.

2. Go to DevOps > Track & Rollback.

3. On the Track & Rollback page, find the target task and click the task ID or Details in the Actions column.

4. In the Tracking Record area, view all changes that match the tracking criteria, including Modify Time, Modify Type, Database, Table, and Primary Key. Click next to a record to view field details.

   tip

   If there are too many records, filter by change time or SQL statement type, or search for the table name. Click Advanced, select the table name, and then use Condition to search specific columns in the target table.

5. Click Download Rollback SQL to download rollback SQL as an SQL file. This action supports Download Current and Download All.

6. Click Details in the Actions column to the right of the target record to view the before-and-after values for that record. Select Only Diff Col. to hide unchanged columns. To restore the data to the earlier state, copy the SQL statement in Rollback SQL and execute it in the database.

Generate and download rollback SQL

When a task contains many tracking records, filter the records first, then use Download Rollback SQL to download rollback SQL in bulk as a .sql file.

Before you begin

• You have created or joined an organization. See Manage organizations.

• The NineData console is in organization mode. To switch from personal mode to organization mode, see Switch to an organization.

• The Track & Rollback task status is Finished.

  tip

  You can also view parsed records when the tracking task status is Parsing.

• You are either an Administrator or the creator of the task.

Procedure

1. Log in to the NineData Console.

2. Go to DevOps > Track & Rollback.

3. On the Track & Rollback page, find the target task and click the task ID or Details in the Actions column.

4. On the Tracking Record tab, filter the tracking records to include the records you want, then click Download Rollback SQL > Download Current.

   To generate rollback SQL for all tracking records, click Download Rollback SQL > Download All.

5. Click the Download tab (located to the right of the Tracking Record tab), then click the Download option in the Actions column next to the target record to download the rollback SQL file to your local machine.

Result

After Binlog parsing finishes, the task detail page lists the matching change records. For DML changes, you can inspect before-and-after values and download rollback SQL.

Next steps

• Review the generated rollback SQL before running it in production.
• Use filters to narrow the record list before downloading SQL for a large task.
• Terminate a parsing task if it starts to affect the source database.