BACKUP|V4.2.1| docs|Distributed Database

BACKUP

Last Updated：2023-12-25 08:49:41 Updated

You can use this statement to configure the data backup destination, initiate full data backups, initiate incremental data backups, or cancel ongoing data backups. Both the sys tenant and user tenants support data backup operations.

For more information about data backup operations in OceanBase Database, see Backup and restore in Manage.

Syntax

Configure the data backup destination

/* Configure the data backup destination for a specified user tenant in the sys tenant. */
ALTER SYSTEM SET DATA_BACKUP_DEST = 'data_backup_path' TENANT = tenant_name;

/* Configure the data backup destination for the current user tenant. */
ALTER SYSTEM SET DATA_BACKUP_DEST = 'data_backup_path';

Initiate a full data backup

/* In the sys tenant, initiate a full data backup for all tenants in the cluster. */
ALTER SYSTEM BACKUP DATABASE;

/* In the sys tenant, initiate a full data backup for a specified tenant in the cluster. */
ALTER SYSTEM BACKUP TENANT [=] tenant_name;

/* Initiate a full data backup for the current user tenant. */
ALTER SYSTEM BACKUP DATABASE [PLUS ARCHIVELOG];

Initiate an incremental data backup

/* In the sys tenant, initiate an incremental data backup for all tenants in the cluster. */
ALTER SYSTEM BACKUP INCREMENTAL DATABASE;

/* In the sys tenant, initiate an incremental data backup for all tenants in the cluster. */
ALTER SYSTEM BACKUP INCREMENTAL TENANT = tenant_name;

/* Initiate an incremental data backup for the current user tenant. */
ALTER SYSTEM BACKUP INCREMENTAL DATABASE;

Cancel an ongoing backup

/* In the sys tenant, cancel the ongoing data backup for all tenants in the cluster. */
ALTER SYSTEM CANCEL BACKUP;

/* In the sys tenant, cancel the ongoing data backup for a specified tenant in the cluster. */
ALTER SYSTEM CANCEL BACKUP TENANT = tenant_name;

/* Cancel the ongoing data backup for the current user tenant. */
ALTER SYSTEM CANCEL BACKUP;

Parameters

Parameter	Description
DATA_BACKUP_DEST	Configures the data backup destination.
data_backup_path	The path of the backup destination. OceanBase Database allows you to use Network File System (NFS), Alibaba Cloud Object Storage Service (OSS), and Tencent Cloud Object Storage (COS) as the backup destination.
tenant_name	The name of the tenant.
PLUS ARCHIVELOG	Indicates that the archived logs will be backed up together with data. In this case, baseline data and archived logs stored in the backup directory constitute a snapshot of the database.
INCREMENTAL	Incremental data backup.

Examples

In the sys tenant, configure the data backup destination for the mysql_tenant tenant only. Specifically, set the destination path to an NFS path.
```
obclient> ALTER SYSTEM SET DATA_BACKUP_DEST = 'file:///data/nfs/backup/data' TENANT = mysql_tenant;
```
In the sys tenant, configure the data backup destination for the mysql_tenant tenant only. Specifically, set the destination path to an OSS path and specify the deletion mode for archive files.
```
obclient> ALTER SYSTEM SET DATA_BACKUP_DEST = 'oss://oceanbase-test-bucket/backup/?host=xxx.aliyun-inc.com&access_id=xxx&access_key=xxx&delete_mode=delete' TENANT = mysql_tenant;
```

In the sys tenant, configure the data backup destination for the mysql_tenant tenant only. Specifically, set the destination path to a COS path and specify the deletion mode for archive files.

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

In the sys tenant, initiate a full data backup for the mysql_tenant tenant only.
```
obclient> ALTER SYSTEM BACKUP TENANT = mysql_tenant;
```
In the sys tenant, initiate an incremental data backup for the mysql_tenant tenant only.
```
obclient> ALTER SYSTEM BACKUP INCREMENTAL TENANT = mysql_tenant;
```
In the sys tenant, stop the ongoing data backup for the mysql_tenant tenant only.
```
obclient> ALTER SYSTEM CANCEL BACKUP TENANT = mysql_tenant;
```

In the primary tenant, use BACKUP DATABASE PLUS ARCHIVELOG to back up data.

Set the log archive destination for the primary tenant and enable log archiving.

obclient> ALTER SYSTEM SET LOG_ARCHIVE_DEST = "LOCATION=file:///data/1/example_archive";
Query OK, 0 rows affected

obclient> ALTER SYSTEM ARCHIVELOG;
Query OK, 0 rows affected

Set the data backup destination. If the primary tenant is a standalone database, you can use an accessible path of the server where the observer process resides as the backup destination path. Otherwise, you need to use a shared storage, such as NFS or OSS, as the backup destination. In this example, the backup destination path is /data/1/example_backup/.
```
obclient> ALTER SYSTEM SET DATA_BACKUP_DEST = 'file:///data/1/example_backup';
Query OK, 0 rows affected
```

Perform data backup.

obclient> ALTER SYSTEM BACKUP DATABASE PLUS ARCHIVELOG;
Query OK, 0 rows affected

Check the backup status. If the backup is being executed, it is recorded in the DBA_OB_BACKUP_JOBS view; otherwise, it is recorded in the DBA_OB_BACKUP_JOB_HISTORY view. In this example, STATUS is COMPLETED, indicating that the backup is completed. The PLUS_ARCHIVELOG column shows the value ON, indicating that the backup is executed by using BACKUP DATABASE PLUS ARCHIVELOG. In this case, the backup directory contains required archived logs that form a database snapshot together with the backup data.

obclient> SELECT * FROM DBA_OB_BACKUP_JOBS;
Empty set

obclient> SELECT * FROM DBA_OB_BACKUP_JOB_HISTORY;
+--------+-------------+---------------+---------------------+------------------+--------------------+-----------------+-------------+-------------+-----------------+--------+----------------------------+----------------------------+-----------+--------+---------+-------------+-------------------------------+
| JOB_ID | INCARNATION | BACKUP_SET_ID | INITIATOR_TENANT_ID | INITIATOR_JOB_ID | EXECUTOR_TENANT_ID | PLUS_ARCHIVELOG | BACKUP_TYPE | JOB_LEVEL   | ENCRYPTION_MODE | PASSWD | START_TIMESTAMP            | END_TIMESTAMP              | STATUS    | RESULT | COMMENT | DESCRIPTION | PATH                          |
+--------+-------------+---------------+---------------------+------------------+--------------------+-----------------+-------------+-------------+-----------------+--------+----------------------------+----------------------------+-----------+--------+---------+-------------+-------------------------------+
|      1 |           1 |             1 |                1004 |                0 | 1004               | ON              | FULL        | USER_TENANT | NONE            |        | 2023-04-07 19:52:35.361398 | 2023-04-07 19:58:57.223196 | COMPLETED |      0 |         |             | file:///data/1/example_backup |
+--------+-------------+---------------+---------------------+------------------+--------------------+-----------------+-------------+-------------+-----------------+--------+----------------------------+----------------------------+-----------+--------+---------+-------------+-------------------------------+
1 row in set

After you use BACKUP DATABASE PLUS ARCHIVELOG to back up data for the primary tenant, if you no longer need log archiving, execute the following statement to disable log archiving:
```
obclient> ALTER SYSTEM NOARCHIVELOG;
Query OK, 0 rows affected
```