ApsaraDB RDS:Restore databases and tables

Differences between restoration modes

You can restore individual databases and tables in standard and fast modes. The fast mode uses a performance-type sandbox instance and is 50% to 95% faster than the standard mode for the same amount of data. To use fast restoration for individual databases and tables, you must first enable the feature, and then restore data to the original instance or a new instance using a backup set generated after the feature was enabled.

For more information, see Overview of data restoration solutions (comparison of restoration speeds).

Prerequisites

Notes

• Enabled by default:

  • Standard database and table restoration is enabled by default for new instances and cannot be disabled.

  • You cannot suspend or cancel a restoration task once it starts.

• Log backup requirements:

  To restore data to a point in time, you must ensure that log backup is enabled for the instance; otherwise, fast database and table restoration will fail.

• Primary/secondary switchover and service interruption:

  • For instances using a high-performance local disk:

    • Restoring data to the original instance triggers a primary/secondary switchover, which may cause a 30- to 120-second service interruption. Ensure that your application has an automatic reconnection mechanism.

    • Restoring data to a new instance does not trigger a primary/secondary switchover.

  • For instances using a cloud disk:

    • Restoring data to the original instance does not trigger a primary/secondary switchover. However, this operation may affect the original instance's performance or even trigger an instance switchover.

    • A full backup is created after the restoration completes.

Limitations

• Limitations for database and table restoration (standard and fast):

  • You can restore data only from databases and tables that use the InnoDB storage engine. Tables using the X-Engine storage engine are not supported.

  • You cannot use this feature to restore partitioned tables, views, stored procedures, or other database objects. To restore these objects, use the full data restoration feature instead.

  • You cannot restore a table if the binary log contains a rename operation on it, or if the table includes a generated column.

  • The database and table restoration task fails if the binary log contains any XA transactions.

  • Constraint restoration: Database and table restoration restores primary keys, unique keys, and indexes but does not restore foreign key constraints, triggers, or full-text indexes.

    Important

    The restoration process automatically removes foreign key constraints to ensure a higher success rate. If you need these constraints, you must manually recreate them after the restoration is complete.

• Table count limits:

  • The restoration task fails if the instance contains more than 50,000 tables.

  • The restoration process may take significantly longer if it involves a large volume of data, many databases and tables, or numerous binary logs within the specified time range.

• Limitations on fast database and table restoration:

  • For an instance with high-performance local disks, you can restore data only to the original instance, not to a new instance.

  • You cannot use fast database and table restoration on instances that have a read-only node, use transparent data encryption (TDE), or do not have log backup enabled.

Billing

1. Enable fast database/table restoration

To use the Fast Database/Table Restoration feature, you must first enable it. Then, you can use a backup set created after the feature is enabled to perform Database/Table Restoration. This feature is available only for instances with high-performance local disks that meet the prerequisites. Enabling this feature does not affect your services.

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 navigation pane on the left, click Backup and Restoration.

   Note

   If a dialog box appears indicating that Fast Database/Table Restoration is a paid feature, you can select Do not show again. and then enable the Free Edition or Paid Edition as needed. If the dialog box does not appear, proceed to the next step.

3. On the Backup and Restoration page, click the Backup Strategy tab.

4. In the Basic Backup section, click Edit, and then set Fast Database/Table Restoration to On.

5. Configure the CDM Billing Method and CDM Retention Period. Fast Database/Table Restoration is available in a Free Edition and a Paid Edition:

   • Free Edition: Supports restoration only from a backup set. Set the CDM Billing Method to Last Point in Time (Free Edition). The restored data is retained for one day.

   • Paid Edition: Supports restoration from a backup set or to a point in time (if log backup is enabled). Set the CDM Billing Method to Any Point in Time (Paid Edition). You can set the retention period for restored data from 1 to 730 days.

6. Click OK.

Step 2: Restore to original or new instance

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 navigation pane on the left, click Backup and Restoration, and then click Database/Table Restoration.

   Note

   • If the Database/Table Restoration button is not displayed in the console, ensure that you meet the requirements described in Prerequisites.

   • If the console stops responding after you click the Database/Table Restoration button, clear your browser cache and try again.

   • If a message indicates insufficient storage capacity, expand the storage capacity before starting the restoration.

3. Configure the restoration destination and method.

   Parameter	Description
   Restore To	Current Instance New Instance (available only for instances that use local SSDs): The system creates a new RDS instance without affecting the original instance. After you verify the data in the new instance, you can also migrate the data back to the original instance.
   Restore Speed	Normal Fast (restoration to the original instance only): The fast database and table restoration feature increases the restoration speed by 50% to 95%. For more information, see Emergency recovery for ApsaraDB RDS for MySQL.
   Restore Method	By Backup Set By Time (requires log backup to be enabled): The system restores a full backup followed by an incremental log backup. You can select any point in time within the log backup retention period. To view or change the log backup retention period, see Automatic backup. For example, if the data backup and log backup retention periods for an RDS instance are both seven days, you can restore data from the last seven days. Backup sets older than seven days are discarded and cannot be used for restoration. Note The free edition of fast database and table restoration does not support point-in-time recovery. It only supports restoration by backup set.

4. Select the databases and tables that you want to restore. You can also specify the names for the restored databases or tables. Then, click OK.

   Note

   • You can select up to 50 databases or tables at a time.

   • When you restore data to the original instance, the data in the original instance is not affected. A restored database cannot have the same name as an existing database, but a restored table can. By default, the system adds the _backup suffix to the restored database name. You can also change the name.

   • If you restore data to the original instance, check the total size of the selected databases and tables at the bottom of the page, and ensure that the instance has sufficient remaining storage capacity.

5. If you restore data to a New Instance from an instance that runs on local SSDs, you must select a billing method and configure the parameters for the new instance.

   • Subscription: You pay upfront when you create the new instance. This option works best for long-term use.

   • Pay-as-you-go: You are billed hourly. This option works best for short-term use, and you can release the instance when you no longer need it.

   Parameter	Description
   Zone	An availability zone is an independent physical location within a region. All availability zones in a region provide the same services and connectivity. You can create the RDS instance in the same availability zone as an ECS instance or in a different one. Note The new instance is in the same region as the original instance. The region cannot be changed.
   Instance Type	Each instance type offers a specific number of CPU cores, memory size, maximum connections, and maximum IOPS.
   Storage Capacity	The storage capacity includes space for data files, system files, log files, and transaction files.
   Network Type	VPC (default). A VPC is an isolated network environment that provides higher security and performance than Classic Network.

6. You can view the progress of the database and table restoration or the creation of the new instance on the Tasks page.

   After you start a database and table recovery operation in the ApsaraDB RDS console and select restore to the original instance, the running state of the ApsaraDB RDS for MySQL instance changes to Creating Temporary Instance. This state indicates that the recovery task is in progress. Your instance remains fully operational during this process, and you can perform other operations such as initiating a backup. The duration of this state is proportional to the volume of data being recovered.

   Important

   • Once a recovery task starts, it cannot be paused or canceled.

   • While a recovery task is running, do not start another recovery operation. Otherwise, the system returns an error message such as "Operation failed. This operation is not supported in the current instance state." Wait for the current task to complete before starting another.

   • If a primary instance has read-only instances, the system automatically synchronizes data to all associated read-only instances after the recovery task for the primary instance is complete, ensuring data consistency between the primary and read-only instances.

7. After you connect to the RDS for MySQL instance, you can view the restored data in the original or new instance.

   Note

   • Ensure the account used to connect to the instance has the required permissions to view or manage databases. We recommend using a privileged account to connect and view data.

   • If you use DMS to connect to the RDS for MySQL instance, you must right-click the instance and select Refresh / Sync Dictionary after the restoration so the new database will appear in the list.

   After restoring to a new instance, you can use DTS to migrate the data back to the original instance if needed.

Related operations

Related APIs

FAQ