SET LOG_ARCHIVE_DEST

Purpose

The ALTER SYSTEM SET LOG_ARCHIVE_DEST statement is used to configure the log archive destination for user tenants. The configuration of the log archive destination mainly includes attributes such as LOCATION, BINDING, and PIECE_SWITCH_INTERVAL.

Limitations and considerations

• The system tenant and Meta tenant do not support backup and recovery, so they do not require a log archive destination configuration.

• Currently, OceanBase Database supports backup media including NFS, Alibaba Cloud OSS, AWS S3, and object storage services compatible with the S3 protocol, such as Huawei OBS, Google GCS, and Tencent Cloud COS. Some backup media may require meeting specific basic requirements before use. For detailed requirements of each backup medium, see Backup media requirements in Overview of physical backup and recovery.

• When configuring LOCATION, BINDING, and PIECE_SWITCH_INTERVAL, separate these attributes with spaces. Also, ensure no spaces are present before or after the equal sign (=) that sets the value for each attribute.

• After the log archive destination is successfully configured, incremental configuration is not supported. That is, you must specify the values for all attributes every time you execute the ALTER SYSTEM SET LOG_ARCHIVE_DEST statement. Otherwise, unspecified attributes will use their default values.

Privilege requirements

This statement must be executed by the root user of the sys tenant (root@sys) or an administrator user of each tenant. Specifically:

• The default administrator user in MySQL-compatible mode is the root user.
• The default administrator user in Oracle-compatible mode is the SYS user.

Syntax

ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=archive_path [BINDING=archive_mode] [PIECE_SWITCH_INTERVAL=piece_switch_interval]' [TENANT = tenant_name];

Parameters

archive_path

Specifies the archive path. The following describes the archive_path format for different types of media.

NFS

ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=file://your-nfs-server-path/your-archive-path[?{zone=zone_name | idc=idc_name | region=region_name}];

The related parameters are described as follows:

• file://your-nfs-server-path/your-archive-path: Specifies the NFS server path and the archive path.

• zone, idc, region: Specifies the source of the archive path, i.e., the range of nodes that can access this path. These are optional parameters, separated from the archive path by a question mark (?). You can select configurations at three different levels: zone, idc, or region. You can only choose one level at a time or not set any. By default, none is set.

  Note

  For V4.2.1, setting the source of the archive path is supported starting from V4.2.1 BP11.

  • If you need to set the region or IDC level, you must first set the region information using the ALTER SYSTEM MODIFY ZONE zone_name SET REGION=region_name; statement, or set the IDC information using the ALTER SYSTEM MODIFY ZONE zone_name SET IDC=idc_name; statement. It is recommended to set the source configuration at the IDC level. This allows the IDC with more network bandwidth resources to perform archiving tasks while providing a wider range of selectable nodes for the archiving task, and users do not need to worry about changes in zones within the IDCs in the cluster.

  • If the specified zone, idc, or region does not exist, an error will be returned indicating that the source information does not exist (error code -9133).

  • Since all traffic for log archiving is written by the leader replica, there is no access priority difference for log archive paths; only the range of nodes that can access the archive path is limited. If you need to specify multiple zones, IDCs, or regions, separate the values with commas (,) or semicolons (;). For example, zone=z1,z2;z3 specifies z1, z2, and z3 as the source for archiving.

  • When configuring zone, idc, or region, the leader replica must be located within the specified zone, idc, or region. Otherwise, archiving cannot proceed.

Object storage

# Alibaba Cloud OSS
ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=oss://your-bucket-name/your-archive-path?host=oss-your-region.aliyuncs.com&access_id=your-access-id&access_key=your-access-key[&delete_mode={delete | tagging}][&checksum_type={md5 | no_checksum}][&{zone=zone_name | idc=idc_name | region=region_name}];

# AWS S3
ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=s3://your-bucket-name/your-archive-path?host=s3.your-region.amazonaws.com&access_id=your-access-id&access_key=your-access-key&s3_region=your-s3-region[&delete_mode={delete | tagging}][&checksum_type={md5 | crc32}][&{zone=zone_name | idc=idc_name | region=region_name}];

# Object storage compatible with the S3 protocol
ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=s3://your-bucket-name/your-archive-path?host=your-service.com&access_id=your-access-id&access_key=your-access-key[&addressing_model={virtual_hosted_style | path_style}][&checksum_type=md5][&{zone=zone_name | idc=idc_name | region=region_name}];

The related parameters are described as follows:

• host: The access domain name of the object storage service.

• access_id: The access key ID for object storage.

• access_key: The access key for object storage.

• delete_mode: Specifies the cleanup mode for archived files. This parameter is available for OSS and AWS S3 storage services. Multiple parameters must be concatenated with the & operator. Currently, the following two values are supported:

  • delete: In this mode, the system directly deletes archived files that meet the cleanup criteria.

    After this mode is enabled, when you automatically clean up archived files, the system will directly delete the archived files that meet the cleanup criteria.

  • tagging: In this mode, the system sets a tag for archive files that meet the cleanup criteria, and the archive files are retained.

    After this mode is configured, when you automatically clean up archived files, the system will tag the files that meet the cleanup criteria. The tag's key will be "delete_mode" and the value will be "tagging". This allows users to manage the lifecycle of these files in object storage based on the set tag.

• checksum_type: Specifies the checksum algorithm for verifying the integrity of the archived file. If not explicitly specified, MD5 is used by default for all object storages. The supported values for this parameter vary by object storage:

  Note

  For V4.2.1, this parameter is supported starting from V4.2.1 BP10.

  • md5: verifies the integrity of the archive file using the MD5 algorithm.

    Notice

    MD5 verification does not take effect for the GetObject API.

  • no_checksum: Do not verify the integrity of the archive file. This value is supported only by OSS. For object storage accessed through the S3 protocol, including AWS S3, OBS, GCS, and COS, this value is not supported due to limitations of the S3 SDK.

  • crc32: Uses the CRC32 algorithm to verify the integrity of the archive file. This value is supported only by AWS S3.

• s3_region: The region where the Amazon S3 bucket is located. This parameter is required when the archive medium is AWS S3.

• addressing_model: The URL format for accessing object storage. This is an optional parameter for accessing object storage services (such as OBS, GCS, and COS) using the S3-compatible protocol. Multiple values are separated by the & symbol. Currently, this parameter supports the following two values:

  Note

  For V4.2.1, this parameter is supported starting from V4.2.1 BP9.

  • virtual_hosted_style: The default value. Indicates accessing object storage in virtual-hosted style.
  • path_style: specifies to access object storage in path-style.

  For example, to configure a user tenant to use OBS as its archive medium and access it in virtual-hosted-style format, you can execute the following statement:

  ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=s3://oceanbase-test-bucket/backup/archive?host=obs.****.myhuaweicloud.com&access_id=****&access_key=****&addressing_model=virtual_hosted_style';
  

• zone, idc, and region: Specify the source for the archive path, that is, the range of nodes that can access this path. These are optional parameters, separated by the English symbol &. You can select configurations at three different levels: zone, idc, or region. Only one level can be selected at a time, or none can be selected. By default, none is selected.

  Note

  For V4.2.1, setting the archive path on the source is supported starting from V4.2.1 BP11.

  • If you need to set the region or IDC level, you must first use the ALTER SYSTEM MODIFY ZONE zone_name SET REGION=region_name; or ALTER SYSTEM MODIFY ZONE zone_name SET IDC=idc_name; statement to configure the region or IDC information. It is recommended to set the source configuration at the IDC level. This way, the archive task can be executed using an IDC with more network bandwidth resources, providing a wider range of available nodes. Additionally, users do not need to worry about changes in zones within the IDCs of the cluster.

  • If the specified zone, idc, or region does not exist, an error will be returned indicating that the source information does not exist (error code -9133).

  • Since all traffic for log archiving is written by the leader replica, there is no access priority difference for log archive paths; only the range of nodes that can access the archive path is limited. If you need to specify multiple zones, IDCs, or regions, separate the values with commas (,) or semicolons (;). For example, zone=z1,z2;z3 specifies z1, z2, and z3 as the source for archiving.

  • When configuring zone, idc, or region, the leader replica must be located within the specified zone, idc, or region. Otherwise, archiving will not proceed.

Examples

• System tenant

  • The system tenant configures an archive path for the mysql_tenant tenant. The archive medium is NFS, the mode is user-business priority, and a piece is split every other day.

    obclient [oceanbase]> ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=file:///data/nfs/backup/archive BINDING=Optional PIECE_SWITCH_INTERVAL=1d' TENANT = mysql_tenant;
    

  • The system tenant configures an archive path for the mysql_tenant tenant. The archive medium is NFS. Zones that can access this archive path are z1, z2, and z3. The mode is user-business priority, and a piece is split every other day.

    obclient [oceanbase]> ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=file:///data/nfs/backup/archive?zone=z1,z2,z3 BINDING=Optional PIECE_SWITCH_INTERVAL=1d' TENANT = mysql_tenant;
    

  • The system tenant configures an archive path for the mysql_tenant tenant. The archive medium is OSS. The cleanup mode for archive files is delete, and the MD5 algorithm is used to verify the integrity of archive files.

    obclient [oceanbase]> ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=oss://oceanbase-test-bucket/backup/archive?host=xxx.aliyun-inc.com&access_id=xxx&access_key=xxx&delete_mode=delete&checksum_type=md5' TENANT = mysql_tenant;
    

  • The system tenant configures the archive destination for the mysql_tenant tenant. The archive medium is OSS. The cleanup mode for archive files is delete. The integrity of archive files is not verified.

    obclient [oceanbase]> ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=oss://oceanbase-test-bucket/backup/archive?host=***.aliyun-inc.com&access_id=***&access_key=***&delete_mode=delete&checksum_type=no_checksum' TENANT = mysql_tenant;
    

  • The system tenant configures an archive path for the mysql_tenant tenant. The archive medium is OSS. The cleanup mode for archive files is delete. Zones that can access this archive path are z1, z2, and z3.

    obclient [oceanbase]> ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=oss://oceanbase-test-bucket/backup/archive?host=xxx.aliyun-inc.com&access_id=xxx&access_key=xxx&delete_mode=delete&zone=z1,z2,z3' TENANT = mysql_tenant;
    

  • The system tenant configures an archive path for the mysql_tenant tenant. The archive medium is AWS S3. The cleanup mode for archive files is delete. The mode is user-business priority, and a piece is split every other day.

    obclient> ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=s3://oceanbase-test-bucket/backup/archive?host=s3.<region>.amazonaws.com&access_id=******&access_key=******&s3_region=******&delete_mode=delete BINDING=Optional PIECE_SWITCH_INTERVAL=1d' TENANT = mysql_tenant;
    

  • The system tenant configures an archive path for the mysql_tenant tenant. The archive medium is AWS S3. The cleanup mode for archive files is tagging. The CRC32 algorithm is used to verify the integrity of archive files.

    obclient [oceanbase]> ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=s3://oceanbase-test-bucket/backup/archive?host=s3.<region>.amazonaws.com&access_id=***&access_key=***&s3_region=***&delete_mode=tagging&checksum_type=crc32' TENANT = mysql_tenant;
    

  • The system tenant configures an archive path for the mysql_tenant tenant. The archive medium is OBS, accessed via the S3 protocol.

    obclient> ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=s3://oceanbase-test-bucket/backup/archive?host=obs.****.myhuaweicloud.com&access_id=****&access_key=****' TENANT = mysql_tenant;
    

  • The system tenant configures an archive path for the mysql_tenant tenant. The archive medium is GCS, accessed via the S3 protocol.

    obclient> ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=s3://oceanbase-test-bucket/backup/archive?host=https://storage.googleapis.com&access_id=****&access_key=****' TENANT = mysql_tenant;
    

  • The system tenant configures an archive path for the mysql_tenant tenant. The archive medium is COS, accessed via the S3 protocol.

    Note

    When using COS as the archive medium, you must first set the cluster-level parameter ob_storage_s3_url_encode_type. For detailed operations, see Prepare for log archiving.

    obclient> ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=s3://oceanbase-test/backup/archive?host=cos.ap-xxxx.myqcloud.com&access_id=***&access_key=***' TENANT = mysql_tenant;
    

• User tenant

  • The tenant mysql_tenant is configured with an archive path, the archive medium is NFS, the user business priority mode is adopted, and a piece is split every other day.

    obclient [oceanbase]> ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=file:///data/nfs/backup/archive BINDING=Optional PIECE_SWITCH_INTERVAL=1d';
    

  • The tenant mysql_tenant has an archive path configured for itself. The archive medium is NFS. The region that can access this archive path is region1. User business priority mode is adopted, and a Piece is split every other day.

    obclient [oceanbase]> ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=file:///data/nfs/backup/archive?region=region1 BINDING=Optional PIECE_SWITCH_INTERVAL=1d';
    

  • For the mysql_tenant tenant, configure an archive path, set the archive media to OSS, set the cleanup mode for archived files to delete, and enable MD5 algorithm for verifying the integrity of archived files.

    obclient [oceanbase]> ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=oss://oceanbase-test-bucket/backup/archive?host=xxx.aliyun-inc.com&access_id=xxx&access_key=xxx&delete_mode=delete&checksum_type=md5';
    

  • For the mysql_tenant tenant, configure the archive path, archive media as OSS, and the cleanup mode for archive files as delete. Do not validate the integrity of archive files.

    obclient [oceanbase]> ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=oss://oceanbase-test-bucket/backup/archive?host=xxx.aliyun-inc.com&access_id=xxx&access_key=xxx&delete_mode=delete&checksum_type=no_checksum';
    

  • The tenant mysql_tenant is configured with an archive path, the archive medium is OSS, the cleanup mode for archived files is delete, and the IDC that can access this archive path is idc3.

    obclient [oceanbase]> ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=oss://oceanbase-test-bucket/backup/archive?host=xxx.aliyun-inc.com&access_id=xxx&access_key=xxx&delete_mode=delete&idc=idc3';
    

  • The user tenant configures an archive path for itself, sets the archive media to AWS S3, sets the cleanup mode for archived files to delete, enables user business priority mode, and splits data into a piece every other day.

    obclient> ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=s3://oceanbase-test-bucket/backup/archive?host=s3.<region>.amazonaws.com&access_id=******&access_key=******&s3_region=******&delete_mode=delete BINDING=Optional PIECE_SWITCH_INTERVAL=1d';
    

  • For the mysql_tenant tenant, configure an archive path, set the archive media to AWS S3, set the cleanup mode for archived files to tagging, and enable CRC32 integrity verification for archived files.

    obclient [oceanbase]> ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=s3://oceanbase-test-bucket/backup/archive?host=s3.<region>.amazonaws.com&access_id=***&access_key=***&s3_region=***&delete_mode=tagging&checksum_type=crc32';
    

  • The user tenant configures an archive path for itself, with the archive medium being OBS, accessible via the S3 protocol.

    obclient> ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=s3://oceanbase-test-bucket/backup/archive?host=obs.****.myhuaweicloud.com&access_id=****&access_key=****';
    

  • The user tenant configures an archive path for itself, with the archive medium being GCS, accessible via the S3 protocol.

    obclient> ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=s3://oceanbase-test-bucket/backup/archive?host=https://storage.googleapis.com&access_id=****&access_key=****';
    

  • The user tenant configures an archive path for itself, with the archive medium being COS, accessed via the S3 protocol.

    Notice

    When using COS as the archiving medium, you must first set the cluster-level parameter **ob_storage_s3_url_encode_type**. For detailed operations, see Prepare for log archiving.

    obclient> ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=s3://oceanbase-test/backup/archive?host=cos.ap-xxxx.myqcloud.com&access_id=***&access_key=***';
    

References

• Configure the source backup and archive paths

• ARCHIVELOG

• NOARCHIVELOG

• Prepare for log archiving