Prepare for backup

Before you back up data, you need to perform some preparatory tasks, such as configuring the backup destination and the backup key.

Configure the backup destination

When you configure the backup destination, note that the sys tenant, which is a cluster management tenant and does not contain user data, does not support backup and restore. Therefore, you do not need to configure the DATA_BACKUP_DEST parameter for the sys tenant.

Considerations

Procedure

1. Log in to the database as the tenant administrator of the sys tenant or user tenant.

   Note

   The administrator user in MySQL mode is root, and that in Oracle mode is SYS.

2. Configure the destination for backup.

   • Configure the destination for backup of a specified tenant from the sys tenant

     ALTER SYSTEM SET DATA_BACKUP_DEST= 'data_backup_path' TENANT = mysql_tenant;
     

   • Configure the destination for backup of the current tenant from a user tenant

     The statement is as follows:

     ALTER SYSTEM SET DATA_BACKUP_DEST= 'data_backup_path';
     

   Notice

   After upgrading from OceanBase Database V4.0.x to V4.1.0, you need to change the backup path. After upgrading from OceanBase Database V4.1.x to V4.2.0, you do not need to change the backup path.

   At present, OceanBase Database supports NFS, Alibaba Cloud OSS, AWS S3, and object storage services compatible with the S3 protocol, such as Huawei Cloud OBS, Google Cloud GCS, and Tencent Cloud COS. Some backup media may have basic requirements. For more information, see Backup media requirements in Overview of physical backup and restore.

   For more information about how to configure the destination for backup, see the following tables.

   Alibaba Cloud OSS

   NFS

   AWS S3

   Compatible object storage services

   When you use OSS as the destination for backup, you can also use the delete_mode parameter to specify the mode for clearing backup files. The delete_mode parameter supports the delete mode and the tagging mode. If you do not explicitly specify the delete_mode parameter, the default delete mode is used.

   Additionally, OSS supports using the checksum_type parameter to specify a checksum algorithm to verify the integrity of backup data. The supported values are md5 and no_checksum. If you do not explicitly specify the checksum_type parameter, the default value md5 is used.

   For more information about the delete_mode parameter and the checksum_type parameter, see SET DATA_BACKUP_DEST.

   Notice

   When you use an object storage service as the backup destination, separate the parameters in the object storage service path with the & character. Make sure that the parameter values contain only uppercase and lowercase English letters, numbers, and the characters /-_$+= and *. If your input contains other characters, the setting may fail.

   • When you use OSS as the backup destination, the tenant administrator of the sys tenant configures the backup path for the mysql_tenant tenant, specifies the delete mode for clearing backup files, and sets the checksum algorithm to md5.

     obclient> ALTER SYSTEM SET DATA_BACKUP_DEST='oss://oceanbase-test-bucket/backup/data?host=***.aliyun-inc.com&access_id=***&access_key=***&delete_mode=delete&checksum_type=md5' TENANT = mysql_tenant;
     

     If you do not want to verify the integrity of backup data, you can set the checksum_type parameter to no_checksum. Here is an example:

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

   • When you use OSS as the backup destination, the tenant administrator configures the backup path for the current tenant, specifies the delete mode for clearing backup files, and sets the checksum algorithm to md5.

     obclient> ALTER SYSTEM SET DATA_BACKUP_DEST='oss://oceanbase-test-bucket/backup/data?host=***.aliyun-inc.com&access_id=***&access_key=***&delete_mode=delete&checksum_type=md5';
     

   In the following example, oss:// indicates that the backup destination is an OSS service, the bucket name is oceanbase-test-bucket, the path within the bucket is /backup/data, and the other parameters are separated with the ? character. The parameters host, access_id, and access_key are used to set the domain name of the Amazon S3 service, the access ID, and the access key, respectively. The delete mode is used to specify to automatically clear backup data after the data expires. The MD5 algorithm is used to verify the integrity of backup data.

   After you set the delete mode or the tagging mode, for more information about how to automatically clear expired backup data, see Automatically clear expired backup data.

   Notice

   When you use NFS as the backup destination, note that:

   • The value of the data_backup_dest parameter must not contain the ? character.
   • The value of the data_backup_dest parameter must be an absolute path that the OBServer node has write permissions on.
   • All OBServer nodes must mount the same NFS server. Additionally, to ensure smooth backup, it is recommended to use the parameters suggested in this topic to mount NFS. For more information, see Deploy an NFS client.

   • When you use NFS as the backup destination, the tenant administrator of the sys tenant configures the backup path for the mysql_tenant tenant.

     obclient> ALTER SYSTEM SET DATA_BACKUP_DEST= 'file:///data/nfs/backup/data' TENANT = mysql_tenant;
     

   • When you use NFS as the backup destination, the tenant administrator configures the backup path for the current tenant.

     obclient> ALTER SYSTEM SET DATA_BACKUP_DEST='file:///data/nfs/backup/data';
     

   In the following example, file:// indicates that the backup destination is an NFS server, and the backup path is file:///data/nfs/backup/data.

   Like OSS, AWS S3 supports using the delete_mode parameter to specify the mode for clearing backup files. The configuration method is the same as for OSS. Additionally, AWS S3 supports using the checksum_type parameter to specify a checksum algorithm to verify the integrity of backup data. The supported values are md5 and crc32. If you do not explicitly specify the checksum_type parameter, the default value md5 is used.

   For more information about the delete_mode parameter and the checksum_type parameter, see SET DATA_BACKUP_DEST.

   Notice

   When you use an object storage service as the backup destination, separate the parameters in the object storage service path with the & character. Make sure that the parameter values contain only uppercase and lowercase English letters, numbers, and the characters /-_$+= and *. If your input contains other characters, the setting may fail.

   • When you use S3 as the backup destination, the tenant administrator of the sys tenant configures the backup path for the mysql_tenant tenant, specifies the delete mode for clearing backup files, and sets the checksum algorithm to crc32.

     obclient> ALTER SYSTEM SET DATA_BACKUP_DEST='s3://oceanbase-test-bucket/backup/data?host=s3.<region>.amazonaws.com&access_id=***&access_key=***&s3_region=***&delete_mode=delete&checksum_type=crc32' TENANT = mysql_tenant;
     

   • When you use S3 as the backup destination, the tenant administrator configures the backup path for the current tenant, specifies the delete mode for clearing backup files, and sets the checksum algorithm to crc32.

     obclient> ALTER SYSTEM SET DATA_BACKUP_DEST='s3://oceanbase-test-bucket/backup/data?host=s3.<region>.amazonaws.com&access_id=***&access_key=***&s3_region=***&delete_mode=delete&checksum_type=crc32';
     

   In the following example, s3:// indicates that the backup destination is an AWS S3 service, the bucket name is oceanbase-test-bucket, the path within the bucket is /backup/data, and the other parameters are separated with the ? character. The parameters host, access_id, and access_key are used to set the domain name of the Amazon S3 service, the access ID, and the access key, respectively. The delete mode is used to specify to automatically clear backup data after the data expires. The CRC32 algorithm is used to verify the integrity of backup data.

   Most object storage services are compatible with the S3 protocol. Therefore, object storage services that are compatible with the S3 protocol and meet the requirements of OceanBase Database (the object storage services must be compatible with several S3 APIs that are implemented in OceanBase System) can be used as the backup destination for OceanBase Database. You can access compatible object storage services in the same way as you access S3, for example, OBS and GCS.

   You can use the checksum_type parameter to specify a checksum algorithm to verify the integrity of backup data when you access compatible object storage services using the S3 protocol. At present, only the md5 value is supported. If you do not explicitly specify the checksum_type parameter, the default value md5 is used. For more information about the checksum_type parameter, see SET DATA_BACKUP_DEST.

   Notice

   When you use an object storage service as the backup destination, separate the parameters in the object storage service path with the & character. Make sure that the parameter values contain only uppercase and lowercase English letters, numbers, and the characters /-_$+= and *. If your input contains other characters, the setting may fail.

   Here is an example of configuring an OBS bucket as the backup destination:

   • When you use OBS as the backup destination, the tenant administrator of the sys tenant configures the backup path for the mysql_tenant tenant.

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

   • When you use OBS as the backup destination, the tenant administrator configures the backup path for the current tenant.

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

   In the preceding example, s3:// indicates that the backup destination is an OBS bucket, the bucket name is oceanbase-test-bucket, the path within the bucket is /backup/data, and the other parameters are separated with the ? character. The parameters host, access_id, and access_key are used to set the domain name of the COS service, the access ID, and the access key, respectively.

   Here is an example of configuring a GCS bucket as the backup destination:

   • When you use GCS as the backup destination, the tenant administrator of the sys tenant configures the backup path for the mysql_tenant tenant.

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

   • When you use GCS as the backup destination, the tenant administrator configures the backup path for the current tenant.

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

   In the preceding example, s3:// indicates that the backup destination is a GCS bucket, the bucket name is oceanbase-test-bucket, the path within the bucket is /backup/data, and the other parameters are separated with the ? character. The parameters host, access_id, and access_key are used to set the domain name of the GCS service, the access ID, and the access key, respectively.

   To use COS as the backup destination, follow these steps:

   1. View the value of the cluster-level parameter ob_storage_s3_url_encode_type.

      obclient> SHOW PARAMETERS LIKE '%ob_storage_s3_url_encode_type%';
      

      The query result is as follows:

      +-------+----------+----------------+----------+-------------------------------+-----------+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+---------+-------------------+
      | zone  | svr_type | svr_ip         | svr_port | name                          | data_type | value   | info                                                                                                                                                                                        | section  | scope   | source  | edit_level        |
      +-------+----------+----------------+----------+-------------------------------+-----------+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+---------+-------------------+
      | zone1 | observer | 172.xx.xxx.xxx |     2882 | ob_storage_s3_url_encode_type | NULL      | default | Determines the URL encoding method for S3 requests."default": Uses the S3 standard URL encoding method."compliantRfc3986Encoding": Uses URL encoding that adheres to the RFC 3986 standard. | OBSERVER | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE |
      +-------+----------+----------------+----------+-------------------------------+-----------+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+---------+-------------------+
      1 row in set
      

   2. If the value is default, change it to compliantRfc3986Encoding.

      obclient>ALTER SYSTEM SET ob_storage_s3_url_encode_type='compliantRfc3986Encoding';
      

      For more information about this parameter, see ob_storage_s3_url_encode_type.

   3. Configure the backup path and use the delete_mode parameter to specify the mode for clearing backup files.

      To use COS as the backup destination, the tenant administrator of the sys tenant configures the backup path for the mysql_tenant tenant and specifies the delete mode. Here is an example:

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

      To use COS as the backup destination, the tenant administrator configures the backup path for the current tenant and specifies the delete mode. Here is an example:

      obclient> ALTER SYSTEM SET DATA_BACKUP_DEST='s3://oceanbase-test/backup/data?host=cos.ap-xxxx.myqcloud.com&access_id=***&access_key=***&delete_mode=delete';
      

      In the preceding example, s3:// indicates that the backup destination is a COS bucket, the bucket name is oceanbase-test, the path within the bucket is /backup/archive, and the other parameters are separated with the ? character. The parameters host, access_id, and access_key are used to set the domain name of the COS service, the access ID, and the access key, respectively. The delete mode is used to specify to automatically clear backup data after the data expires.

3. After the configuration is completed, the tenant administrator of the sys tenant can query the CDB_OB_BACKUP_PARAMETER view for the backup path and other information of each tenant in the current cluster. For more information, see Query data backup parameters.

Considerations and notes after configuration

After the data_backup_dest parameter is configured, the system will create a format file in the specified directory as a verification file to check the validity of the backup destination and the integrity of the data in the destination directory. Therefore, when you configure the data_backup_dest parameter, note the following considerations:

• If the format file does not exist, the specified destination directory must be empty. Otherwise, an error -9080 will occur, indicating that the format file does not exist.

• If the format file exists, the content of the file must pass the verification. Otherwise, an error -9081 will occur, indicating that the format file is mismatched. The content verification of the format file mainly checks whether the cluster, tenant, and backup destination type specified in the file are consistent with those of the current operation.

• If the format file does not exist or the verification fails during a backup task, the task will fail.

Back up the keys

Before you back up data, consider the encryption status of the source tenant. If the source tenant has transparent encryption enabled, you also need to back up the keys of the source tenant.

1. Log in to the database as the tenant administrator of the sys tenant or a user tenant.

2. Back up the keys.

   • Back up the keys of a specified tenant in the sys tenant

     ALTER SYSTEM BACKUP KEY TENANT = tenant_name TO 'backup_key_path' ENCRYPTED BY 'password';
     

   • Back up the keys of the current tenant in a user tenant

     Execute the following statement:

     ALTER SYSTEM BACKUP KEY TO 'backup_key_path' ENCRYPTED BY 'password';
     

   In the statements:

   • backup_key_path: the path where the keys are to be backed up. You need to specify this parameter. It cannot be the same as the path for data backup or log archive. You can customize this path.

   • password: the password for encrypting the key backup file.

   Here is an example of backing up the keys of the mysql_tenant tenant to the file:///data_backup_dest/key path in the sys tenant:

   obclient [(none)]> ALTER SYSTEM BACKUP KEY TENANT = mysql_tenant TO 'file:///data_backup_dest/key' ENCRYPTED BY '******';
   

3. After the configuration is successful, you can query the path where the keys are backed up by using a view.

   • Query the CDB_OB_BACKUP_STORAGE_INFO view in the sys tenant for the key backup path

     SELECT * FROM oceanbase.CDB_OB_BACKUP_STORAGE_INFO;
     

   • Query the DBA_OB_BACKUP_STORAGE_INFO view in a user tenant for the key backup path

     MySQL mode

     Oracle mode

     Execute the following statement:

     SELECT * FROM oceanbase.DBA_OB_BACKUP_STORAGE_INFO;
     

     Execute the following statement:

     SELECT * FROM SYS.DBA_OB_BACKUP_STORAGE_INFO;
     

     For more information about the fields in the CDB_OB_BACKUP_STORAGE_INFO and DBA_OB_BACKUP_STORAGE_INFO views, see CDB_OB_BACKUP_STORAGE_INFO and DBA_OB_BACKUP_STORAGE_INFO.