KingbaseES V8R6 集群运维系列 -- 手工配置集群VIP
案例说明：
在集群前期部署过程中，如果没有配置vip，但部署运行后，因应用需求，需要配置vip。对于KingbaseES V8R6集群手工配置vip操作比较简单，只需要修改repmgr.conf文件即可。
适用版本：
KingbaseES V8R6
一、集群架构信息
1、前期部署
2、查看集群节点状态信息
kingbase@uos01:~/cluster/R6HA/kha/kingbase/bin$ ./repmgr cluster show
ID | Name | Role | Status | Upstream | Location | Priority | Timeline | Connection string
----+---------+---------+-----------+----------+----------+----------+----------+
1 | node238 | primary | * running | | default | 100 | 1 | host=192.168.7.238 user=esrep dbname=esrep port=54321 connect_timeout=10 keepalives=1 keepalives_idle=10 keepalives_interval=1 keepalives_count=3
2 | node239 | standby | running | node238 | default | 100 | 1 | host=192.168.7.239 user=esrep dbname=esrep port=54321 connect_timeout=10 keepalives=1 keepalives_idle=10 keepalives_interval=1 keepalives_count=3
3、查看repmgr.conf文件
kingbase@uos01:~/cluster/R6HA/kha/kingbase/etc$ cat repmgr.conf
on_bmj=off
node_id=1
node_name='node238'
promote_command='/home/kingbase/cluster/R6HA/kha/kingbase/bin/repmgr standby promote -f /home/kingbase/cluster/R6HA/kha/kingbase/etc/repmgr.conf'
follow_command='/home/kingbase/cluster/R6HA/kha/kingbase/bin/repmgr standby follow -f /home/kingbase/cluster/R6HA/kha/kingbase/etc/repmgr.conf -W --upstream-node-id=%n'
conninfo='host=192.168.7.238 user=esrep dbname=esrep port=54321 connect_timeout=10 keepalives=1 keepalives_idle=10 keepalives_interval=1 keepalives_count=3'
log_file='/home/kingbase/cluster/R6HA/kha/kingbase/hamgr.log'
data_directory='/home/kingbase/cluster/R6HA/kha/kingbase/data'
sys_bindir='/home/kingbase/cluster/R6HA/kha/kingbase/bin'
ssh_options='-q -o ConnectTimeout=10 -o StrictHostKeyChecking=no -o ServerAliveInterval=2 -o ServerAliveCountMax=5 -p 22'
reconnect_attempts=3
reconnect_interval=5
failover='automatic'
recovery='manual'
monitoring_history='no'
trusted_servers='192.168.7.1'
synchronous='quorum'
repmgrd_pid_file='/home/kingbase/cluster/R6HA/kha/kingbase/hamgrd.pid'
ping_path='/usr/bin'
#从以上配置文件获知，文件中没有virtual_ip的配置项
二、修改repmgr.conf配置文件配置vip（需要在所有节点执行）
1、确定配置vip的网卡
2: enp0s3: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 08:00:27:56:02:82 brd ff:ff:ff:ff:ff:ff
inet 192.168.7.238/24 brd 192.168.7.255 scope global noprefixroute enp0s3
valid_lft forever preferred_lft forever
#配置vip的网卡必须和物理ip是同一个设备。
2、确定ip和arping可执行文件路径和权限
确定ip和arping可执行文件路径：
kingbase@uos01:~/cluster/R6HA/kha/kingbase/bin$ which arping
/usr/bin/arping
root@uos01:~# which ip
/usr/sbin/ip
查看arping版本：
当前系统自带arping版本：
kingbase@uos01:~/cluster/R6HA/kha/kingbase/bin$ arping -V
arping utility, iputils-s20180629
数据库自带arping版本：
kingbase@uos01:~/cluster/R6HA/kha/kingbase/bin$ ls arping
arping
kingbase@uos01:~/cluster/R6HA/kha/kingbase/bin$ ./arping -V
arping utility, iputils-s20200808kb
配置ip和arping可执行文件权限（配置setuid权限）：
（对于arping的版本可以选择系统自带，建议使用数据库自带的版本，兼容性更好，本案例使用了系统自带的版本。）
root@uos01:~# ls -lh /usr/bin/arping
-rwxr-xr-x 1 root root 27K Jan 14 2020 /usr/bin/arping
root@uos01:~# ls -lh /usr/bin/ip
-rwxr-xr-x 1 root root 575K Jun 4 2021 /usr/bin/ip
# arping增加setuid权限（注意：通用机环境arping的属主应该是root用户）
root@uos01:~# chmod 4755 /usr/bin/arping
root@uos01:~# chmod 4755 /usr/sbin/ip
root@uos01:~# ls -lh /usr/bin/arping
-rwsr-xr-x 1 root root 27K Jan 14 2020 /usr/bin/arping
root@uos01:~# ls -lh /usr/sbin/ip
lrwxrwxrwx 1 root root 7 Jun 4 2021 /usr/sbin/ip -> /bin/ip
root@uos01:~# ls -lh /bin/ip
-rwsr-xr-x 1 root root 575K Jun 4 2021 /bin/ip
注意：
1）ip命令用于加载和卸载vip。
2）arping命令用于vip切换中的arp cache的清理和测试。
3、修改repmgr.conf配置文件
Tips：
最新的V8R6的版本增加了net_device_ip参数（配置本机的物理ip）
三、重新启动集群（sys_monitor.sh启动）
kingbase@uos01:~/cluster/R6HA/kha/kingbase/bin$ ./sys_monitor.sh restart
......
server started
......
2021-03-01 12:22:57 Success to load virtual ip [192.168.7.244/24] on primary host [192.168.7.238].
2021-03-01 12:22:57 Try to ping vip on host 192.168.7.238 ...
2021-03-01 12:22:59 Try to ping vip on host 192.168.7.239 ...
.......
2021-03-01 12:23:03 repmgrd on "[192.168.7.239]" start success.
ID | Name | Role | Status | Upstream | repmgrd | PID | Paused? | Upstream last seen
----+---------+---------+-----------+----------+---------+-------+---------+--------------------
1 | node238 | primary | * running | | running | 15043 | no | n/a
2 | node239 | standby | running | node238 | running | 6440 | no | n/a
2021-03-01 12:23:07 Done.
# 从以上信息可获知，集群重启后已经开始加载VIP地址 [192.168.7.244/24]
四、验证集群状态
1、查看系统vip的加载
2: enp0s3: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 08:00:27:56:02:82 brd ff:ff:ff:ff:ff:ff
inet 192.168.7.238/24 brd 192.168.7.255 scope global noprefixroute enp0s3
valid_lft forever preferred_lft forever
inet 192.168.7.244/24 scope global secondary enp0s3:3
valid_lft forever preferred_lft forever
# 从以上获知，vip加载在主库节点成功===
2、查看集群节点状态
kingbase@uos01:~/cluster/R6HA/kha/kingbase/bin$ ./repmgr cluster show
ID | Name | Role | Status | Upstream | Location | Priority | Timeline | Connection string
----+---------+---------+-----------+----------+----------+----------+----------+
1 | node238 | primary | * running | | default | 100 | 1 | host=192.168.7.238 user=esrep dbname=esrep port=54321 connect_timeout=10 keepalives=1 keepalives_idle=10 keepalives_interval=1 keepalives_count=3
2 | node239 | standby | running | node238 | default | 100 | 1 | host=192.168.7.239 user=esrep dbname=esrep port=54321 connect_timeout=10 keepalives=1 keepalives_idle=10 keepalives_interval=1 keepalives_count=3
3、通过vip连接数据库查看流复制状态
kingbase@uos01:~/cluster/R6HA/kha/kingbase/bin$ ./ksql -h 192.168.7.244 -U system test
ksql (V8.0)
Type "help" for help.
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
-------+----------+---------+------------------+---------------+-----------------+
14935 | 16384 | esrep | node239 | 192.168.7.239 | | 58172 | 2021-03-01 12:22:51.831920+08 | | streaming | 0/6000670 | 0/6000670 | 0/6000670 | 0/6000670 | | | | 1 | quorum | 2021-03-01 12:24:30.751707+08
(1 row)
五、主备switchover切换测试
1、切换前集群节点状态
kingbase@uos02:~/cluster/R6HA/kha/kingbase/bin$ ./repmgr cluster show
ID | Name | Role | Status | Upstream | Location | Priority | Timeline | Connection string
----+---------+---------+-----------+----------+----------+----------+----------+
1 | node238 | primary | * running | | default | 100 | 1 | host=192.168.7.238 user=esrep dbname=esrep port=54321 connect_timeout=10 keepalives=1 keepalives_idle=10 keepalives_interval=1 keepalives_count=3
2 | node239 | standby | running | node238 | default | 100 | 1 | host=192.168.7.239 user=esrep dbname=esrep port=54321 connect_timeout=10 keepalives=1 keepalives_idle=10 keepalives_interval=1 keepalives_count=3
2、执行switchover的切换
kingbase@uos02:~/cluster/R6HA/kha/kingbase/bin$ ./repmgr standby switchover --siblings-follow
NOTICE: executing switchover on node "node239" (ID: 2)
WARNING: option "--sibling-nodes" specified, but no sibling nodes exist
.......
DETAIL: node "node239" is now primary and node "node238" is attached as standby
INFO: unpausing repmgrd on node "node238" (ID 1)
INFO: unpause node "node238" (ID 1) successfully
INFO: unpausing repmgrd on node "node239" (ID 2)
INFO: unpause node "node239" (ID 2) successfully
NOTICE: STANDBY SWITCHOVER has completed successfully
3、查看切换后vip的加载
kingbase@uos02:~/cluster/R6HA/kha/kingbase/bin$ ip add sh
2: enp0s3: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 08:00:27:c9:c0:27 brd ff:ff:ff:ff:ff:ff
inet 192.168.7.239/24 brd 192.168.7.255 scope global noprefixroute enp0s3
valid_lft forever preferred_lft forever
inet 192.168.7.244/24 scope global secondary enp0s3:3
valid_lft forever preferred_lft forever
# 由以上获知，vip已经加载到新的主库上
4、查看切换后的节点状态（切换状态正常）
kingbase@uos02:~/cluster/R6HA/kha/kingbase/bin$ ./repmgr cluster show
ID | Name | Role | Status | Upstream | Location | Priority | Timeline | Connection string
----+---------+---------+-----------+----------+----------+----------+----------+
1 | node238 | standby | running | node239 | default | 100 | 1 | host=192.168.7.238 user=esrep dbname=esrep port=54321 connect_timeout=10 keepalives=1 keepalives_idle=10 keepalives_interval=1 keepalives_count=3
2 | node239 | primary | * running | | default | 100 | 2 | host=192.168.7.239 user=esrep dbname=esrep port=54321 connect_timeout=10 keepalives=1 keepalives_idle=10 keepalives_interval=1 keepalives_count=3
5、查看原主库vip（已经被卸载）
2: enp0s3: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 08:00:27:56:02:82 brd ff:ff:ff:ff:ff:ff
inet 192.168.7.238/24 brd 192.168.7.255 scope global noprefixroute enp0s3
valid_lft forever preferred_lft forever
六、集群failover switch测试
1、关闭主库数据库服务
kingbase@uos02:~/cluster/R6HA/kha/kingbase/bin$ ./sys_ctl stop -D ../data
waiting for server to shut down.... done
server stopped
2、查看failover后集群节点状态
Tips：
在recovery='manual'情况下，failover切换后，原主库不能自动恢复为备库加入集群，需要手工恢复。
kingbase@uos01:~/cluster/R6HA/kha/kingbase/bin$ ./repmgr cluster show
ID | Name | Role | Status | Upstream | Location | Priority | Timeline | Connection string
----+---------+---------+----------------------+-----------+----------+----------+
1 | node238 | standby | ! running as primary | ? node239 | default | 100 | 3 | host=192.168.7.238 user=esrep dbname=esrep port=54321 connect_timeout=10 keepalives=1 keepalives_idle=10 keepalives_interval=1 keepalives_count=3
2 | node239 | primary | ? unreachable | | default | 100 | ? | host=192.168.7.239 user=esrep dbname=esrep port=54321 connect_timeout=10 keepalives=1 keepalives_idle=10 keepalives_interval=1 keepalives_count=3
WARNING: following issues were detected
- node "node238" (ID: 1) is registered as standby but running as primary
- unable to connect to node "node238" (ID: 1)'s upstream node "node239" (ID: 2)
- unable to determine if node "node238" (ID: 1) is attached to its upstream node "node239" (ID: 2)
- unable to connect to node "node239" (ID: 2)
- node "node239" (ID: 2) is registered as an active primary but is unreachable
# 从以上获知，在主库数据库服务宕机后，发生failover的切换，原备库被切换为新的主库，在节点状态中原主库的状态为”unreachable“。
3、原主库启动数据库服务
kingbase@uos02:~/cluster/R6HA/kha/kingbase/bin$ ./sys_ctl start -D ../data
.......
server started
4、查看集群节点状态
kingbase@uos01:~/cluster/R6HA/kha/kingbase/bin$ ./repmgr cluster show
ID | Name | Role | Status | Upstream | Location | Priority | Timeline | Connection string
----+---------+---------+-----------+----------+----------+----------+----------+
1 | node238 | primary | * running | | default | 100 | 3 | host=192.168.7.238 user=esrep dbname=esrep port=54321 connect_timeout=10 keepalives=1 keepalives_idle=10 keepalives_interval=1 keepalives_count=3
2 | node239 | primary | ! running | | default | 100 | 2 | host=192.168.7.239 user=esrep dbname=esrep port=54321 connect_timeout=10 keepalives=1 keepalives_idle=10 keepalives_interval=1 keepalives_count=3
WARNING: following issues were detected
- node "node239" (ID: 2) is running but the repmgr node record is inactive
# 从以上获知，原主库启动后不能作为备库，自动加入到集群中，现在集群出现了”双主“现象，需要人工处理，将原主库重新join到集群。
5、将原主库重新加入到集群中
kingbase@uos02:~/cluster/R6HA/kha/kingbase/bin$ ./repmgr node rejoin -h 192.168.7.238 -U esrep -d esrep --force-rewind
NOTICE: sys_rewind execution required for this node to attach to rejoin target node 1
......
NOTICE: NODE REJOIN successful
DETAIL: node 2 is now attached to node 1
6、查看集群节点状态
kingbase@uos01:~/cluster/R6HA/kha/kingbase/bin$ ./repmgr cluster show
ID | Name | Role | Status | Upstream | Location | Priority | Timeline | Connection string
----+---------+---------+-----------+----------+----------+----------+----------+
1 | node238 | primary | * running | | default | 100 | 3 | host=192.168.7.238 user=esrep dbname=esrep port=54321 connect_timeout=10 keepalives=1 keepalives_idle=10 keepalives_interval=1 keepalives_count=3
2 | node239 | standby | running | node238 | default | 100 | 2 | host=192.168.7.239 user=esrep dbname=esrep port=54321 connect_timeout=10 keepalives=1 keepalives_idle=10 keepalives_interval=1 keepalives_count=3
# 从以上获知，现在集群节点状态已经恢复正常
7、查看failover后vip的加载
2: enp0s3: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 08:00:27:56:02:82 brd ff:ff:ff:ff:ff:ff
inet 192.168.7.238/24 brd 192.168.7.255 scope global noprefixroute enp0s3
valid_lft forever preferred_lft forever
inet 192.168.7.244/24 scope global secondary enp0s3:3
valid_lft forever preferred_lft forever
# 从以上获知，vip已经加载到新的主库
8、查看主备流复制状态
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
-------+----------+---------+------------------+---------------+-----------------+
16412 | 16384 | esrep | node239 | 192.168.7.239 | | 59958 | 2021-03-01 12:40:19.069026+08 | | streaming | 0/802B900 | 0/802B900 | 0/802B900 | 0/802B900 | | | | 1 | quorum | 2021-03-01 12:42:08.616363+08
(1 row)
七、配置过程中的故障信息
kingbase@uos01:~/cluster/R6HA/kha/kingbase/bin$ ./sys_monitor.sh restart
the dir "/sbin" has no execute file "arping", please set [arping_path] in /home/kingbase/cluster/R6HA/kha/kingbase/bin/../etc/repmgr.conf
kingbase@uos01:~/cluster/R6HA/kha/kingbase/bin$ ./sys_monitor.sh restart
.......
server started
2021-03-01 12:19:34 execute to start DB on "[192.168.7.238]" success, connect to check it.
2021-03-01 12:19:35 DB on "[192.168.7.238]" start success.
2021-03-01 12:19:35 Try to ping trusted_servers on host 192.168.7.238 ...
2021-03-01 12:19:37 Try to ping trusted_servers on host 192.168.7.239 ...
2021-03-01 12:19:40 begin to start DB on "[192.168.7.239]".
incorrect command permissions for the virtual ip.
waiting for server to start.... done
server started
2021-03-01 12:19:40 execute to start DB on "[192.168.7.239]" success, connect to check it.
2021-03-01 12:19:41 DB on "[192.168.7.239]" start success.
ERROR: No execute permission for "/usr/sbin/ip"
incorrect command permissions for the virtual ip.
2021-03-01 12:19:42 There is no primary DB running, will do nothing and exit.
# 从以上故障获知，在配置文件没有设置arping可执行文件的路径及ip和arping可执行文件没有设置setuid权限
八、总结：
以下是操作步骤：
1） 确定需要配置的vip地址，需和物理ip同网段，并且没有被使用。
2） 查看arping和ip可执行文件的路径及arping的版本。
3） 对ip和arping可执行文件配置setuid权限（s权限）。
4） 修改repmgr.conf文件添加配置项。
5） 重新启动集群并验证集群状态。
6） 主备切换测试。
7） 应用连接vip访问测试。
KINGBASE研究院
