Overall process and estimated time - PolarDB - Alibaba Cloud Documentation Center

If you make accidental changes to tables, you can use the database and table restoration feature to restore them to the original cluster.

You can restore data to an earlier point in time or from a backup set (snapshot). If the earlier point in time coincides with the backup set's creation time, you can use the backup set. Otherwise, you can restore data only to the earlier point in time.

Note

The fast Import feature is now available in the canary release for the new database and table restoration workflow in PolarDB for MySQL 8.0.1 with revision version 8.0.1.1.48 or later.

Overall restoration process

The restoration process generally includes the following key steps regardless of the method you use: 1. Create a temporary node and restore data at an earlier point in time to the node. 2. Restore the data to the original cluster.

The new version of the database and table restoration feature was released on April 3, 2024. Compared with the original version, it restores data to the original cluster faster. The restored data can also be automatically synchronized to the hot standby storage cluster and the Global Database Network (GDN) secondary cluster, which significantly accelerates the restoration.

Supported versions

An Enterprise Edition supports the new version of the feature only if it runs one of the following database engine versions:

PolarDB for MySQL 8.0.2 with revision version 8.0.2.2.25.3 or later.
PolarDB for MySQL 8.0.1 with revision version 8.0.1.1.44 or later.
PolarDB for MySQL 5.7 with revision version 5.7.1.0.30 or later
PolarDB for MySQL 5.6 with revision version 5.6.1.0.42 or later.

Flowcharts of original and new versions

If your cluster runs a version that supports the new version of the feature, you can use the feature to restore databases or tables. The following figure shows the database and table data restoration process of the original and new versions.

Note

We recommend that you restore data during off-peak hours.

Estimated time

Estimated time for each step

Step	Estimated time

Step	Estimated time
Create a temporary node and restore data from the backup set to the node.	About 3 to 10 minutes
Restore incremental data by using redo logs. Note Redo logs are used only to restore data to an earlier point in time. The restoration duration varies based on the size of redo logs.	1.5 GB/minute
Restore data to the original cluster.	See Test speeds of database and table restoration.

Note

The preceding data is for reference only.
More time is required to restore terabytes of data in databases and tables. If you want to quickly restore full data, you can use a backup set (snapshot). This process usually takes several minutes. For more information, see Method 1 for full restoration: Restore data from a backup set.

Test speeds of database and table restoration

CPU and memory (dedicated)	Test data	innodb_io_capacity	innodb_io_capacity_max	Original workflow			New workflow			Speed comparison
CPU and memory (dedicated)	Test data	innodb_io_capacity	innodb_io_capacity_max	Hot standby storage cluster enabled	Restoration duration	Restoration speed	Restoration speed configuration	Restoration duration	Restoration speed	Hot standby storage cluster enabled (original workflow)	Speed increase

CPU and memory (dedicated)	Test data	innodb_io_capacity	innodb_io_capacity_max	Original workflow			New workflow			Speed comparison
				Hot standby storage cluster enabled	Restoration duration	Restoration speed	Restoration speed configuration	Restoration duration	Restoration speed	Hot standby storage cluster enabled (original workflow)	Speed increase
2 cores, 8 GB memory	About 200 GB per table	4000	8000	Yes	3 hours 38 minutes 25 seconds	1.03 GB/minute	Standard	1 hour 43 minutes 36 seconds	2.16 GB/minute	Yes	110%
				No	2 hours 23 minutes 0 seconds	1.57 GB/minute				No	38%
4 cores, 16 GB memory	About 200 GB per table	4000	8000	Yes	3 hours 3 minutes 31 seconds	1.14 GB/minute	Quick	54 minutes 13 seconds	3.70 GB/minute	Yes	225%
										No	88%
							Standard	1 hour 20 minutes	2.5 GB/minute	Yes	119%
				No	1 hour 45 minutes 53 seconds	1.97 GB/minute				No	27%
							Security	2 hours 12 minutes	1.52 GB/minute	Yes	33%
										No	-30%
		8000	16000	Yes	3 hours 3 minutes 15 seconds	1.14 GB/minute	Quick	42 minutes 18 seconds	4.76 GB/minute	Yes	318%
										No	142%
							Standard	54 minutes 16 seconds	3.70 GB/minute	Yes	225%
				No	1 hour 45 minutes 53 seconds	1.97 GB/minute				No	88%
							Security	1 hour 20 minutes	2.5 GB/minute	Yes	119%
										No	27%
8 cores, 32 GB memory	About 200 GB per table	4000	8000	Yes	2 hours 50 minutes 56 seconds	1.19 GB/minute	Quick	54 minutes 39 seconds	3.70 GB/minute	Yes	211%
										No	80%
							Standard	1 hour 21 minutes	2.47 GB/minute	Yes	108%
				No	1 hour 38 minutes 57 seconds	2.05 GB/minute				No	20%
							Security	2 hours 12 minutes	1.52 GB/minute	Yes	28%
										No	-35%
		18000	36000	Yes	2 hours 51 minutes 5 seconds	1.19 GB/minute	Quick	41 minutes 48 seconds	4.88 GB/minute	Yes	310%
										No	273%
							Standard	54 minutes 43 seconds	3.70 GB/minute	Yes	211%
				No	1 hour 38 minutes 33 seconds	1.31 GB/minute				No	182%
							Security	1 hour 21 minutes	2.47 GB/minute	Yes	108%
										No	89%
16 cores, 64 GB memory	About 200 GB per table	4000	8000	Yes	2 hours 55 minutes 26 seconds	1.17 GB/minute	Quick	53 minutes 28 seconds	3.77 GB/minute	Yes	222%
										No	88%
							Standard	1 hour 20 minutes	2.5 GB/minute	Yes	114%
				No	1 hour 42 minutes 20 seconds	2.01 GB/minute				No	24%
							Security	2 hours 12 minutes	1.52 GB/minute	Yes	30%
										No	-32%
		20000	40000	Yes	2 hours 53 minutes 49 seconds	1.19 GB/minute	Quick	41 minutes 1 second	4.88 GB/minute	Yes	310%
										No	138%
							Standard	54 minutes 5 seconds	3.70 GB/minute	Yes	211%
				No	1 hour 40 minutes 35 seconds	2.05 GB/minute				No	80%
							Security	1 hour 20 minutes	2.5 GB/minute	Yes	110%
										No	22%

Note

Restoration speed is the speed at which data is restored to the original cluster, and does not include the time consumed to create temporary nodes or apply incremental logs in the restoration process.
The restoration speed depends on whether hot standby storage cluster is enabled, the primary node specifications, the innodb_io_capacity parameter value, the restoration speed configuration, and the number of tables that you want to restore.
You can adjust the restoration speed by modifying the values of the innodb_io_capacity and innodb_io_capacity_max parameters. Modifying the parameters has minimal impact on the restoration speed of the original version but greatly enhances it in the new version.
The restoration speed is categorized into three configurations based on the input/output operations per second (IOPS) usage: quick, standard, and security. A higher allocated IOPS means a faster restoration speed, especially when restoring large tables.
Restoration Speed Configuration has minimal impact on the restoration speed of the original version but significantly enhances it in the new version.
For the small specifications of 2 CPU cores and 8 GB of memory, the I/O performance may greatly vary based on the workloads and reduce the effectiveness of restoration speed configurations. Therefore, the restoration speed configuration test results for this specification are not listed.
The preceding test data does not include scenarios involving numerous tables. Restoring numerous tables affects the restoration speed.
The preceding test data is only for reference. The actual restoration speed depends on various factors such as the underlying hardware and network conditions.