Track & Rollback
The NineData Track & Rollback feature is used to analyze changes or deletions of data or object structures in the database and generate rollback statements for quick data recovery. This ensures the integrity of business data after accidental operations or data errors.
Background Information
As enterprises grow, the structure and business data of their databases gradually increase and become more complex, making data maintenance increasingly difficult. For example, a program bug may cause data errors in the business database, or after a data change operation, the values in the business database may not meet expectations. At such times, it is necessary to quickly locate the update statements that caused the failure and perform rollback operations to restore the data to its previous state.
Function Introduction
The Track & Rollback feature of NineData helps users track executed change statements in the target database and locate and rollback based on the change type and execution 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.
Usage Restrictions
- After the Track & Rollback task is created, tracking records (Binlog parsing records) will be retained for one month, and will be cleared after one month, so please check in time.
- 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
tipIf the data source is a standby database, to ensure complete Binlog logs are obtained, you also need to enable the
log_slave_updates
parameter.
Submitting Tasks
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, please contact your system administrator.
- Have the permission Track & Rollback. For how to apply for this permission, see Permission Application and Approval.
Operating Steps
Log in to the NineData Console.
Click DevOps > Track & Rollback in the left navigation bar.
On the Track & Rollback page, click the Create Track & Rollback button in the upper right corner of the page.
On the Create Track & Rollback page, configure according to the table below.
Parameter Description Name Enter the name of the Track & Rollback task. For easy retrieval and management, please use a meaningful name. 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, please 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, please 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. Click Create Track & Rollback to automatically jump to the Details page.
In the Pre-check node, wait for the task status to change to Pending. In the Pre-check node, you can also perform the following operations:
- 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.
- Click the
Click Submit Approval in the upper right corner of the page. In the pop-up window, select the workflow approvers and click OK.
tip- Depending on the configuration of the approval process, different numbers of approvers need to be selected here. Please select according to the actual situation.
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.
After the task status changes to Pending Approval, you can perform the following operations 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.
- Click the
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.
Approving Tasks
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.
Operating Steps
Log in to the NineData Console.
Click DevOps > Track & Rollback in the left navigation bar.
On the Track & Rollback page, find the target task and click the task ID or Details in the Actions column on the right.
tipYou can also hover over the
icon at the top of the page and click the target task on the Todo tab.
On the Details page, review the approval form, and choose Transfer, Approved, or Rejected according to the actual situation.
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 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
- Log in to the NineData Console.
- In the left navigation bar, click DevOps > Track & Rollback.
- 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.
- Click Terminate in the upper right corner of the page, and click OK in the confirmation window.
Viewing 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.
tipYou can also view parsed records when the tracking task status is Parsing.
You are either an Administrator or the creator or an Approver of the current task .
Steps
Log in to the NineData Console.
Click on DevOps > Track & Rollback in the left navigation bar.
On the Track & Rollback page, locate the target task and click on the task ID or the Details in the Actions column to the right of the task.
In the Tracking Record area, view all the changes that meet the requirements, including Modify Time, Modify Type, Database, Table, Primary Key. Click on the
button on the left of the target record to view the field details.
tipIf there are too many records, you can filter the required records by change time, SQL statement type, or directly search for the table name to locate the target record. Click on Advanced, select the table name, and then use Condition to perform a more granular search on the columns in the target table.
Click on Download Rollback SQL on the page to download the rollback SQL of the tracking record in the form of an SQL file to your local computer, which facilitates batch data rollback. It supports two options: Download Current and Download All.
Click on the Details in the Actions column to the right of the target record to view the detailed records before and after the changes corresponding to the current record. Selecting Only Diff Col. can hide columns that have not been changed. To restore to the state before the change, simply copy the SQL statement in Rollback SQL to the database for execution.
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.
tipYou 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
Log in to the NineData Console.
In the left navigation bar, click DevOps > Track & Rollback.
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.
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.
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.