All Products
Search
Document Center

ApsaraDB RDS:Query data from DBS-generated logical backup files

Last Updated:Dec 01, 2023

If you want to query data from the backup files of an ApsaraDB RDS for MySQL instance, you must create an RDS instance, replicate the backup files to the new RDS instance, and then restore the data on the new RDS instance. This process is time-consuming. This topic describes how to query data in real time from backup files without the need to restore these files.

Prerequisites

Scenarios

You want to query data from backup sets at high speeds and low costs. DLA supports real-time queries from full backup files. This relieves the need to restore data and reduces costs.

Usage notes

  • DLA supports only the queries of data from the backup sets that are generated by DBS for ApsaraDB RDS for MySQL instances.

  • DLA supports only the queries of data from full backup files. DLA does not support the queries of data from incremental backup files.

  • DLA must reside in the same region as the Object Storage Service (OSS) bucket that stores the backup sets of your RDS instance.

  • DLA supports only the queries of data from logical backup files.

Procedure

  1. Configure the root account, endpoint, and OSS access permissions on DLA.

    • For more information about how to configure the endpoint, see Create an endpoint.

    • To configure the OSS access permissions, perform the following operations:

      Note

      If OSS access permissions are configured, you can skip this step.

      1. Log on to the DLA console.

      2. In the left-side navigation pane, choose Data Lake Management > Meta information discovery.

      3. Click Go to the Wizard in OSS data source.

      4. Click Click Here to Authorize next to Role Name OSS Access Authorization Role AliyunOpenAnalyticsAccessingOSSRole.

  2. Create a schema on DBS.

    1. Log on to the DBS console.

    2. In the left-side navigation pane, click Backup Schedules.

    3. Find the backup schedule that you want to use. Then, click the ID of the backup schedule in the Backup Schedule ID/Name column or click Manage in the Actions column to go to the Configure Task page.

    4. In the left-side navigation pane, choose Backup Tasks > Full Data.

    5. Find the backup set that you want to use and click Query Backup Set in the Actions column. In the message that appears, click OK.

      Note

      After you click OK, DLA automatically creates a schema for the backup set.

      image.png

  3. Query data from the full backup file that is generated by DBS.

    1. Log on to the DLA console.

    2. In the left-side navigation pane, choose Serverless Presto > SQL access point.

    3. On the SQL access point page, click Log on in DMS.

    4. In the dialog box that appears, enter the information that is used to log on to your RDS instance and click Login.

      Note

      Data Management (DMS) automatically specifies the Database Type, Instance Region, and Connection string address parameters. You must confirm the settings of these parameters and enter the username and password that are used to log on to your RDS instance.

    5. Execute the following SQL statements on DLA and your RDS instance to check whether the data volume on DLA is the same as the data volume on your RDS instance:

      select 'bill' as tableName ,count(id) as countNumber from `bill`
      union ALL
      select 'dim_code_desc' as tableName ,count(id) as countNumber from `dim_code_desc` ;
    6. Execute the following SQL statement on DLA to run a multi-table join query:

      select  t.* from dim_code_desc   as t1,  BILL t
      where   t1.id= t.id
      and t1.code_id like '9%';

      Verify that the query result from DLA is the same as the query result from your RDS instance.

In this example, if the system uses the full backup data of your RDS instance to create an RDS instance, restores data to the new RDS instance, configures an IP address whitelist, and then returns the data that you query, it requires approximately 1 hour to complete the restoration and the query process is complicated. The combination of DBS and DLA relieves the need to restore data. In addition, this combination allows you to check for and restore a small amount of data that is accidentally deleted.