SQL backup and rollback (Public preview)

Background information

When you change data in the SQL Console's new interactive mode, you can enable backups. This lets you quickly roll back data if you make a mistake. DMS provides two backup policies:

• General backup: Non-MySQL relational databases use this backup method. The system first identifies the data rows affected by the change and generates INSERT or REPLACE INTO statements.

• Precision backup: RDS for MySQL, PolarDB for MySQL, and self-managed MySQL databases can use precision backup. This method builds on the general backup capability. It adds support for SQL-level backups through binary logging (binlog). It generates rollback scripts for the data affected by the SQL statements.

For more information about the SQL Console's new interactive mode, see Change table data and schemas in the new interactive mode of SQL Console (Public preview).

Differences between SQL backup and rollback and data tracking

Both the SQL backup and rollback feature and the data tracking feature can be used to roll back incorrect data. The differences between the two features are as follows:

For more information about the data tracking feature, see Data tracking.

Prerequisites

• To use precision backup, the following conditions must be met:

  • The database is one of the following types: RDS for MySQL, a self-managed MySQL database of version 5.6 or later, or PolarDB for MySQL of version 5.6 or 5.7.

  • Binary logging (binlog) is enabled for the database, and the format is set to ROW.

  • If the database is a self-managed MySQL database of version 8.0 or later, the SESSION_VARIABLES_ADMIN permission must be granted to the current database account. For more information about this permission, see System Variable Privileges.

    For example, if the database account is set_session_sysvars, the authorization statement is as follows:

    GRANT SESSION_VARIABLES_ADMIN ON *.* TO set_session_sysvars;

• Non-MySQL relational databases, such as SQL Server, PostgreSQL, and Oracle, including self-managed databases, support only general backup.

• This feature is available for instances with the Flexible Management capability (5 trial uses only) or the Stable Change capability (unlimited uses).

  Note

  • Instances with the Flexible Management capability can use this feature only if trial uses are available. The system allocates five trial uses of this feature to each tenant. If you run out of trial uses and need to continue using the feature, you must purchase the Stable Change service. For more information about how to purchase the service, see Purchase DMS services.

  • You cannot back up data in the SQL Console for instances with the Security Collaboration capability. To back up data for these instances, you must submit a data change ticket.

Precautions

• If the conditions for precision backup are met, you must retrieve the backup data within the binlog retention period after you enable SQL backup. You cannot retrieve backup data after the binlog retention period expires.

  Note

  • For the binlog retention period of RDS for MySQL, see RDS for MySQL log backup.

  • For the binlog retention period of a self-managed MySQL database, see the official MySQL documentation.

  • For the binlog retention period of PolarDB for MySQL, see Enable binary logging.

• Retrieved backup data is stored by DMS for 30 days.

• Backups for schema evolution are not supported.

• The SQL backup and rollback feature is in public preview. If you encounter any issues, search for the DingTalk group ID 21991247 in DingTalk and join the group to provide feedback.

• Precision backup and general backup do not support lock-free data changes.

• Databases that use general backup do not support backing up INSERT operations.

Precision backup and rollback

1. Log in to DMS 5.0.

2. In the Database Instance list in the navigation pane on the left, find and double-click the name of your target database to open the SQL Console page.

   Note

   Alternatively, you can enter the name of the target database in the search box of the database instance list and click the search icon.

3. Enter an UPDATE, DELETE, or INSERT statement and click Execute.

4. Enable backup.

   In the Change Suggestion section of the Confirm Change panel, enable backup. You can also specify a time to execute the SQL statement. Then, click Execute Directly or Execute Asynchronously in the Background.

   Asynchronous execution in the background changes table data or the table schema by submitting a ticket.

5. Roll back the data.

   1. After the statement executes successfully, on the Execution Result tab, click Get Backup.

   2. In the Group Task Details dialog box, click Get Backup again.

      You can click the refresh icon at any time to view the progress of the backup retrieval.

   3. After the backup is retrieved, click Download Rollback Script to download the script to your computer as a .zip file.

   4. Open the SQL file from the compressed file, copy the rollback SQL statement, paste it into the SQL Console, and click Execute.

   5. In the Confirm Change panel, click Execute.

General backup and rollback

This section uses an operation on an RDS for PostgreSQL database as an example. In this example, a table named tb_test is already created.

-- Create a table
CREATE TABLE tb_test (
  id INT NOT NULL,
  name VARCHAR(50) NOT NULL,
  age INT NOT NULL,
  PRIMARY KEY (id)
);

-- Insert data

INSERT INTO tb_test (id, name, age) VALUES
  (1, 'Apple', 25),
  (2, 'Lily', 30),
  (3, 'Lucy', 35);

1. Log in to DMS 5.0.

2. In the Database Instance list in the navigation pane on the left, find and double-click the name of your target database to open the SQL Console page.

   Note

   Alternatively, you can enter the name of the target database in the search box of the database instance list and click the search icon.

3. Enter an UPDATE or DELETE statement and click Execute.

   Example of an UPDATE statement: UPDATE tb_test SET name='Apples' WHERE name='Lucy';

4. Enable backup.

   In the Change Suggestion section of the Confirm Change panel, select Enable Backup. Choose whether to execute the SQL statement at a scheduled time. Then, click Execute Directly or Execute Asynchronously in the Background.

   Asynchronous execution in the background changes table data or the table schema by submitting a ticket.

5. Download the backup.

   In the Execution Results section at the bottom of the page, click Download Backup to download the backup to your computer as a .zip file.

6. In the SQL window, execute a CREATE statement to create a temporary table named tb_test_rollback that has the same schema as the original table.

   Example SQL statement: CREATE TABLE tb_test_rollback (LIKE tb_test);

7. Open the SQL file in the compressed file and change the table name in the INSERT script to the name of the temporary table.

8. In the SQL window, execute the modified INSERT statement.

9. Write an UPDATE statement to correct the data in the original table.

   The UPDATE statement in this example is as follows:

   UPDATE tb_test a
   SET name = b.name
   FROM tb_test_rollback b 
   WHERE a.id = b.id;

References

After you perform an SQL backup and rollback, you may need to verify that the data in the table is restored. For more information, see Query and change table schemas.