All Products
Search

This Product

    ApsaraDB RDS:Restore individual databases and tables

    Document Center

    ApsaraDB RDS:Restore individual databases and tables

    Last Updated:Nov 08, 2024

    ApsaraDB RDS for MySQL supports the standard restoration feature and the fast restoration feature for individual databases and tables. You can restore individual databases and tables of your ApsaraDB RDS for MySQL instance to the original RDS instance or a new RDS instance by backup set or point in time. The features can be used in scenarios in which misoperations are performed and data needs to be quickly restored or in which historical data needs to be analyzed.

    Comparison of the modes of restoration for individual databases and tables

    ApsaraDB RDS for MySQL provides the standard restoration feature and the fast restoration feature for individual databases and tables. The fast restoration feature for individual databases and tables is implemented by using performance-type sandbox instances. If the volume of data remains unchanged, the fast restoration feature for individual databases and tables accelerates 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 for an ApsaraDB RDS for MySQL instance and Restore individual databases and tables of an ApsaraDB RDS for MySQL instance.

    image

    Item

    Standard restoration feature for individual databases and tables

    Fast restoration feature for individual databases and tables

    Restoration range

    Specific databases and tables of an RDS instance.

    Specific databases and tables of an RDS instance.

    Restoration destination

    • If your RDS instance uses local disks, you can restore data to the original RDS instance or a new RDS instance.

    • If your RDS instance uses cloud disks, you can restore data only to the original RDS instance.

    • If your RDS instance uses local disks, you can restore data only to the original RDS instance.

    • If your RDS instance uses cloud disks, the fast restoration feature for individual databases and tables is not supported.

    Restoration speed

    The restoration speed varies based on the data volume. Data restoration is relatively slow.

    The restoration speed varies based on the data volume. Data restoration is accelerated by approximately 50% to 95% compared with the standard restoration feature for individual databases and tables.

    Restoration method

    You can restore data from backup sets or to a specific point in time.

    • Free edition: You can restore data to the point in time at which the last full data backup is performed.

    • Paid edition: You can restore data from backup sets or to a specific point in time.

    Note

    • You can restore data to any point in time within the log backup retention period only after the log backup feature is enabled for your RDS instance. For more information, see Use the automatic backup feature. If the log backup feature is disabled, you can restore data only to the point in time at which the data is backed up.

    • The restoration range varies based on data backup retention period, log backup retention period, and the point in time at which you enable the restoration feature for individual databases and tables of your RDS instance. The maximum retention period is 730 days for both data backup files and log backup files. For more information, see Retain the backup files for a long period of time. You can call the DescribeLocalAvailableRecoveryTime operation to query the time range within which an RDS instance can be restored.

    • For more information about data restoration methods, see Overview of data restoration methods.

    Prerequisites

    Standard restoration feature for individual databases and tables

    Local disk

    • The RDS instance meets the following requirements:

      • The major engine version is MySQL 8.0, MySQL 5.7, or MySQL 5.6.

      • The RDS instance runs RDS High-availability Edition or runs MySQL 5.7 on RDS Enterprise Edition.

      • Your RDS instance uses local disks.

      Note

      You can go to the Basic Information page of your RDS instance to obtain the preceding information.

    • The number of tables that are created in the RDS instance is less than 50,000.

    • The AliyunServiceRoleForDBS service-linked role is created by using your Alibaba Cloud account if you use the backup feature of ApsaraDB RDS for the first time. For more information, see How do I create a service-linked role for DBS?

    • The restoration feature for individual databases and tables is enabled for the RDS instance.

      You can go to the Backup and Restoration page and check whether the feature is enabled on the Backup Strategy tab. If the feature is disabled, turn on Database/Table Restoration.

      Important

      • After you enable the restoration feature for individual databases and tables, the backup file format is changed from TAR to xbstream.

      • By default, the restoration feature for individual databases and tables is enabled for new RDS instances and cannot be disabled.

      • The backup sets that are generated before you enable the restoration feature for individual databases and tables cannot be used to restore individual databases and tables. After you enable the restoration feature for individual databases and tables, you must manually back up full data of your RDS instance. Then, you can use the backup sets that are generated after you enable the feature to restore individual databases and tables. For more information, see Perform a manual backup.

        You can restore full data of an RDS instance based on your business requirements. For more information, see Restore full data.

    Cloud disk

    • The RDS instance meets the following requirements:

      • The RDS instance runs MySQL 5.7 or MySQL 8.0.

      • The RDS instance runs RDS Basic Edition, High-availability Edition or Cluster Edition.

      • The RDS instance uses the standard SSD, Enterprise SSD (ESSD), or general ESSD storage type.

      Note

      You can go to the Basic Information page of your RDS instance to obtain the preceding information.

    • The AliyunServiceRoleForDBS service-linked role is created by using your Alibaba Cloud account if you use the backup feature of ApsaraDB RDS for the first time. For more information, see How do I create a service-linked role for DBS?

    • The restoration feature for individual databases and tables is enabled for the RDS instance.

      • The restoration feature for individual databases and tables is automatically enabled for RDS instances that are created on and after December 01, 2023 and cannot be disabled.

      • The restoration feature for individual databases and tables is forcefully disabled for RDS instances that are created before December 01, 2023. In this case, you can go to the Backup Strategy tab of the Backup and Restoration page of your RDS instance to enable the feature again. After the feature is enabled, you can use all valid backup sets to immediately restore individual databases and tables. After the feature is enabled, you cannot disable the feature.

        Important

        If the existing backup sets are invalid and cannot be used to restore individual databases and tables, you can manually back up full data of your RDS instance after you enable the restoration feature for individual databases and tables for the RDS instance. Then, you can use the backup sets that are generated after you enable the feature to restore individual databases and tables. For more information, see Perform a manual backup.

    Fast restoration for individual databases and tables

    • The RDS instance meets the following requirements:

      • The major engine version is MySQL 8.0, MySQL 5.7, or MySQL 5.6.

      • The RDS instance runs RDS High-availability Edition.

      • The RDS instance uses local disks. The fast restoration feature for individual databases and tables is not supported for RDS instances that use cloud disks.

      • The RDS instance resides in one of the following regions: China (Hangzhou), China (Shanghai), China (Beijing), China (Zhangjiakou), China (Hohhot), and China (Shenzhen).

      Note

      You can go to the Basic Information page of the RDS instance to obtain the preceding information.

    • The RDS instance runs the InnoDB storage engine.

    • The AliyunServiceRoleForDBS service-linked role is created by using your Alibaba Cloud account if you use the backup feature of ApsaraDB RDS for the first time. For more information, see How do I create a service-linked role for DBS?

    • The capacity-type sandbox feature is disabled for the RDS instance. If the feature is enabled, you must disable the feature. For more information, see Use the emergency recovery feature.

      Note

      If you enable the capacity-type sandbox feature and fast restoration for individual databases and tables at the same time, the sandbox is upgraded to a performance-type sandbox, which incurs more fees for emergency recovery.

    • The fast restoration feature for individual databases and tables is enabled for the RDS instance.

      You can go to the Backup and Restoration page and check whether the feature is enabled on the Backup Strategy tab. If the feature is disabled, turn on Fast Database/Table Restoration. For more information, see Enable the fast restoration feature for individual databases and tables.

      Important

      The backup sets that are generated before you enable the fast restoration feature for individual databases and tables cannot be used to restore individual databases and tables. After you enable the fast restoration feature for individual databases and tables, you must manually back up full data of your RDS instance. Then, you can use the backup sets that are generated after you enable the feature to restore individual databases and tables. For more information, see Manually back up an ApsaraDB RDS for MySQL instance.

      You can restore full data of an RDS instance based on your business requirements. For more information, see Restore full data.

    Usage notes

    • By default, the standard restoration feature for individual databases and tables is enabled for new RDS instances and cannot be disabled. For more information about the usage notes of the standard restoration feature and the fast restoration feature for individual databases and tables, see the Prerequisites section in this topic.

    • If you want to restore data of your RDS instance to a point in time, you must make sure that the log backup feature is enabled for the RDS instance. For more information, see Use the automatic backup feature.

    • If you restore data of your RDS instance that uses local disks to the same RDS instance, a primary/secondary switchover is triggered during the restoration. The switchover may cause a service interruption that lasts approximately 30 to 120 seconds. Make sure that your application is configured to automatically reconnect to the RDS instance. If you restore the data of the RDS instance to a new RDS instance, no primary/secondary switchovers are triggered during the restoration.

      Important

      If you use the restoration feature for individual databases and tables to restore data of your RDS instance that uses cloud disks to the same RDS instance, no primary/secondary switchovers are triggered during the restoration. However, in some cases, the performance of the RDS instance may decrease and an instance switchover may occur. For more information about the impacts of an instance switchover, see Impacts of an instance switchover.

    • You cannot suspend or cancel a restoration task after it is started.

    • A full backup is generated after the databases and tables of an RDS instance that uses cloud disks are restored.

    Limits

    • If you use the fast restoration feature for individual databases and tables to restore individual databases or tables of an RDS instance that uses local disks, you can restore the data only to the original RDS instance rather than a new RDS instance.

    • The fast restoration of individual databases and tables cannot be performed on instances for which the log backup feature is disabled.

    • The fast restoration feature for individual databases and tables is not supported by instances that contain read-only nodes.

    • The fast restoration feature for individual databases and tables is not supported by instances for which the Transparent Date Encryption (TDE) feature is enabled.

    • If binary logs involved in the restoration record the operation to rename a table or a generated column exists in the table, the restoration feature for individual databases and tables is not supported for the table.

    • If the restoration involves a large number of databases and tables or a large volume of data or a large number of binary logs are generated within the specified period of time, the restoration is prolonged.

    Billing rules

    Standard restoration feature for individual databases and tables

    Local disk

    By default, the standard restoration feature for individual databases and tables is enabled and is provided free of charge. If you use the standard restoration feature to restore individual databases or tables to a new RDS instance, you are charged for the new RDS instance. You can view the price details when you create the RDS instance.

    Note

    • If you want to temporarily use an RDS instance, you can create a pay-as-you-go RDS instance. After data is restored to the new RDS instance, you can migrate the data to the original RDS instance and then release the new RDS instance. For more information, see Migrate data between ApsaraDB RDS instances and Release or unsubscribe from an ApsaraDB RDS for MySQL instance.

    • You are immediately charged for the new RDS instance after the instance is created.

    • The serverless billing method is not supported for the RDS instances that use local disks.

    Cloud disk

    Starting December 1, 2023, if you enable the restoration feature for individual databases and tables for an RDS instance that uses cloud disks, the backup architecture is upgraded, and the amount of backup data is increased by 30% to 80%. If the total amount of backup data does not exceed the free quota, no fees are generated. If the total amount exceeds the free quota, you are charged for the excess backup storage that you use. For more information, see View and manage the size of backup files. For more information about billing rules, see Backup storage fees.

    Fast restoration for individual databases and tables

    The fast restoration feature for individual databases and tables is not supported for RDS instances that use cloud disks. The feature is supported for RDS instances that use local disks and supports free and paid editions.

    • Free edition: If you enable and use the fast restoration feature for individual databases and tables of this edition, no fees are generated.

    • Paid edition: If you enable and use the fast restoration feature for individual databases and tables of this edition, you are charged based on the actual data volume by using the pay-as-you-go billing method. Unit price: USD 0.008 per GB-day

    Note

    • Free edition: You can restore data to the point in time at which the last full data backup is performed.

    • Paid edition: You can restore data from backup sets or to a specific point in time.

    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 and then restore databases and tables. The feature is supported only for RDS instances that use local disks and meet the requirements in the "Prerequisites" section of this topic. For more information, see Procedure. 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 of the page that appears, click Backup and Restoration.

      Note

      • If your RDS instance uses local disks, the free and paid editions are provided for the fast restoration feature for individual databases and tables. The free edition allows you to restore data only to the point in time at which the last full backup is performed. The paid edition allows you to restore data to any point in time. For more information about billing, see Billing rules.

      • If the [Notice] Fast Restoration Becomes Commercially Available and Billed dialog box appears, you can select Do not show again. and then enable the free or paid edition based on your business requirements. 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 Basic Backup section, click Edit. In the dialog box that appears, turn on Fast Database/Table Restoration.

      Note

      • For RDS instances that use local disks, if Database/Table Restoration is turned off, the Restore Speed option is not displayed. You must turn on Database/Table Restoration. For more information, see Usage notes.

      • If your RDS instance uses local disks, you must configure the CDM Billing Method and CDM Retention Period parameters.

        • Free edition: Set the CDM Billing Method parameter to Previous Full Backup Point in Time (Free Edition). The restored data is retained for one day.

        • Paid edition: Set the CDM Billing Method parameter to Any Point in Time (Paid Edition). You can set the retention period of restored data to 1 to 730 days.

    5. Click OK.

    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: Follow Step 5 to automatically create an RDS instance. The databases and tables are restored to the new RDS instance. The restoration does not affect the original 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

      Note

      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: If you select this option, you can restore the data from a data backup file.

      • 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 information about how to view or change the log backup retention period, see Use the automatic backup feature.

        For example, if the both 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 the last 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 after the log backup feature is enabled. For more information about how to enable the feature, see Use the automatic backup feature.

    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, data in the original RDS instance is not affected and the names of the restored databases can be the same as the names of the original databases. However, 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 the 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 from these 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 of Primary Node or Zone of Secondary Node

      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.

      CPU and Memory

      The instance type of the new RDS instance. Each instance type supports a different number of CPU cores, memory capacity, maximum number of connections, and maximum IOPS. For more information, see Primary ApsaraDB RDS instance types.

      Storage 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 parameter

      • 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 go to the Tasks page to view the creation progress of the RDS instance. You can also use one of the following methods to restore individual databases and tables:

    6. Optional. Connect to the RDS instance and view the data that is restored to the original RDS instance or the new RDS instance. For more information, see Connect to an ApsaraDB RDS for MySQL instance.

      Note

      Make sure that the account that you use to connect to the RDS instance has the permissions to view or manage databases on the RDS instance. We recommend that you use a privileged account to connect to the RDS instance. For more information, see Create an account.

    What to do next

    Correct the online data

    Restoration destination

    Correction method

    Current instance

    Execute the RENAME TABLE statement to rename the restored table.

    New instance

    After you restore the data of the original RDS instance to the new RDS instance, you can use Data Transmission Service (DTS) to migrate some or all data of the required databases and tables to the original RDS instance to correct the online data of the original RDS instance. For more information, see Migrate data between ApsaraDB RDS for MySQL instances.

    Note

    When you create a task, use the new RDS instance as the source database and the original RDS instance as the destination database, and set the Access Method parameter for both the source and destination databases to Alibaba Cloud Instance.

    Disable the fast restoration feature for individual databases and tables

    If you do not require the fast restoration feature for individual databases and tables, you can disable the feature. Disabling the feature does not affect the use of the standard restoration feature for individual databases and tables.

    To disable the fast restoration feature for individual databases and tables, you can perform the following steps: Go to the Backup and Restoration page of your RDS instance and access the Backup Strategy tab. In the Basic Backup section, click Edit. In the dialog box that appears, turn off Fast Database/Table Restoration.

    image.png

    Change the edition of the fast restoration feature for individual databases and tables

    You can change the edition of the fast restoration feature for individual databases and tables from the paid edition to the free edition based on your business requirements. This reduces costs. For more information, see Billing rules. After you change the edition of the feature to the free edition, you can restore data from backup sets or to the point in time at which the last full data backup is performed. You can no longer restore data to a specific point.

    To change the edition of the feature to the free edition, you can perform the following steps: Go to the Backup and Restoration page of your RDS instance and access the Backup Strategy tab. In the Basic Backup section, click Edit. In the dialog box that appears, set CDM Billing Method to Previous Full Backup Point in Time (Free Edition).

    image.png

    Related operations

    Operation

    Description

    RestoreTable

    Restores the specified individual databases and tables of an instance to the original instance.

    CloneDBInstance

    Restores the specified individual databases and tables of an instance to a new instance.

    DescribeLocalAvailableRecoveryTime

    Queries the time range within which you can restore data by using a backup file of an instance.

    FAQ

    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. What do I do?

    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 I cannot select the databases or tables that I want to restore and the Meta information is too large. (Error code: InvalidMeta.TooLarge) error message is displayed?

    This error occurs because the number of tables in databases of your RDS instance exceeds 50,000. In this case, we recommend that you restore full data of your RDS instance to a new RDS instance. For more information, see Restore full data.

    Note

    When I restore databases and tables of my RDS instance to a point in time, no restoration time is displayed, and the error message indicating that the specified restoration time is invalid and the InvalidRestoreTime.Format error code are displayed. What do I do?

    When you restore databases and tables of your RDS instance to a point in time, you must make sure that the log backup feature is enabled for the RDS instance. You can go to the Backup and Restoration page of the RDS instance and view or modify the status of Log Backup on the Backup Strategy tab. For more information, see Log backup settings.

    The error message indicating that the specified restoration time is invalid and the InvalidMeta.Empty error code are displayed when I restore databases and tables of my RDS instance from backup sets or to a point in time. What do I do?

    The backup sets that are generated before you enable the restoration feature for individual databases and tables for your RDS instance that uses local disks cannot be used to restore individual databases and tables. After you enable the restoration feature for individual databases and tables, you must manually back up full data of your RDS instance. Then, you can use the backup sets that are generated after you enable the feature to restore individual databases and tables. For more information, see Manually back up an ApsaraDB RDS for MySQL instance.

    Note

    You can restore full data of an RDS instance based on your business requirements. For more information, see Restore full data.

    What do I do if the restored table is empty?

    Check whether the original table contains data at the point in time that you selected for the restoration. If the original table does not contain data at the point in time that you selected for the restoration, you must select a point in time at which the table contains data and then restore the table.

    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 and Change regular data.

    After I log on to the ApsaraDB RDS console as a RAM user, I cannot find the Restore Individual Database/Table button on the Backup and Restoration page of My RDS instance. What do I do?

    If you cannot find the Restore Individual Database/Table button on the Backup and Restoration page of your RDS instance, check whether the AliyunDBSFullAccess policy is attached to the RAM user. For more information about how to grant permissions to a RAM user, see Grant permissions to a RAM user.

    How do I determine whether to use the restoration feature for individual databases and tables or the emergency recovery feature?

    • If the data of your RDS instance is accidentally deleted or modified, or the data is lost or damaged due to other unexpected events, you can use the restoration feature for individual databases and tables to restore specific databases and tables of the RDS instance to a specified point in time. This feature is suitable for scenarios in which you want to restore specific databases and tables. For example, if some data of your RDS instance is lost due to misoperations, you can use this feature to restore only the affected tables.

    • If the data of a sandbox instance is lost or damaged due to misoperations or accidents, you can use the emergency recovery feature to restore the instance to a specified point in time. This feature is suitable for the comparison of history data and the modification of dirty data. You can also use DTS to import the data of a sandbox instance to the original instance to restore lost or damaged data.

    Note

    If you enable the fast restoration feature for individual databases and tables and the emergency recovery feature at the same time, CDM combines the data stored for the two features to reduce storage costs. For more information about data restoration methods, see Overview of data restoration methods.

    Do I have other methods to restore individual databases and tables of an RDS instance that uses cloud disks?

    Yes, you can use DBS to back up and restore databases on your RDS instance or self-managed databases on ECS instances. 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 databases and tables, see Restore data by database or table or How do I back up and restore the databases of an ApsaraDB RDS for MySQL instance?