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

Feature description

Billing rules

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

1. Access RDS Instances, select a region at the top, and then click the ID of the target RDS instance.
2. In the left-side navigation pane, click Backup and Restoration. On the page that appears, click Restore Database.
   Note You can also click Restore Database (Previously Clone Database) in the Distributed by Instance Role section of the Basic Information page.
3. On the Restore Database (Previously Clone 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 instance is an instance for which you pay an upfront fee. For long-term use, the subscription billing method is more cost-effective than the pay-as-you-go billing method. You are offered lower prices for longer subscription periods. Pay-As-You-Go: A pay-as-you-go 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 need your pay-as-you-go RDS instance, you can release the RDS instance to reduce costs.
   Restore Mode	By Time: This mode allows you to restore data to a specific point in time within the log backup retention period that you specified. The point in time is accurate to seconds. For more information about how to view or change the retention period of log backup files, see Enable the automatic backup feature for an ApsaraDB RDS for MySQL instance. By Backup Set: This mode allows you to restore data from a backup set. Logical backup files are not supported. Note The By Time option is available only if the log backup feature is enabled.
   Architecture	X86: Each vCPU corresponds to the hyper-thread of a processor core. ARM: Each vCPU corresponds to the physical core of a processor. The ARM architecture delivers stable performance by using dedicated resources. For more information, see Architectures.
   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 to which the primary RDS instance belongs. The Zone of Secondary Node parameter specifies the zone to which the secondary RDS instance belongs. 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: We recommend that you select this deployment method to ensure zone-level disaster recovery. If you select this deployment method, the value of the Zone of Primary Node parameter differs from the value of the Zone of Secondary Node parameter. 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 allocated memory and I/O resources. However, it shares CPU and storage resources with the other general-purpose RDS 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 allocated CPU, memory, storage, and I/O resources. The dedicated host instance family is the highest configuration of the dedicated instance family. A dedicated host RDS instance 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.
   Capacity	The maximum amount of storage that is provisioned to store data files, system files, binary log files, and transaction files in the new RDS instance. You can change the storage capacity at a step size of 5 GB.

4. 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.

5. Click Next: Confirm Order.
6. 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 the RDS instance uses the subscription billing method.
   Note If you select the subscription billing method for the new RDS instance, we recommend that you select Auto-Renew Enabled. This way, you do not need to manually 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.
7. Optional. Log on to the new RDS instance and verify the data. For more information, see Use a database client or the CLI to connect to an ApsaraDB RDS for MySQL instance.

Correct the online data on the new RDS instance

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 of an ApsaraDB RDS for MySQL instance. 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 log backup retention period. If the log backup feature is disabled, you can restore the data to the point in time when the data backup was created.

• Can I restore the data of my RDS instance to a specific point in time if no data backup files are generated?

  No, you cannot restore the data of your RDS instance to a specific point in time if no data backup files are generated. 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 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 Enable the automatic backup feature for an ApsaraDB RDS for MySQL instance.

• Why am I charged for database restoration?
  You are charged for the new RDS instance. You can view the price of the new RDS instance when you create the instance.

  Note

  • We recommend that you create a pay-as-you-go RDS instance for the restoration. After the restoration is complete, you can release the new RDS instance. For more information about how to release an RDS instance, see 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 SSDs.

  Operation	Time required
  Create an RDS instance	5 minutes
  Configure an RDS instance	15 minutes
  Download a backup file	200 GB per hour
  Start an RDS instance	5 minutes
  Download a binary log file	200 GB per 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 file is 30 minutes, the period of time that is required for the restoration of full data is approximately 3 hours, which is the sum of the time periods 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 Create a sandbox instance for emergency disaster recovery of an ApsaraDB RDS for MySQL database.
  Affecting factors: The restoration speed varies based on various factors, and the restoration may fail in some scenarios. You may also need to troubleshoot the errors that occur when SQL statements are executed. 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.

  Note 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 you 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.
• How do I restore data to the original RDS instance, another existing RDS instance, or a self-managed database?

  Destination of the data restoration	Solution
  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 of an ApsaraDB RDS for MySQL 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. 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.