ApsaraDB RDS:Restore individual databases and tables of an ApsaraDB RDS for MySQL instance

Feature description

The fast restoration and standard restoration features for individual databases and tables are supported. The fast restoration feature for individual databases and tables uses performance-type sandbox instances to accelerate data restoration by approximately 50% to 95% compared with the standard restoration feature for individual databases and tables. For more information, see Use the emergency recovery feature of an ApsaraDB RDS for MySQL instance and Enable the fast restoration feature for individual databases and tables.

Prerequisites

Billing rules

Usage notes

• By default, the restoration feature for individual databases and tables is enabled for RDS instances that use local SSDs and cannot be disabled.
• If your RDS instance uses local SSDs, the backup data that is generated before you enable the restoration feature for individual databases and tables cannot be used to restore individual databases and tables. You can restore full data of your RDS instance. For more information, see Restore full data of an ApsaraDB RDS for MySQL instance.
• If your RDS instance uses local SSDs, you must manually back up full data of your RDS instance after you enable the restoration feature for individual databases and tables. Then, you can restore individual databases and tables from the backup data that is generated after you enable restoration feature for individual databases and tables. For more information, see Manually back up an ApsaraDB RDS for MySQL instance.
  Note After you enable the restoration feature for individual databases and tables, the backup file format is changed from TAR to xbstream.
• If you restore individual databases and tables to the original RDS instance, a primary/secondary switchover is triggered during restoration. The switchover may cause a transient connection that lasts approximately 30 seconds. Make sure that your application is configured to automatically reconnect to the RDS instance. If you restore individual databases and tables to a new RDS instance, no primary/secondary switchovers are triggered during restoration.
• If you use the fast restoration feature for individual databases and tables to restore individual databases or tables of an RDS instance, you can restore the data only to the original RDS instance rather than a new RDS instance.

Enable the fast restoration feature for individual databases and tables

To use the fast restoration feature for individual databases and tables for data restoration, you must perform the following steps to enable the feature first and restore data. Enabling the feature does not affect your workloads.

1. 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.
2. In the left-side navigation pane, click Backup and Restoration.
   Note If the dialog box that shows a notice on the feature appears, you can select Do not show again. and click Activate Free Edition Now or Enable Paid Edition to enable the fast restoration feature for individual databases and tables. If the dialog box does not appear, you can proceed with the subsequent steps.
3. On the Backup and Restoration page, click the Backup Strategy tab.
4. In the Base Backup section, click Edit. In the dialog box that appears, turn on Fast Restoration.
   Note

   • For RDS instances that use local SSDs, if Database/Table Restoration is turned off, the Restore Speed option is not displayed. You must turn on Database/Table Restoration. For more information about the restoration feature for individual databases and tables, see Usage notes.
   • For RDS instances that use local SSDs, you must configure the CDM Billing Method and CDM Retention Period parameters.
5. Click Confirm.

Procedure

1. 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.
2. In the left-side navigation pane, click Backup and Restoration. On the page that appears, click Restore Individual Database/Table.
   Note If the Restore Individual Database/Table button is not displayed in the ApsaraDB RDS console, check whether the requirements that are described in the Prerequisites section are met.
3. Configure the following parameters.

   Parameter	Description
   Restore To	Current Instance New Instance: If you select this option, the system automatically creates an RDS instance based on Step 5. The data in databases and tables of the original RDS instance are restored to the new RDS instance. After you verify the data in the new RDS instance, you can migrate the data back to the original RDS instance. For more information, see Migrate data between ApsaraDB RDS for MySQL instances. Note The New Instance option is available only for RDS instances that use local disks.
   Restore Speed	Standard Fast: The fast restoration feature for individual databases and tables uses performance-type sandbox instances to accelerate data restoration by approximately 50% to 95% compared with the standard individual database and table restoration feature. For more information, see Use the emergency recovery feature of an ApsaraDB RDS for MySQL instance. Note If your RDS instance uses cloud disks, you can set the Restore Speed parameter only to Fast. The fast restoration feature for individual databases and tables is now in public preview. You can use the feature free of charge. If you use the fast restoration feature for individual databases and tables to restore individual databases or tables of an RDS instance, you can restore the data only to the original RDS instance rather than a new RDS instance.
   Restore Method	By Backup Set By Time: If you select this option, the system restores full backup data and then incremental log backup data. You can select any point in time within the log backup retention period. For more information about how to view or change the log backup retention period, see Enable the automatic backup feature for an ApsaraDB RDS for MySQL instance. For example, if both the data backup retention period and the log backup retention period of an RDS instance are seven days, you can restore data that is generated within seven days. Backup sets that are generated seven days ago are not retained and cannot be restored. Note The By Time option is available only when the log backup feature is enabled. For more information about how to enable the log backup feature, see Log backup settings.

4. Select the databases and tables that you want to restore. You can also specify the name of each restored database or table. Then, click OK.
   Note

   • You can select up to 50 databases or tables at a time.
   • When you restore databases and tables to the original RDS instance, the names of the restored databases can be the same as the names of the original databases, but the names of the restored tables cannot be the same as the names of the original tables. By default, the system adds _backup to the name of a restored database. You can rename the restored database or table.
   • If you restore databases and tables to the original RDS instance, you must make sure that the available storage of the RDS instance is sufficient to store the data of the databases and tables.
5. Select a billing method and configure the parameters related to the new RDS instance. This step is required only when you select New instance and Standard.
   • 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.
   • Pay-as-you-go: You are charged an hourly fee for a pay-as-you-go instance based on your actual resource usage. The pay-as-you-go billing method is suitable for short-term use. If you no longer need a pay-as-you-go instance, you can release the instance to reduce costs.

   Parameter	Description
   Zone	The zone in which the new RDS instance resides. Each zone is an independent physical location within a region. Zones in the same region do not have substantial differences. The new RDS instance can reside in the same zone as the Elastic Compute Service (ECS) instance that you want to connect or in a different zone. Note The new RDS instance must reside in the same region as the original RDS instance. You cannot change the region.
   Instance Type	The instance type of the new RDS instance. Each instance type supports a different number of cores, memory capacity, maximum number of connections, and maximum IOPS. For more information, see Primary ApsaraDB RDS instance types.
   Capacity	The amount of storage that is provisioned to store data files, system files, log files, and transaction files in the new RDS instance.
   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.

   Note You can also use one of the following methods to restore individual databases and tables:

   • Use Database Backup (DBS) to create a logical backup. Then, restore data from the generated logical backup file. For more information, see Back up an ApsaraDB RDS for MySQL or self-managed MySQL instance by using logical backup and Restore a MySQL database from a logical backup.
   • Manually create a logical backup. Then, restore data from the generated logical backup file to a self-managed database. For more information, see Manually back up an ApsaraDB RDS for MySQL instance and Restore the data of an ApsaraDB RDS for MySQL instance from a logical backup file to a self-managed MySQL instance.
   • Use the mysqldump extension to back up and restore specific databases and tables. For more information, see Use mysqldump to back up and restore an ApsaraDB RDS for MySQL instance.

Correct the online data

FAQ

• What do I do if the "The operation failed. The RDS instance is not in a ready state." error message is displayed when I restore a database or table of my RDS instance in the ApsaraDB RDS console?

  Tasks in the ApsaraDB RDS console are executed in sequence. If an ongoing restoration task or incomplete task exists, this error message is displayed when you execute another restoration task at the same time. In this case, wait until the ongoing restoration task or incomplete task is complete and then execute another restoration task.

• What do I do if the restored table is empty?

  Make sure that the table contains data at the point in time that you selected for the restoration. If the table does not contain data at the point in time that you selected for the restoration, you must select a table that contains data at the selected point in time.

• How do I restore data that is accidentally deleted from an RDS instance?

  You can use the data tracking feature of Data Management (DMS) to identify data changes in a specific period of time and generate SQL statements for rolling back the data changes. Then, you can submit a data change ticket to change data in the RDS instance by executing the generated SQL statements. For more information, see Use the data tracking feature of DMS to restore the data of an ApsaraDB RDS for MySQL instance and Change regular data.

• Do I have other methods to restore individual databases and tables of an RDS instance that uses standard SSDs or ESSDs?
  Yes, you can use Database Backup (DBS) to back up and restore databases on your RDS instance or self-managed databases on an ECS instance. You can also use DBS to download backup files to your on-premises computer. For more information, see Overview.

  Note

  • For more information about how to restore individual databases and tables of an RDS instance that uses standard SSDs or ESSDs, see Restore data by database or table.
  • For more information about how to restore individual databases and tables of an RDS instance, see Use mysqldump to back up and restore an ApsaraDB RDS for MySQL instance.

Related operations