View data backup parameters

After you perform a backup operation, you can view parameters to obtain backup-related configuration information.

View user tenant data backup parameters as the sys tenant

1. Log in to the sys tenant of the cluster as the root user.

2. Run the following statements respectively to obtain backup-related parameter configuration information.

   • View the current number of working threads for high availability low-priority threads such as backup and backup cleanup.

     Example:

     obclient [(none)]> SHOW PARAMETERS LIKE '%ha_low_thread_score%'\G
     

     The query result is as follows:

     *************************** 1. row ***************************
              zone: zone1
          svr_type: observer
            svr_ip: 172.xx.xxx.xxx
          svr_port: 2882
              name: ha_low_thread_score
         data_type: NULL
             value: 0
              info: the current work thread score of high availability low thread. Range: [0,100] in integer. Especially, 0 means default value
           section: OBSERVER
             scope: TENANT
            source: DEFAULT
        edit_level: DYNAMIC_EFFECTIVE
     default_value: 0
         isdefault: 1
     1 row in set
     

     The tenant-level parameter ha_low_thread_score specifies the number of current working threads for high availability low-priority threads such as backup and backup cleanup. The default value is 0. If the data backup task runs too slowly, you can increase the value of the ha_low_thread_score parameter. We recommend that you double the value each time. For more information about the ha_low_thread_score parameter, see ha_low_thread_score.

     Example of setting the number of concurrent writes to the file system during backup:

     obclient [(none)]>  ALTER SYSTEM SET ha_low_thread_score = 4 TENANT = mysql_tenant;
     

     In the example, the value of the ha_low_thread_score parameter is set to 4, indicating that the number of working threads for the backup task of tenant mysql_tenant is 4.

   • View backup parameter information of tenants (excluding source-side configuration information).

     The sys tenant can use the oceanbase.CDB_OB_BACKUP_PARAMETER view to view backup parameter information of all tenants in the current cluster.

     obclient [(none)]> SELECT * FROM oceanbase.CDB_OB_BACKUP_PARAMETER\G
     

     Example query result:

     *************************** 1. row ***************************
     TENANT_ID: 1004
          NAME: data_backup_dest
         VALUE: oss://oceanbase-test-bucket/backup/data/?host=xxx.aliyun-inc.com&access_id=xxx&access_key=xxx&delete_mode=delete&checksum_type=md5&enable_worm=true
     1 row in set
     

     In this example, the data backup path (data_backup_dest) of the tenant with TENANT_ID 1004 is oss://oceanbase-test-bucket/backup/data/?host=xxx.aliyun-inc.com&access_id=xxx&access_key=xxx, and the backup file cleanup mode (delete_mode) is delete. The delete_mode parameter supports the following two modes:

     • delete mode: indicates that the cleanup mode directly deletes backup files that meet the cleanup requirements.

       In this mode, when you clean up backup files, the system directly deletes backup files that meet the cleanup requirements.

     • tagging mode: indicates that the cleanup mode sets tags on backup files that meet the cleanup requirements. The backup files are retained.

       In this mode, when you clean up backup files, the system sets tags on backup files that meet the cleanup requirements. The tag key is "delete_mode" and the value is "tagging", so that you can manage the lifecycle of these files on OSS or S3 based on the tags.

     In addition, in the backup path parameters of this tenant, checksum_type=md5 indicates that the MD5 algorithm is used to verify the integrity of backup files; enable_worm=true indicates that OceanBase Database performs write and delete operations on this path according to WORM rules.

   • View configuration information of tenant backup paths (including source-side configuration information).

     The CDB_OB_BACKUP_STORAGE_INFO view records the source-side configuration information of backup or archive paths for all tenants.

     obclient [(none)]> SELECT TENANT_ID, PATH, DEST_TYPE, EXTENSION FROM oceanbase.CDB_OB_BACKUP_STORAGE_INFO WHERE DEST_TYPE='backup_data';
     

     Example query result:

     +-----------+---------------------------------------------+-------------+-----------------------------------------------+
     | TENANT_ID | PATH                                        | DEST_TYPE   | EXTENSION                                     | 
     +-----------+---------------------------------------------+-------------+-----------------------------------------------+
     |      1004 | oss://oceanbase-test-bucket/backup/data     | backup_data | checksum_type=md5&enable_worm=true&zone=zone1 |
     +-----------+---------------------------------------------+-------------+-----------------------------------------------+
     1 row in set
     

     In the query result, for the tenant with tenant ID 1004, the backup path is oss://oceanbase-test-bucket/backup/data. checksum_type=md5 indicates that the MD5 algorithm is used to verify the integrity of backup files; enable_worm=true indicates that OceanBase Database performs write and delete operations on this path according to WORM rules; the source-side configuration is zone=zone1, indicating that only nodes in zone1 can access this path.

View data backup parameters of the current tenant as a user tenant

1. Log in to the database as the tenant administrator.

   Note

   In MySQL-compatible mode, the administrator user is root. In Oracle-compatible mode, the administrator user is SYS.

2. Run the following statements respectively to view backup-related parameter configuration information.

   • View the current number of working threads for high availability low-priority tasks such as backup and backup cleanup.

     Example:

     obclient(root@mysql001)[(none)]> SHOW PARAMETERS LIKE '%ha_low_thread_score%'\G
     

     The query result is as follows:

     *************************** 1. row ***************************
              zone: zone1
          svr_type: observer
            svr_ip: 172.xx.xxx.xxx
          svr_port: 2882
              name: ha_low_thread_score
         data_type: INT
             value: 0
              info: the current work thread score of high availability low thread. Range: [0,100] in integer. Especially, 0 means default value
           section: OBSERVER
             scope: TENANT
            source: DEFAULT
        edit_level: DYNAMIC_EFFECTIVE
     default_value: 0
         isdefault: 1
     1 row in set
     

     The tenant-level parameter ha_low_thread_score specifies the current number of working threads for high availability low-priority tasks such as backup and backup cleanup. The default value is 0. If the data backup task runs too slowly, you can increase the value of the ha_low_thread_score parameter. We recommend that you double the value each time. For more information about the ha_low_thread_score parameter, see ha_low_thread_score.

     Example of setting the number of concurrent writes to the file system during backup for the current tenant:

     obclient(root@mysql001)[(none)]> ALTER SYSTEM SET ha_low_thread_score = 4;
     

     In the example, the value of the ha_low_thread_score parameter is set to 4, indicating that the number of working threads for the backup task of the current tenant is 4.

   • View backup parameter information of the current tenant (excluding source-side configuration information).

     A user tenant can use the oceanbase.DBA_OB_BACKUP_PARAMETER view (MySQL-compatible mode) or the SYS.DBA_OB_BACKUP_PARAMETER view (Oracle-compatible mode) to view the configured backup parameter information of the current tenant.

     MySQL-compatible mode

     Oracle-compatible mode

     Example query in MySQL-compatible mode:

     obclient(root@mysql001)[oceanbase]> SELECT * FROM oceanbase.DBA_OB_BACKUP_PARAMETER;
     

     Example query in Oracle-compatible mode:

     obclient(SYS@oracle001)[SYS]> SELECT * FROM SYS.DBA_OB_BACKUP_PARAMETER;
     

     Example query result:

     +------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
     | NAME             | VALUE                                                                                                                                                 |
     +------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
     | data_backup_dest | oss://oceanbase-test-bucket/backup/data/?host=xxx.aliyun-inc.com&access_id=xxx&access_key=xxx&delete_mode=delete&checksum_type=md5&enable_worm=true   |
     +------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
     1 row in set
     

     Based on the query result, in the backup path parameters of the current tenant, checksum_type=md5 indicates that the MD5 algorithm is used to verify the integrity of backup files; enable_worm=true indicates that OceanBase Database performs write and delete operations on this path according to WORM rules.

   • View configuration information of the backup path of the current tenant (including source-side configuration information).

     A user tenant can use the oceanbase.DBA_OB_BACKUP_STORAGE_INFO view (MySQL-compatible mode) or the SYS.DBA_OB_BACKUP_STORAGE_INFO view (Oracle-compatible mode) to view configuration information of the backup or archive path of the current tenant.

     MySQL-compatible mode

     Oracle-compatible mode

     Example query in MySQL-compatible mode:

     obclient(root@mysql001)[oceanbase]> SELECT PATH, DEST_TYPE, EXTENSION FROM oceanbase.DBA_OB_BACKUP_STORAGE_INFO WHERE DEST_TYPE='backup_data';
     

     Example query in Oracle-compatible mode:

     obclient(SYS@oracle001)[SYS]> SELECT PATH, DEST_TYPE, EXTENSION FROM SYS.DBA_OB_BACKUP_STORAGE_INFO WHERE DEST_TYPE='backup_data';
     

     Example query result:

     +---------------------------------------------+-------------+-----------------------------------------------+
     | PATH                                        | DEST_TYPE   | EXTENSION                                     | 
     +---------------------------------------------+-------------+-----------------------------------------------+
     | oss://oceanbase-test-bucket/backup/data     | backup_data | checksum_type=md5&enable_worm=true&zone=zone1 |
     +---------------------------------------------+-------------+-----------------------------------------------+
     1 row in set
     

     In the query result, the backup path of the current tenant is oss://oceanbase-test-bucket/backup/data; checksum_type=md5 indicates that the MD5 algorithm is used to verify the integrity of backup files; enable_worm=true indicates that OceanBase Database performs write and delete operations on this path according to WORM rules; the source-side configuration is zone=zone1, indicating that only nodes in zone1 can access this path.