Restoring a Table to a Specified Point in Time
RDS supports table PITR to ensure data integrity and reduce impact on the original instance performance. You can select a table and restore it to a specified point in time. The most recent full backup file will be downloaded from OBS to a temporary DB instance for restoration. After the restoration is complete, binlogs will be replayed on the temporary instance to the specified point in time. Table data will then be written back to the original DB instance. The average restoration rate is 20 MB/s.
The time required depends on the amount of data to be backed up and restored on the DB instance. Restoring tables will not overwrite data in the DB instance. You can select tables to be restored.
- This function is supported only for MySQL DB instances.
- MySQL table PITR does not support tables with foreign keys.
- MySQL 8.0 DB instances do not support table PITR.
- During the table PITR, DB instances and read replicas cannot be rebooted or deleted, and their instance specifications cannot be modified.
- During the table PITR, the database or table information to be restored is read from the latest full backup before the selected time point. You can select any time point within the restoration time range. Therefore, a database or table can be restored to the earliest full backup time point when its information exists.
After the table is restored, a new table will be generated in the DB instance. Ensure the DB instance has sufficient storage space for the generated table.
- Log in to the management console.
- Click in the upper left corner and select a region and a project.
- Click in the upper left corner of the page and choose Database > Relational Database Service. The RDS console is displayed.
- On the Instance Management page, click the target DB instance.
- Choose Backups & Restorations in the navigation pane on the left. On the Full Backups page, choose More > Restore Table above the backup list. Alternatively, on the Binlog Backups page, click Restore Table above the backup list.
- Set the restoration date, time range, time point, and tables to be restored, and click Next: Confirm.
- To facilitate your operations, you can search for the tables and databases to be restored.
- After the restoration is complete, new tables with timestamps appended as suffixes to original table names are generated in the DB instance. You can rename the new tables.
- The new table name must be unique and consist of 1 to 64 characters. Only letters, digits, underscores (_), hyphens (-), and dollar signs ($) are allowed.
- Table PITR does not support the restoration of databases whose names contain periods (.).Figure 1 Restoring tables to a specified point in time
- If you have enabled operation protection, click Send Code in the displayed Identity Verification dialog box and enter the obtained verification code. Then, click OK.
Two-factor authentication improves the security of your account and cloud product. For details about how to enable operation protection, see the Identity and Access Management User Guide.
- On the displayed page, confirm the information and click Submit.
- On the Instance Management page, the DB instance status is Restoring. During the restoration process, services are not interrupted.
You can also view the progress and result of restoring tables to a specified point in time on the Task Center page.
After the restoration is successful, you can manage data in the tables as required.
- Data is restored at an average speed of 40 MB/s.
- Restoring a table to a specified point in time does not affect new data. The restored table is a temporary table with a timestamp suffix. You can manage the data in the temporary table as required.
After the restoration is successful, you can log in to the DB instance for verification.