ApsaraDB RDS for MySQL allows you to restore individual databases and tables. If you unintentionally delete one or more databases or tables of an RDS instance, you can restore the databases or tables from a backup set.

Prerequisites

  • The RDS instance runs one of the following MySQL versions and RDS editions:
    • MySQL 8.0 on RDS High-availability Edition (with local SSDs)
    • MySQL 5.7 on RDS High-availability Edition (with local SSDs)
    • MySQL 5.6 on RDS High-availability Edition
  • The RDS instance does not use 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.Enable the Restore Individual Database/Table function
    Note
    • After this function is enabled, a new backup format is 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.
Note For more information about data restoration at the instance level, see Restore the data of an RDS MySQL instance.

Precautions

  • If you restore individual databases and tables to the original RDS instance, a primary/secondary switchover is triggered. This may cause a transient connection error. Make sure that your application is configured to automatically reconnect to the original RDS instance. If you restore individual databases and tables to a new RDS instance, no primary/secondary switchover is triggered.
  • The Restore Individual Database/Table function triggers a compression format change for backup files from TAR to xbstream. After the change, the storage space occupied by these backup files slightly increases. Keep an eye on your backup storage usage. If your backup storage usage exceeds the specified free quota, you are charged for your excess backup storage usage. We recommend that you specify a proper backup cycle based on your business needs to maximize the usage of free backup storage.
  • The Restore Individual Database/Table function restores only the specified tables. You must select all of the tables that you want to restore. The restoration fails in the following scenarios:
    • The specified tables 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 was 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 determine 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. In the left-side navigation pane, click Instances. In the top navigation bar, select the region where the target RDS instance resides.
    Select a region
  3. Find the target instance and click the instance 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 of this topic.
    Restore Individual Database/Table
  6. In the dialog box that appears, configure the following parameters.
    Configure the parameters that are used to restore individual databases and tables
    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: restores data to a point in time within the specified log backup retention period. For more information about how to view and modify the log backup 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 triggers a primary/secondary switchover. In addition, all of the read-only instances attached with the original RDS instance are 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.
  8. On the purchase page, configure parameters for the new RDS instance and complete the payment. This step is required only when you select New Instance for Restore To. The following table describes the parameters.
    Parameter Description
    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 The type of the new RDS instance. 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: the recommended network type. A virtual private cloud (VPC) is an isolated virtual network that provides higher security and better performance than the classic network.

FAQ

  • After the supported file compression format is changed from TAR to xbstream, will the original backup files in TAR format become unavailable?

    No, the original backup files in TAR format are still available.

  • 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

Operation Description
Restore databases or tables Restores individual databases and tables of an ApsaraDB for RDS instance to the original instance.
Clone instance Restores individual databases and tables of an ApsaraDB for RDS instance to a new instance.
Query the time range for restoration Queries the time range to which an ApsaraDB for RDS instance can be restored.