Track and roll back database changes

Use Track & Rollback to analyze database data changes or object changes, locate the operation that caused an issue, 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 need to be investigated and rolled back.

Supported scope

NineData tracks executed change statements in the target database and lets you filter records by change type and time range.

• Supported Change Types:

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

• Supported Data Sources: MySQL 5.6 and above, supporting self-built and cloud databases.

Limitations

• After a Track & Rollback task is created, tracking records (Binlog parsing records) are retained for one month and then cleared.
• If the database Binlog retains records for more than one month, the maximum traceable period is within the last month up to the current time.
• The rollback SQL statement generation function of the Track & Rollback task currently only supports DML statements and does not support DDL statements.

Prerequisites

• 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 the proper functioning of the Data Track and Rollback feature, you need to grant the following privileges to the target database account:

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

• The data source has enabled Binlog, and the Binlog-related parameters are set as follows:

  • binlog_format=ROW

  • binlog_row_image=FULL

  tip

  If the data source is a standby database, to ensure complete Binlog logs are obtained, you also need to enable the log_slave_updates parameter.

Create a Track & Rollback task

Prerequisites

• Have permissions for the target data source's databases and tables. If not, you can apply for them first. For more information, see Permission Application and Approval.
• Have the module permission "Data Track&Rollback" (usually granted by default). If you cannot see this entry, contact your system administrator.
• Have the permission Track & Rollback. For how to apply for this permission, see Permission Application and Approval.

Procedure

1. Log in to the NineData Console.

2. Click DevOps > Track & Rollback in the left navigation bar.

3. On the Track & Rollback page, click the Create Track & Rollback button in the upper right corner of the page.

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

   Parameter	Description
   Name	Enter a meaningful task name so that you can find and manage the task 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 it first. For more information, see Permission Application and Approval.
   Database	Select the database name for Track & Rollback. Note: If you do not have permission for this database, apply for it first. For more information, see Permission Application and Approval.
   Tables	Select the table name(s) for Track & Rollback, supporting multiple selections.
   Condition (Optional)	If tracking a single table, you can set a filter condition so that only results that meet this condition will be tracked.
   Modify Type	Select the types of changes to be tracked, multiple selections are allowed. Click Select All to select all change types.
   Time Zone	Select the time zone of your region. The system will perform Track & Rollback based on the time point of the selected time zone, with the default being Beijing time zone.
   Tracking Time	Select the time range for changes to be tracked, with a selectable period of 72 hours. You can track data within the last month up to the current time.
   Note (Optional)	Enter remarks for the current task.

5. Click Create Track & Rollback to automatically jump to the Details page.

6. In the Pre-check node, wait for the task status to change to Pending. In the Pre-check node, you can also perform these actions:

   • Click the icon in the upper right corner of the page to refresh the task status.
   • Click Duplicate in the upper right corner of the page to copy a new task with the same configuration.
   • Click Withdraw in the upper right corner of the page to directly withdraw the task or edit and resubmit the task.
   tip

   • Pre-check automatically 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 any problems are found during the process, specific information will be provided to facilitate identification and modification.
   • If the data source is not configured with approval process, the task will automatically skip the Submit Approval node and proceed to the Parsing Binlog node. After parsing is completed, all changes that meet the criteria will be listed in the Tracking Record area of the current page, and the task will end.

7. Click Submit Approval in the upper right corner of the page. In the pop-up window, 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 perform these actions before approval:

   • Click the icon in the upper right corner of the page to refresh the task status.
   • Click Withdraw in the upper right corner of the page to directly withdraw the task or edit and resubmit the task.
   • Click Transfer in the upper right corner of the page to change the approver of this task.

9. After the task status changes to Approved, the system will perform Binlog parsing. During the parsing process, all changes that meet the criteria will be listed in the Tracking Record area of the current page.

Approve a task

Prerequisites

• Have created or joined an organization. For more information, see Managing Organizations.
• The NineData Console is in organization mode. For how to switch from personal mode to organization mode, see Switching to Organization Mode.
• The current Track & Rollback task status is Pending Approval.
• You are Administrator or the Approver for the current task.

Procedure

1. Log in to the NineData Console.

2. Click DevOps > Track & Rollback in the left navigation bar.

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

   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 form, and choose Transfer, Approved, or Rejected.

   Operation	Description
   Transfer	Transfer the task to another user for approval.
   Approved	Approve the application. After clicking Approved, you can enter Reason as needed and click OK.
   Rejected	Reject the application. After clicking Rejected, you also need to enter Reason for rejection and click OK.

Terminate a task

If the task affects the database, it can be terminated immediately.

Prerequisites

• Have created or joined an organization. For more information, see Managing Organizations.
• The NineData Console is in organization mode. For how to switch from personal mode to organization mode, see Switching to Organization Mode.
• The current Track & Rollback task status is Parsing.
• You are either an Administrator or the creator or an Approver of the current task.

Procedure

1. Log in to the NineData Console.

2. In the left navigation bar, click DevOps > Track & Rollback.
3. On the Track & Rollback page, find the target task, and click the task ID or the Details in the Actions column on the right side of the task.
4. Click Terminate in the upper right corner of the page, and click OK in the confirmation window.

View tracking records

Prerequisites

• Organization creation or joining is completed. For more information, see Managing Organizations.

• NineData Console is in organization mode. For instructions on switching from personal mode to organization mode, see Switching to Organization.

• The current status of the Track & Rollback task is Finished.

  tip

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

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

Steps

1. Log in to the NineData Console.

2. Click DevOps > Track & Rollback in the left navigation bar.

3. On the Track & Rollback page, locate the target task and click the task ID or the Details in the Actions column to the right of the task.

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 the button on the left of the target 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 for batch rollback. 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 there are too many tracking records, you might need to filter out the required records through advanced search and then download them in bulk as a .sql file for easy rollback. In this scenario, you can use the Download Rollback SQL feature.

Prerequisites

• Organization creation or joining is completed. For more information, see Managing Organizations.

• NineData Console is in organization mode. For instructions on switching from personal mode to organization mode, see Switching to Organization.

• The current status of the Track & Rollback task 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 current task.

Steps

1. Log in to the NineData Console.

2. In the left navigation bar, click DevOps > Track & Rollback.

3. On the Track & Rollback page, locate the target task and click the Task ID or the Details option in the Actions column on the right side of the task.

4. On the Tracking Record tab, filter the tracking records as needed, then click Download Rollback SQL > Download Current.

   If you need to generate rollback SQL for all tracking records, you can 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.