Back up to a specified directory

In daily operations, you may need to back up data to a specific storage directory rather than the default path specified by the system parameter data_backup_dest. You can use the TO syntax to specify the backup target path when initiating a data backup; the backup data will be written to this specified path. This is a one-time action that does not modify system parameters or affect subsequent automatic scheduled backups.

Prerequisites

Before initiating a backup to a specified directory, confirm the following:

• Ensure that archiving mode is currently enabled and the STATUS of the log archiving task is DOING.

  For operations related to viewing the status of a log archiving task, see View archiving progress.

  For operations related to enabling archiving mode, see Enable archiving mode.

• Ensure the backup target path is available. The backup target path supports storage media such as NFS, Alibaba Cloud OSS, AWS S3, and object storage compatible with the S3 protocol. The format of dest_path must comply with the path specifications of the corresponding backup medium. For detailed requirements of each backup medium, see Prepare for backup.

Limitations

• The cluster version must be V4.4.2 BP3 or later. If it is lower, the system will return error code ERROR 1235 (0A000) : %s not supported, indicating that the current cluster version does not meet the minimum requirement. Please upgrade the cluster before using this feature.

• If archiving mode is not enabled, or the status of the log archiving task is not DOING, the system will return error code ERROR 9040 (HY000) : backup can not start. Data backup depends on archived logs. Please enable archiving mode and ensure the log archiving task is running normally before initiating a backup.

• When the sys tenant initiates a backup using the TO syntax, it only supports specifying one tenant. It does not support initiating backups for multiple tenants simultaneously, nor does it support omitting the TENANT clause. If any of these constraints are violated, the system will return error code ERROR 1235 (0A000) : %s not supported.

  • When the sys tenant executes BACKUP TENANT = t1, t2 TO 'dest_path' and specifies multiple tenants at once, the multiple tenants will share the same target root path, causing backup sets from different tenants to overwrite each other.
  • When the sys tenant executes BACKUP DATABASE TO 'dest_path' without specifying the TENANT clause, target tenant information is missing, and the sys tenant cannot determine for which tenant to initiate the backup.

• User tenants can only perform backup operations for themselves. They cannot specify other tenants using the TENANT clause; otherwise, the system will return error code ERROR 1235 (0A000) : %s not supported.

• The specified target path must be valid and accessible; otherwise, the system will return error code ERROR 9116 (HY000) : no I/O operation permission of the object storage.

• If no historical backups exist at the specified target path (first-time backup), an incremental backup will automatically downgrade to a full backup.

• Initiating a backup using the TO syntax does not modify the value of the system parameter data_backup_dest. Subsequent backups without the TO syntax will still use the default path configured by the system parameter or cause an error.

Initiate backup to a specified directory

After meeting the prerequisites above, you can initiate backup to a specified directory as follows.

Initiate backup to a specified directory from the sys tenant

The sys tenant can initiate full or incremental backup for a specified tenant in the cluster and back up the data to a specified directory.

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

2. (Optional) Set the backup concurrency for the tenant.

   Backup concurrency is controlled by the tenant-level parameter ha_low_thread_score. This parameter specifies the number of threads used by medium- and low-priority task queues such as backups and backup cleanup. Its value range is [0, 100], and the default value is 0. Before starting a backup, you can appropriately increase the value of the ha_low_thread_score parameter. It is recommended to double the value each time. For operations related to viewing the ha_low_thread_score parameter, see View data backup-related parameters.

   For detailed information about the ha_low_thread_score parameter, see ha_low_thread_score.

   • Set the backup concurrency for all tenants in the cluster

     obclient [(none)]> ALTER SYSTEM SET ha_low_thread_score = 10 TENANT = all_user;
     

     or

     obclient [(none)]> ALTER SYSTEM SET ha_low_thread_score = 10 TENANT = all;
     

     Note

     Starting from OceanBase Database V4.2.1, TENANT = all_user has the same semantics as TENANT = all. When the scope needs to apply to all user tenants, it is recommended to use TENANT = all_user. The latter TENANT = all will be deprecated and will no longer be used.

   • Set the backup concurrency for a specified tenant in the cluster

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

3. Execute the following statement to initiate backup to a specified directory.

   The sys tenant supports the following full and incremental backup methods with specified paths:

   • Full backup

     ALTER SYSTEM BACKUP TENANT = <tenant_name> TO 'dest_path' [PLUS ARCHIVELOG];
     

   • Incremental backup

     ALTER SYSTEM BACKUP INCREMENTAL TENANT = <tenant_name> TO 'dest_path' [PLUS ARCHIVELOG];
     

   Here, tenant_name is the name of the tenant to be backed up, and dest_path is the target backup path. PLUS ARCHIVELOG is optional. Adding PLUS ARCHIVELOG allows archive logs to be backed up to the specified directory together with the data during backup. Ultimately, a complete dataset including archive logs is generated in the backup directory. This dataset is recoverable. You can use this dataset to restore the tenant's data to the MIN_RESTORE_SCN point without relying on the archive logs.

   Note

   The `TO` clause of the BACKUP command supports specifying only one tenant. You cannot back up multiple tenants simultaneously, nor can you omit the `TENANT` clause.

   Examples:

   • Perform a full backup for tenant mysql_tenant to the specified directory.

     obclient [(none)]> ALTER SYSTEM BACKUP TENANT = mysql_tenant TO 'file:///data/backup/sys_one_time_dest';
     

     After the command is executed successfully, the system will back up the full data of tenant mysql_tenant to the specified directory /data/backup/sys_one_time_dest.

   • Perform an incremental backup for tenant mysql_tenant to the specified directory.

     obclient [(none)]> ALTER SYSTEM BACKUP INCREMENTAL TENANT = mysql_tenant TO 'file:///data/backup/sys_one_time_dest';
     

     After the command is executed successfully, the system will back up the incremental data of tenant mysql_tenant to the specified directory /data/backup/sys_one_time_dest.

   • Perform a full backup for tenant mysql_tenant and back up archive logs to the specified directory at the same time.

     obclient [(none)]> ALTER SYSTEM BACKUP TENANT = mysql_tenant TO 'file:///data/backup/self_contained_dest' PLUS ARCHIVELOG;
     

     After the command is executed successfully, the system will generate a complete dataset with archive logs in the specified directory /data/backup/self_contained_dest.

   • Perform an incremental backup for tenant mysql_tenant and back up archive logs to the specified directory at the same time.

     obclient [(none)]> ALTER SYSTEM BACKUP INCREMENTAL TENANT = mysql_tenant TO 'file:///data/backup/self_contained_dest' PLUS ARCHIVELOG;
     

     After the command is executed successfully, the system will back up the incremental data of tenant mysql_tenant to the specified directory /data/backup/self_contained_dest, and back up the archive logs to this directory as well.

   During the backup process, you can view the backup progress in real time. For more information, see View data backup progress.

Initiate backup to a specified directory from a user tenant

A user tenant can perform a full or incremental backup for itself and back up the data to a specified directory without affecting other tenants.

1. The tenant administrator logs in to the database.

   In this example, you can log in to tenant mysql_tenant as the root user, or log in to tenant oracle_tenant as the SYS user.

2. (Optional) Set the backup concurrency for the current tenant.

   Backup concurrency is controlled by the tenant-level parameter ha_low_thread_score. This parameter specifies the number of threads used by medium- and low-priority task queues such as backups and backup cleanup. Its value range is [0, 100], and the default value is 0. Before starting a backup, you can appropriately increase the value of the ha_low_thread_score parameter. It is recommended to double the value each time. For operations related to viewing the ha_low_thread_score parameter, see View data backup-related parameters.

   For detailed information about the ha_low_thread_score parameter, see ha_low_thread_score.

   obclient [(none)]> ALTER SYSTEM SET ha_low_thread_score = 10;
   

3. Execute the following statement to initiate a backup to the specified directory.

   User tenants support the following full and incremental backup methods with specified paths:

   • Full backup

     ALTER SYSTEM BACKUP DATABASE TO 'dest_path' [PLUS ARCHIVELOG];
     

   • Incremental backup

     ALTER SYSTEM BACKUP INCREMENTAL DATABASE TO 'dest_path' [PLUS ARCHIVELOG];
     

   Here, dest_path is the target path for the backup. PLUS ARCHIVELOG is optional. Adding PLUS ARCHIVELOG allows archive logs to be backed up together with the data during the backup process. Ultimately, a complete dataset including archive logs will be generated in the backup directory. This dataset is recoverable; you can use it to restore the tenant's data to the MIN_RESTORE_SCN point without relying on the archive logs.

   Examples:

   • Initiate a full backup of the current tenant to a specified directory.

     obclient [(none)]> ALTER SYSTEM BACKUP DATABASE TO 'file:///data/backup/one_time_dest';
     

     After the command executes successfully, the system will back up the full data of the current tenant to the specified directory /data/backup/one_time_dest.

   • Initiate an incremental backup of the current tenant to a specified directory.

     obclient [(none)]> ALTER SYSTEM BACKUP INCREMENTAL DATABASE TO 'file:///data/backup/one_time_dest';
     

     After the command executes successfully, the system will back up the incremental data of the current tenant to the specified directory /data/backup/one_time_dest.

   • Initiate a full backup of the current tenant and simultaneously back up archive logs to a specified directory.

     obclient [(none)]> ALTER SYSTEM BACKUP DATABASE TO 'file:///data/backup/self_contained_dest' PLUS ARCHIVELOG;
     

     After the command executes successfully, the system will generate a complete dataset including archive logs in the specified directory /data/backup/self_contained_dest.

   • Initiate an incremental backup of the current tenant and simultaneously back up archive logs to a specified directory.

     obclient [(none)]> ALTER SYSTEM BACKUP INCREMENTAL DATABASE TO 'file:///data/backup/self_contained_dest' PLUS ARCHIVELOG;
     

     After the command executes successfully, the system will back up the incremental data of the current tenant to the specified directory /data/backup/self_contained_dest and also back up the archive logs to this directory.

   During the backup process, you can view the backup progress in real time. For specific operations, see View data backup progress.

User tenant behavior description

When initiating a full backup (BACKUP DATABASE), the execution behavior under different configuration combinations is shown in the following table.

When initiating an incremental backup (BACKUP INCREMENTAL DATABASE), the execution behavior in different scenarios is shown in the following table.

References

• Initiate a full data backup

• Initiate an incremental data backup

• View the progress of a data backup job

• View the history of data backups

• BACKUP

• Manually clear the backup or archive path