案例说明：
KingbaseES V8R6通过sys_backup.sh执行物理备份，默认sys_backup.sh执行备份初始化时，需要连接test数据库进行身份的认证；在一些生产环境为了安全需求，会删除test库或被误删除，导致sys_rman连接数据库失败 。如果是误删除，比较容易解决，重新创建一个test库即可。如果是安全需求删除，不能重建test库，则需要修改sys_backup.sh脚本，修改认证库，本案例将详细介绍如何修改脚本完成sys_backup.sh的初始化和备份。
适用版本：KingbaseES V8R6
本案例数据库版本：
prod=# select version();
version
----------------------------------------------------------------------------------------------------------------------
KingbaseES V008R006C005B0041 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-46), 64-bit
(1 row)
一、问题复现
1、test数据库被删除
[kingbase@node101 bin]$ ./ksql -U system test -p 54325
ksql (V8.0)
Type "help" for help.
test=# /c prod
You are now connected to database "prod" as user "system".
prod=# drop database test;
DROP DATABASE
prod=# /l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+--------+----------+----------+-------------+-------------------
prod | system | UTF8 | ci_x_icu | zh_CN.UTF-8 |
prod1 | system | UTF8 | ci_x_icu | zh_CN.UTF-8 |
security | system | UTF8 | ci_x_icu | zh_CN.UTF-8 |
template0 | system | UTF8 | ci_x_icu | zh_CN.UTF-8 | =c/system +
| | | | | system=CTc/system
template1 | system | UTF8 | ci_x_icu | zh_CN.UTF-8 | =c/system +
| | | | | system=CTc/system
(5 rows)
2、执行sys_backup.sh init
[kingbase@node101 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)
ERROR: create stanza failed, check log file /opt/Kingbase/ES/V8R6_041/Server/log/sys_rman_stanza-create.log
查看日志信息：
=如日志信息所示，由于test库不存在，导致sys_rman连接数据库失败。=
2022-06-30 17:18:44.380 P00 INFO: stanza-create command end: aborted with exception [056]
2022-06-30 17:21:22.930 P00 INFO: stanza-create command begin 2.27: --config=/home/kingbase/kbbr1_repo/sys_rman.conf --exec-id=10771-16130362 --kb1-path=/data/kingbase/v8r6_041/data --kb1-port=54325 --kb1-user=system --log-level-console=info --log-level-file=info --log-path=/opt/Kingbase/ES/V8R6_041/Server/log --log-subprocess --repo1-path=/home/kingbase/kbbr1_repo --stanza=kingbase
WARN: unable to check kb-1: [DbConnectError] unable to connect to 'dbname='test' port=54325 user='system'': FATAL: database "test" does not exist
ERROR: [056]: unable to find primary cluster - cannot proceed
2022-06-30 17:21:22.944 P00 INFO: stanza-create command end: aborted with exception [056]
二、解决问题
=由于生产要求，不能重建test库。=
1、查看sys_backup.conf
[kingbase@node101 bin]$ cat sys_backup.conf |grep target
# target db style enum: single/cluster
_target_db_style="single"
# 首先确定，本次备份是singl还是cluster备份模式。
2、编辑sys_backup.sh
=由于sys_backup.sh可用于single和cluster的备份模式，所以在修改脚本前必须确定此次备份的模式，然后修改对应funcation下的执行语句，减少对脚本不必要的修改。此次案例备份采用的single模式，所以只需要修改“ function func_single_init()”下的语句即可。=
[kingbase@node101 bin]$ cat sys_backup.sh
196 function func_single_init()
197 {
........
289 /bin/echo "# create stanza and check...(maybe 60+ seconds)"
290 ${_os_rm_cmd} -rf "${_repo_path}/archive"
291 ${_os_rm_cmd} -rf "${_repo_path}/backup"
#需要修改的语句，增加：---kb1-database=template1
292 ${_rman_bin} --config=${_rman_conf_file} --stanza=${_stanza_name} --kb1-database=template1 --log-level-console=info stanza-create >> "${_log_dir}/sys_rman_stanza-create.log" 2>&1
293 if [ "X0" != "X$?" ] ; then
294 /bin/echo "ERROR: create stanza failed, check log file ${_log_dir}/sys_rman_stanza-create.log"
295 exit 2
296 fi
297 if [ "Xcluster_standby_step1" != "X${_cluster_standby_step}" ] ; then
298 ###########################
#需要修改的语句，增加：---kb1-database=template1
299 ${_rman_bin} --config=${_rman_conf_file} --stanza=${_stanza_name} --kb1-database=template1 --log-level-console=info check >> "${_log_dir}/sys_rman_check.log" 2>&1
300 if [ "X0" != "X$?" ] ; then
301 /bin/echo "ERROR: check stanza failed, check log file ${_log_dir}/sys_rman_check.log"
302 exit 3
303 fi
304 ###########################
305 fi
306 /bin/echo "# create stanza and check...DONE"
307
308 if [ "Xcluster_standby_step1" != "X${_cluster_standby_step}" ] ; then
309 ###########################
310 /bin/echo "# initial first full backup...(maybe several minutes)"
#需要修改的语句，增加：---kb1-database=template1
311 ${_rman_bin} --config=${_rman_conf_file} --stanza=${_stanza_name} --kb1-database=template1 --log-level-console=info --archive-copy --type=full backup >> "${_log_dir}/sys_rman_backup.log" 2>&1
313 if [ "X0" != "X$?" ] ; then
314 /bin/echo "ERROR: full backup failed, check log file ${_log_dir}/sys_rman_backup.log"
315 exit 4
316 fi
317 /bin/echo "# initial first full backup...DONE"
318 ###########################
319 fi
320
321 /bin/echo "# Initial sys_rman OK."
322 /bin/echo "'sys_backup.sh start' should be executed when need back-rest feature."
323
324 } # end of function single init
#如上所示，在sys_rman的执行语句中，指定用template1库做连接认证。
3、执行sys_backup.sh init
[kingbase@node101 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.
# 如上所示，执行init成功。
三、完善备份配置
1、编辑sys_rman.conf
[kingbase@node101 bin]$ cat ~/kbbr1_repo/sys_rman.conf
# Genarate by script at 20220701102150, should not change manually
[kingbase]
kb1-path=/data/kingbase/v8r6_041/data
kb1-port=54325
kb1-user=system
kb1-database=template1
[global]
repo1-path=/home/kingbase/kbbr1_repo
repo1-retention-full=5
log-path=/opt/Kingbase/ES/V8R6_041/Server/log
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
band-width=0
# 如上所示，增加kb1-database参数。
2、建立备份计划
[kingbase@node101 bin]$ ./sys_backup.sh start
Enable some sys_rman in crontab-daemon
Set full-backup in 7 days
Set incr-backup in 1 days
0 2 */7 * * kingbase /opt/Kingbase/ES/V8R6_041/Server/bin/sys_rman --config=/home/kingbase/kbbr1_repo/sys_rman.conf --stanza=kingbase --archive-copy --type=full backup >> /opt/Kingbase/ES/V8R6_041/Server/log/sys_rman_backup_full.log 2>&1
0 4 */1 * * kingbase /opt/Kingbase/ES/V8R6_041/Server/bin/sys_rman --config=/home/kingbase/kbbr1_repo/sys_rman.conf --stanza=kingbase --archive-copy --type=incr backup >> /opt/Kingbase/ES/V8R6_041/Server/log/sys_rman_backup_incr.log 2>&1
3、验证备份信息
[kingbase@node101 bin]$ /opt/Kingbase/ES/V8R6_041/Server/bin/sys_rman --config=/home/kingbase/kbbr1_repo/sys_rman.conf --stanza=kingbase info
stanza: kingbase
status: ok
cipher: none
db (current)
wal archive min/max (V008R006C005B0041): 000000010000000000000082/000000010000000000000083
full backup: 20220701-102154F
timestamp start/stop: 2022-07-01 10:21:54 / 2022-07-01 10:22:30
wal start/stop: 000000010000000000000083 / 000000010000000000000083
database size: 736MB, database backup size: 736MB
repo1: backup set size: 102MB, backup size: 102MB
四、总结
sys_rman备份需要连接数据库，在生产环境中尽量不要删除默认的数据库，以免带来不必要的应用问题。
原创文章，作者：kirin，如若转载，请注明出处：https://blog.ytso.com/272894.html