Database and table restoration lets you recover accidentally modified or deleted tables back to the original cluster without restoring the entire cluster. Use this feature when you need table-level or database-level granularity, and expect a restoration time measured in minutes to a few hours depending on data size and cluster configuration.
If restoring multiple terabytes of data, consider full cluster restoration from a backup set instead — that process typically completes in a few minutes. For more information, see Method 1 for full restoration: Restore data from a backup set.
The new database and table restoration workflow, which includes the Fast Import feature, is available in a phased release for PolarDB for MySQL 8.0.1 clusters with minor version 8.0.1.1.49 or later.
Choose a restore method
| Point-in-time restore | Restore from a backup set | |
|---|---|---|
| When to use | Target time does not match any existing backup set | Target time matches the creation time of a backup set |
| Convenience | Requires replaying redo logs after restoring the backup | More straightforward — no log replay step |
How it works
Both restore methods follow the same core sequence:
A temporary node is created.
Data from the target point in time is restored to the temporary node — either from a backup set, or from a backup set plus redo log replay (point-in-time restore only).
The data is copied from the temporary node back to the original cluster.
The new restoration workflow, released on April 3, 2024, significantly reduces the time for step 3. Data is also automatically synchronized to Hot Standby Clusters and Global Database Network (GDN) secondary clusters, eliminating additional manual sync time.
If your cluster's revision version meets the requirements in the next section, the new workflow is used automatically.
Schedule restoration during off-peak hours to minimize the impact on your workload.
Supported versions
Database and table restoration is available on PolarDB Enterprise Edition and Standard Edition. The new restoration workflow requires a higher revision version than the basic feature.
To check your cluster's revision version, go to Basic Information > Configuration Information.
| Edition series | MySQL version | Architecture | Basic features (min revision version) | New restoration process (min revision version) |
|---|---|---|---|---|
| Enterprise Edition (Cluster Edition) | 5.6 | X86 | 5.6.1.0.25 | 5.6.1.0.42 |
| Enterprise Edition (Cluster Edition) | 5.7 | X86 | 5.7.1.0.8 | 5.7.1.0.36 |
| Enterprise Edition (Cluster Edition) | 8.0.1 | X86 | 8.0.1.1.14 | 8.0.1.1.46 |
| Enterprise Edition (Cluster Edition) | 8.0.2 | X86 | 8.0.2.2.0 | 8.0.2.2.26 |
| Standard Edition | 5.6 | X86 | 5.6.1.0.42 | 5.6.1.0.42 |
| Standard Edition | 5.7 | X86 | 5.7.1.0.30 | 5.7.1.0.30 |
| Standard Edition | 8.0.1 | X86 | 8.0.1.1.38.2 | 8.0.1.1.38.2 |
| Standard Edition | 8.0.1 | Yitian (ARM) | 8.0.1.1.41 | 8.0.1.1.41 |
| Standard Edition | 8.0.2 | X86 | 8.0.2.2.21 | 8.0.2.2.21 |
Estimated time
Time per step
| Step | Estimated time |
|---|---|
| Create a temporary node and restore data from the backup set | About 3 to 10 minutes |
| Replay incremental data from redo logs (point-in-time restore only) | 1.5 GB/minute |
| Restore data to the original cluster | See Restoration speed reference below |
The preceding data is for reference only.
Restoration speed reference
The following tables show measured restoration speeds for a single 200 GB table under different configurations. "Restoration speed" refers to the rate at which data is copied back to the original cluster — it does not include the time to create the temporary node or replay redo logs.
Key factors that affect restoration speed:
Whether a Hot Standby Cluster is enabled
Primary node CPU and memory specifications
The
innodb_io_capacityandinnodb_io_capacity_maxparameter values — dynamically adjustable; changes have a major effect on the new workflow and a minor effect on the old workflowThe restoration speed configuration (Quick, Standard, or Safe) — also has a major effect on the new workflow and a minor effect on the old workflow
Restoration speed configurations:
| Configuration | IOPS usage | Trade-off |
|---|---|---|
| Quick | Highest | Fastest restoration, but consumes more cluster I/O — use when minimizing downtime is the priority |
| Standard | Medium | Balances restoration speed against cluster I/O impact |
| Safe | Lowest | Minimizes cluster I/O impact, but restoration takes longer |
Because the 2-core, 8 GB specification is small and has high I/O fluctuations, the restoration speed configuration may not have a noticeable effect. Therefore, test results for this configuration are not listed for different speed configurations.
2 cores, 8 GB — innodb_io_capacity: 4000 / innodb_io_capacity_max: 8000
| Hot standby for storage | Old workflow — duration | Old workflow — speed | New workflow config | New workflow — duration | New workflow — speed | Speed increase vs. old workflow |
|---|---|---|---|---|---|---|
| Yes | 3h 38m 25s | 1.03 GB/min | Standard | 1h 43m 36s | 2.16 GB/min | +110% |
| No | 2h 23m 0s | 1.57 GB/min | Standard | 1h 43m 36s | 2.16 GB/min | +38% |
4 cores, 16 GB — innodb_io_capacity: 4000 / innodb_io_capacity_max: 8000
| Hot standby for storage | Old workflow — duration | Old workflow — speed | New workflow config | New workflow — duration | New workflow — speed | Speed increase vs. old workflow |
|---|---|---|---|---|---|---|
| Yes | 3h 3m 31s | 1.14 GB/min | Quick | 54m 13s | 3.70 GB/min | +225% |
| No | — | — | Quick | — | — | +88% |
| Yes | 3h 3m 31s | 1.14 GB/min | Standard | 1h 20m | 2.5 GB/min | +119% |
| No | — | — | Standard | 1h 45m 53s | 1.97 GB/min | +27% |
| Yes | 3h 3m 31s | 1.14 GB/min | Safe | 2h 12m | 1.52 GB/min | +33% |
| No | — | — | Safe | — | — | -30% |
4 cores, 16 GB — innodb_io_capacity: 8000 / innodb_io_capacity_max: 16000
| Hot standby for storage | Old workflow — duration | Old workflow — speed | New workflow config | New workflow — duration | New workflow — speed | Speed increase vs. old workflow |
|---|---|---|---|---|---|---|
| Yes | 3h 3m 15s | 1.14 GB/min | Quick | 42m 18s | 4.76 GB/min | +318% |
| No | — | — | Quick | — | — | +142% |
| Yes | 3h 3m 15s | 1.14 GB/min | Standard | 54m 16s | 3.70 GB/min | +225% |
| No | — | — | Standard | 1h 45m 53s | 1.97 GB/min | +88% |
| Yes | 3h 3m 15s | 1.14 GB/min | Safe | 1h 20m | 2.5 GB/min | +119% |
| No | — | — | Safe | — | — | +27% |
8 cores, 32 GB — innodb_io_capacity: 4000 / innodb_io_capacity_max: 8000
| Hot standby for storage | Old workflow — duration | Old workflow — speed | New workflow config | New workflow — duration | New workflow — speed | Speed increase vs. old workflow |
|---|---|---|---|---|---|---|
| Yes | 2h 50m 56s | 1.19 GB/min | Quick | 54m 39s | 3.70 GB/min | +211% |
| No | — | — | Quick | — | — | +80% |
| Yes | 2h 50m 56s | 1.19 GB/min | Standard | 1h 21m | 2.47 GB/min | +108% |
| No | — | — | Standard | 1h 38m 57s | 2.05 GB/min | +20% |
| Yes | 2h 50m 56s | 1.19 GB/min | Safe | 2h 12m | 1.52 GB/min | +28% |
| No | — | — | Safe | — | — | -35% |
8 cores, 32 GB — innodb_io_capacity: 18000 / innodb_io_capacity_max: 36000
| Hot standby for storage | Old workflow — duration | Old workflow — speed | New workflow config | New workflow — duration | New workflow — speed | Speed increase vs. old workflow |
|---|---|---|---|---|---|---|
| Yes | 2h 51m 5s | 1.19 GB/min | Quick | 41m 48s | 4.88 GB/min | +310% |
| No | — | — | Quick | — | — | +273% |
| Yes | 2h 51m 5s | 1.19 GB/min | Standard | 54m 43s | 3.70 GB/min | +211% |
| No | — | — | Standard | 1h 38m 33s | 1.31 GB/min | +182% |
| Yes | 2h 51m 5s | 1.19 GB/min | Safe | 1h 21m | 2.47 GB/min | +108% |
| No | — | — | Safe | — | — | +89% |
16 cores, 64 GB — innodb_io_capacity: 4000 / innodb_io_capacity_max: 8000
| Hot standby for storage | Old workflow — duration | Old workflow — speed | New workflow config | New workflow — duration | New workflow — speed | Speed increase vs. old workflow |
|---|---|---|---|---|---|---|
| Yes | 2h 55m 26s | 1.17 GB/min | Quick | 53m 28s | 3.77 GB/min | +222% |
| No | — | — | Quick | — | — | +88% |
| Yes | 2h 55m 26s | 1.17 GB/min | Standard | 1h 20m | 2.5 GB/min | +114% |
| No | — | — | Standard | 1h 42m 20s | 2.01 GB/min | +24% |
| Yes | 2h 55m 26s | 1.17 GB/min | Safe | 2h 12m | 1.52 GB/min | +30% |
| No | — | — | Safe | — | — | -32% |
16 cores, 64 GB — innodb_io_capacity: 20000 / innodb_io_capacity_max: 40000
| Hot standby for storage | Old workflow — duration | Old workflow — speed | New workflow config | New workflow — duration | New workflow — speed | Speed increase vs. old workflow |
|---|---|---|---|---|---|---|
| Yes | 2h 53m 49s | 1.19 GB/min | Quick | 41m 1s | 4.88 GB/min | +310% |
| No | — | — | Quick | — | — | +138% |
| Yes | 2h 53m 49s | 1.19 GB/min | Standard | 54m 5s | 3.70 GB/min | +211% |
| No | — | — | Standard | 1h 40m 35s | 2.05 GB/min | +80% |
| Yes | 2h 53m 49s | 1.19 GB/min | Safe | 1h 20m | 2.5 GB/min | +110% |
| No | — | — | Safe | — | — | +22% |
The test data above covers a single-table, ~200 GB scenario. Restoring many tables simultaneously also affects speed. Actual performance depends on the underlying hardware model and network conditions. All figures are for reference only.