KingbaseES V8R6集群运维案例之---sys_rman备份异机恢复案例
案例说明：
kingbaseES V8R6集群在主库通过sys_rman进行备份，然后将备份传送到异机（单节点主机，部署和原备份主机相同版本的数据库）进行恢复（restore）。
案例架构：
如下图所示：在repmgr cluster的主库上执行sys_rman备份，备份后将备份拷贝到node2的db server上进行restore。
数据库版本：
prod=# select version();
version
----------------------------------------------------------------------------------------------------------------------
KingbaseES V008R006C003B0010 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-46), 64-bit
(1 row)
集群架构：
[kingbase@node1 bin]$ ./repmgr cluster show
ID | Name | Role | Status | Upstream | Location | Priority | Timeline | Connection string
----+---------+---------+-----------+----------+----------+----------+----------+---------------------------------------------------------------------------------------------------------------------------------------------------
1 | node248 | primary | * running | | default | 100 | 27 | host=192.168.7.248 user=esrep dbname=esrep port=54321 connect_timeout=10 keepalives=1 keepalives_idle=10 keepalives_interval=1 keepalives_count=2
3 | node243 | standby | running | node248 | default | 110 | 27 | host=192.168.7.243 user=esrep dbname=esrep port=54321 connect_timeout=10 keepalives=1 keepalives_idle=10 keepalives_interval=1 keepalives_count=2
一、在集群主库执行sys_rman备份
1、查看sys_backup.conf配置
[kingbase@node1 bin]$ cat sys_backup.conf |grep -v ^$|grep -v ^#
_target_db_style="single"
_one_db_ip="192.168.7.248"
_repo_ip="192.168.7.248"
_stanza_name="kingbase"
_os_user_name="kingbase"
_repo_path="/home/kingbase/kbbr_repo"
_repo_retention_full_count=5
_crond_full_days=7
_crond_diff_days=0
_crond_incr_days=1
_crond_full_hour=2
_crond_diff_hour=3
_crond_incr_hour=4
_os_ip_cmd="/sbin/ip"
_os_rm_cmd="/bin/rm"
_os_sed_cmd="/bin/sed"
_os_grep_cmd="/bin/grep"
_single_data_dir="/home/kingbase/cluster/R6HA/KHA/kingbase/data"
_single_bin_dir="/home/kingbase/cluster/R6HA/KHA/kingbase/bin"
_single_db_user="system"
_single_db_port="54321"
2、在主库端执行sys_rman备份初始化（自动执行第一次全备）
[kingbase@node1 bin]$ ./sys_backup.sh init
# generate single sys_rman.conf...DONE
# update single archive_command with sys_rman.archive-push...DONE
# create stanza and check...(maybe 60+ seconds)
# 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.
二、在独立db server创建备份集
1、查看独立数据库服务器版本
[kingbase@node2 bin]$ ./ksql -V
ksql (Kingbase) V008R006C003B0010
2、创建和集群主库相同的数据存储目录
[kingbase@node2 bin]$ pwd
/home/kingbase/cluster/R6HA/KHA/kingbase/bin
3、启动数据库服务
[kingbase@node2 bin]$ ps -ef |grep kingbase
kingbase 828 1 0 16:03 ? 00:00:00 /home/kingbase/cluster/R6HA/KHA/kingbase/bin/kingbase -D ../data
kingbase 829 828 0 16:03 ? 00:00:00 kingbase: logger
kingbase 831 828 0 16:03 ? 00:00:00 kingbase: checkpointer
kingbase 832 828 0 16:03 ? 00:00:00 kingbase: background writer
kingbase 833 828 0 16:03 ? 00:00:00 kingbase: walwriter
kingbase 835 828 0 16:03 ? 00:00:00 kingbase: autovacuum launcher
kingbase 836 828 0 16:03 ? 00:00:00 kingbase: archiver failed on 0000001B00000005000000FD
kingbase 837 828 0 16:03 ? 00:00:00 kingbase: stats collector
kingbase 838 828 0 16:03 ? 00:00:00 kingbase: ksh writer
kingbase 839 828 0 16:03 ? 00:00:00 kingbase: ksh collector
kingbase 840 828 0 16:03 ? 00:00:00 kingbase: sys_kwr collector
kingbase 841 828 0 16:03 ? 00:00:00 kingbase: logical replication launcher
4、查看sys_backup.conf配置
[kingbase@node2 bin]$ cat sys_backup.conf |grep -v ^$|grep -v ^#
_target_db_style="single"
_one_db_ip="192.168.7.249"
_repo_ip="192.168.7.249"
_stanza_name="kingbase"
_os_user_name="kingbase"
_repo_path="/home/kingbase/kbbr_repo"
_repo_retention_full_count=5
_crond_full_days=7
_crond_diff_days=0
_crond_incr_days=1
_crond_full_hour=2
_crond_diff_hour=3
_crond_incr_hour=4
_os_ip_cmd="/sbin/ip"
_os_rm_cmd="/bin/rm"
_os_sed_cmd="/bin/sed"
_os_grep_cmd="/bin/grep"
_single_data_dir="/home/kingbase/cluster/R6HA/KHA/kingbase/data"
_single_bin_dir="/home/kingbase/cluster/R6HA/KHA/kingbase/bin"
_single_db_user="system"
_single_db_port="54321"
5、执行sys_rman备份初始化
[kingbase@node2 bin]$ ./sys_backup.sh init
# generate single sys_rman.conf...DONE
# update single archive_command with sys_rman.archive-push...DONE
# create stanza and check...(maybe 60+ seconds)
# 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.
6、查看备份存储目录信息
[kingbase@node2 ~]$ cd kbbr_repo/
[kingbase@node2 kbbr_repo]$ ls -lh
total 4.0K
drwxr-x---. 3 kingbase kingbase 21 Mar 1 16:05 archive
drwxr-x---. 3 kingbase kingbase 21 Mar 1 16:05 backup
-rw-rw-r--. 1 kingbase kingbase 390 Mar 1 16:05 sys_rman.conf
[kingbase@node2 kbbr_repo]$ cat sys_rman.conf
# Genarate by script at 20210301160536, should not change manually
[kingbase]
kb1-path=/home/kingbase/cluster/R6HA/KHA/kingbase/data
kb1-port=54321
kb1-user=system
[global]
repo1-path=/home/kingbase/kbbr_repo
repo1-retention-full=5
log-path=/tmp/
log-level-file=info
log-level-console=info
log-subprocess=y
process-max=4
#### default gz, support: gz none
compress-type=gz
compress-level=3
7、查看sys_rman备份信息
[kingbase@node2 bin]$ /home/kingbase/cluster/R6HA/KHA/kingbase/bin/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 (12-1): 0000001B00000005000000FD/0000001B0000000600000000
full backup: 20210301-160540F
timestamp start/stop: 2021-03-01 16:05:40 / 2021-03-01 16:09:30
wal start/stop: 0000001B0000000600000000 / 0000001B0000000600000000
database size: 1.2GB, backup size: 1.2GB
repository size: 989.3MB, repository backup size: 989.3MB
三、执行sys_rman备份异机还原（restore）
1、将本机备份数据目录改名
[kingbase@node2 kbbr_repo]$ mv archive archive.bk
[kingbase@node2 kbbr_repo]$ mv backup backup.bk
2、将异机备份拷贝到本地
[kingbase@node1 kbbr_repo]$ scp -r backup/ node2:~/kbbr_repo/
[kingbase@node1 kbbr_repo]$ scp -r archive/ node2:~/kbbr_repo/
[kingbase@node2 kbbr_repo]$ ls -lh
total 4.0K
drwxr-x---. 3 kingbase kingbase 21 Mar 1 16:17 archive
drwxr-x---. 3 kingbase kingbase 21 Mar 1 16:05 archive.bk
drwxr-x---. 3 kingbase kingbase 21 Mar 1 16:17 backup
drwxr-x---. 3 kingbase kingbase 21 Mar 1 16:05 backup.bk
-rw-rw-r--. 1 kingbase kingbase 390 Mar 1 16:05 sys_rman.conf
3、查看sys_rman备份信息
=== 注意：可以查看到sys_rman的备份信息，说明备份可用===
[kingbase@node2 bin]$ /home/kingbase/cluster/R6HA/KHA/kingbase/bin/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 (12-1): 0000001B00000005000000FC/0000001B0000000600000000
full backup: 20210301-123502F
timestamp start/stop: 2021-03-01 12:35:02 / 2021-03-01 12:44:24
wal start/stop: 0000001B0000000600000000 / 0000001B0000000600000000
database size: 1.2GB, backup size: 1.2GB
repository size: 988.3MB, repository backup size: 988.3MB
4、停止数据库服务
[kingbase@node2 bin]$ ./sys_ctl stop -D ../data
waiting for server to shut down.... done
server stopped
5、将原数据库目录改名并创建新的数据库目录
[kingbase@node2 kingbase]$ mv data data.old
[kingbase@node2 kingbase]$ mkdir data
6、通过sys_rman执行restore
[kingbase@node2 bin]$ /home/kingbase/cluster/R6HA/KHA/kingbase/bin/sys_rman --config /home/kingbase/kbbr_repo/sys_rman.conf --stanza=kingbase restore
.......
2021-03-01 16:29:16.555 P04 INFO: restore file /home/kingbase/cluster/R6HA/KHA/kingbase/data/.wallet/userkey.kr (0B, 100%)
2021-03-01 16:29:16.557 P01 INFO: restore file /home/kingbase/cluster/R6HA/KHA/kingbase/data/.wallet/tspkey.kr (0B, 100%)
2021-03-01 16:29:16.558 P02 INFO: restore file /home/kingbase/cluster/R6HA/KHA/kingbase/data/.wallet/tbcolkey.kr (0B, 100%)
2021-03-01 16:29:16.560 P00 INFO: write updated /home/kingbase/cluster/R6HA/KHA/kingbase/data/kingbase.auto.conf
2021-03-01 16:29:16.564 P00 INFO: restore global/sys_control (performed last to ensure aborted restores cannot be started)
2021-03-01 16:29:16.970 P00 INFO: restore command end: completed successfully (145432ms)
7、查看还原后的数据信息
1）查看数据目录信息
[kingbase@node2 kingbase]$ du -sh data
1.2G data
[kingbase@node2 kingbase]$ du -sh data.old
1.3G data.old
2、启动数据库查看数据信息
[kingbase@node2 bin]$ ./sys_ctl start -D ../data
waiting for server to start....2021-03-01 16:31:58.373 CST [3559] LOG: sepapower extension initialized
2021-03-01 16:31:58.405 CST [3559] LOG: starting KingbaseES V008R006C003B0010 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-46), 64-bit
2021-03-01 16:31:58.407 CST [3559] LOG: listening on IPv4 address "0.0.0.0", port 54321
2021-03-01 16:31:58.408 CST [3559] LOG: listening on IPv6 address "::", port 54321
2021-03-01 16:31:58.431 CST [3559] LOG: listening on Unix socket "/tmp/.s.KINGBASE.54321"
2021-03-01 16:31:58.461 CST [3559] LOG: redirecting log output to logging collector process
2021-03-01 16:31:58.461 CST [3559] HINT: Future log output will appear in directory "sys_log".
. done
server started
[kingbase@node2 bin]$ ./ksql -U system test
ksql (V8.0)
Type "help" for help.
test=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+--------+----------+-------------+-------------+-------------------
esrep | esrep | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
kingbase | system | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
orcl | system | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
prod | system | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
prod1 | system | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
security | system | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | system | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/system +
| | | | | system=CTc/system
template1 | system | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/system +
| | | | | system=CTc/system
test | system | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(9 rows)
=== 从以上获知，恢复完成===
四、总结
此次恢复测试，是在异机上构建了和原备份主机相同的数据库存储目录，异机恢复数据成功。在做异机的物理备份恢复时，尽量选择和原备份主机相同的目录，这样恢复过程一般不容易出现错误，对于不同数据存储目录结构的恢复，还需再做测试。
