OB 企业版 3.2 备份恢复实践

数据库技术闲谈 2023-06-13

1412

概述

本文主要分享 OceanBase 企业版 3.2 在 SQL 命令行下的集群备份和不完全恢复（恢复到任意时间点）验证。

文中使用的是前面在小资源规格的虚拟机上搭建的 OB 集群。跟传统数据库一样，OB 支持数据的全量备份、增量备份和事务日志的备份（归档）。

不一样的是 OB 是分布式数据库集群，有多副本。备份的时候可能多个节点同时向备份目录写备份文件。所以，OB 的备份介质需要能被各个节点共享读写。目前仅支持 NFS 目录和阿里云 OSS 目录。理论上未来支持 AWS 的 S3 和腾讯云的云存储应该也是有可能的。

3.2 版本的 OB 备份是以集群粒度进行备份，还原恢复的时候是以租户为粒度。在 4.x 版本 OB支持了租户的备份。

OB 的备份恢复相关的视图非常丰富，相关的使用文档也很齐全。

本次实践是将一个三副本架构的 OB 集群备份，并将里面一个 MySQL 租户还原到一个单副本架构的新租户中。新租户就在当前 OB 集群中（也可以在其他独立的 OB 集群中）。OB 租户还原不支持覆盖形式恢复，可以恢复出新名字的租户。如果需要替换原租户，可以通过租户重命名的方式。

掌握 OB 手动备份恢复技能也是理解 OCP 对 OB 的自动化备份恢复功能的基础，这部分也是 OBCP 和 OBCE 操作必考内容。

备份介质准备

OB 的备份恢复支持 NFS 目录和 OSS 目录。线下测试环境只有 NFS 技术可用。

（可选）挂载 NAS 盘

这步通过虚拟机软件提供的 NAS 存储用来做 NFS 的盘。NAS 通常支持 NFS 协议和 iSCSI 协议读写。由于我这里的 NAS 不知道为什么不支持 NFS 协议，所以我改用 iSCSI 协议连接 NAS 盘，并通过服务器 65 部署一个 NFS Server。iSCISI 协议不支持并发读写。

安装 iSCSI 相关软件。

sudo yum -y install iscsi-initiator-utils
sudo systemctl start iscsid
sudo systemctl status iscsid




[root@server065 obproxy-master]# systemctl status iscsid
? iscsid.service - Open-iSCSI
   Loaded: loaded (/usr/lib/systemd/system/iscsid.service; disabled; vendor preset: disabled)
   Active: active (running) since Mon 2023-06-12 14:21:54 CST; 4s ago
     Docs: man:iscsid(8)
           man:iscsiuio(8)
           man:iscsiadm(8)
 Main PID: 28743 (iscsid)
   Status: "Ready to process requests"
    Tasks: 1
   Memory: 1.6M
   CGroup: /system.slice/iscsid.service
           └─28743 /sbin/iscsid -f




Jun 12 14:21:54 server065 systemd[1]: Starting Open-iSCSI...
Jun 12 14:21:54 server065 systemd[1]: Started Open-iSCSI.

（可选）设置 CHAP 认证。

如果 NAS 盘设置了 CHAP 认证，那么客户端需要配置用户名和密码。

vi etc/iscsi/iscsid.conf




node.session.auth.authmethod = CHAP
node.session.auth.username = username
node.session.auth.password = password

发现 iSCSI 卷。

iscsiadm -m discovery -t st -p 10.0.0.205:3260




[root@server065 obproxy-master]# iscsiadm -m discovery -t st -p 10.0.0.205:3260
10.0.0.205:3260,1 iqn.2000-01.com.synology:DS918.default-target.392e48dbfa5
[fe80::211:32ff:fec5:b34f]:3260,1 iqn.2000-01.com.synology:DS918.default-target.392e48dbfa5

挂载 iSCSI 卷。

iscsiadm -m node -T iqn.2000-01.com.synology:DS918.default-target.392e48dbfa5 -p 10.0.0.205:3260 -l




[root@server065 obproxy-master]# iscsiadm -m node -T iqn.2000-01.com.synology:DS918.default-target.392e48dbfa5 -p 10.0.0.205 -l
Logging in to [iface: default, target: iqn.2000-01.com.synology:DS918.default-target.392e48dbfa5, portal: 10.0.0.205,3260] (multiple)
Login to [iface: default, target: iqn.2000-01.com.synology:DS918.default-target.392e48dbfa5, portal: 10.0.0.205,3260] successful.

注意：由于iSCSI 协议限制，请勿将一个 iSCSI 卷挂载到多个 Linux 客户端上。

查看 iSCSI 卷。

[root@server065 obproxy-master]# ls -lrth dev/sd* |tail -n 1
brw-rw----. 1 root disk 8, 32 Jun 12 14:26 dev/sdc
[root@server065 obproxy-master]# lsblk dev/sdc
NAME MAJ:MIN RM  SIZE RO TYPE MOUNTPOINT
sdc    8:32   0  200G  0 disk

后面的方法就跟本地盘一样了，先格式化，然后挂载文件系统。

mkfs.ext4 dev/sdc
mkdir -p backup
mount -t noatime,nodealloc,nodiratime,default dev/sdc backup

（可选）卸载 iSCSI 卷。

iscsiadm -m node -T iqn.2000-01.com.synology:DS918.default-target.392e48dbfa5 -p 10.0.0.205:3260 -u




[root@server061 ~]# iscsiadm -m node -T iqn.2000-01.com.synology:DS918.default-target.392e48dbfa5 -p 10.0.0.205:3260 -u
Logging out of session [sid: 1, target: iqn.2000-01.com.synology:DS918.default-target.392e48dbfa5, portal: 10.0.0.205,3260]
Logout of [sid: 1, target: iqn.2000-01.com.synology:DS918.default-target.392
e48dbfa5, portal: 10.0.0.205,3260] successful.

部署 NFS Server

NFS 服务器的 CPU 和内存不要太小，服务器要稳定。否则 NFS 服务器自身的性能问题可能会影响挂载该 NFS 目录的节点的稳定运行。

安装 NFS 软件

sudo yum -y install nfs-utils rpcbind

sudo vim etc/exports
/backup  10.0.0.65/16(rw,sync,all_squash)

设置 NFS Exports 目录的所有者为 nfsnobody 。

sudo chown nfsnobody:nfsnobody -R backup

配置 NFS 参数。

这一步照做。

sudo vim etc/sysconfig/nfs
RPCNFSDARGS="-N 2 -N 3 -U"
RPCNFSDCOUNT=16
NFSD_V4_GRACE=90
NFSD_V4_LEASE=90

重启 NFS 服务。

sudo systemctl restart nfs-config
sudo systemctl restart nfs-server

设置 Slot Table

vim + /etc/sysctl.conf
sunrpc.tcp_max_slot_table_entries=128

报错处理：

[root@server065 ~]# sysctl -p
sysctl: cannot stat proc/sys/sunrpc/tcp_max_slot_table_entries: No such file or directory
[root@server065 ~]# modprobe sunrpc
[root@server065 ~]# lsmod|grep rpc
sunrpc                366617  0
[root@server065 ~]# sysctl -p
sunrpc.tcp_max_slot_table_entries = 128

部署 NFS 客户端

OB 集群所有节点都要求部署 NFS 客户端。

为了减轻工作量，这里使用 ansible 操作。

vim + etc/ansible/hosts




[dbservers]
10.0.0.61
10.0.0.62
10.0.0.63

后面使用 ansible 批量操作 OB 集群节点。

安装 NFS 软件

ansible dbservers -m shell -a "sudo yum -y install nfs-utils "

设置 Slot Table

这里使用 ansible 批量操作 OB 集群节点。

ansible dbservers -m shell -a "modprobe sunrpc"
ansible dbservers -m shell -a "sysctl -w sunrpc.tcp_max_slot_table_entries=128"
ansible dbservers -m shell -a "sudo sysctl -a 2>/dev/null |grep sunrpc.tcp_max_slot_table_entries "
ansible dbservers -m shell -a "echo 'sunrpc.tcp_max_slot_table_entries=128'

 >> etc/sysctl.conf "

确认一下各个 OB 节点能发现 NFS 目录。

[root@server065 ~]# ansible dbservers -m shell -a "showmount -e 10.0.0.65"
10.0.0.62 | CHANGED | rc=0 >>
Export list for 10.0.0.65:
/backup 10.0.0.65/16
10.0.0.63 | CHANGED | rc=0 >>
Export list for 10.0.0.65:
/backup 10.0.0.65/16
10.0.0.61 | CHANGED | rc=0 >>
Export list for 10.0.0.65:
/backup 10.0.0.65/16
[root@server065 ~]#

各个 OB 节点挂载 NFS 目录到本地文件系统目录。注意挂载选项要求。

ansible dbservers -m shell -a "mkdir -p backup"
ansible dbservers -m shell -a "sudo mount -tnfs4 -o rw,nfsvers=4.1,sync,lookupcache=positive,hard,timeo=600,wsize=1048576,rsize=1048576,namlen=255 10.0.0.65:/backup backup"
ansible dbservers -m shell -a "mount |grep backup"

NFS 协议支持多点写入。

date > /tmp/tmpfile
ansible dbservers -m copy -a "src=/tmp/tmpfile dest=/backup/"
ansible dbservers -m shell -a "ls -lrth backup/ && cat backup/tmpfile"

多点读写测试通过。

测试 NFS 性能

登录其中一个 OB 集群节点 61 ，用 FIO 测试 NFS 目录读写性能。

fio -filename=/backup/fio-file.test -direct=1  -rw=randwrite -bs=2048K -size=100G  -runtime=300 -group_reporting -name=mytest  -ioengine=libaio -numjobs=1 -iodepth=64 -iodepth_batch=8 -iodepth_low=8 -iodepth_batch_complete=8

结果如下：

[root@server061 ~]# fio -filename=/backup/fio-file.test -direct=1  -rw=randwrite -bs=2048K -size=100G  -runtime=300 -group_reporting -name=mytest  -ioengine=libaio -numjobs=1 -iodepth=64 -iodepth_batch=8 -iodepth_low=8 -iodepth_batch_complete=8
mytest: (g=0): rw=randwrite, bs=(R) 2048KiB-2048KiB, (W) 2048KiB-2048KiB, (T) 2048KiB-2048KiB, ioengine=libaio, iodepth=64
fio-3.13
Starting 1 process
mytest: Laying out IO file (1 file  102400MiB)
Jobs: 1 (f=1): [w(1)][100.0%][w=140MiB/s][w=70 IOPS][eta 00m:00s]
mytest: (groupid=0, jobs=1): err= 0: pid=25490: Mon Jun 12 15:53:33 2023
  write: IOPS=71, BW=143MiB/s (150MB/s)(42.0GiB/300168msec)
    slat (usec): min=1273, max=84113, avg=4363.27, stdev=4352.55
    clat (msec): min=114, max=1445, avg=537.07, stdev=231.09
     lat (msec): min=117, max=1447, avg=541.43, stdev=231.36
    clat percentiles (msec):
     |  1.00th=[  150],  5.00th=[  180], 10.00th=[  220], 20.00th=[  326],
     | 30.00th=[  384], 40.00th=[  468], 50.00th=[  535], 60.00th=[  600],
     | 70.00th=[  659], 80.00th=[  726], 90.00th=[  835], 95.00th=[  927],
     | 99.00th=[ 1133], 99.50th=[ 1200], 99.90th=[ 1401], 99.95th=[ 1435],
     | 99.99th=[ 1452]
   bw (  KiB/s): min=32702, max=229376, per=99.68%, avg=146303.09, stdev=37605.19, samples=600
   iops        : min=   15, max=  112, avg=71.10, stdev=18.42, samples=600
  lat (msec)   : 250=12.37%, 500=32.01%, 750=38.43%, 1000=14.33%, 2000=2.87%
  cpu          : usr=1.32%, sys=2.35%, ctx=8653, majf=0, minf=131171
  IO depths    : 1=0.0%, 2=0.0%, 4=0.0%, 8=0.1%, 16=28.6%, 32=57.1%, >=64=14.3%
     submit    : 0=0.0%, 4=0.0%, 8=100.0%, 16=0.0%, 32=0.0%, 64=0.0%, >=64=0.0%
     complete  : 0=0.0%, 4=0.0%, 8=100.0%, 16=0.0%, 32=0.0%, 64=0.0%, >=64=0.0%
     issued rwts: total=0,21512,0,0 short=0,0,0,0 dropped=0,0,0,0
     latency   : target=0, window=0, percentile=100.00%, depth=64




Run status group 0 (all jobs):
  WRITE: bw=143MiB/s (150MB/s), 143MiB/s-143MiB/s (150MB/s-150MB/s), io=42.

0GiB (45.1GB), run=300168-300168msec

读写性能在 150MB/s 左右。

在 NFS 服务器上使用 tsar 观察 NFS 盘和网络读写性能。

tsar --cpu --traffic --load --io -I sdc -s user,sys,wait,util,bytin,bytout,rs,ws,rsecs,wsecs,rawait,wawait,rarqsz,warqsz,svctm,load1,load5,load15 -l -i 3

这个可能是网络上到了瓶颈。查看网卡速率是千兆网卡。

[root@server065 ~]# ethtool ens192 |grep -i speed
        Speed: 1000Mb/s

生产环境建议用万兆网络，并且 NFS 服务器 IP 网段跟 OB 集群网段建议分开（走不同的网卡和交换机）。

OB 集群手动备份

OB 版本是企业版 OceanBase-v3.2.4.1 。

配置 OB 备份

使用 root 登录 SYS 租户，通过 OB 集群参数配置备份目录和选项。

ALTER SYSTEM SET backup_dest='file:///backup/obbackup';
show parameters like 'backup_dest';




(root@10.0.0.61:2883) [oceanbase]> ALTER SYSTEM SET backup_dest='file:///backup/obbackup';
Query OK, 0 rows affected (0.09 sec)




(root@10.0.0.61:2883) [oceanbase]> show parameters like 'backup_dest';
+-------+----------+-----------+----------+-------------+-----------+-------------------------+-------------+----------+---------+---------+-------------------+
| zone  | svr_type | svr_ip    | svr_port | name        | data_type | value                   | info        | section  | scope   | source  | edit_level        |
+-------+----------+-----------+----------+-------------+-----------+-------------------------+-------------+----------+---------+---------+-------------------+
| ZONE3 | observer | 10.0.0.63 |     2882 | backup_dest | NULL      | file:///backup/obbackup | backup dest | OBSERVER | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE |
| ZONE2 | observer | 10.0.0.62 |     2882 | backup_dest | NULL      | file:///backup/obbackup | backup dest | OBSERVER | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE |
| zone1 | observer | 10.0.0.61 |     2882 | backup_dest | NULL      | file:///backup/obbackup | backup dest | OBSERVER | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE |
+-------+----------+-----------+----------+-------------+-----------+-------------------------+-------------+----------+---------+---------+-------------------+
3 rows in set (0.05 sec)

配置 OB 备份参数。

可以通过配置项 backup_dest_option 设置备份相关的参数。

详情参考：备份前准备-OceanBase 数据库 -OceanBase文档中心-分布式数据库使用文档

ALTER SYSTEM SET backup_dest_option='log_archive_checkpoint_interval=2m&recovery_window=7d&auto_delete_obsolete_backup=true&log_archive_piece_switch_interval=1d&backup_copies=0';
show parameters like 'backup_dest_option';

发起日志备份

为了能实现恢复到任意时间点目标，首先要做事务日志备份。

配置备份模式。

备份模式支持 Optional （默认）和 Mandatory 模式。

Optional
模式表示以用户业务优先。在该模式下，当备份（日志归档）来不及的情况下，日志可能来不及备份就回收了，可能会发生备份断流。
Mandatory
模式表示以备份优先。在该模式下如果备份跟不上用户数据的写入，可能会导致用户无法写入。

事务日志的备份也叫日志归档。日志归档时可以开启压缩，压缩算法支持 zstd_1.3.8 和 lz4_1.0 （默认）。后者时默认压缩算法，压缩效果和性能比较平衡。

ALTER SYSTEM SET backup_log_archive_option = 'optional compression= enable';

设置后可以动态修改压缩算法。

ALTER SYSTEM SET backup_log_archive_option='optional compression= zstd_1.3.8';

详情请参考：发起日志备份-OceanBase 数据库 -OceanBase文档中心-分布式数据库使用文档

开启日志归档（备份）：

ALTER SYSTEM ARCHIVELOG;

查看日志归档备份状态：

SELECT INCARNATION , LOG_ARCHIVE_ROUND , TENANT_ID , STATUS , START_PIECE_ID , BACKUP_PIECE_ID , MIN_FIRST_TIME  
  FROM CDB_OB_BACKUP_ARCHIVELOG;

INCARNATION	LOG_ARCHIVE_ROUND	TENANT_ID	STATUS	START_PIECE_ID	BACKUP_PIECE_ID	MIN_FIRST_TIME
1	1	1	DOING	1	1	2023-06-12 16:40:43.467257
1	1	1,001	DOING	1	1	2023-06-12 16:40:43.467257
1	1	1,002	DOING	1	1	2023-06-12 16:40:43.467257

SELECT INCARNATION , TENANT_ID ,ROUND_ID ,BACKUP_PIECE_ID ,COPY_ID ,CREATE_DATE ,START_TS ,CHECKPOINT_TS ,STATUS ,FILE_STATUS ,START_PIECE_ID 
  FROM CDB_OB_BACKUP_PIECE_FILES;

INCARNATION	TENANT_ID	ROUND_ID	BACKUP_PIECE_ID	COPY_ID	CREATE_DATE	START_TS	CHECKPOINT_TS	STATUS	FILE_STATUS	START_PIECE_ID
1	1	1	1	0	20,230,612	2023-06-12 16:40:43.467257	2023-06-12 16:42:37.819554	ACTIVE	AVAILABLE	1
1	1,001	1	1	0	20,230,612	2023-06-12 16:40:43.467257	2023-06-12 16:42:37.819554	ACTIVE	AVAILABLE	1
1	1,002	1	1	0	20,230,612	2023-06-12 16:40:43.467257	2023-06-12 16:42:37.819554	ACTIVE	AVAILABLE	1

发起数据备份

日志备份开始后，才可以发起全量数据备份。

(可选）设置备份密码。

备份的时候可以指定备份密码，会话级别生效。

SET ENCRYPTION ON IDENTIFIED BY 'aaAA11__' ONLY;

发起全量备份。

ALTER SYSTEM BACKUP DATABASE;

查看正在备份的任务。

SELECT INCARNATION , BS_KEY , BACKUP_TYPE , TENANT_ID , PARTITION_COUNT , MACRO_BLOCK_COUNT ,FINISH_PARTITION_COUNT ,FINISH_MACRO_BLOCK_COUNT , INPUT_BYTES ,OUTPUT_BYTES , START_TIME , COMPLETION_TIME ,status  
  FROM oceanbase.CDB_OB_BACKUP_PROGRESS;

INCARNATION	BS_KEY	BACKUP_TYPE	TENANT_ID	PARTITION_COUNT	MACRO_BLOCK_COUNT	FINISH_PARTITION_COUNT	FINISH_MACRO_BLOCK_COUNT	INPUT_BYTES	OUTPUT_BYTES	START_TIME	COMPLETION_TIME	status
1	2	D	1	0	0	0	0	0	0	2023-06-12 16:55:50.655	2023-06-12 16:55:51.366	RUNNING
1	2	D	1,001	382	0	0	0	0	0	2023-06-12 16:55:50.655	2023-06-12 16:55:51.289	RUNNING
1	2	D	1,002	396	244	115	244	511,827,836	353,543,492	2023-06-12 16:55:50.655	2023-06-12 16:55:51.169	RUNNING

查看备份的文件历史。

SELECT BS_KEY, INCARNATION , TENANT_ID , COPY_ID , BACKUP_TYPE , ENCRYPTION_MODE ,STATUS ,FILE_STATUS ,START_TIME , COMPLETION_TIME , ELAPSED_SECONDES , COMPRESSED , OUTPUT_BYTES , COMPRESSION_RATIO , OUTPUT_RATE_BYTES_DISPLAY 
FROM oceanbase.CDB_OB_BACKUP_SET_FILES
ORDER BY BS_KEY DESC, TENANT_ID  ;

BS_KEY	INCARNATION	TENANT_ID	COPY_ID	BACKUP_TYPE	ENCRYPTION_MODE	STATUS	FILE_STATUS	START_TIME	COMPLETION_TIME	ELAPSED_SECONDES	COMPRESSED	OUTPUT_BYTES	COMPRESSION_RATIO	OUTPUT_RATE_BYTES_DISPLAY
3	1	1	0	D	PASSWORD	SUCCESS	AVAILABLE	2023-06-12 17:01:23.063	2023-06-12 17:03:59.704	157	NO	8,827,456,562	0.89	53.74MB/S
3	1	1,001	0	D	PASSWORD	SUCCESS	AVAILABLE	2023-06-12 17:01:23.063	2023-06-12 17:03:49.287	146	NO	4,402,999,690	0.89	28.72MB/S
3	1	1,002	0	D	PASSWORD	SUCCESS	AVAILABLE	2023-06-12 17:01:23.063	2023-06-12 17:03:58.703	156	NO	4,424,456,872	0.9	27.11MB/S
2	1	1	0	D	NONE	SUCCESS	AVAILABLE	2023-06-12 16:55:50.655	2023-06-12 16:58:25.377	155	NO	8,827,030,358	0.89	54.41MB/S
2	1	1,001	0	D	NONE	SUCCESS	AVAILABLE	2023-06-12 16:55:50.655	2023-06-12 16:58:14.711	144	NO	4,402,771,997	0.89	29.15MB/S
2	1	1,002	0	D	NONE	SUCCESS	AVAILABLE	2023-06-12 16:55:50.655	2023-06-12 16:58:22.782	152	NO	4,424,258,361	0.89	27.74MB/S
1	1	1	0	D	NONE	SUCCESS	AVAILABLE	2023-06-12 16:53:03.003	2023-06-12 16:55:25.685	143	NO	8,825,824,213	0.9	58.99MB/S
1	1	1,001	0	D	NONE	SUCCESS	AVAILABLE	2023-06-12 16:53:03.003	2023-06-12 16:55:14.511	132	NO	4,402,220,571	0.9	31.92MB/S
1	1	1,002	0	D	NONE	SUCCESS	AVAILABLE	2023-06-12 16:53:03.003	2023-06-12 16:55:23.815	141	NO	4,423,603,642	0.9	29.96MB/S

发起增量备份。

增量备份时自最近一次全量备份开始后所有修改过的宏块的备份。

ALTER SYSTEM BACKUP INCREMENTAL DATABASE;

BS_KEY

INCARNATION

TENANT_ID

COPY_ID

BACKUP_TYPE

ENCRYPTION_MODE

STATUS

FILE_STATUS

START_TIME

COMPLETION_TIME

ELAPSED_SECONDES

COMPRESSED

OUTPUT_BYTES

COMPRESSION_RATIO

OUTPUT_RATE_BYTES_DISPLAY

PASSWORD

SUCCESS

AVAILABLE

2023-06-12 17:19:35.910

2023-06-12 17:21:13.262

47,289,547

0.31

0.46MB/S

1,001

PASSWORD

SUCCESS

AVAILABLE

2023-06-12 17:19:35.910

2023-06-12 17:21:11.064

9,376,926

0.14

0.09MB/S

1,002

PASSWORD

SUCCESS

AVAILABLE

2023-06-12 17:19:35.910

2023-06-12 17:21:10.961

37,912,621

0.43

0.38MB/S

校验备份数据

物理备份成功后，可以根据需要对数据备份和日志备份进行校验。

校验本集群所有备份集和对应的日志。

ALTER SYSTEM VALIDATE DATABASE;




(root@10.0.0.61:2883) [oceanbase]> ALTER SYSTEM VALIDATE DATABASE;
Query OK, 0 rows affected (0.04 sec)

校验任务是异步发起的，所以上面命令立即返回。可以查看数据校验任务进度和结果。

查看正在进行的数据校验任务进度和历史任务进度。

SELECT job_id, tenant_id, TENANT_NAME, INCARNATION , BACKUP_SET_ID , PROGRESS_PERCENT , STATUS ,VALIDATE_TYPE ,BACKUP_DEST ,START_TIME ,END_TIME ,`RESULT` , COMMENT  
FROM CDB_OB_BACKUP_VALIDATION_JOB;
SELECT job_id, tenant_id, TENANT_NAME, INCARNATION , BACKUP_SET_ID , PROGRESS_PERCENT , STATUS ,VALIDATE_TYPE ,BACKUP_DEST ,START_TIME ,END_TIME ,`RESULT` , COMMENT  
  FROM CDB_OB_BACKUP_VALIDATION_JOB_HISTORY;

job_id

tenant_id

TENANT_NAME

INCARNATION

BACKUP_SET_ID

PROGRESS_PERCENT

STATUS

VALIDATE_TYPE

BACKUP_DEST

START_TIME

END_TIME

RESULT

COMMENT

100

FINISH

2023-06-12 17:47:50.546

2023-06-12 17:52:16.400

查看正在进行的数据校验任务详情和历史任务详情。

SELECT job_id, task_id, TENANT_ID , INCARNATION , BACKUP_SET_ID ,VALIDATE_TYPE ,STATUS ,BACKUP_DEST , START_TIME ,END_TIME ,TOTAL_PG_COUNT ,FINISH_PG_COUNT , TOTAL_PARTITION_COUNT ,FINISH_PARTITION_COUNT ,RESULT, COMMENT 
  FROM CDB_OB_TENANT_BACKUP_VALIDATION_TASK
ORDER BY START_TIME  DESC ;
SELECT job_id, task_id, TENANT_ID , INCARNATION , BACKUP_SET_ID ,VALIDATE_TYPE ,STATUS ,BACKUP_DEST , START_TIME ,END_TIME ,TOTAL_PG_COUNT ,FINISH_PG_COUNT , TOTAL_PARTITION_COUNT ,FINISH_PARTITION_COUNT ,RESULT, COMMENT 
  FROM CDB_OB_BACKUP_VALIDATION_TASK_HISTORY
ORDER BY START_TIME  DESC ;

job_id	task_id	TENANT_ID	INCARNATION	BACKUP_SET_ID	VALIDATE_TYPE	STATUS	BACKUP_DEST	START_TIME	END_TIME	TOTAL_PG_COUNT	FINISH_PG_COUNT	TOTAL_PARTITION_COUNT	FINISH_PARTITION_COUNT	RESULT	COMMENT
1	0	1,002	1	4	0	FINISH	file:///backup/obbackup	2023-06-12 17:47:50.678	2023-06-12 17:52:15.913	398	398	396	396	0
1	0	1,002	1	3	0	FINISH	file:///backup/obbackup	2023-06-12 17:47:50.673	2023-06-12 17:52:15.900	398	398	396	396	0
1	0	1,002	1	2	0	FINISH	file:///backup/obbackup	2023-06-12 17:47:50.667	2023-06-12 17:52:15.879	398	398	396	396	0
1	0	1,002	1	1	0	FINISH	file:///backup/obbackup	2023-06-12 17:47:50.656	2023-06-12 17:52:15.819	398	398	396	396	0
1	0	1,001	1	4	0	FINISH	file:///backup/obbackup	2023-06-12 17:47:50.651	2023-06-12 17:52:16.022	382	382	382	382	0
1	0	1,001	1	3	0	FINISH	file:///backup/obbackup	2023-06-12 17:47:50.648	2023-06-12 17:52:16.002	382	382	382	382	0
1	0	1,001	1	2	0	FINISH	file:///backup/obbackup	2023-06-12 17:47:50.644	2023-06-12 17:52:15.981	382	382	382	382	0
1	0	1,001	1	1	0	FINISH	file:///backup/obbackup	2023-06-12 17:47:50.640	2023-06-12 17:52:15.934	382	382	382	382	0
1	0	1	1	4	0	FINISH	file:///backup/obbackup	2023-06-12 17:47:50.635	2023-06-12 17:47:50.635	778	0	778	0	0
1	0	1	1	3	0	FINISH	file:///backup/obbackup	2023-06-12 17:47:50.631	2023-06-12 17:47:50.631	778	0	778	0	0
1	0	1	1	2	0	FINISH	file:///backup/obbackup	2023-06-12 17:47:50.628	2023-06-12 17:47:50.628	778	0	778	0	0
1	0	1	1	1	0	FINISH	file:///backup/obbackup	2023-06-12 17:47:50.617	2023-06-12 17:47:50.620	778	0	778	0	0

OB 集群也可以针对具体的备份集或文件进行校验。

ALTER SYSTEM VALIDATE BACKUPSET 4 ;
ALTER SYSTEM VALIDATE BACKUPPIECE 1;

job_id	tenant_id	TENANT_NAME	INCARNATION	BACKUP_SET_ID	PROGRESS_PERCENT	STATUS	VALIDATE_TYPE	BACKUP_DEST	START_TIME	END_TIME	RESULT	COMMENT
3	1		1	1	100	FINISH	2		2023-06-12 18:01:41.616	2023-06-12 18:02:19.656	0
2	1		1	4	100	FINISH	1		2023-06-12 18:00:16.658	2023-06-12 18:00:40.488	0

job_id	task_id	TENANT_ID	INCARNATION	BACKUP_SET_ID	VALIDATE_TYPE	STATUS	BACKUP_DEST	START_TIME	END_TIME	TOTAL_PG_COUNT	FINISH_PG_COUNT	TOTAL_PARTITION_COUNT	FINISH_PARTITION_COUNT	RESULT	COMMENT
3	0	1,002	1	1	2	FINISH	file:///backup/obbackup	2023-06-12 18:01:41.649	2023-06-12 18:02:19.617	398	398	0	0	0
3	0	1,001	1	1	2	FINISH	file:///backup/obbackup	2023-06-12 18:01:41.638	2023-06-12 18:02:19.629	382	382	0	0	0
3	0	1	1	1	2	FINISH	file:///backup/obbackup	2023-06-12 18:01:41.630	2023-06-12 18:01:41.629	0	0	0	0	0
2	0	1,002	1	4	1	FINISH	file:///backup/obbackup	2023-06-12 18:00:16.685	2023-06-12 18:00:40.443	396	396	396	396	0
2	0	1,001	1	4	1	FINISH	file:///backup/obbackup	2023-06-12 18:00:16.680	2023-06-12 18:00:40.460	382	382	382	382	0
2	0	1	1	4	1	FINISH	file:///backup/obbackup	2023-06-12 18:00:16.675	2023-06-12 18:00:16.675	778	0	778	0	0

更详细的备份校验方法请参考官方文档：数据校验概述-OceanBase 数据库 -OceanBase文档中心-分布式数据库使用文档。官网文档在备份恢复方面文档非常详细。

至此，备份校验结束。下面开始不完全恢复。

OB 集群手动恢复

构造用于验证备份和恢复的业务表。

下面在数据备份后建一个表，插入两个时间点的数据。后面目标是恢复到两个时间点之间。

create table t_backup(id bigint not null auto_increment primary key, c1 varchar(100) , c2 timestamp default current_timestamp );
insert into t_backup(c1) values('test backup and restore');




insert into t_backup(c1) values('test backup and restore 2. ');




(root@10.0.0.61:2883) [test]> select * from t_backup;
+----+-----------------------------+---------------------+
| id | c1                          | c2                  |
+----+-----------------------------+---------------------+
|  1 | test backup and restore     | 2023-06-12 17:42:01 |
|  2 | test backup and restore 2.  | 2023-06-12 17:42:26 |
+----+-----------------------------+---------------------+
2 rows in set (0.02 sec)

后面目标是不完全恢复到时间点：2023-06-12 17:42:15 。如果一切正常，这个表只能看到一笔记录。

准备恢复的租户资源

首先确认集群剩余可用资源。

select a.zone,concat(a.svr_ip,':',a.svr_port) observer, cpu_total, (cpu_total-cpu_assigned) cpu_free
, round(mem_total/1024/1024/1024) mem_total_gb, round((mem_total-mem_assigned)/1024/1024/1024,2) mem_free_gb
, round(disk_total/1024/1024/1024) disk_total_gb, round((disk_total-disk_assigned)/1024/1024/1024) disk_free_gb
from __all_virtual_server_stat a join __all_server b on (a.svr_ip=b.svr_ip and a.svr_port=b.svr_port)
order by a.zone, a.svr_ip
;

zone	observer	cpu_total	cpu_free	mem_total_gb	mem_free_gb	disk_total_gb	disk_free_gb
zone1	10.0.0.61:2882	14	2	8	0	100	-1,000
ZONE2	10.0.0.62:2882	14	11	8	5	100	-100
ZONE3	10.0.0.63:2882	14	11	8	5	100	-100

创建资源单元规格。

CREATE RESOURCE UNIT b_4c2g max_cpu 4, max_memory '2g', max_iops 10240, max_disk_size 53687091200, max_session_num 64, MIN_CPU=4, MIN_MEMORY='2g', MIN_IOPS=10240;

创建资源池，这里 zone1
已经没有可分配资源了。考虑在 zone3
上分配一个单副本的租户。

CREATE RESOURCE POOL restore_pool unit = 'b_4c2g', unit_num = 1, zone_list = ('ZONE3');

开始恢复

(可选）设置加密信息。

SET @kms_encrypt_info = 'aaAA11__';

这个暂时忽略。

设置恢复参数

这个主要是控制恢复速度的。OB 的备份恢复都是异步任务进行，所以中间有一些必要的轮询等待时间。

ALTER SYSTEM SET restore_concurrency = 4;
ALTER SYSTEM SET _restore_idle_time = '30s';
SET DECRYPTION IDENTIFIED BY 'aaAA11__';

执行全量恢复

全量恢复时指定新租户的名字，恢复路径，恢复到的时间点信息。

ALTER SYSTEM RESTORE restored_obmysql FROM obmysql at 'file:///backup/obbackup' until '2023-06-12 17:42:15' with 'backup_cluster_name=obdemo&backup_cluster_id=1680747946&pool_list=restore_pool';




(root@10.0.0.61:2883) [oceanbase]> ALTER SYSTEM RESTORE restored_obmysql FROM obmysql at 'file:///backup/obbackup' until '2023-06-12 17:42:15' with 'backup_cluster_name=obdemo&backup_cluster_id=1680747946&pool_list=restore_pool';
Query OK, 0 rows affected (0.14 sec)

恢复也是以异步任务的形态进行。

查看恢复任务进度和详情

SELECT JOB_ID, TENANT_ID , TENANT_NAME, BACKUP_TENANT_ID ,BACKUP_TENANT_NAME ,BACKUP_CLUSTER_ID ,BACKUP_CLUSTER_NAME ,STATUS , START_TIME ,COMPLETION_TIME , PARTITION_COUNT , MACRO_BLOCK_COUNT , FINISH_PARTITION_COUNT , FINISH_MACRO_BLOCK_COUNT ,RESTORE_START_TIMESTAMP , RESTORE_FINISH_TIMESTAMP , BACKUP_SET_LIST , BACKUP_PIECE_LIST ,info
FROM oceanbase.CDB_OB_RESTORE_PROGRESS;

JOB_ID

TENANT_ID

TENANT_NAME

BACKUP_TENANT_ID

BACKUP_TENANT_NAME

BACKUP_CLUSTER_ID

BACKUP_CLUSTER_NAME

STATUS

START_TIME

COMPLETION_TIME

PARTITION_COUNT

MACRO_BLOCK_COUNT

FINISH_PARTITION_COUNT

FINISH_MACRO_BLOCK_COUNT

RESTORE_START_TIMESTAMP

RESTORE_FINISH_TIMESTAMP

BACKUP_SET_LIST

BACKUP_PIECE_LIST

info

1,005

restored_obmysql

1,002

obmysql

1,680,747,946

obdemo

RESTORE_USER_REPLICA

2023-06-12 18:28:30.790

[NULL]

244

-1

2023-06-12 17:19:35.910

2023-06-12 17:42:15.000

file:///backup/obbackup/obdemo/1680747946/incarnation_1/1002/data/backup_set_3_full_20230612,file:///backup/obbackup/obdemo/1680747946/incarnation_1/1002/data/backup_set_4_inc_20230612

file:///backup/obbackup/obdemo/1680747946/incarnation_1/1002/clog/1_1_20230612

SELECT JOB_ID, TENANT_ID , TENANT_NAME, BACKUP_TENANT_ID ,BACKUP_TENANT_NAME ,BACKUP_CLUSTER_ID ,BACKUP_CLUSTER_NAME ,STATUS , START_TIME ,COMPLETION_TIME , PARTITION_COUNT , MACRO_BLOCK_COUNT , FINISH_PARTITION_COUNT , FINISH_MACRO_BLOCK_COUNT ,RESTORE_START_TIMESTAMP , RESTORE_FINISH_TIMESTAMP , BACKUP_SET_LIST , BACKUP_PIECE_LIST ,info 
FROM oceanbase.CDB_OB_RESTORE_HISTORY;

JOB_ID

TENANT_ID

TENANT_NAME

BACKUP_TENANT_ID

BACKUP_TENANT_NAME

BACKUP_CLUSTER_ID

BACKUP_CLUSTER_NAME

STATUS

START_TIME

COMPLETION_TIME

PARTITION_COUNT

MACRO_BLOCK_COUNT

FINISH_PARTITION_COUNT

FINISH_MACRO_BLOCK_COUNT

RESTORE_START_TIMESTAMP

RESTORE_FINISH_TIMESTAMP

BACKUP_SET_LIST

BACKUP_PIECE_LIST

info

1,005

restored_obmysql

1,002

obmysql

1,680,747,946

obdemo

RESTORE_SUCCESS

2023-06-12 18:28:30.790

2023-06-12 18:32:06.176

244

-1

244

-1

2023-06-12 17:19:35.910

2023-06-12 17:42:15.000

file:///backup/obbackup/obdemo/1680747946/incarnation_1/1002/data/backup_set_3_full_20230612,file:///backup/obbackup/obdemo/1680747946/incarnation_1/1002/data/backup_set_4_inc_20230612

file:///backup/obbackup/obdemo/1680747946/incarnation_1/1002/clog/1_1_20230612

验证恢复结果

登录到恢复出来的新租户，账户名不变。查看目标表。

[root@server065 ~]# mysql -h10.0.0.61 -uroot@restored_obmysql#obdemo -P2883 -paaAA11__ -c -A test
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4706
Server version: 5.6.25 OceanBase 3.2.4.1 (r101000052023010822-346aa35c32e99d1b82d713f75f0072c45bdf7aab) (Built Jan  8 2023 22:52:43)




Copyright (c) 2000, 2021, Oracle and/or its affiliates.




Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.




Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.




(root@10.0.0.61:2883) [test]> select * from t_backup;
+----+-------------------------+---------------------+
| id | c1                      | c2                  |
+----+-------------------------+---------------------+
|  1 | test backup and restore | 2023-06-12 17:42:01 |
+----+-------------------------+---------------------+
1 row in set (0.54 sec)




(root@10.0.0.61:2883) [test]>

确认恢复后的租户资源分布。

(root@10.0.0.61:2883) [test]> SELECT unit_id, unit_config_name, resource_pool_name, ZONE, tenant_id,tenant_name,svr_ip,max_cpu,min_cpu,round(max_memory/1024/1024/1024) max_mem_gb
    -> FROM oceanbase.gv$unit ;
+---------+------------------+--------------------+-------+-----------+------------------+-----------+---------+---------+------------+
| unit_id | unit_config_name | resource_pool_name | ZONE  | tenant_id | tenant_name      | svr_ip    | max_cpu | min_cpu | max_mem_gb |
+---------+------------------+--------------------+-------+-----------+------------------+-----------+---------+---------+------------+
|    1010 | b_4c2g           | restore_pool       | ZONE3 |      1005 | restored_obmysql | 10.0.0.63 |       4 |       4 |          2 |
+---------+------------------+--------------------+-------+-----------+------------------+-----------+---------+---------+------------+
1 row in set (0.08 sec)

如果需要用恢复出来的租户替换原租户，可以删除原租户或者重命名。将恢复出来的租户重命名为原租户名字。

(root@10.0.0.61:2883) [oceanbase]> drop tenant obmysql;
Query OK, 0 rows affected (0.07 sec)




(root@10.0.0.61:2883) [oceanbase]> alter tenant restored_obmysql rename global_name to obmysql;
Query OK, 0 rows affected (0.04 sec)




(root@10.0.0.61:2883) [oceanbase]> select tenant_id, tenant_name , locality from __all_tenant;
+-----------+-----------------------------------------+---------------------------------------------+
| tenant_id | tenant_name                             | locality                                    |
+-----------+-----------------------------------------+---------------------------------------------+
|         1 | sys                                     | FULL{1}@zone1                               |
|      1001 | oboracle                                | FULL{1}@zone1                               |
|      1002 | __recycle_$_1680747946_1686204048874496 | FULL{1}@ZONE2, FULL{1}@ZONE3, FULL{1}@zone1 |
|      1005 | obmysql                                 | FULL{1}@ZONE3                               |
+-----------+-----------------------------------------+---------------------------------------------+
4 rows in set (0.01 sec)

OB 企业版 3.2 备份恢复实践

概述

备份介质准备

（可选）挂载 NAS 盘

部署 NFS Server

部署 NFS 客户端

测试 NFS 性能

OB 集群手动备份

配置 OB 备份

发起日志备份

发起数据备份

校验备份数据

OB 集群手动恢复

准备恢复的租户资源

开始恢复

验证恢复结果

更多阅读

评论