Query data from DBS-generated logical backup files

Scenarios

DLA supports real-time queries from full backup files. This relieves the need to restore data and reduces costs.

Precautions

• DLA supports only the queries of data from the data backup files that are created 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 data backup files 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 these operations.
     1. Log on to the DLA console.
     2. In the left-side navigation pane, choose Data Lake Management > Metadata management.
     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 Database Backup 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 Schedule ID/Name column or click Manage in the Actions column.
   4. In the left-side navigation pane, choose Backup Tasks > Full Data.
   5. Find the data backup file that you want to use. In the Actions column, click Query Backup Set. In the Query Backup Data message, click OK.
      Note After you click OK, DLA automatically creates a schema for the data backup file.
3. Query data from the full backup file that is created 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 Login instance dialog box, 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%';

      Run a multi-table join query on your RDS instance. Then, compare the query result from DLA and the query result from your RDS instance.

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

      In this example, if ApsaraDB RDS clones your RDS instance to create an RDS instance, restores data from a full backup file to the new RDS instance, configures an IP address whitelist, and then returns the data that you query, about 1 hour is required 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 recover a small amount of data that is accidentally deleted.