KingbaseES R6集群sys_rman备份异机恢复案例

案例说明:

kingbaseES R6集群在主库通过sys_rman进行备份,然后将备份传送到异机(单节点主机,部署和原备份主机相同版本的数据库)进行恢复(restore)。

案例架构:

如下图所示:在repmgr cluster的主库上执行sys_rman备份,备份后将备份拷贝到node2的db server上进行restore。
KingbaseES R6集群sys_rman备份异机恢复案例

数据库版本:

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)

=== 从以上获知,恢复完成===

四、总结

  此次恢复测试,是在异机上构建了和原备份主机相同的数据库存储目录,异机恢复数据成功。在做异机的物理备份恢复时,尽量选择和原备份主机相同的目录,这样恢复过程一般不容易出现错误,对于不同数据存储目录结构的恢复,还需再做测试。
上一篇:大数据术语之ClickHouse


下一篇:rtp协议详解