KingbaseES R3 集群一键修改集群用户密码案例

案例说明：

在KingbaseES R3集群的最新版本中增加了kingbase_monitor.sh一键修改集群用户密码的功能，本案例是对此功能的测试。

kingbaseES R3集群一键修改密码说明：

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 R3集群一键修改密码测试案例：

数据库版本：

集群架构：

一、查看集群状态

[kingbase@node1 bin]$  ./ksql -U SYSTEM -W 123456 TEST -p 9999

ksql (V008R003C002B0270)

Type "help" for help.

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、查看kingbasecluster进程

[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     13110 11490  0 14:07 pts/1    00:00:00 su - kingbase

kingbase 13112 13110  0 14:07 pts/1    00:00:00 -bash

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     18316 18241  0 16:40 pts/0    00:00:00 kingbasecluster: wait for connection request

root     18317 18241  0 16:40 pts/0    00:00:00 kingbasecluster: wait for connection request

root     18318 18241  0 16:40 pts/0    00:00:00 kingbasecluster: wait for connection request

root     18319 18241  0 16:40 pts/0    00:00:00 kingbasecluster: wait for connection request

root     18320 18241  0 16:40 pts/0    00:00:00 kingbasecluster: wait for connection request

root     18321 18241  0 16:40 pts/0    00:00:00 kingbasecluster: wait for connection request

root     18322 18241  0 16:40 pts/0    00:00:00 kingbasecluster: wait for connection request

root     18323 18241  0 16:40 pts/0    00:00:00 kingbasecluster: wait for connection request

root     18324 18241  0 16:40 pts/0    00:00:00 kingbasecluster: wait for connection request

root     18325 18241  0 16:40 pts/0    00:00:00 kingbasecluster: wait for connection request

root     18326 18241  0 16:40 pts/0    00:00:00 kingbasecluster: wait for connection request

root     18327 18241  0 16:40 pts/0    00:00:00 kingbasecluster: wait for connection request

root     18328 18241  0 16:40 pts/0    00:00:00 kingbasecluster: wait for connection request

root     18329 18241  0 16:40 pts/0    00:00:00 kingbasecluster: wait for connection request

root     18330 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服务

[kingbase@node1 bin]$ ./ksql -U SYSTEM -W 12345678 TEST -p 9999

ksql (V008R003C002B0270)

Type "help" for help.

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)

[kingbase@node1 bin]$ ./ksql -U SYSTEM -W 12345678 TEST

ksql (V008R003C002B0270)

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_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

DEL VIP NOW AT 2021-03-01 16:49:05 ON enp0s3

No VIP on my dev, nothing to do.

2021-03-01 16:48:48 Done...

2021-03-01 16:48:48 stop kingbasecluster [192.168.7.248] ...

remove status file  /home/kingbase/cluster/kha/run/kingbasecluster/kingbasecluster_status

DEL VIP NOW AT 2021-03-01 16:48:53 ON enp0s3

No VIP on my dev, nothing to do.

2021-03-01 16:48:53 Done...

2021-03-01 16:48:53 stop kingbase [192.168.7.243] ...

set /home/kingbase/cluster/kha/db/data down now...

2021-03-01 16:48:56 Done...

2021-03-01 16:48:57 Del kingbase VIP [192.168.7.245/24] ...

DEL VIP NOW AT 2021-03-01 16:49:16 ON enp0s3

No VIP on my dev, nothing to do.

2021-03-01 16:48:58 Done...

2021-03-01 16:48:58 stop kingbase [192.168.7.248] ...

set /home/kingbase/cluster/kha/db/data down now...

2021-03-01 16:49:04 Done...

2021-03-01 16:49:05 Del kingbase VIP [192.168.7.245/24] ...

DEL VIP NOW AT 2021-03-01 16:49:05 ON enp0s3

execute: [/sbin/ip addr del 192.168.7.245/24 dev enp0s3]

Oprate del ip cmd end.

2021-03-01 16:49:05 Done...

......................

all stop..

ping trust ip 192.168.7.1 success ping times :[3], success times:[2]

ping trust ip 192.168.7.1 success ping times :[3], success times:[2]

start crontab kingbase position : [3]

Redirecting to /bin/systemctl restart  crond.service

start crontab kingbase position : [2]

Redirecting to /bin/systemctl restart  crond.service

ADD VIP NOW AT 2021-03-01 16:49:18 ON enp0s3

execute: [/sbin/ip addr add 192.168.7.245/24 dev enp0s3 label enp0s3:2]

execute: /home/kingbase/cluster/kha/db/bin/arping -U 192.168.7.245 -I enp0s3 -w 1

ARPING 192.168.7.245 from 192.168.7.245 enp0s3

Sent 1 probes (1 broadcast(s))

Received 0 response(s)

ping vip 192.168.7.245 success ping times :[3], success times:[2]

ping vip 192.168.7.245 success ping times :[3], success times:[2]

now,there is a synchronous standby.

wait kingbase recovery 5 sec...

start crontab kingbasecluster line number: [6]

Redirecting to /bin/systemctl restart  crond.service

start crontab kingbasecluster line number: [3]

Redirecting to /bin/systemctl restart  crond.service

......................

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 R3集群，kingbase_monitor.sh一键修改密码的功能，增加了管理员管理集群用户的方便性；但是需要注意的是，在修改集群用户密码时，将会将集群所有node的kingbasecluster服务stop，这个在生产环境使用时，需要注意。

巴特西

KingbaseES R3 集群一键修改集群用户密码案例

最新文章

热门文章