When accidental operations are performed on tables, you can use the table and database restoration feature to restore the tables to the original cluster.
You can restore data to an earlier point in time or from a backup set (snapshot). The earlier point in time to which you want to restore data may coincide with the time when a backup set was created. In this case, you can restore data from the backup set. If no backup sets were created at the point in time to which you want to restore data, you can only choose to restore data to the earlier point in time.
Overall restoration process
The key steps of the restoration process are the same regardless of whether you restore data to an earlier point of time or from a backup set. The restoration process mainly includes the following steps: 1. Create a temporary node and restore data at an earlier point of 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, the new version reduces the time required to restore data to the original cluster. The restored data can also be automatically synchronized to the hot standby storage cluster and the Global Database Network (GDN) secondary cluster, which significantly reduces the overall restoration time.
A PolarDB for MySQL cluster whose Database Edition is Enterprise Edition supports the new version of the database and table restoration feature only if the cluster runs one of the following database engine versions:
PolarDB for MySQL 8.0.2 whose revision version is 8.0.2.2.25.3 or later.
PolarDB for MySQL 8.0.1 whose revision version is 8.0.1.1.44 or later.
PolarDB for MySQL 5.7 whose revision version is 5.7.1.0.30 or later.
PolarDB for MySQL 5.6 whose revision version is 5.6.1.0.42 or later.
If your cluster runs a version that supports the new version of the database and table restoration feature, you can use the new version of the database and table restoration feature to restore your databases or tables. The following figure shows the database and table data restoration process of the original and new versions.
We recommend that you restore data during off-peak hours.
Estimated time required for restoration
The following table describes the estimated time required to restore data. The estimated time is provided only for reference.
Step | Estimated time |
Create a temporary node and restore data from the backup set to the node. | Approximately 3 to 10 minutes |
Restore incremental data by using redo logs. Note Redo logs are used only when you want to restore data to an earlier point in time. The restoration duration varies based on the size of redo logs that are used. | 1.5 GB/minute |
Restore data to the original cluster. |
More time is required to restore terabytes of data in databases and tables. For a quick restoration, you can restore full data from a backup set (snapshot). In most cases, this process requires several minutes to complete. For more information, see Method 1 for full restoration: Restore data from a backup set.
The following table provides the test speeds of database and table restoration. The restoration speed in this table indicates only the speed at which data is restored to the original cluster.
Table 1. Test speeds of database and table restoration
CPU and memory (dedicated) | Test data | innodb_io_capacity | innodb_io_capacity_max | Hot standby storage cluster enabled | Time required for the original version | Time required for the new version | Restoration speed for the original version | Restoration speed for the new version | Speed increase of the new version |
2 cores, 8 GB memory | 224.06 GB common table | 4000 | 8000 | Yes | 3 hours 38 minutes 25 seconds | 1 hour 43 minutes 36 seconds | 1.03 GB/minute | 2.16 GB/minute | 111% |
No | 2 hours 23 minutes 0 seconds | 1.57 GB/minute | 38% | ||||||
4 cores, 16 GB memory | 208.83 GB common table | 4000 | 8000 | Yes | 3 hours 3 minutes 31 seconds | 1 hour 36 minutes 41 seconds | 1.14 GB/minute | 2.16 GB/minute | 89% |
No | 1 hour 45 minutes 53 seconds | 1.97 GB/minute | 10 % | ||||||
8000 | 16000 | Yes | 3 hours 3 minutes 15 seconds | 1 hour 3 minutes 49 seconds | 1.14 GB/minute | 3.27 GB/minute | 187 % | ||
No | 1 hour 45 minutes 53 seconds | 1.97 GB/minute | 66 % | ||||||
8 cores, 32 GB memory | 202.97 GB common table | 4000 | 8000 | Yes | 2 hours 50 minutes 56 seconds | 1 hour 28 minutes 26 seconds | 1.19 GB/minute | 2.30 GB/minute | 93% |
No | 1 hour 38 minutes 57 seconds | 2.05 GB/minute | 12% | ||||||
18000 | 36000 | Yes | 2 hours 51 minutes 5 seconds | 42 minutes 39 seconds | 1.19 GB/minute | 4.76 GB/minute | 301% | ||
No | 1 hour 38 minutes 33 seconds | 1.31 GB/minute | 131% | ||||||
16 cores, 64 GB memory | 206.01 GB common table | 4000 | 8000 | Yes | 2 hours 55 minutes 26 seconds | 1 hour 31 minutes 14 seconds | 1.17 GB/minute | 2.26 GB/minute | 93% |
No | 1 hour 42 minutes 20 seconds | 2.01 GB/minute | 12% | ||||||
20000 | 40000 | Yes | 2 hours 53 minutes 49 seconds | 1 hour 0 minutes 9 seconds | 1.19 GB/minute | 3.42 GB/minute | 189% | ||
No | 1 hour 40 minutes 35 seconds | 2.05 GB/minute | 67% |
The database and table restoration speed is affected by the following factors: whether hot standby storage cluster is enabled, the primary node specifications, the
innodb_io_capacityparameter value, and the number of tables you want to restore.The preceding test data does not include scenarios that involve a large number of tables. If you want to restore a large number of tables, the restoration process requires a longer period of time to complete.
You can adjust the restoration speed by dynamically adjusting the values of the
innodb_io_capacityandinnodb_io_capacity_maxparameters. The adjustment of the parameters has minimal impact on the restoration speed of the original version but significantly enhances the restoration speed of the new version.The preceding test data is provided only for reference. The actual restoration speed is affected by various factors such as the underlying hardware and network conditions.