金仓数据库物理备份还原
前提条件
安装配置好金仓数据库服务
数据库开启归档
归档开启步骤如下
- 创建备份目录
mkdir /home/kingbase/kbbr_repo
- 修改配置文件kingbase.conf中的参数
本次配置文件为/KingbaseES/V8/data/kingbase.conf
修改以下两项
archive_mode = on
archive_command = ''
- 重启数据库
sys_ctl restart -D /KingbaseES/V8/data/
waiting for server to shut down.... done
server stopped
waiting for server to start....2022-03-09 02:41:14.356 GMT [7382] 警告: max_connections should be less than or equal than 10 (restricted by license)
2022-03-09 02:41:14.356 GMT [7382] 提示: the value of max_connect is set 10
2022-03-09 02:41:14.358 GMT [7382] 警告: max_connections should be less than or equal than 10 (restricted by license)
2022-03-09 02:41:14.358 GMT [7382] 提示: the value of max_connect is set 10
2022-03-09 10:41:14.372 CST [7382] 日志: sepapower extension initialized
2022-03-09 10:41:14.378 CST [7382] 日志: 正在启动 KingbaseES V008R006C005B0023 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-46), 64-bit
2022-03-09 10:41:14.383 CST [7382] 日志: 正在监听IPv4地址"0.0.0.0"，端口 54321
2022-03-09 10:41:14.383 CST [7382] 日志: 正在监听IPv6地址"::"，端口 54321
2022-03-09 10:41:14.389 CST [7382] 日志: 在Unix套接字 "/tmp/.s.KINGBASE.54321"上侦听
2022-03-09 10:41:14.747 CST [7382] 日志: 日志输出重定向到日志收集进程
2022-03-09 10:41:14.747 CST [7382] 提示: 后续的日志输出将出现在目录 "sys_log"中.
done
server started
备份恢复步骤
- 编辑Server/bin/sys_backup.conf 文件
cp Server/share/sys_backup.conf Server/bin/
修改如下
# target db style enum: single/cluster
# 修改成single单机
_target_db_style="single"
# one kingbase node IP
# just provide one IP, script will use 'repmgr cluster show' get other node IP
# 设置ip地址
_one_db_ip="192.168.1.100"
# local repo IP, inner-REPO, must be same as one_db_ip, means repo located in one db node
# outer repo IP, outer-REPO, means repo located in outer node
_repo_ip="192.168.1.100"
# label of this cluster
_stanza_name="kingbase"
# OS user name of database
_os_user_name="kingbase"
# !!!! dir to store the backup files
# should be accessable for the OS user
# 设置归档路径
_repo_path="/home/kingbase/kbbr_repo"
# count of keep, over the count FULL-backup will be remove
_repo_retention_full_count=9
# count of days, interval to do FULL-backup
_crond_full_days=7
# count of days, interval to do DIFF-backup
_crond_diff_days=0
# count of days, interval to do INCR-backup
_crond_incr_days=1
# HOUR to do the FULL-backup
_crond_full_hour=2
# HOUR to do the DIFF-backup
_crond_diff_hour=3
# HOUR to do the INCR-backup
_crond_incr_hour=4
# OS cmd define
_os_ip_cmd="/sbin/ip"
_os_rm_cmd="/bin/rm"
_os_sed_cmd="/bin/sed"
_os_grep_cmd="/bin/grep"
# !!! these follow 4 parameter ONLY for single style
# data dir of single
# 设置数据目录
_single_data_dir="/KingbaseES/V8/data/"
# bin dir of single
# 设置可执行脚本路径
_single_bin_dir="/KingbaseES/V8/Server/bin/"
# database user of single
_single_db_user="system"
# database port of single
_single_db_port="54321"
- 初始化
初始化需要输入一次root的密码
[kingbase@node1 V8]$ sys_backup.sh init
The authenticity of host '192.168.1.100 (192.168.1.100)' can't be established.
ECDSA key fingerprint is SHA256:BcMryJTlVkuHOFYkkvdMS0JrF9r4wuh7XgDGsEj6hMM.
ECDSA key fingerprint is MD5:b5:93:44:49:b2:2f:46:a4:27:38:b8:c4:97:b7:29:43.
Are you sure you want to continue connecting (yes/no)? yes
Please input password ...
root@192.168.1.100's password:
local <-> root@192.168.1.100 ssh pwd-less OK.
# generate single sys_rman.conf...DONE
# update single archive_command with sys_rman.archive-push...DONE
# create stanza and check...(maybe 60+ seconds)
2022-03-09 10:47:50.338 CST [7383] 警告: max_connections should be less than or equal than 10 (restricted by license)
2022-03-09 10:47:50.338 CST [7383] 提示: the value of max_connect is set 10
# create stanza and check...DONE
# initial first full backup...(maybe several minutes)
# initial first full backup...DONE
# Initial sys_rman OK.
'sys_backup.sh start' should be executed when need back-rest feature.
- 创建test表
创建test表用于测试
CREATE TABLE
test=# insert into test values (1),(2),(3);
INSERT 0 3
- 全量备份
sys_rman --config=/home/kingbase/kbbr_repo/sys_rman.conf --stanza=kingbase --archive-copy --type=full backup
备份成功查看目录生成的备份文件
[kingbase@node1 kbbr_repo]$ ll /home/kingbase/kbbr_repo/
总用量 4
drwxr-x---. 3 kingbase kingbase 22 3月 9 10:47 archive
drwxr-x---. 3 kingbase kingbase 22 3月 9 10:47 backup
-rw-rw-r--. 1 kingbase kingbase 386 3月 9 10:47 sys_rman.conf
- 查看备份集
[kingbase@node1 kbbr_repo]$ 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 (V008R006C005B0023-1): 000000010000000000000001/000000010000000000000005
full backup: 20220309-104752F
timestamp start/stop: 2022-03-09 10:47:52 / 2022-03-09 10:47:57
wal start/stop: 000000010000000000000003 / 000000010000000000000003
database size: 80.4MB, backup size: 80.4MB
repository size: 9.3MB, repository backup size: 9.3MB
full backup: 20220309-105052F
timestamp start/stop: 2022-03-09 10:50:52 / 2022-03-09 10:50:58
wal start/stop: 000000010000000000000005 / 000000010000000000000005
database size: 80.4MB, backup size: 80.4MB
repository size: 9.3MB, repository backup size: 9.3MB
- 全量恢复
(1)创建新的data
mkdir /home/kingbase/KingbaseES/V8/data1
(2)修改sys_rman.conf
/home/kingbase/kbbr_repo/sys_rman.conf
kb1-path=/home/kingbase/KingbaseES/V8/data1
(3)进行还原
sys_rman --config=/home/kingbase/kbbr_repo/sys_rman.conf --stanza=kingbase restore
还原后查看目录有数据了
[kingbase@node1 data1]$ ll /home/kingbase/KingbaseES/V8/data1
总用量 64
-rw-------. 1 kingbase kingbase 255 3月 9 10:50 backup_label
drwx------. 7 kingbase kingbase 67 3月 9 11:00 base
-rw-------. 1 kingbase kingbase 46 3月 9 10:47 current_logfiles
drwx------. 2 kingbase kingbase 4096 3月 9 11:00 global
-rw-------. 1 kingbase kingbase 303 3月 9 11:00 kingbase.auto.conf
-rw-------. 1 kingbase kingbase 27687 3月 9 10:47 kingbase.conf
-rw-------. 1 kingbase kingbase 0 3月 9 11:00 recovery.signal
drwx------. 3 kingbase kingbase 19 3月 9 11:00 sys_aud
drwx------. 2 kingbase kingbase 6 3月 9 11:00 sys_bulkload
drwx------. 2 kingbase kingbase 6 3月 9 11:00 sys_commit_ts
drwx------. 2 kingbase kingbase 54 3月 9 11:00 sys_csnlog
drwx------. 2 kingbase kingbase 6 3月 9 11:00 sys_dynshmem
-rw-------. 1 kingbase kingbase 4692 3月 4 16:39 sys_hba.conf
-rw-------. 1 kingbase kingbase 1628 3月 4 16:39 sys_ident.conf
drwxrwxr-x. 2 kingbase kingbase 4096 3月 9 11:00 sys_log
drwx------. 4 kingbase kingbase 68 3月 9 11:00 sys_logical
drwx------. 4 kingbase kingbase 36 3月 9 11:00 sys_multixact
drwx------. 2 kingbase kingbase 6 3月 9 11:00 sys_notify
drwx------. 2 kingbase kingbase 6 3月 9 11:00 sys_replslot
drwx------. 2 kingbase kingbase 6 3月 9 11:00 sys_serial
drwx------. 2 kingbase kingbase 6 3月 9 11:00 sys_snapshots
drwx------. 2 kingbase kingbase 6 3月 9 11:00 sys_stat
drwx------. 2 kingbase kingbase 6 3月 9 11:00 sys_stat_tmp
drwx------. 2 kingbase kingbase 6 3月 9 11:00 sys_tblspc
drwx------. 2 kingbase kingbase 6 3月 9 11:00 sys_twophase
-rw-------. 1 kingbase kingbase 3 3月 4 16:39 SYS_VERSION
drwx------. 3 kingbase kingbase 60 3月 9 11:00 sys_wal
drwx------. 2 kingbase kingbase 18 3月 9 11:00 sys_xact
- 修改新目录的配置文件kingbase.conf端口，启动数据库，并验证test表是否恢复
port = 54322
指定数据目录启动
sys_ctl start -D /home/kingbase/KingbaseES/V8/data1/
登录验证数据是否恢复
[kingbase@node1 data1]$ ksql test -U SYSTEM -p 54322
ksql (V8.0)
输入 "help" 来获取帮助信息.
test=# select * from test;
id
----
1
2
3
(3 行记录)
使用以下命令支持根据时间点还原
sys_rman --config=/home/kingbase/kbbr_repo/sys_rma n.conf --stanza=kingbase --type=time --target='2020
-05-07 16:28:17' restore
使用以下命令差异备份
sys_rman --config=/home/kingbase/kbbr_repo/sys_rman.conf -- stanza=kingbase --archive-copy --type=diff backup
使用以下命令增量备份
sys_rman --config=/home/kingbase/kbbr_repo/sys_rman.conf -- stanza=kingbase --archive-copy --type=incr backup
