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 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 Methods of backing up and restoring an ApsaraDB RDS for MySQL instance.
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 Use the automatic backup feature.

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 after the first full backup is performed. 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 about the period of time that is required for data restoration, see FAQ.

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. The subscription billing method is more cost-effective than the pay-as-you-go billing method 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. We recommend that you select the pay-as-you-go billing method for short-term use. If you no longer require an pay-as-you-go RDS instance, you can release it to reduce costs. Serverless: A serverless RDS instance is an instance for which you are charged based on the actual amount of used resources. This billing method is dynamic. Serverless is suitable for scenarios in which intermittent scheduled tasks are involved and workloads are fluctuating or unpredictable. For more information, see Pricing of serverless RDS instances.
Restoration Mode	By Backup Set: This method allows you to restore data from a backup set. Logical backup files are not supported. By Point in Time: This method allows you to restore data to a point in time after the first full backup that is performed within the log backup retention period. For more information about how to view or change the retention period of log backup files, see Use the automatic backup feature. Note The By Point in Time 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 select an enhanced SSD (ESSD) for the Storage Type parameter, the Standard and Economy product types are available. For more information, see Product types. If you select Local SSD for the Storage Type parameter, only the Standard product type is available. If you set the Edition parameter to Cluster Edition, the Standard and Economy product types are available.
Zone of Primary Node and Zone of Secondary Node	A zone is an independent physical location within a region. The Zone of Primary Node parameter specifies the zone in which the primary RDS instance resides. The Zone of Secondary Node parameter specifies the zone in which the secondary RDS instance resides. You can select the Single-zone Deployment or Multi-zone Development method. Single-zone Deployment: If you select this deployment method, the values of the Zone of Primary Node and Zone of Secondary Node parameters are the same. Multi-zone Development: If you select this deployment method, the values of the Zone of Primary Node and Zone of Secondary Node parameters are different. We recommend that you select this deployment method to perform zone-disaster recovery. You must manually configure the Zone of Primary Node and Zone of Secondary Node parameters. 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 consists of only one primary RDS instance and supports only the single-zone deployment method.
Instance Type	General-purpose (Entry-level): A general-purpose RDS instance exclusively occupies the memory and I/O resources that are allocated. However, this type of instance shares CPU and storage resources with other general-purpose instances that are deployed on the same host. Dedicated (Enterprise-level): You can select a dedicated instance type or a dedicated host instance type. 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. Dedicated Cluster: A dedicated cluster exclusively occupies all the resources on a VM or physical host. The permissions to manage the hosts in a dedicated cluster can be granted to you. This allows you to create multiple database instances on a host. For more information, see Create a host. 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 data migration 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.

What to do next

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.

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. For more information, see Migrate data between ApsaraDB RDS for MySQL instances. Method 2: Use the restoration feature for databases and tables to restore full data to the original RDS instance. For more information, see Restore individual databases and tables. 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. For more information, see Migrate data between ApsaraDB RDS for MySQL instances. 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. For more information, see Migrate data between ApsaraDB RDS for MySQL instances. 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 Download the backup files of an ApsaraDB RDS for MySQL instance and 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 a CSV file or an SQL file.

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. For more information, see Restore individual 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? Can I use the data tracking feature of DMS to retrieve the data backups that are deleted?

No, you cannot restore the data that is generated seven days ago. Data backups are retained based on the retention period that is specified. After the retention period elapses, data backups are automatically deleted and cannot be restored. 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 data 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 Use the automatic backup feature.

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?

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 local disks.

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 of full data is approximately 3 hours, which is the sum of the periods of time that are listed in the preceding table.
If you want to restore data at a faster speed, you can enable a sandbox instance. The system automatically synchronizes the data that you want to restore to the sandbox instance for you to perform quick restoration. For more information, see Use the emergency recovery feature.

Affecting 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 full data: If the data volume is large, the restoration speed is low.
Volume of incremental 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.
Partitioned tables: If partitioned tables exist, data restoration is not supported. This rule takes effect if you use MySQL 8.0.
Storage type: When you use standard SSDs or enhanced SSDs (ESSDs), the restoration speed is higher than the restoration speed when you use 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.