查看集群信息
# 查看所有集群
obd cluster list
# 启动集群ob3cluster
obd cluster start ob3cluster
# 停止集群
obd cluster stop ob3cluster
登录数据库
# root用户的sys租户登录数据库
obclient -hxxx.xxx.xxx.xxx -uxxx:sys:root -P2883 -pxxxxxx -c -A -DOceanbase
NFS 配置
mount -tnfs4 -o rw,nfsvers=4.1,sync,lookupcache=positive,hard,timeo=600,wsize=1048576,rsize=1048576,namlen=255 192.168.xx.xx:/storage/ob421 /storage/ob421
nfsvers=4.1 ：由于备份依赖 nfs 4 原生的文件锁，建议使用 nfs 4.1 及以上版本。nfs 4.0 有一个已知 Bug，在重命名文件以后可能会读到旧文件。
sync: 使用同步写保证数据能及时刷到服务端，从而保证数据的一致性.
lookupcache=positive: 用于避免并发访问目录或者文件时误报目录或文件不存在的问题，保证数据的一致性。
hard: 在 NFS 不可用的情况下，系统会卡住应用的读写请求，以保证数据的一致性。不能使用 soft 选项，会有数据错误的风险。
timeo ：用于指定重试的等待时间，单位为 0.1s。在设置时，建议不要设置得过大，建议值为 600 。
wsize ：表示写的数据块大小，建议设置为 1048576 。
rsize ：表示读的数据块大小，建议设置为 1048576 。
namlen ：建议设置为 255 。
192.168.xx.xx ：表示 NFS 服务器的 IP 地址。
日志归档
设置日志归档目录
ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=file:///storage/ob421/archive/mysqlt' TENANT = mysqlt;
设置好之后的目录情况：
[root@localhost mysqlt]# pwd
/storage/ob421/archive/mysqlt
[root@localhost mysqlt]# ll
total 8
drwx------ 2 nfsnobody nfsnobody 4096 Dec 14 09:56 check_file
-rw------- 1 nfsnobody nfsnobody 148 Dec 14 09:56 format.obbak
[root@localhost mysqlt]# ll check_file/
total 4
-rw------- 1 nfsnobody nfsnobody 145 Dec 14 09:56 1002_connect_file_20231214T095621.obbak
[root@localhost mysqlt]#
开启日志归档模式
ALTER SYSTEM ARCHIVELOG TENANT = mysqlt;
但日志归档状态为BEGINNING，多了目录piece_d1001r0p1, rounds：
[root@localhost mysqlt]# ll
total 16
drwx------ 2 nfsnobody nfsnobody 4096 Dec 14 09:56 check_file
-rw------- 1 nfsnobody nfsnobody 148 Dec 14 09:56 format.obbak
drwx------ 4 nfsnobody nfsnobody 4096 Dec 14 10:01 piece_d1001r1p1
drwx------ 2 nfsnobody nfsnobody 4096 Dec 14 10:01 rounds
[root@localhost mysqlt]#
当日志归档状态变成DOING，目录情况：
[root@localhost mysqlt]# ll
total 20
drwx------ 2 nfsnobody nfsnobody 4096 Dec 14 09:56 check_file
-rw------- 1 nfsnobody nfsnobody 148 Dec 14 09:56 format.obbak
drwx------ 5 nfsnobody nfsnobody 4096 Dec 14 10:03 piece_d1001r1p1
drwx------ 2 nfsnobody nfsnobody 4096 Dec 14 10:03 pieces
drwx------ 2 nfsnobody nfsnobody 4096 Dec 14 10:01 rounds
[root@localhost mysqlt]#
关闭日志归档模式
ALTER SYSTEM NOARCHIVELOG TENANT = mysqlt;
查看日志归档进度
SELECT * FROM oceanbase.CDB_OB_ARCHIVELOG\G
刚开始的归档状态是BEGINING:
obclient [Oceanbase]> SELECT * FROM oceanbase.CDB_OB_ARCHIVELOG\G
*************************** 1. row ***************************
TENANT_ID: 1002
DEST_ID: 1001
ROUND_ID: 1
INCARNATION: 1
DEST_NO: 0
STATUS: BEGINNING
START_SCN: 1702519281626895202
START_SCN_DISPLAY: 2023-12-14 10:01:21.626895
CHECKPOINT_SCN: 1702519281626895202
CHECKPOINT_SCN_DISPLAY: 2023-12-14 10:01:21.626895
COMPATIBLE: 1
BASE_PIECE_ID: 1
USED_PIECE_ID: 1
PIECE_SWITCH_INTERVAL: 86400000000
UNIT_SIZE: 1
COMPRESSION: none
INPUT_BYTES: 0
INPUT_BYTES_DISPLAY: 0.00MB
OUTPUT_BYTES: 0
OUTPUT_BYTES_DISPLAY: 0.00MB
COMPRESSION_RATIO: 0.00
DELETED_INPUT_BYTES: 0
DELETED_INPUT_BYTES_DISPLAY: 0.00MB
DELETED_OUTPUT_BYTES: 0
DELETED_OUTPUT_BYTES_DISPLAY: 0.00MB
COMMENT:
PATH: file:///storage/ob421/archive/mysqlt
1 row in set (0.004 sec)
obclient [Oceanbase]>
过一会归档完成，状态就变成DOING了：
obclient [Oceanbase]> SELECT * FROM oceanbase.CDB_OB_ARCHIVELOG\G
*************************** 1. row ***************************
TENANT_ID: 1002
DEST_ID: 1001
ROUND_ID: 1
INCARNATION: 1
DEST_NO: 0
STATUS: DOING
START_SCN: 1702519281626895202
START_SCN_DISPLAY: 2023-12-14 10:01:21.626895
CHECKPOINT_SCN: 1702519401550565270
CHECKPOINT_SCN_DISPLAY: 2023-12-14 10:03:21.550565
COMPATIBLE: 1
BASE_PIECE_ID: 1
USED_PIECE_ID: 1
PIECE_SWITCH_INTERVAL: 86400000000
UNIT_SIZE: 1
COMPRESSION: none
INPUT_BYTES: 69213550
INPUT_BYTES_DISPLAY: 66.01MB
OUTPUT_BYTES: 69213550
OUTPUT_BYTES_DISPLAY: 66.01MB
COMPRESSION_RATIO: 1.00
DELETED_INPUT_BYTES: 0
DELETED_INPUT_BYTES_DISPLAY: 0.00MB
DELETED_OUTPUT_BYTES: 0
DELETED_OUTPUT_BYTES_DISPLAY: 0.00MB
COMMENT:
PATH: file:///storage/ob421/archive/mysqlt
1 row in set (0.003 sec)
obclient [Oceanbase]>
查看归档参数
SELECT * FROM oceanbase.CDB_OB_ARCHIVE_DEST\G
数据备份
设置数据备份目录
ALTER SYSTEM SET DATA_BACKUP_DEST= 'file:///storage/ob421/data/mysqlt' TENANT = mysqlt;
-- 如果想清空备份目录配置，将DATA_BACKUP_DEST置为空即可。
ALTER SYSTEM SET DATA_BACKUP_DEST='' TENANT=mysqlt;
设置数据备份目录之后的目录情况：
[root@localhost mysqlt]# pwd
/storage/ob421/data/mysqlt
[root@localhost mysqlt]# ll
total 8
drwx------ 2 nfsnobody nfsnobody 4096 Dec 14 10:07 check_file
-rw------- 1 nfsnobody nfsnobody 145 Dec 14 10:07 format.obbak
[root@localhost mysqlt]# ll check_file/
total 4
-rw------- 1 nfsnobody nfsnobody 142 Dec 14 10:07 1002_connect_file_20231214T100726.obbak
[root@localhost mysqlt]#
发起全量数据备份
ALTER SYSTEM BACKUP TENANT=mysqlt;
发起全量数据备份之后的目录结构：
[root@localhost mysqlt]# ll
total 16
drwx------ 5 nfsnobody nfsnobody 4096 Dec 14 10:14 backup_set_1_full
drwx------ 2 nfsnobody nfsnobody 4096 Dec 14 10:14 backup_sets
drwx------ 2 nfsnobody nfsnobody 4096 Dec 14 10:07 check_file
-rw------- 1 nfsnobody nfsnobody 145 Dec 14 10:07 format.obbak
发起增量数据备份
ALTER SYSTEM BACKUP INCREMENTAL TENANT=mysqlt;
增量数据备份之后的目录结构：
[root@localhost mysqlt]# ll
total 20
drwx------ 5 nfsnobody nfsnobody 4096 Dec 14 10:14 backup_set_1_full
drwx------ 5 nfsnobody nfsnobody 4096 Dec 14 10:21 backup_set_2_inc
drwx------ 2 nfsnobody nfsnobody 4096 Dec 14 10:21 backup_sets
drwx------ 2 nfsnobody nfsnobody 4096 Dec 14 10:07 check_file
-rw------- 1 nfsnobody nfsnobody 145 Dec 14 10:07 format.obbak
停止备份
ALTER SYSTEM CANCEL BACKUP TENANT = mysqlt;
停止的时候，备份状态为：CANCELING -> CANCELED
查看数据备份进度
SELECT * FROM oceanbase.CDB_OB_BACKUP_JOBS\G
备份进度，状态会从 INIT -> DOING -> COMPLETED, 结束之后再查询 oceanbase.CDB_OB_BACKUP_JOBS会变为空。
obclient [Oceanbase]> SELECT * FROM oceanbase.CDB_OB_BACKUP_JOBS\G
*************************** 1. row ***************************
TENANT_ID: 1
JOB_ID: 1
INCARNATION: 1
BACKUP_SET_ID: 0
INITIATOR_TENANT_ID: 1
INITIATOR_JOB_ID: 1
EXECUTOR_TENANT_ID: 1002
PLUS_ARCHIVELOG: OFF
BACKUP_TYPE: FULL
JOB_LEVEL: CLUSTER
ENCRYPTION_MODE: NONE
PASSWD:
START_TIMESTAMP: 2023-12-14 10:11:19.263166
END_TIMESTAMP: NULL
STATUS: DOING
RESULT: 0
COMMENT:
DESCRIPTION:
PATH:
*************************** 2. row ***************************
TENANT_ID: 1002
JOB_ID: 1
INCARNATION: 1
BACKUP_SET_ID: 1
INITIATOR_TENANT_ID: 1
INITIATOR_JOB_ID: 1
EXECUTOR_TENANT_ID: 1002
PLUS_ARCHIVELOG: OFF
BACKUP_TYPE: FULL
JOB_LEVEL: USER_TENANT
ENCRYPTION_MODE: NONE
PASSWD:
START_TIMESTAMP: 2023-12-14 10:11:19.273702
END_TIMESTAMP: NULL
STATUS: DOING
RESULT: 0
COMMENT:
DESCRIPTION:
PATH: file:///storage/ob421/data/mysqlt
2 rows in set (0.003 sec)
查看数据备份结果
SELECT * FROM oceanbase.CDB_OB_BACKUP_JOB_HISTORY\G
数据备份结果：
obclient [Oceanbase]> SELECT * FROM oceanbase.CDB_OB_BACKUP_JOB_HISTORY\G
*************************** 1. row ***************************
TENANT_ID: 1
JOB_ID: 1
INCARNATION: 1
BACKUP_SET_ID: 0
INITIATOR_TENANT_ID: 1
INITIATOR_JOB_ID: 1
EXECUTOR_TENANT_ID: 1002
PLUS_ARCHIVELOG: OFF
BACKUP_TYPE: FULL
JOB_LEVEL: CLUSTER
ENCRYPTION_MODE: NONE
PASSWD:
START_TIMESTAMP: 2023-12-14 10:11:19.263166
END_TIMESTAMP: 2023-12-14 10:14:19.320221
STATUS: COMPLETED
RESULT: 0
COMMENT:
DESCRIPTION:
PATH:
*************************** 2. row ***************************
TENANT_ID: 1002
JOB_ID: 1
INCARNATION: 1
BACKUP_SET_ID: 1
INITIATOR_TENANT_ID: 1
INITIATOR_JOB_ID: 1
EXECUTOR_TENANT_ID: 1002
PLUS_ARCHIVELOG: OFF
BACKUP_TYPE: FULL
JOB_LEVEL: USER_TENANT
ENCRYPTION_MODE: NONE
PASSWD:
START_TIMESTAMP: 2023-12-14 10:11:19.273702
END_TIMESTAMP: 2023-12-14 10:14:12.823506
STATUS: COMPLETED
RESULT: 0
COMMENT:
DESCRIPTION:
PATH: file:///storage/ob421/data/mysqlt
2 rows in set (0.003 sec)
查看租户备份路径信息
SELECT * FROM oceanbase.CDB_OB_BACKUP_PARAMETER\G
返回信息：
obclient [Oceanbase]> SELECT * FROM oceanbase.CDB_OB_BACKUP_PARAMETER\G
*************************** 1. row ***************************
TENANT_ID: 1002
NAME: data_backup_dest
VALUE: file:///storage/ob421/data/mysqlt
1 row in set (0.009 sec)
obclient [Oceanbase]>
备份集清理策略
-- 创建清理策略，名字叫policy_1, 可恢复窗口是7天，也可以设置其他比如：2h, 3w
ALTER SYSTEM ADD DELETE BACKUP POLICY 'policy_1' RECOVERY_WINDOW
'7d' TENANT MySQL;
-- 查看清理策略
SELECT * FROM oceanbase.CDB_OB_BACKUP_DELETE_POLICY;
-- 删除清理策略
ALTER SYSTEM DROP DELETE BACKUP POLICY policy_name
TENANT tenant_name;
恢复数据
恢复前准备
恢复前需要创建出资源单元和资源池，恢复出来的新租户要使用该资源池。
发起恢复
-- 恢复到指定时间戳
ALTER SYSTEM RESTORE dest_tenant_name FROM uri UNTIL TIME={timestamp} WITH 'pool_list=restore_pool';
-- 恢复到指定SCN
ALTER SYSTEM RESTORE dest_tenant_name FROM uri UNTIL SCN={scn} WITH 'pool_list=restore_pool';
-- 恢复到最新位点
ALTER SYSTEM RESTORE mysqlt_restore FROM 'file:///storage/data/mysqlt, file:///storage/archive/mysqlt' WITH 'pool_list=restore_pool';
取消恢复
ALTER SYSTEM CANCEL RESTORE tenant_name;
查看恢复进度
oceanbase.CDB_OB_RESTORE_PROGRESS这个视图只有在有恢复任务的时候才有数据，如果没有恢复任务，该视图内容为空。
SELECT * FROM oceanbase.CDB_OB_RESTORE_PROGRESS;
查看恢复结果
SELECT * FROM oceanbase.CDB_OB_RESTORE_HISTORY;
其他命令
ZONE
-- 获取集群zone信息
SELECT * FROM oceanbase.DBA_OB_ZONES;
租户
-- 查询租户
select tenant_id, tenant_name from __all_tenant;
-- 删除租户
drop tenant tenant_name1;
资源单元
-- 获取资源单元
select * from DBA_OB_UnitS;
SELECT * FROM oceanbase.__all_unit_config;
-- 创建资源单元
CREATE RESOURCE UNIT restore_unit MAX_CPU 1.5, MEMORY_SIZE = '5G', MAX_IOPS 10240, MIN_IOPS=10240;
资源池
-- 获取资源池
SELECT * FROM DBA_OB_RESOURCE_POOLS;
SELECT * FROM oceanbase.__all_resource_pool;
-- 创建资源池
CREATE RESOURCE POOL restore_pool unit = 'restore_unit', unit_num = 1, zone_list = ('zone1');