This topic describes how to restore the individual databases and tables that are accidentally deleted from an ApsaraDB RDS for MySQL instance. You can restore these databases and tables from backup files.

Prerequisites

  • Your 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
  • Your RDS instance does not run the X-Engine storage engine.
  • The number of tables that are created on your RDS instance does not exceed 50,000. If the number exceeds 50,000, the Restore Individual Database/Table feature is unavailable.
  • The Restore Individual Database/Table feature is enabled. You can perform the following steps to enable this feature: Log on to the ApsaraDB RDS console, go to the Backup and Restoration page for your RDS instance, click the Backup Settings tab, click the Edit button on that tab, and then set the Restore Individual Database/Table parameter to Enabled in the dialog box that appears.
    Note
    • By default, the Restore Individual Database/Table feature is enabled for each new RDS instance. You cannot disable this feature.
    • The data that is generated before the Restore Individual Database/Table feature is enabled cannot be restored by using this feature. We recommend that you use the point-in-time recovery (PITR) feature to restore such data. For more information, see Restore the data of an ApsaraDB RDS for MySQL instance.
    • After the Restore Individual Database/Table feature is enabled, the backup file format that is supported by your RDS instance is changed from TAR to xbstream.
    Enable the Restore Individual Database/Table feature in the ApsaraDB RDS console
  • If you want to restore individual databases and tables of your RDS instance to the same instance, your RDS instance meets the following requirements:
    • Your RDS instance is in the Running state and is not locked.
    • Your RDS instance does not have ongoing migration tasks.
    • If you want to restore individual databases and tables to a point in time, the log backup feature is enabled.
    • If you want to restore individual databases and tables from a backup set, at least one backup set is available.
  • If you want to restore individual databases and tables of your RDS instance to a new instance, your RDS instance meets the following requirements:
    • Your 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 feature is enabled.
    • If you want to restore individual databases and tables from a backup set, at least one backup set is available.
Note For more information about instance-level data restoration, see Restore the data of an ApsaraDB RDS for MySQL instance.

Precautions

  • If you restore individual databases and tables of your RDS instance to the same instance, a primary/secondary switchover is triggered during the restoration. The switchover may cause a transient connection error of about 30 seconds. Make sure that your application is configured to automatically reconnect to your RDS instance. If you restore individual databases and tables of your RDS instance to a new instance, no primary/secondary switchovers are triggered during the restoration.
  • The Restore Individual Database/Table feature triggers a backup file format change from TAR to xbstream. This change causes a small increase in your backup storage usage. We recommend that you monitor your backup storage usage. If your backup storage usage exceeds the specified free quota, you are charged extra fees. We recommend that you specify a proper backup cycle based on your business requirements to maximize the usage of the free backup storage. For more information, see View the free quota for backup storage of an ApsaraDB RDS for MySQL instance.
  • The Restore Individual Database/Table feature restores only the selected tables. You must select all the tables that you want to restore. The restoration fails in the following cases:
    • The selected tables are deleted during the specified period. The specified period spans from the point in time when the last backup set is generated to the point in time to which you want to restore the selected tables.
    • The restoration includes a table that you have not selected. For example, you selected Table B, but Table B was renamed from Table A before the specified point in time arrives. In this case, the restoration fails because you did not select Table A.

    If you cannot identify all the tables that you need to restore, you can restore all the data of your RDS instance. For more information, see Restore the data of an ApsaraDB RDS for MySQL instance.

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

Procedure

  1. Go to the Backup and Restoration page.
    1. Log on to the ApsaraDB for RDS console. In the left-side navigation pane, click Instances. In the top navigation bar, select the region where your RDS instance resides.
      选择地域
    2. Find your RDS instance and click its ID. In the left-side navigation pane, click Backup and Restoration.
  2. In the upper part of the page that appears, click Restore Individual Database/Table.
    Note If the Restore Individual Database/Table button does not appear, you can check whether all the requirements that are specified in the "Prerequisites" section of this topic are met.
    Restore Individual Database/Table button
  3. Configure the following parameters.
    Restore Individual Database/Table dialog box - 1
    Parameter Description
    Restore To
    • Current Instance: restores individual databases and tables to the same RDS instance.
    • New Instance: restores individual databases and tables to a new RDS instance.
    Restore Method
    • By Backup Set
    • By Time: restores individual databases and tables to a point in time within the specified log backup retention period. For more information about how to view and change 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 feature 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.
    Restorable 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, ApsaraDB RDS restarts all the read-only RDS instances that are attached to your RDS instance. If your RDS instance is being maintained, the amount of data that you want to restore is small, or the data replication to the read-only RDS instances is interrupted, the backend automatically selects the Logical Restoration option.
    Note This parameter appears only when read-only RDS instances are attached to your RDS instance.
    Databases and Tables to Restore Select the databases and tables that you want to restore.
    Selected Databases and Tables
    • This section shows the selected databases and tables. You can specify new names that you want to use for these databases and tables on the destination RDS instance.
    • This section shows the total storage space that is occupied by the selected databases and tables. This section also shows the remaining storage space of your RDS instance. Make sure that the remaining storage space of your RDS instance is sufficient.
    Note If you restore the selected databases and tables to the original RDS instance, these databases and tables must have new names that are different from the original names. By default, ApsaraDB RDS adds _backup to the original names. After the restoration is complete, you can change the new names.
  4. Click OK.
  5. On the ApsaraDB RDS buy page, configure parameters for a new RDS instance and complete the payment. This step is required only when you set the Restore To parameter to New Instance.
    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.

    The new RDS instance can reside in the same zone or in different zones than the Elastic Compute Service (ECS) instance that you want to connect.
    Note The new RDS instance must reside in the same region as the original RDS instance. You cannot change the region.
    CPU and Memory The instance 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: 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.

FAQ

  • After the backup file format is changed from TAR to xbstream, are the existing backup files in TAR format unavailable?

    No, after the backup file format is changed from TAR to xbstream, the existing backup files in TAR format are still available.

  • Why does the Restore Individual Database/Table feature suddenly become unavailable for my RDS instance?

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

Related operations

Operation Description
Restore individual databases or tables Restores individual databases and tables of an ApsaraDB RDS instance to the same instance.
Clone instance Restores individual databases and tables of an ApsaraDB RDS instance to a new instance.
Query the time range for restoration Queries the restorable time range that is supported by a backup file of an ApsaraDB RDS instance.