KingbaseES V8R3 集群运维系列之 -- 一键修改集群用户密码
案例说明：
在KingbaseES V8R3集群的最新版本中增加了kingbase_monitor.sh一键修改集群用户密码的功能，由于KingbaseES V8R3集群用户密码除了修改数据库登录密码，还要修改对应的配置文件，手工修改容易遗漏。通过对一键修改密码功能的操作，可以在生产过程中简化对集群的维护。
kingbaseES V8R3集群一键修改密码说明：
1、 命令行命令
kingbase_monitor.sh change_password user old_password new_password
kingbase_monitor.sh change_password user old_password new_password —修改集群使用的用户的密码。
2、描述
kingbase_monitor.sh change_password user old_password new_password判断用户是否为集群使用的用户，如果是则修改用户密码，如果不是则提示用户集群未使用该用户，请使用sql方式修改用户密码。
3、选项
以下是脚本执行参数：
change_password
脚本调用修改用户密码函数
user
要进行修改密码的用户，只能是集群初始化时指定的用户
old_password
用户的旧密码
new_password
用户的新密码
例：
4、注意事项
1.必须在集群所有节点状态正常的情况下才能执行修改密码操作
2.kingbase_monitor.sh change_password 用户名 '旧密码' '新密码' 使用一键修改密码功能时 旧密码，新密码参数必须使用''括起来
3.用户必须是集群流复制使用的用户才能进行修改密码，否则会提示集群未使用该用户，请使用sql alter user命令进行修改密码
适用版本：
KingbaseES V8R3
集群架构：
一、查看集群状态
TEST=# show pool_nodes;
node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay
---------+---------------+-------+--------+-----------+---------+------------+-------------------+-------------------
0 | 192.168.7.243 | 54321 | up | 0.500000 | standby | 0 | false | 0
1 | 192.168.7.248 | 54321 | up | 0.500000 | primary | 0 | true | 0
(2 rows)
TEST=# select * from sys_stat_replication;
PID | USESYSID | USENAME | APPLICATION_NAME | CLIENT_ADDR | CLIENT_HOSTNAME | CLIENT_PORT | BACKEND_START | BACKEND_XMIN | STATE | SENT_LOCATION | WRITE_LOCATION | FLUSH_LOCATION | REPLAY_LOCATION | SYNC_PRIORITY | SYNC_STATE
------+----------+---------+------------------+---------------+-----------------+-------------+--
9903 | 10 | SYSTEM | node243 | 192.168.7.243 | | 47620 | 2021-03-01 16:15:28.263706+08 | | streaming | 0/13003B50 | 0/13003B50 | 0/13003B50 | 0/13003B50 | 1 | sync
(1 row)
二、修改system用户密码
# 查看kingbase_monitor.sh功能
[kingbase@node1 bin]$ ./kingbase_monitor.sh
-----------------------------------------------------------------------
2021-03-01 16:20:55 KingbaseES automation beging...
usage: ./kingbase_monitor.sh start | stop | restart | set [--restart] | change_password user old_password new_password
# 一键修改用户密码
[kingbase@node1 bin]$ ./kingbase_monitor.sh change_password SYSTEM '123456' '12345678'
-----------------------------------------------------------------------
2021-03-01 16:25:34 KingbaseES automation beging...
Begin alter user password
2021-03-01 16:25:55: pid 20642: LOG: stop request sent to kingbasecluster. waiting for termination...
..done.
2021-03-01 16:25:41: pid 14549: LOG: stop request sent to kingbasecluster. waiting for termination...
..done.
Alter user password OK
=注意：由以上信息获知，在修改集群用户密码时，将会stop主备库的kingbasecluster服务，在生产环境修改时，需要注意，尽量不要在业务运行期间修改。=
三、验证密码修改效果
1、验证system用户密码修改结果
测试修改SUPERMANAGER_V8ADMIN用户密码：（修改失败）
2、查看kingbasecluster 服务（所有节点）
[kingbase@node1 bin]$ netstat -an |grep 9999
unix 2 [ ACC ] STREAM LISTENING 2999949 @/tmp/dbus-fXYPBXlK
unix 2 [ ACC ] STREAM LISTENING 2999948 @/tmp/dbus-h5GlLPYf
unix 2 [ ] STREAM CONNECTED 2999944
[kingbase@node1 bin]$ netstat -an |grep 9000
[kingbase@node1 bin]$ netstat -an |grep 9694
3、查看kingbasecluster日志
主库：
备库：
=通过以上信息获知，system用户密码修改成功，但是主备库上的kingbasecluster服务都被stop。=
四、手工启动kingbasecluster服务（root用户）
1、root用户手工启动kingbasecluster服务
[root@node1 ~]# /home/kingbase/cluster/kha/kingbasecluster/bin/kingbasecluster -n >>/home/kingbase/cluster/kha/log/cluster.log 2>&1 &
[1] 18241
2、查看数据库和集群进程
[root@node1 ~]# ps -ef |grep kingbase
kingbase 9254 1 0 16:13 ? 00:00:00 /home/kingbase/cluster/kha/db/bin/kingbase -D /home/kingbase/cluster/kha/db/data
kingbase 9256 9254 0 16:13 ? 00:00:00 kingbase: logger process
kingbase 9816 9254 0 16:15 ? 00:00:00 kingbase: checkpointer process
kingbase 9817 9254 0 16:15 ? 00:00:00 kingbase: writer process
kingbase 9818 9254 0 16:15 ? 00:00:00 kingbase: wal writer process
kingbase 9819 9254 0 16:15 ? 00:00:00 kingbase: autovacuum launcher process
kingbase 9820 9254 0 16:15 ? 00:00:00 kingbase: archiver process failed on 000000020000000000000010
kingbase 9821 9254 0 16:15 ? 00:00:00 kingbase: stats collector process
kingbase 9822 9254 0 16:15 ? 00:00:00 kingbase: bgworker: syslogical supervisor
kingbase 9903 9254 0 16:15 ? 00:00:00 kingbase: wal sender process SYSTEM 192.168.7.243(47620) streaming 0/13004420
root 18241 17531 0 16:40 pts/0 00:00:00 /home/kingbase/cluster/kha/kingbasecluster/bin/kingbasecluster -n
root 18260 18241 0 16:40 pts/0 00:00:00 kingbasecluster: watchdog
root 18310 18241 0 16:40 pts/0 00:00:00 kingbasecluster: lifecheck
root 18312 18310 0 16:40 pts/0 00:00:00 kingbasecluster: heartbeat receiver
root 18314 18310 0 16:40 pts/0 00:00:00 kingbasecluster: heartbeat sender
root 18315 18241 0 16:40 pts/0 00:00:00 kingbasecluster: wait for connection request
........
root 18334 18241 0 16:40 pts/0 00:00:00 kingbasecluster: PCP: wait for connection request
root 18335 18241 0 16:40 pts/0 00:00:00 kingbasecluster: worker process
3、验证kingbasecluster服务
TEST=# show pool_nodes;
node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay
---------+---------------+-------+--------+-----------+---------+------------+-------------------+-------------------
0 | 192.168.7.243 | 54321 | up | 0.500000 | standby | 0 | true | 0
1 | 192.168.7.248 | 54321 | up | 0.500000 | primary | 0 | false | 0
(2 rows)
TEST=# select * from sys_stat_replication;
PID | USESYSID | USENAME | APPLICATION_NAME | CLIENT_ADDR | CLIENT_HOSTNAME | CLIENT_PORT | BACKEND_START | BACKEND_XMIN | STATE | SENT_LOCATION | WRITE_LOCATION | FLUSH_LOCATION | REPLAY_LOCATION | SYNC_PRIORITY | SYNC_STATE
------+----------+---------+------------------+---------------+-----------------+-------------+-
9903 | 10 | SYSTEM | node243 | 192.168.7.243 | | 47620 | 2021-03-01 16:15:28.263706+08 | | streaming | 0/13004420 | 0/13004420 | 0/13004420 | 0/13004420 | 1 | sync
(1 row)
五、测试kingbase_monitor.sh一键重启（可选）
[kingbase@node1 bin]$ ./kingbase_monitor.sh restart
-----------------------------------------------------------------------
2021-03-01 16:48:42 KingbaseES automation beging...
2021-03-01 16:48:42 stop kingbasecluster [192.168.7.243] ...
remove status file /home/kingbase/cluster/kha/run/kingbasecluster/kingbasecluster_status
......................
all started..
...
now we check again
=======================================================================
| ip | program| [status]
[ 192.168.7.243]| [kingbasecluster]| [active]
[ 192.168.7.248]| [kingbasecluster]| [active]
[ 192.168.7.243]| [kingbase]| [active]
[ 192.168.7.248]| [kingbase]| [active]
=======================================================================
六、总结
对于KingbaseES V8R3集群，kingbase_monitor.sh一键修改密码的功能，提升了集群运维管理的效率；但是需要注意的是，在修改集群用户密码时，将会将集群所有node的kingbasecluster服务stop，这个在生产环境使用时，需要注意。
KINGBASE研究院
