KingbaseESV8R6版本物理备份还原

最新推荐文章于 2024-02-19 10:35:07 发布

铁狮子头930212

最新推荐文章于 2024-02-19 10:35:07 发布

阅读量444

点赞数

文章标签：数据库 linux 运维

本文链接：https://blog.csdn.net/weixin_42415175/article/details/133759877

版权

一.测试环境

OS：CentOS7.5
KingbaseES：V8R6C7B0024

二.测试内容

包含单独表空间的单机数据库基于时间点的物理备份还原

三.测试步骤

1.初始化单机实例

执行初始化命令：initdb --enable-ci -USYSTEM -W -D /home/k86/data

[k86@192-168-192-168 V8]$ initdb --enable-ci -USYSTEM -W -D /home/k86/data
属于此数据库系统的文件宿主为用户 "k86".
此用户也必须为服务器进程的宿主.
数据库簇将使用本地化语言 "zh_CN.UTF-8"进行初始化.
默认的数据库编码已经相应的设置为 "UTF8".
initdb: 找不到适合区域设置的文本搜索配置 "zh_CN.UTF-8"
缺省的文本搜索配置将会被设置到"simple"

字符串的比较不区分大小写.
禁止为数据页生成校验和.

输入新的超级用户口令:
再输入一遍:

创建目录 /home/k86/data ... 成功
正在创建子目录 ... 成功
选择动态共享内存实现 ......posix
选择默认最大联接数 (max_connections) ... 100
选择默认共享缓冲区大小 (shared_buffers) ... 128MB
选择默认时区...PRC
创建配置文件 ... 成功
开始设置加密设备
正在初始化加密设备...成功
正在运行自举脚本 ...成功
正在执行自举后初始化 ...成功
创建安全数据库...成功
加载安全数据库...成功
同步数据到磁盘...成功

initdb: 警告: 为本地连接启用"trust"身份验证
你可以通过编辑 sys_hba.conf 更改或你下次
执行 initdb 时使用 -A或者--auth-local和--auth-host选项.

成功。您现在可以用下面的命令开启数据库服务器：

    sys_ctl -D /home/k86/data -l 日志文件 start

注意：执行初始化操作时必须添加-W项，--enable-ci参数为设置实例库为不敏感库

2.修改配置文件参数并启动库

修改/home/k86/data/kingbase.conf文件中的archive_mode参数为on、archive_command参数为‘/bin/date’，保存退出后启动数据库

[k86@192-168-192-168 ~]$ vi data/kingbase.conf
[k86@192-168-192-168 ~]$ sys_ctl -D /home/k86/data/ start
等待服务器进程启动 ....2023-09-14 08:53:38.459 CST [10164] 日志:  sepapower extension initialized
2023-09-14 08:53:38.471 CST [10164] 日志:  正在启动 KingbaseES V008R006C007B0024 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit
2023-09-14 08:53:38.471 CST [10164] 日志:  正在监听IPv4地址"0.0.0.0"，端口 54326
2023-09-14 08:53:38.471 CST [10164] 日志:  正在监听IPv6地址"::"，端口 54326
2023-09-14 08:53:38.473 CST [10164] 日志:  在Unix套接字 "/tmp/.s.KINGBASE.54326"上侦听
2023-09-14 08:53:38.943 CST [10164] 日志:  日志输出重定向到日志收集进程
2023-09-14 08:53:38.943 CST [10164] 提示:  后续的日志输出将出现在目录 "sys_log"中.
 完成
服务器进程已经启动

3.创建物理备份目录并完成物理备份设置

创建物理备份目录：mkdir -p /home/k86/backup/rman

设置物理备份配置文件：vi sys_backup.conf，具体设置如下（红色箭头指向的为修改项）：

4.执行物理备份初始化并启动物理备份

[k86@192-168-192-168 ~]$ cd cluster/cluster/fcj/kingbase/

[k86@192-168-192-168 kingbase]$ pwd
/home/k86/cluster/cluster/fcj/kingbase


[k86@192-168-192-168 kingbase]$ bin/sys_backup.sh init
# pre-condition: check the non-archived WAL files
# 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.

[k86@192-168-192-168 kingbase]$ bin/sys_backup.sh start
# pre-condition: check the non-archived WAL files
Enable some sys_rman in crontab-daemon
no crontab for k86
Set full-backup in 7 days
Set incr-backup in 1 days
0 2 */7 * * /home/k86/cluster/cluster/fcj/kingbase/bin/sys_rman --config=/home/k86/backup/rman/kbbr_repo/sys_rman.conf --stanza=k86_single --archive-copy --type=full backup >> /home/k86/cluster/cluster/fcj/kingbase/log/sys_rman_backup_full.log 2>&1
0 4 */1 * * /home/k86/cluster/cluster/fcj/kingbase/bin/sys_rman --config=/home/k86/backup/rman/kbbr_repo/sys_rman.conf --stanza=k86_single --archive-copy --type=incr backup >> /home/k86/cluster/cluster/fcj/kingbase/log/sys_rman_backup_incr.log 2>&1

5.创建表空间、数据库、表并插入测试数据

a.创建表空间：

[k86@192-168-192-168 ~]$ mkdir -p sys_tbl/mydb

[k86@192-168-192-168 ~]$ cd sys_tbl/mydb/

[k86@192-168-192-168 mydb]$ pwd
/home/k86/sys_tbl/mydb

[k86@192-168-192-168 kingbase]$ ksql -p54326 -USYSTEM -W TEST
口令：
ksql (V8.0)
输入 "help" 来获取帮助信息.

TEST=# \db
          表空间列表
    名称     | 拥有者 | 所在地
-------------+--------+--------
 sys_default | system |
 sys_global  | system |
 sysaudit    | system |
(3 行记录)
TEST=# create tablespace tbl_mydb location '/home/k86/sys_tbl/mydb';
CREATE TABLESPACE

b.创建数据库、建表、插入数据：

TEST=# create database mydb tablespace tbl_mydb;
CREATE DATABASE
TEST=# \c mydb -
口令：
您现在已经连接到数据库 "mydb",用户 "SYSTEM".
mydb=# create table  rman_test(c1 int,c2 varchar(10));
CREATE TABLE
mydb=# insert into rman_test values (1,'a'),(2,'b'),(3,'c');
INSERT 0 3
mydb=# select * from rman_test ;
 c1 | c2
----+----
  1 | a
  2 | b
  3 | c

6.执行一次物理备份全备

[k86@192-168-192-168 mydb]$ crontab -l
0 2 */7 * * /home/k86/cluster/cluster/fcj/kingbase/bin/sys_rman --config=/home/k86/backup/rman/kbbr_repo/sys_rman.conf --stanza=k86_single --archive-copy --type=full backup >> /home/k86/cluster/cluster/fcj/kingbase/log/sys_rman_backup_full.log 2>&1
0 4 */1 * * /home/k86/cluster/cluster/fcj/kingbase/bin/sys_rman --config=/home/k86/backup/rman/kbbr_repo/sys_rman.conf --stanza=k86_single --archive-copy --type=incr backup >> /home/k86/cluster/cluster/fcj/kingbase/log/sys_rman_backup_incr.log 2>&1

[k86@192-168-192-168 mydb]$ /home/k86/cluster/cluster/fcj/kingbase/bin/sys_rman --config=/home/k86/backup/rman/kbbr_repo/sys_rman.conf --stanza=k86_single --archive-copy --type=full backup >> /home/k86/cluster/cluster/fcj/kingbase/log/sys_rman_backup_full.log 2>&1

[k86@192-168-192-168 mydb]$ /home/k86/cluster/cluster/fcj/kingbase/bin/sys_rman --config=/home/k86/backup/rman/kbbr_repo/sys_rman.conf --stanza=k86_single info
stanza: k86_single
    status: ok
    cipher: none

    db (current)
        wal archive min/max (V008R006C007B0024): 000000010000000000000005/00000001000000000000000A

        full backup: 20230914-090218F
            timestamp start/stop: 2023-09-14 09:02:18 / 2023-09-14 09:02:20
            wal start/stop: 000000010000000000000005 / 000000010000000000000005
            database size: 81.8MB, database backup size: 81.8MB
            repo1: backup set size: 81.8MB, backup size: 81.8MB

        full backup: 20230914-091042F
            timestamp start/stop: 2023-09-14 09:10:42 / 2023-09-14 09:10:44
            wal start/stop: 000000010000000000000007 / 000000010000000000000007
            database size: 94.6MB, database backup size: 94.6MB
            repo1: backup set size: 94.6MB, backup size: 94.6MB

        full backup: 20230914-092219F
            timestamp start/stop: 2023-09-14 09:22:19 / 2023-09-14 09:22:22
            wal start/stop: 00000001000000000000000A / 00000001000000000000000A
            database size: 94.6MB, database backup size: 94.6MB
            repo1: backup set size: 94.6MB, backup size: 94.6MB

7.删除表数据并执行物理备份恢复

mydb=# truncate rman_test ;
TRUNCATE TABLE
mydb=# select * from rman_test ;
 c1 | c2
----+----
(0 行记录)


[k86@192-168-192-168 kingbase]$ sys_rman  restore  --config=/home/k86/backup/rman/kbbr_repo/sys_rman.conf --stanza=k86_single --type=time --target='2023-09-14 09:10:45' --kb1-path=/home/k86/20230914/data
2023-09-14 09:16:56.081 P00   INFO: restore command begin 2.27: --band-width=0 --config=/home/k86/backup/rman/kbbr_repo/sys_rman.conf --exec-id=10854-0297e65b --link-all --log-level-console=info --log-level-file=info --log-path=/home/k86/cluster/cluster/fcj/kingbase/log --log-subprocess --non-archived-space=1024 --kb1-path=/home/k86/20230914/data --process-max=4 --repo1-path=/home/k86/backup/rman/kbbr_repo --stanza=k86_single --target="2023-09-14 09:10:45" --type=time
2023-09-14 09:16:56.100 P00   INFO: repo1: restore backup set 20230914-091042F, recovery will start at 2023-09-14 09:10:42
2023-09-14 09:16:56.100 P00   INFO: remap data directory to '/home/k86/data'
ERROR: [040]: unable to restore to path '/home/k86/sys_tbl/mydb/SYS_12_202211151' because it contains files
       HINT: try using --delta if this is what you intended.
2023-09-14 09:16:56.101 P00   INFO: restore command end: aborted with exception [040]

由上述报错信息：由于单独创建的表空间包含文件，无法直接进行物理备份还原。此时可通过在sys_rman restore恢复命令中添加--tablespace-map（表空间映射）来解决，如下所示：

mkdir -p /home/k86/20230914/tbl_1

sys_rman  restore  --config=/home/k86/backup/rman/kbbr_repo/sys_rman.conf --stanza=k86_single --type=time --target='2023-09-14 09:21:59' --kb1-path=/home/k86/20230914/data --tablespace-map=tbl_mydb=/home/k86/20230914/tbl_1
2023-09-14 11:07:05.769 P00   INFO: restore command begin 2.27: --band-width=0 --config=/home/k86/backup/rman/kbbr_repo/sys_rman.conf --exec-id=13517-2bbbadbe --link-all --log-level-console=info --log-level-file=info --log-path=/home/k86/cluster/cluster/fcj/kingbase/log --log-subprocess --non-archived-space=1024 --kb1-path=/home/k86/20230914/data --process-max=4 --repo1-path=/home/k86/backup/rman/kbbr_repo --stanza=k86_single --tablespace-map=tbl_mydb=/home/k86/20230914/tbl_1 --target="2023-09-14 09:21:59" --type=time
2023-09-14 11:07:05.787 P00   INFO: repo1: restore backup set 20230914-091042F, recovery will start at 2023-09-14 09:10:42
2023-09-14 11:07:05.787 P00   INFO: remap data directory to '/home/k86/20230914/data'
2023-09-14 11:07:05.787 P00   INFO: map tablespace 'sys_tblspc/16387' to '/home/k86/20230914/tbl_1'
2023-09-14 11:07:05.858 P00   INFO: Restore Process: FILE: 1 / 2580 0%       SZIE: 1155072 bytes / 99145123 bytes 1.1MB / 94.6MB 1%
2023-09-14 11:07:05.858 P00   INFO: Restore Process: FILE: 2 / 2580 0%       SZIE: 2310144 bytes / 99145123 bytes 2.2MB / 94.6MB 2%
2023-09-14 11:07:05.861 P00   INFO: Restore Process: FILE: 3 / 2580 0%       SZIE: 3391488 bytes / 99145123 bytes 3.2MB / 94.6MB 3%
2023-09-14 11:07:05.879 P00   INFO: Restore Process: FILE: 4 / 2580 0%       SZIE: 4440064 bytes / 99145123 bytes 4.2MB / 94.6MB 4%
2023-09-14 11:07:05.879 P00   INFO: Restore Process: FILE: 5 / 2580 0%       SZIE: 5595136 bytes / 99145123 bytes 5.3MB / 94.6MB 5%
2023-09-14 11:07:05.879 P00   INFO: Restore Process: FILE: 6 / 2580 0%       SZIE: 6463488 bytes / 99145123 bytes 6.2MB / 94.6MB 6%
2023-09-14 11:07:05.887 P00   INFO: Restore Process: FILE: 7 / 2580 0%       SZIE: 7618560 bytes / 99145123 bytes 7.3MB / 94.6MB 7%
2023-09-14 11:07:05.887 P00   INFO: Restore Process: FILE: 8 / 2580 0%       SZIE: 8257536 bytes / 99145123 bytes 7.9MB / 94.6MB 8%
2023-09-14 11:07:05.888 P00   INFO: Restore Process: FILE: 9 / 2580 0%       SZIE: 8806400 bytes / 99145123 bytes 8.4MB / 94.6MB 8%
2023-09-14 11:07:05.895 P00   INFO: Restore Process: FILE: 10 / 2580 0%       SZIE: 9297920 bytes / 99145123 bytes 8.9MB / 94.6MB 9%
2023-09-14 11:07:05.896 P00   INFO: Restore Process: FILE: 11 / 2580 0%       SZIE: 9682944 bytes / 99145123 bytes 9.2MB / 94.6MB 9%
2023-09-14 11:07:05.898 P00   INFO: Restore Process: FILE: 12 / 2580 0%       SZIE: 9936896 bytes / 99145123 bytes 9.5MB / 94.6MB 10%
......
2023-09-14 11:07:06.845 P00   INFO: Restore Process: FILE: 2572 / 2580 99%       SZIE: 99145123 bytes / 99145123 bytes 94.6MB / 94.6MB 100%
2023-09-14 11:07:06.845 P00   INFO: Restore Process: FILE: 2573 / 2580 99%       SZIE: 99145123 bytes / 99145123 bytes 94.6MB / 94.6MB 100%
2023-09-14 11:07:06.845 P00   INFO: Restore Process: FILE: 2574 / 2580 99%       SZIE: 99145123 bytes / 99145123 bytes 94.6MB / 94.6MB 100%
2023-09-14 11:07:06.845 P00   INFO: Restore Process: FILE: 2575 / 2580 99%       SZIE: 99145123 bytes / 99145123 bytes 94.6MB / 94.6MB 100%
2023-09-14 11:07:07.148 P00   INFO: Restore Process: FILE: 2576 / 2580 99%       SZIE: 99145123 bytes / 99145123 bytes 94.6MB / 94.6MB 100%
2023-09-14 11:07:07.148 P00   INFO: Restore Process: FILE: 2577 / 2580 99%       SZIE: 99145123 bytes / 99145123 bytes 94.6MB / 94.6MB 100%
2023-09-14 11:07:07.148 P00   INFO: Restore Process: FILE: 2578 / 2580 99%       SZIE: 99145123 bytes / 99145123 bytes 94.6MB / 94.6MB 100%
2023-09-14 11:07:07.148 P00   INFO: Restore Process: FILE: 2579 / 2580 99%       SZIE: 99145123 bytes / 99145123 bytes 94.6MB / 94.6MB 100%
2023-09-14 11:07:07.248 P00   INFO: Restore Process: FILE: 2580 / 2580 100%       SZIE: 99145123 bytes / 99145123 bytes 94.6MB / 94.6MB 100%
2023-09-14 11:07:07.249 P00   INFO: write updated /home/k86/20230914/data/kingbase.auto.conf
2023-09-14 11:07:07.258 P00   INFO: restore global/sys_control (performed last to ensure aborted restores cannot be started)
2023-09-14 11:07:07.259 P00   INFO: restore size = 94.6MB, file total = 2580
2023-09-14 11:07:07.260 P00   INFO: restore command end: completed successfully (1494ms)

8.数据验证：

[k86@192-168-192-168 ~]$ sys_ctl -D /home/k86/20230914/data/ start
等待服务器进程启动 ....2023-09-14 11:16:35.347 CST [13696] 日志:  sepapower extension initialized
2023-09-14 11:16:35.350 CST [13696] 日志:  正在启动 KingbaseES V008R006C007B0024 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit
2023-09-14 11:16:35.350 CST [13696] 日志:  正在监听IPv4地址"0.0.0.0"，端口 54326
2023-09-14 11:16:35.350 CST [13696] 日志:  正在监听IPv6地址"::"，端口 54326
2023-09-14 11:16:35.352 CST [13696] 日志:  在Unix套接字 "/tmp/.s.KINGBASE.54326"上侦听
2023-09-14 11:16:35.398 CST [13696] 日志:  日志输出重定向到日志收集进程
2023-09-14 11:16:35.398 CST [13696] 提示:  后续的日志输出将出现在目录 "sys_log"中.
 完成
服务器进程已经启动
[k86@192-168-192-168 ~]$ vi 20230914/data/kingbase.auto.conf
[k86@192-168-192-168 ~]$ ksql -p54326 -USYSTEM -W mydb
口令：
ksql (V8.0)
输入 "help" 来获取帮助信息.

mydb=# select * from rman_test ;
 c1 | c2
----+----
  1 | a
  2 | b
  3 | c
(3 行记录)

至此，基于时间点的物理备份还原操作结束。

9.注意：

1. 如果存在多个独立的表空间可以使用--tablespace-map-all配置参数将所有的表空间一并映射到指定的目录下；
2. 通过--kb1-path和--tablespace-map可以指定要恢复到的目标data目录和表空间目录，不需要停止现有的单实例数据库服务，适合恢复部分数据库对象；
3. 也可以添加--delta参数将data和表空间目录恢复至原目录，即/home/k86/data和/home/k86/sys_tbl，而不需要指定kb1-path和--tablespace-map参数（此方法不建议使用）

铁狮子头930212

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
KingbaseESV8R6版本物理备份还原

修改/home/k86/data/kingbase.conf文件中的archive_mode参数为on、archive_command参数为‘/bin/date’，保存退出后启动数据库。执行初始化命令：initdb --enable-ci -USYSTEM -W -D /home/k86/data。注意：执行初始化操作时必须添加-W项，--enable-ci参数为设置实例库为不敏感库。创建物理备份目录：mkdir -p /home/k86/backup/rman。4.执行物理备份初始化并启动物理备份。
复制链接

扫一扫