KingbaseES R6 手工创建主备流复制案例

数据库版本：

TEST=# 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)

主机节点信息：

系统环境准备：（参考金仓官方文档）

https://help.kingbase.com.cn/stage-api/profile/document/kes/v8r6/html/highly/highly-tools/cluster-use/cluster-use-2.html

一、配置主库环境

1、启动数据库服务

[kingbase@node101 ~]$ cd /opt/Kingbase/ES/V8R6_041/Server/bin

[kingbase@node101 bin]$ ./sys_ctl start -D /data/kingbase/v8r6_041/data

waiting for server to start....2022-03-29 22:07:19.775 CST [15822] LOG:  sepapower extension initialized

......

server started

2、建立测试数据

[kingbase@node101 bin]$ ./ksql -U SYSTEM TEST

TEST=# create database prod;

CREATE DATABASE

TEST=# \c prod

You are now connected to database "prod" as user "SYSTEM".

prod=# create table t1 (id int ,name varchar(10));

CREATE TABLE

prod=# insert into t1 values (generate_series(1,10000),'usr'||generate_series(1,10000));

INSERT 0 10000

prod=# select count(*) from t1;

 count

-------

 10000

(1 row)

3、配置sys_hba.conf支持远程主机replication

重新加载sys_hba.conf：

TEST=# select sys_reload_conf();

 sys_reload_conf

-----------------

 t

(1 row)

4、主备流复制参数配置

5、主库创建replication slot

二、配置备库系统环境

1、安装数据库软件（和主库相同版本）

=注意：只需安装软件，不需要创建数据库。=

2、创建备库数据存储目录（尽量和主库一致）

[root@node102 soft]# mkdir -p /data/kingbase/v8r6_041/data

[root@node102 soft]# chown -R kingbase.kingbase /data/kingbase/

三、手工clone流复制备库

1、查看备库端sys_basebackup工具

2、通过sys_basebackup克隆备库

[kingbase@node102 bin]$ ./sys_basebackup -h 192.168.1.101 -U SYSTEM  -D /data/kingbase/v8r6_041/data -P -v -X stream -F p -S slot_node102

Password:

sys_basebackup: initiating base backup, waiting for checkpoint to complete

sys_basebackup: checkpoint completed

sys_basebackup: write-ahead log start point: 0/33000028 on timeline 1

sys_basebackup: starting background WAL receiver

556390/556390 kB (100%), 1/1 tablespace

sys_basebackup: write-ahead log end point: 0/330000F8

sys_basebackup: waiting for background process to finish streaming ...

sys_basebackup: syncing data to disk ...

sys_basebackup: base backup completed

参数说明:

-h    指定所连接的主库的ip

-U    连接数据库主库的用户

-P    显示进度详细信息

-v    显示备份详情

-X    指定wal日志获取模式

-F   备份文件的输出格式（plain或tar）

四、配置备库流复制环境

1、创建备库标识文件

[kingbase@node102 data]$ pwd

/data/kingbase/v8r6_041/data

[kingbase@node102 data]$ touch standby.signal

2、编辑备库连接字符串

[kingbase@node102 data]$ cat kingbase.auto.conf

# Do not edit this file manually!

# It will be overwritten by the ALTER SYSTEM command.

primary_conninfo='host=192.168.1.101 user=system  password=123456 port=54321 application_name=node102 connect_timeout=10 keepalives=1 keepalives_idle=10 keepalives_interval=1 keepalives_count=3'

recovery_target_timeline = 'latest'

primary_slot_name = 'slot_node102'

=如上所示，在kingbase.auto.conf中配置明文口令有一点的风险，也可以采取以下方案：=

1）配置主库端sys_hba.conf文件，支持指定的网段做‘replication’采用‘trust’方式认证。

2）在备库kingbase.auto.conf文件中，不指定用户的口令。

五、启动备库流复制服务

1、配置data目录权限

[kingbase@node102 bin]$ chmod 700 /data/kingbase/v8r6_041/data

2、启动备库数据库服务

[kingbase@node102 bin]$ ./sys_ctl start -D /data/kingbase/v8r6_041/data

waiting for server to start....2022-03-29 22:46:49.429 CST [9781] LOG:  sepapower extension initialized

......

server started

3、查看备库数据库进程

[kingbase@node102 bin]$ ps -ef |grep kingbase

/opt/Kingbase/ES/V8R6_041/KESRealPro/V008R006C005B0041/Server/bin/kingbase -D /data/kingbase/v8r6_041/data

kingbase  9782  9781  0 22:46 ?        00:00:00 kingbase: logger

kingbase  9783  9781  0 22:46 ?        00:00:00 kingbase: startup   waiting for 000000010000000000000034

kingbase  9784  9781  0 22:46 ?        00:00:00 kingbase: checkpointer

kingbase  9785  9781  0 22:46 ?        00:00:00 kingbase: background writer

kingbase  9786  9781  0 22:46 ?        00:00:00 kingbase: stats collector

六、验证主备流复制

1、查看replication slots

test=# select * from sys_replication_slots;

  slot_name   | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed

_flush_lsn

--------------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+----------

-----------

 slot_node102 |        | physical  |        |          | f         | t      |      21977 |      |              | 0/34000130  |

 slot_node101 |        | physical  |        |          | f         | f      |            |      |              |             |

(2 rows)

2、查看主备流复制状态

test=# select * from sys_stat_replication;

  pid  | usesysid | usename | application_name |  client_addr  | client_hostname | client_port |         backend_start         | backend

_xmin |   state   |  sent_lsn  | write_lsn  | flush_lsn  | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state

|          reply_time

-------+----------+---------+------------------+---------------+-----------------+-------------+-------------------------------+--------

 21977 |       10 | system  | node102          | 192.168.1.102 |                 |       48657 | 2022-03-29 23:29:53.857475+08 |

      | streaming | 0/34000130 | 0/34000130 | 0/34000130 | 0/34000130 |           |           |            |             0 | async

| 2022-03-29 23:20:02.074682+08

(1 row)

3、测试数据同步

主库执行DML：

备库查看：

七、配置主备同步模式

=默认创建主备流复制后，同步模式为‘async’。=

1、配置主库kingbase.conf

注意：此备库节点名称要和备库中kingbase.auto.con中配置一致。

2、reload conf文件

[kingbase@node101 bin]$ ./sys_ctl reload -D /data/kingbase/v8r6_041/data

server signaled

3、查看主备流复制同步模式

八、配置故障问题

1、当在备库kingbase.auto.conf中配置加密口令连接主库

加密口令的明文信息：

[kingbase@node102 sys_log]$ echo 'MTIzNDU2' |base64 -d

123456

2、备库日志提示“用户认证失败”错误，明文口令无此错误。

并且在主备库已经配置了.encpwd:

九、总结

对于KingbaseES R6单机环境手工配置主备流复制有一定的复杂度，和KingbaseES R3配置略有不同。

巴特西

KingbaseES R6 手工创建主备流复制案例

最新文章

热门文章