Restore full data of an ApsaraDB RDS for MySQL instance - ApsaraDB RDS

You can use the data backup files and log backup files of an ApsaraDB RDS for MySQL instance to restore the data of the RDS instance to a new RDS instance. This method is suitable for the analysis of historical data and the restoration of data on which unintended operations are performed.

Prerequisites

The original RDS instance meets the following requirements:

The RDS instance is in the Running state and is not locked.
No ongoing migration tasks exist.
A backup is completed. ApsaraDB RDS provides the automatic backup feature. For more information about backup methods, see Overview of backup methods.
The log backup feature is enabled. This requirement must be met if you want to restore data to a specific point in time. For more information about how to enable the feature, see Use the log backup feature for an ApsaraDB RDS for MySQL instance.
At least one physical backup is completed. This requirement must be met if you want to restore data from backup sets. For more information, see Configure automatic backup.

Feature description

Item	Description
Restoration range	The entire RDS instance is restored.
Specifications of the new RDS instance	The new RDS instance uses the same whitelist configurations, backup configurations, and parameter configurations as the original RDS instance.
Account information about the new RDS instance	Account information that is stored at the point of time for data restoration is included in the new RDS instance. Account information in data backup files that you select for data restoration is also included in the new RDS instance.
Data on the new RDS instance	The data on the new RDS instance is the same as the data in the specified backup file of the original RDS instance.
Point in time for data restoration	If the log backup feature is disabled, you can restore the data only to the point in time at which the data is backed up. If the log backup feature is enabled, you can restore the data to any point in time within the log backup retention period. If the point-in-time recovery (PITR) feature is enabled, you can restore the data to any point in time based on the value of the Time Range of Specific Points in Time for Restoration parameter. The PITR feature is an enhancement of the log backup feature. Note For more information about how to view the retention period of the log backup files, see Use the log backup feature. For more information, see Differences between the PITR and log backup features.
Time required for data restoration	The period of time that is required for data restoration varies based on various factors. For example, if you restore 200 GB of data, approximately 3 hours are required. For more information, see the "FAQ" section of this topic.

Billing rules

You are charged for the new RDS instance that is used to restore data. You can view the price of the new RDS instance when you create the instance.

Note

If you want to temporarily use an RDS instance, you can create a pay-as-you-go or serverless RDS instance. After data is restored to the new RDS instance, you can migrate the data to the original RDS instance and then release the new RDS instance. For more information, see Migrate data between ApsaraDB RDS instances and Release or unsubscribe from an ApsaraDB RDS for MySQL instance.
You are immediately charged for the new RDS instance after the instance is created.

Enable the full data restoration feature

You do not need to manually enable the full data restoration feature. After an RDS instance is created, the system automatically performs periodic backups on the RDS instance. You can use the data backup files and log backup files that are generated to restore full data of the RDS instance.

Procedure

You can use the backup data of the original RDS instance to create another RDS instance for data restoration. This method does not affect the performance of the original RDS instance.

Go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the ID of the instance.
In the left-side navigation pane, click Backup and Restoration.
On the page that appears, click Restore Database.
Note
You can also click Restore Instance in the Instance Distribution section of the Basic Information page.

On the Restore Instance page, select a point in time to which you want to restore data or select a backup set from which you want to restore data. Then, configure other parameters.

Parameter	Description
Billing Method	Subscription: A subscription RDS instance is an instance for which you pay an upfront fee. This method is suitable for long-term use. You are offered lower prices for longer subscription durations. Pay-as-you-go: A pay-as-you-go RDS instance is billed per hour based on your actual resource usage. This method is suitable for short-term use. If you no longer require an pay-as-you-go RDS instance, you can release it.
Restoration Mode	By Backup Set: This method allows you to restore data from a specified backup set. Logical backup files are not supported. By Point in Time: This method allows you to restore data to a point in time within the log backup retention period. This option is available only if the log backup feature is enabled.
Product Type	If you set the Edition parameter to Basic Edition, this parameter is not displayed. If you set the Edition parameter to High-availability Edition, the product type that you can select depends on the storage type of your RDS instance. If you set the Storage Type parameter to ESSD or Premium ESSD, the Standard and YiTian product types are available. For more information, see Product types. If you set the Storage Type parameter to PremiumLocal SSD, only the Standard product type is available. If you set the Edition parameter to Cluster Edition, the Standard and YiTian product types are available.
Zone of Primary Node and Zone of Secondary Node	You can select the Single-zone Deployment or Multi-zone Development method. Single-zone Deployment: If you select this deployment method, the primary and secondary RDS instances are deployed in the same zone. Multi-zone Development: If you select this deployment method, the primary and secondary RDS instances are deployed across zones to implement zone-disaster recovery. We recommend that you select this deployment method. Note After the RDS instance is created, you can view information about the new RDS instance and its secondary RDS instance on the Service Availability page. If you select RDS Basic Edition, the database system supports only the single-zone deployment method.
Instance Type	General-purpose Instance Types: A general-purpose RDS instance exclusively occupies the memory and I/O resources. However, this type of instance shares CPU and storage resources with other general-purpose instances that are deployed on the same host. Dedicated Instance Types: A dedicated RDS instance exclusively occupies the CPU, memory, storage, and I/O resources that are allocated. The dedicated host instance family is the highest configuration of the dedicated instance family. A dedicated host RDS instance exclusively occupies all the CPU, memory, storage, and I/O resources on the host on which the instance is deployed. Note Each instance type supports a specific number of cores, memory capacity, maximum number of connections, and maximum IOPS. For more information, see Primary ApsaraDB RDS instance types.
Storage Capacity	The maximum amount of storage that is provisioned to store data files, system files, binary log files, and transaction files in the RDS instance. You can adjust the storage capacity at a step size of 5 GB.

Click Next: Instance Configuration to configure the network type and resource group of the RDS instance.

Parameter	Description
Network Type	Classic Network: the traditional type of network. VPC: the recommended type of network. A virtual private cloud (VPC) is an isolated virtual network that provides higher security and higher performance than the classic network. If you select the VPC network type, you must configure the VPC and vSwitch of Primary Node parameters. If you set the Deployment Method parameter to Multi-zone deployment in the previous step, you must also configure the vSwitch of Secondary Node parameter. Note The network type of the new RDS instance must be the same as the network type of the Elastic Compute Service (ECS) instance that you want to connect. If the new RDS instance and the ECS instance reside in VPCs, make sure that the instances reside in the same VPC. If the new RDS instance and the ECS instance reside in different VPCs, these instances cannot communicate over an internal network.
Resource Group	You can use resource groups to categorize the resources of your Alibaba Cloud account. Resource groups help you simplify the management of resources and permissions in your Alibaba Cloud account. You can select an existing resource group or create a resource group. If you do not need to group resources, select Default Resource Group.

Click Next: Confirm Order.
Confirm the settings in the Parameters section, configure the Purchase Plan and Duration parameters, read and select Terms of Service, click Pay Now, and then complete the payment. You must configure the Duration parameter only when you select the subscription billing method for the RDS instance.
Note
If you select the subscription billing method for the new RDS instance, we recommend that you select Auto-Renew Enabled below the Duration parameter. This way, you do not need to renew the new RDS instance on a regular basis. This also helps prevent interruptions on your workloads on the new RDS instance if a payment becomes overdue.
Optional. Log on to the new RDS instance and verify the data. For more information, see Use a client or the CLI to connect to an ApsaraDB RDS for MySQL instance.

Correct the online data on the new RDS instance

After you restore the data of the original RDS instance to the new RDS instance, you can use Data Transmission Service (DTS) to migrate some or all data of the required databases and tables to the original RDS instance to correct the online data of the original RDS instance. For more information, see Migrate data between ApsaraDB RDS for MySQL instances.

Note

When you create a task, use the new RDS instance as the source database and the original RDS instance as the destination database, and set the Access Method parameter for both the source and destination databases to Alibaba Cloud Instance.

References

This topic is suitable for full data restoration. For more information about how to restore individual databases and tables, see Restore individual databases and tables. For more information, see Overview of data restoration methods.

If you want to restore data to the original RDS instance, an existing RDS instance, or a self-managed instance, you can use multiple methods. The following table describes the methods.

Important

You cannot restore downloaded backup files to an existing RDS for MySQL instance. To achieve this goal, you can restore the backup files to a new instance, verify the data on the new instance, and then migrate data from the new instance to an existing instance.

Restoration destination	Restoration method
Original RDS instance	Method 1: Restore the data of the original RDS instance to a new RDS instance, verify the data on the new RDS instance, and then migrate some or all data of the required databases and tables to the original RDS instance. Method 2: Use the restoration feature for databases and tables to restore full data to the original RDS instance. Method 3: Use Database Backup (DBS) to create a logical backup. Then, restore data to the original RDS instance by using the logical backup file. For more information, see Restore a MySQL database from a logical backup.
Another existing RDS instance	Method 1: Restore the data of the original RDS instance to a new RDS instance, verify the data on the new RDS instance, and then migrate the data to another existing RDS instance. Method 2: Use DBS to create a logical backup. Then, restore data to another existing RDS instance by using the logical backup file. For more information, see Restore a MySQL database from a logical backup.
Self-managed database	Method 1: Restore the data of the original RDS instance to a new RDS instance, verify the data on the new RDS instance, and then migrate the data to a self-managed database. Method 2: Use DBS to create a logical backup. Then, restore data to the self-managed database by using the logical backup file. For more information, see Restore a MySQL database from a logical backup. Method 3: Download a backup file. Then, restore data to the self-managed database from the backup file. For more information, see Restore the data of an ApsaraDB RDS for MySQL instance from a physical backup file to a self-managed MySQL database, Restore the data of an ApsaraDB RDS for MySQL instance from a logical backup file to a self-managed MySQL instance, or Restore the data of an ApsaraDB RDS for MySQL instance to a self-managed MySQL instance by using snapshot backup files.

For more information about how to restore the data of an RDS instance that runs a different database engine, see the following topics:

FAQ

How do I restore one or more individual databases that are deleted?

You can use the restoration feature for individual databases and tables to restore one or more databases and tables. If your RDS instance does not support the restoration of individual databases or tables, you can restore the data of the deleted databases or tables to a new RDS instance, verify the data on the new RDS instance, and then migrate the data from the new RDS instance back to your original RDS instance.

Can I restore the data of my RDS instance to a specific point in time?

Yes, you can restore the data of your RDS instance to a specific point in time. If the log backup feature is enabled, you can restore the data of your RDS instance to a point in time within the retention period of log backup files. If the log backup feature is disabled, you can restore the data to the point in time when the data backup was created.

If no data backup files are created for my RDS instance, can I restore data to a specific point in time?

No, you cannot restore the data of your RDS instance to a specific point in time if no data backup files are available. To restore data to a specific point in time, you must find a full data backup that is completed before the specified point in time and restore the data of the data backup file that is generated from the full data backup. Then, you must restore the incremental data from the log backup file that is generated at the specified point in time.

If the backup retention period is set to seven days, can I restore data that is generated seven days ago?

No, you cannot restore the data that is generated seven days ago. After the retention period elapses, data backups are automatically deleted and cannot be restored.

If the backup retention period is set to seven days, can I use the data tracking feature of DMS to retrieve the data backups that are deleted?

No, you cannot use the data tracking feature of Data Management (DMS) to retrieve the data backups that are deleted. Data tracking allows you to use binary logs to restore data. However, the backup retention period is seven days, and you cannot track binary logs that are generated seven days ago. You can modify the backup retention period based on the descriptions in Configure automatic backup.

Why am I charged for database restoration?

You are charged for the new RDS instance that is used to restore data. You can view the price of the new RDS instance when you create the instance.

Note

If you want to temporarily use an RDS instance, you can create a pay-as-you-go or serverless RDS instance. After data is restored to the new RDS instance, you can migrate the data to the original RDS instance and then release the new RDS instance. For more information, see Migrate data between ApsaraDB RDS instances and Release or unsubscribe from an ApsaraDB RDS for MySQL instance.
You are immediately charged for the new RDS instance after the instance is created.

How long does it require to restore data to a new RDS instance?

The period of time that is required to restore data to a new RDS instance depends on the data volume and network conditions. In most cases, several minutes to several hours are required for the data restoration.

Example of the period of time required for data restoration

The following table lists the periods of time that are required to restore data to a new RDS instance that provides 2 cores and 4 GB of memory and runs RDS High-availability Edition with Premium Local SSDs.

Operation	Time required
Create an RDS instance	5 minutes
Configure an RDS instance	15 minutes
Download a backup file	200 GB-hour
Start an RDS instance	5 minutes
Download a binary log file	200 GB-hour
Apply a binary log file	Based on the specific content of the binary log file

Note

For example, if you want to restore 200 GB of data and the period of time that is required to apply the binary log files is 30 minutes, the period of time that is required to complete the restoration is approximately 3 hours. This applies if the backup data and binary logs are serially downloaded.
If you want to restore data at a faster speed, you can enable a sandbox instance. The system automatically synchronizes the data to the sandbox instance for you to perform quick restoration. For more information, see Use the emergency recovery feature.

Factors

The restoration speed varies based on a number of factors, and the restoration may fail in a few circumstances. You may also need to manually troubleshoot the errors that occur due to the executions of SQL statements. The following factors affect the restoration speed:

Volume of backup data: If the data volume is large, the restoration speed is low.
Large transactions: If binary log files contain information about large transactions, the restoration speed is low.
Hot data updates: If binary log files contain information about hot data updates, the restoration speed is low.
Foreign key constraints: Foreign key constraints increase the costs of verification and decrease the restoration speed.
Number of binary log records: If you restore data to a specific point in time, a large number of binary log records are required, which decreases the restoration speed.
Storage type: When you use cloud disks, the restoration speed is higher than the restoration speed when you use Premium Local SSDs.
Specifications: High specifications indicate a high restoration speed.
Database engine version: If the database engine version that you use supports parallel replication, the restoration speed is high. If the database engine version that you use does not support parallel replication, the restoration speed is low.

Important

The following factors may cause restoration failures:

Binary logs are not parsed as expected because the new RDS instance runs an earlier database engine version than the original RDS instance.
Table names or column names contain Chinese characters or special characters.
Binary logs in the original RDS instance are deleted.
Tables that do not have primary keys cannot be restored because the implicit_primary_key parameter is set to off in the original RDS instance.

When I create an RDS instance to which I want to restore data, why am I unable to select a vSwitch from the vSwitch of Primary Node drop-down list?

If no vSwitches are available in the zone that is specified in the Basic Configurations step, you cannot select a vSwitch from the vSwitch of Primary Node drop-down list in the Instance Configuration step. In this case, click the link to go to the VPC console. In the VPC console, create a vSwitch. Then, you can select a vSwitch from the vSwitch of Primary Node drop-down list.