备份策略
开启归档
人大金仓物理备份 必须要 开启归档
修改 archive_mode 为 on, archive_command 参数取消注释键,不需要设置值,物理备份策略配置完自动赋值
修改 sys_backup.conf
sys_backup.sh 的默认配置文件是sys_backup.conf。 该文件 位于 '安装目录/Server/share' 目录下,在执行 sys_baackup.sh 时,首先会找到 '安装目录/Server/bin'下的该文件,如果不存在,会在 '安装目录/Server/share' 目录下 寻找
复制文件
cd /opt/Kingbase/ES/V8/Server/share cp sys_backup.conf ../bin/
复制该文件到 bin目录下
修改文件
根据以上参数说明 结合 本地数据库的环境,正确配置该文件,我这里备份策略配置的是每天两点全备一次,每天四点增量备份一次。备份目录路径为 /home/kingbase/kbbr_repo
设置免密登录
设置 system 用户 本地免密登录
在 IPV4 下方 修改 地址为 环路IP(127.0.0.1) 验证方法 为 trust。注意顺序是第一个(IPV4范围内配置的顺序),保存退出
开启 secourecmdd
cd /opt/Kingbase/ES/V8/ClientTools/guitools/DeployTools/zip cd securecmdd/bin sh sys_HAscmdd.sh init sh sys_HAscmdd.sh start
如果 securecmdd 没有解压 先解压
重启数据库
sys_ctl -D /opt/Kingbase/ES/V8/data restart
初始化备份任务
sys_backup.sh init
查看备份目录,初始化过程会 自动创建备份目录
启动备份任务
sys_backup.sh start
启动定时任务,系统会自动添加定时备份任务
停止/取消备份任务
sys_backup.sh unpause ---暂停备份任务 sys_backup.sh stop ---取消备份任务
手动备份
注意:以下备份内容全部包括归档
全库备份
sys_rman --config=/home/kingbase/kbbr_repo/sys_rman.conf --stanza=kingbase --archive-copy --type=full backup
查看备份信息
[kingbase@localhost kingbase]$ sys_rman --config=/home/kingbase/kbbr_repo/sys_rman.conf --stanza=kingbase info stanza: kingbase status: ok cipher: none db (current) wal archive min/max (V008R006C008B0014): 000000010000000000000002/00000001000000000000000A full backup: 20240319-215830F timestamp start/stop: 2024-03-19 21:58:30 / 2024-03-19 21:58:53 wal start/stop: 000000010000000000000004 / 000000010000000000000004 database size: 147.5MB, database backup size: 147.5MB repo1: backup set size: 147.5MB, backup size: 147.5MB full backup: 20240320-020002F timestamp start/stop: 2024-03-20 02:00:02 / 2024-03-20 02:00:23 wal start/stop: 000000010000000000000006 / 000000010000000000000006 database size: 147.5MB, database backup size: 147.5MB repo1: backup set size: 147.5MB, backup size: 147.5MB incr backup: 20240320-020002F_20240320-040002I timestamp start/stop: 2024-03-20 04:00:02 / 2024-03-20 04:00:03 wal start/stop: 000000010000000000000008 / 000000010000000000000008 database size: 147.5MB, database backup size: 16MB repo1: backup set size: 147.5MB, backup size: 16MB backup reference list: 20240320-020002F full backup: 20240320-140124F timestamp start/stop: 2024-03-20 14:01:24 / 2024-03-20 14:01:47 wal start/stop: 00000001000000000000000A / 00000001000000000000000A database size: 147.5MB, database backup size: 147.5MB repo1: backup set size: 147.5MB, backup size: 147.5MB
差异备份
sys_rman --config=/home/kingbase/kbbr_repo/sys_rman.conf --stanza=kingbase --archive-copy --type=diff backup
查看备份信息
[kingbase@localhost kingbase]$ sys_rman --config=/home/kingbase/kbbr_repo/sys_rman.conf --stanza=kingbase info stanza: kingbase status: ok cipher: none db (current) wal archive min/max (V008R006C008B0014): 000000010000000000000002/00000001000000000000000C full backup: 20240319-215830F timestamp start/stop: 2024-03-19 21:58:30 / 2024-03-19 21:58:53 wal start/stop: 000000010000000000000004 / 000000010000000000000004 database size: 147.5MB, database backup size: 147.5MB repo1: backup set size: 147.5MB, backup size: 147.5MB full backup: 20240320-020002F timestamp start/stop: 2024-03-20 02:00:02 / 2024-03-20 02:00:23 wal start/stop: 000000010000000000000006 / 000000010000000000000006 database size: 147.5MB, database backup size: 147.5MB repo1: backup set size: 147.5MB, backup size: 147.5MB incr backup: 20240320-020002F_20240320-040002I timestamp start/stop: 2024-03-20 04:00:02 / 2024-03-20 04:00:03 wal start/stop: 000000010000000000000008 / 000000010000000000000008 database size: 147.5MB, database backup size: 16MB repo1: backup set size: 147.5MB, backup size: 16MB backup reference list: 20240320-020002F full backup: 20240320-140124F timestamp start/stop: 2024-03-20 14:01:24 / 2024-03-20 14:01:47 wal start/stop: 00000001000000000000000A / 00000001000000000000000A database size: 147.5MB, database backup size: 147.5MB repo1: backup set size: 147.5MB, backup size: 147.5MB diff backup: 20240320-140124F_20240320-140620D timestamp start/stop: 2024-03-20 14:06:20 / 2024-03-20 14:06:22 wal start/stop: 00000001000000000000000C / 00000001000000000000000C database size: 147.5MB, database backup size: 16MB repo1: backup set size: 147.5MB, backup size: 16MB backup reference list: 20240320-140124F [kingbase@localhost kingbase]$
注意:差异备份的备份集名称由 上一个全备备份集名称 和 当前差异备份集名称 组成
增量备份
sys_rman --config=/home/kingbase/kbbr_repo/sys_rman.conf --stanza=kingbase --archive-copy --type=incr backup
查看备份信息
[kingbase@localhost kingbase]$ sys_rman --config=/home/kingbase/kbbr_repo/sys_rman.conf --stanza=kingbase info stanza: kingbase status: ok cipher: none db (current) wal archive min/max (V008R006C008B0014): 000000010000000000000002/00000001000000000000000E full backup: 20240319-215830F timestamp start/stop: 2024-03-19 21:58:30 / 2024-03-19 21:58:53 wal start/stop: 000000010000000000000004 / 000000010000000000000004 database size: 147.5MB, database backup size: 147.5MB repo1: backup set size: 147.5MB, backup size: 147.5MB full backup: 20240320-020002F timestamp start/stop: 2024-03-20 02:00:02 / 2024-03-20 02:00:23 wal start/stop: 000000010000000000000006 / 000000010000000000000006 database size: 147.5MB, database backup size: 147.5MB repo1: backup set size: 147.5MB, backup size: 147.5MB incr backup: 20240320-020002F_20240320-040002I timestamp start/stop: 2024-03-20 04:00:02 / 2024-03-20 04:00:03 wal start/stop: 000000010000000000000008 / 000000010000000000000008 database size: 147.5MB, database backup size: 16MB repo1: backup set size: 147.5MB, backup size: 16MB backup reference list: 20240320-020002F full backup: 20240320-140124F timestamp start/stop: 2024-03-20 14:01:24 / 2024-03-20 14:01:47 wal start/stop: 00000001000000000000000A / 00000001000000000000000A database size: 147.5MB, database backup size: 147.5MB repo1: backup set size: 147.5MB, backup size: 147.5MB diff backup: 20240320-140124F_20240320-140620D timestamp start/stop: 2024-03-20 14:06:20 / 2024-03-20 14:06:22 wal start/stop: 00000001000000000000000C / 00000001000000000000000C database size: 147.5MB, database backup size: 16MB repo1: backup set size: 147.5MB, backup size: 16MB backup reference list: 20240320-140124F incr backup: 20240320-140124F_20240320-141650I timestamp start/stop: 2024-03-20 14:16:50 / 2024-03-20 14:16:52 wal start/stop: 00000001000000000000000E / 00000001000000000000000E database size: 147.5MB, database backup size: 16MB repo1: backup set size: 147.5MB, backup size: 16MB backup reference list: 20240320-140124F
备份还原
全库还原
模拟故障,删除全库
[kingbase@localhost kingbase]$ cd /opt/Kingbase/ES/V8/ [kingbase@localhost V8]$ ll 总用量 4 lrwxrwxrwx. 1 kingbase kingbase 61 3月 13 20:02 ClientTools -> /opt/Kingbase/ES/V8/KESRealPro/V008R006C008B0014/ClientTools/ drwx------. 23 kingbase kingbase 4096 3月 20 14:20 data lrwxrwxrwx. 1 kingbase kingbase 53 3月 13 20:02 doc -> /opt/Kingbase/ES/V8/KESRealPro/V008R006C008B0014/doc/ lrwxrwxrwx. 1 kingbase kingbase 57 3月 13 20:02 install -> /opt/Kingbase/ES/V8/KESRealPro/V008R006C008B0014/install/ lrwxrwxrwx. 1 kingbase kingbase 59 3月 13 20:02 Interface -> /opt/Kingbase/ES/V8/KESRealPro/V008R006C008B0014/Interface/ drwxrwxr-x. 3 kingbase kingbase 31 3月 13 20:01 KESRealPro lrwxrwxrwx. 1 kingbase kingbase 60 3月 13 20:02 KingbaseHA -> /opt/Kingbase/ES/V8/KESRealPro/V008R006C008B0014/KingbaseHA/ lrwxrwxrwx. 1 kingbase kingbase 60 3月 13 20:02 license.dat -> /opt/Kingbase/ES/V8/KESRealPro/V008R006C008B0014/license.dat lrwxrwxrwx. 1 kingbase kingbase 56 3月 13 20:02 Server -> /opt/Kingbase/ES/V8/KESRealPro/V008R006C008B0014/Server/ lrwxrwxrwx. 1 kingbase kingbase 58 3月 13 20:02 SupTools -> /opt/Kingbase/ES/V8/KESRealPro/V008R006C008B0014/SupTools/ lrwxrwxrwx. 1 kingbase kingbase 59 3月 13 20:02 Uninstall -> /opt/Kingbase/ES/V8/KESRealPro/V008R006C008B0014/Uninstall/ [kingbase@localhost V8]$ [kingbase@localhost V8]$ rm -rf data
还原
sys_rman --config=/home/kingbase/kbbr_repo/sys_rman.conf --stanza=kingbase restore 2024-03-20 14:24:20.365 P00 INFO: restore command begin 2.27: --band-width=0 --cmd-ssh=/opt/Kingbase/ES/V8/Server/bin/sys_securecmd --config=/home/kingbase/kbbr_repo/sys_rman.conf --exec-id=14728-fb26e27b --kb1-path=/opt/Kingbase/ES/V8/data --link-all --log-level-console=info --log-level-file=info --log-path=/opt/Kingbase/ES/V8/Server/log --log-subprocess --non-archived-space=1024 --process-max=4 --repo1-path=/home/kingbase/kbbr_repo --stanza=kingbase 2024-03-20 14:24:20.398 P00 INFO: repo1: restore backup set 20240320-140124F_20240320-141650I, recovery will start at 2024-03-20 14:16:50 ERROR: [040]: unable to restore to path '/home/test/SYS_12_202305151' because it contains files HINT: try using --delta if this is what you intended. 2024-03-20 14:24:20.398 P00 INFO: restore command end: aborted with exception [040]
出现 ERROR: [040]: 错误,这个错误的原因是 有一个表空间文件 创建在 /home/test下,将该文件删除,重新还原成功。
不完全恢复(时间点)
查看时间戳,模拟误删数据
[kingbase@localhost V8]$ ksql test system 输入 "help" 来获取帮助信息. test=# test=# select now(); now ---------------------------- 2024-03-22 14:20:32.493211 (1 行记录) test=# test=# drop table t01; DROP TABLE test=# test=# test=# test=# exit sys_ctl -D /opt/Kingbase/ES/V8/data stop ---关闭数据库 rm -rf /opt/Kingbase/ES/V8/data ---删除目录
记录时间点
还原
sys_rman --config=/home/kingbase/kbbr_repo/sys_rman.conf --stanza=kingbase --type=time --target='2024-03-22 14:20:32' --target-action=promote restore
验证
[kingbase@localhost V8]$ ksql test system 输入 "help" 来获取帮助信息. test=# test=# test=# test=# \d+ t01; 数据表 "public.t01" 栏位 | 类型 | 校对规则 | 可空的 | 预设 | 存储 | 统计目标 | 描述 ------+---------+----------+--------+------+-------+----------+------ id | integer | | | | plain | | 访问方法 heap test=#
表成功还原
不完全恢复(事务ID)
查看备份集信息
sys_rman --config=/home/kingbase/kbbr_repo/sys_rman.conf --stanza=kingbase info
开启事务,记录ID
test=# begin; BEGIN test=# test=# test=# test=# select txid_current(); txid_current -------------- 851 (1 行记录) test=# test=# test=# test=# create table t01(id int); CREATE TABLE test=# test=# test=# end; COMMIT test=# drop table t01; DROP TABLE test=# test=# test=#
还原
sys_rman --config=/home/kingbase/kbbr_repo/sys_rman.conf --stanza=kingbase --type=xid --target='851' --set='20240322-143323F' --target-action=promote restore
注意
验证
[kingbase@localhost V8]$ ksql test system 输入 "help" 来获取帮助信息. test=# test=# test=# test=# \d+ t01 数据表 "public.t01" 栏位 | 类型 | 校对规则 | 可空的 | 预设 | 存储 | 统计目标 | 描述 ------+---------+----------+--------+------+-------+----------+------ id | integer | | | | plain | | 访问方法 heap test=# test=#
t01表成功还原
恢复备份至新的目录
创建目标目录
mkdir /home/data chmod 700 /home/data chown -R kingbase.kingbase /home/data
还原
sys_rman --config=/home/kingbase/kbbr_repo/sys_rman.conf --stanza=kingbase --kb1-path=/home/data restore
指定新的数据目录启动
sys_ctl -D /opt/Kingbase/ES/V8/data stop --关掉原库 sys_ctl -D /home/data start --指定新的数据目录启动