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

Prerequisites

• The RDS instance is running one of the following MySQL versions and RDS editions:
  • MySQL 8.0 in the High-availability Edition (with local SSDs)
  • MySQL 5.7 in the High-availability Edition (with local SSDs)
  • MySQL 5.6 in the High-availability Edition
• Instance storage engine is not X-Engine.
• The number of tables on the RDS instance does not exceed 50,000.
• The Restore Individual Database/Table function is enabled. To enable this function, follow these steps: Log on to the ApsaraDB for RDS console, navigate to the Backup and Restoration page, click the Backup Settings tab and then the Edit button on the tab, and in the dialog box that appears select Enabled for Restore Individual Database/Table.
  Note

  • After this function is enabled, a new backup format will be used to support the restoration of individual databases and tables. This function cannot be disabled after it is enabled.
  • This function is enabled for each new RDS instance by default and cannot be disabled.
• If you want to restore individual databases and tables to the original RDS instance, the original RDS instance must meet the following requirements:
  • The original RDS instance is in the Running state and is not locked.
  • The original RDS instance does not have an ongoing migration task.
  • If you want to restore individual databases and tables to a point in time, the log backup function is enabled for the original RDS instance.
  • If you want to restore individual databases and tables from a backup set, the original RDS instance has at least one backup set.
• If you want to restore individual databases and tables to a new RDS instance, the original RDS instance must meet the following requirements:
  • The original RDS instance is in the Running state and is not locked.
  • If you want to restore individual databases and tables to a point in time, the log backup function is enabled for the original RDS instance.
  • If you want to restore individual databases and tables from a backup set, the original RDS instance has at least one backup set.

Precautions

• When you restore individual databases and tables to the original RDS instance, a primary/secondary switchover is triggered. This may incur a brief disconnection. Make sure that your application is configured to automatically reconnect to the original RDS instance.
• The Restore Individual Database/Table function triggers a format conversion of backup files from the TAR format to the xbstream format. As a result, the storage space occupied by these backup files slightly increases. Keep an eye on your backup space usage. If your backup space usage exceeds the capacity of the specified free tier, additional fees are incurred. We recommend that you specify a proper backup cycle that best meets your business needs and maximizes the usage of backup space within the free tier.
• The Restore Individual Database/Table function only restores the specified tables. You must select all of the tables that you want to restore. The restoration will fail in the following scenarios:
  • The tables that you specified are deleted during the period spanning from the point in time when the last backup set was generated to the point in time to which you want to restore data.
  • The restoration involves a table that you have not specified. For example, you specified to restore Table B, but Table B is renamed from Table A before the specified point in time. In this situation, the restoration fails because you did not specify Table A.

  If you cannot identify all of the tables involved, you can only restore data at the instance level. For more information, see Restore the data of an RDS MySQL instance.

• You can select a maximum of 50 databases or tables at a time.

Procedure

1. Log on to the ApsaraDB for RDS console.
2. Select the region where the target RDS instance resides.
3. Find the target RDS instance and click its ID.
4. In the left-side navigation pane, click Backup and Restoration.
5. In the upper-right corner of the page, click Restore Individual Database/Table.
   Note If the Restore Individual Database/Table button does not appear, see the "Prerequisites" section.
   
6. In the dialog box that appears, configure the following parameters.

   Parameter	Description
   Restore To	Current Instance: restores individual databases and tables to the original RDS instance. New Instance: restores individual databases and tables to a new RDS instance.
   Restore Method	By Backup Set By Time: If you select this option, you can specify a point in time within the specified log retention period and restore data to that point in time. For more information about viewing and modifying the log retention period, see Back up an ApsaraDB RDS for MySQL instance. Note The By Time option appears only when the log backup function is enabled.
   Backup Set	Select the backup set from which you want to restore individual databases and tables. Note This parameter appears only when you set the Restore Method parameter to By Backup Set.
   Restore Time	Select the point in time to which you want to restore individual databases and tables. Note This parameter appears only when you set the Restore Method parameter to By Time.
   Restore Mode	Logical Restoration: The restoration is slow. Physical Restoration: The restoration is fast, but incurs a primary/secondary switchover. In addition, all of the read-only instances attached with the original RDS instance will be restarted. If the original RDS instance is being maintained, the volume of data that you want to restore is small, or the data replication with the read-only instances is interrupted, the backend automatically selects Logical Restoration. Note This parameter appears only when the original RDS instance has read-only instances.
   Databases and Tables to Restore	Select the databases and tables that you want to restore.
   Selected Databases and Tables	This section displays the selected databases and tables. You can specify new names for these databases and tables. This section also displays the total size of the selected databases and tables and the remaining storage space. Make sure that the remaining storage space is sufficient before the restoration.

   
7. Click OK.
   Note If you set the Restore To parameter to New Instance, you are directed to the page where you can purchase a new RDS instance. On this page, you need to configure the following parameters and complete the payment.

   Parameter	Description
   Edition	Basic Edition: The database system consists of only one instance. Computing is separated from storage. This edition is cost-effective, but is not recommended for production environments. High-availability Edition: The database system consists of one primary instance and one secondary instance. These instances work in the classic high-availability architecture. Enterprise Edition: The database system consists of one primary instance and two secondary instances. These instances reside in three different zones of the same region to provide finance-level reliability. The Enterprise Edition is available only in four regions: China (Hangzhou), China (Shanghai), China (Shenzhen), and China (Beijing). For more information, see ApsaraDB for RDS edition overview. The editions available vary based on the database engine version that you select.
   Zone	The zone where the new RDS instance resides. Each zone is an independent physical location within a region. Zones in the same region provide the same services. You can create the new RDS instance and the ECS instance to which you want to connect in the same zone or in different zones. Note The new RDS instance must reside in the same region as the original RDS instance. You cannot change the region.
   Instance Type	Each instance type supports a specific number of CPU cores, memory capacity, maximum number of connections, and maximum input/output operations per second (IOPS). For more information, see Primary instance types.
   Capacity	The storage capacity that the new RDS instance has available to store data files, system files, binary log files, and transaction files.
   Network Type	Classic Network: a traditional type of network. VPC: This is the recommended network type. A Virtual Private Cloud (VPC) is an isolated virtual network that provides higher security and better performance than a classic network.

FAQ

• After the format of a backup file is converted from TAR to xbstream, will it become unavailable?

  No, the backup file is still available after a format conversion from TAR to xbstream.

• Why does the Restore Individual Database/Table function suddenly become unavailable?

  Check whether the number of tables on your RDS instance exceeds 50,000. If the number exceeds 50,000, the Restore Individual Database/Table function is unavailable.

Related operations