KingbaseES V8R3 集群运维系列 -- 修改数据库服务端口

​

案例说明：
KingbaseES V8R3集群数据库服务端口（默认：54321）的配置在数据库及集群多个配置文件中都存在，修改端口需要更改所有的配置文件，本案例详细描述了数据库服务端口修改操作步骤，可用于生产环境下数据库服务端口修改的借鉴。
注意：对于数据库服务端口的修改，kingbase_monitor.sh不支持一键参数修改。

适用版本：
KingbaseES V8R3

操作系统：

[kingbase@node101 bin]$ cat /etc/centos-release
CentOS Linux release 7.9.2009 (Core)

集群架构：

TEST=# show pool_nodes;
 node_id |   hostname    | port  | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay
---------+---------------+-------+--------+-----------+---------+------------+-------------------+-------------------
 0       | 192.168.1.101 | 54321 | up     | 0.500000  | primary | 0          | false             | 0
 1       | 192.168.1.102 | 54321 | up     | 0.500000  | standby | 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
------+----------+---------+------------------+---------------+-----------------+-------------+-------------------------------+--------------+-----------+---------------+----------------+----------------+-----------------+---------------+------------
 4743 |       10 | SYSTEM  | node2            | 192.168.1.102 |                 |       24029 | 2023-01-09 14:39:34.599367+08 |              | streaming | 0/B40000D0    | 0/B40000D0     | 0/B40000D0     | 0/B40000D0      |             2 | sync
(1 row)

当前数据库服务端口：

TEST=# show port;
 port
-------
 54321
(1 row)

一、数据库配置文件
1、数据库配置文件存储路径

[kingbase@node101 bin]$ pwd
/home/kingbase/cluster/HAR3/db/data

2、数据库服务配置文件

#kingbase.conf 
[kingbase@node101 bin]$ cat kingbase.conf |grep port|grep -v '#'
port='54321'

#主库recovery.done文件
[kingbase@node101 bin]$ cat recovery.done |grep port|grep -v '#'
primary_conninfo='port=54321 host=192.168.1.101 user=SYSTEM password=MTIzNDU2Cg== application_name=node1'

#备库recovery.conf文件
kingbase@node102 bin]$ cat recovery.conf |grep -i port
primary_conninfo='port=54321 host=192.168.1.101 user=SYSTEM password=MTIzNDU2Cg== application_name=node2'

#kingbase.auto.conf（默认没有数据库服务端口配置）
[kingbase@node101 bin]$ cat kingbase.auto.conf
# Do not edit this file manually!
# It will be overwritten by ALTER SYSTEM command.
SYNCHRONOUS_STANDBY_NAMES = '1 (node1, node2)'

3、集群数据库模板配置文件

1）配置文件存储路径：

[kingbase@node101 etc]$ pwd
/home/kingbase/cluster/HAR3/db/etc

2）配置文件

#recovery.done配置文件模板
[kingbase@node101 bin]$ cat recovery.done |grep port|grep -v '#'
primary_conninfo='port=54321 host=192.168.1.101 user=SYSTEM password=MTIzNDU2Cg== application_name=node1'

#kingbase.conf配置文件模板
[kingbase@node101 bin]$ cat kingbase.conf |grep port|grep -v '#'
port='54321'

#数据库HAmodule.conf配置文件
[kingbase@node101 bin]$ cat HAmodule.conf |grep -i kb_port
KB_PORT="54321"

二、集群配置文件
1、集群配置文件存储路径

[kingbase@node101 kingbasecluster]$ pwd
/home/kingbase/cluster/HAR3/kingbasecluster/etc

2、集群配置文件

#kingbasecluster.conf配置文件
[kingbase@node101 kingbasecluster]$ cat kingbasecluster.conf|grep -i backend_port
backend_port0=54321
backend_port0=54321
backend_port1=54321

#kingbasecluster.conf.sample模板配置文件
[kingbase@node101 kingbasecluster]$ cat kingbasecluster.conf.sample|grep backend_port
backend_port0 = 54321

#集群HAmodule.conf配置文件
[kingbase@node101 kingbasecluster]$ cat HAmodule.conf |grep -i kb_port
#database listening port,default 54321.example:KB_PORT="54321"
KB_PORT="54321"

三、数据库服务端口修改脚本

Tips:
此脚本只在CentOS 7下bash环境执行了测试，其他系统的shell环境，需要测试后借鉴使用。执行脚本前需要关闭所有节点的数据库服务。

[kingbase@node101 ~]$ cat update_kb_port.sh
#!/bin/bash
#update_kb_port.sh
# Edit KingbasES V8R3 cluster db port
#以下目录为数据库和集群配置文件的存储路径
Db_path='/home/kingbase/cluster/HAR3/db/data'
Db_etc='/home/kingbase/cluster/HAR3/db/etc'
Cluster_etc='/home/kingbase/cluster/HAR3/kingbasecluster/etc'

#数据库修改前后的服务端口
Old_kb_port=54321
New_kb_port=54323

#注意：修改集群数据库端口，需要先关闭整个集群的数据库服务。
# check db service is alive
Srv_is_alive=`netstat -an|grep $Old_kb_port|grep -i listen|head -1`

if [ -z "$Srv_is_alive" ] ;then
#updat数据库配置文件中的数据库服务端口
echo "Update database config: "

cd $Db_path
echo "Current diretory is : $Db_path"

if [ -f kingbase.conf ];then
  sed -i 's/#port/port/g' kingbase.conf
  sed -i "s/$Old_kb_port/$New_kb_port/g" kingbase.conf
  cat kingbase.conf|grep -i port
else
  echo "Kingbase.conf is not exist!"
fi

if [ -f recovery.conf ];then
  sed -i "s/$Old_kb_port/$New_kb_port/g" recovery.conf
  cat recovery.conf|grep -i port
else
  echo "recovery.conf is not exist!"
fi

if [ -f recovery.done ];then
  sed -i "s/$Old_kb_port/$New_kb_port/g" recovery.done
  cat recovery.done|grep -i port
else
  echo "recovery.done is not exist!"
fi

if [ -f kingbase.auto.conf ];then
  sed -i "s/$Old_kb_port/$New_kb_port/g" kingbase.auto.conf
  cat kingbase.auto.conf|grep -i port
else
  echo "kingase.auto.conf is not exist!"
fi

#updat数据库配置模板文件中的数据库服务端口
echo ""
echo "Update database etc config: "
cd $Db_etc

if [ -f HAmodule.conf ];then
  sed -i "s/$Old_kb_port/$New_kb_port/g" HAmodule.conf
  cat HAmodule.conf|grep -i kb_port
else
  echo "HAmodule.conf is not exist!"
fi

if [ -f recovery.done ];then
  sed -i "s/$Old_kb_port/$New_kb_port/g" recovery.done
  cat recovery.done|grep -i port
else
  echo "recovery.done is not exist!"
fi

if [ -f kingbase.conf ];then
  sed -i 's/#port/port/g' kingbase.conf
  sed -i "s/$Old_kb_port/$New_kb_port/g" kingbase.conf
  cat kingbase.conf|grep -i port
else
  echo "Kingbase.conf is not exist!"
fi

#updat集群配置文件中的数据库服务端口
echo ""
echo "Update kingbasecluster config: "

cd $Cluster_etc

if [ -f HAmodule.conf ];then
  sed -i "s/$Old_kb_port/$New_kb_port/g" HAmodule.conf
  cat HAmodule.conf|grep -i kb_port
else
  echo "HAmodule.conf is not exist!"
fi

if [ -f kingbasecluster.conf ];then
  sed -i "s/$Old_kb_port/$New_kb_port/g" kingbasecluster.conf
  cat kingbasecluster.conf|grep -i backend_port
else
  echo "kingbasecluster.conf is not exist!"
fi

if [ -f kingbasecluster.conf.sample ];then
  sed -i "s/$Old_kb_port/$New_kb_port/g" kingbasecluster.conf.sample
  cat kingbasecluster.conf.sample|grep -i backend_port
else
  echo "kingbasecluster.conf.sample is not exist!"
fi

else
 echo 'Database is alive,please first shutdown cluster .'
 exit 0
fi

四、关闭集群数据库服务
[kingbase@node101 bin]$ ./kingbase_monitor.sh stop

五、执行脚本更改数据库服务端口

Tips:
此脚本需要在集群所有节点执行。

[kingbase@node101 ~]$ sh update_kb_port.sh
Update database config:
Current diretory is : /home/kingbase/cluster/HAR3/db/data
port = 54323                    # (change requires restart)
                                        # supported by the operating system:
                                        # supported by the operating system:
# ERROR REPORTING AND LOGGING
                                        #   %r = remote host and port
port='54323'
recovery.conf is not exist!
recovery.done is not exist!

Update database etc config:
#database listening port,default 54323.example:KB_PORT="54323"
KB_PORT="54323"
primary_conninfo='port=54323 host=192.168.1.101 user=SYSTEM password=MTIzNDU2Cg== application_name=node1'
port = 54323                            # (change requires restart)
                                        # supported by the operating system:
                                        # supported by the operating system:
# ERROR REPORTING AND LOGGING
                                        #   %r = remote host and port
port='54323'

Update kingbasecluster config:
#database listening port,default 54323.example:KB_PORT="54323"
KB_PORT="54323"
backend_port0=54323
#backend_port1=5433
backend_port0=54323
backend_port1=54323
backend_port1=54323

六、重新启动集群

#启动集群
[kingbase@node101 bin]$ ./kingbase_monitor.sh restart
-----------------------------------------------------------------------
2023-01-09 16:43:15 KingbaseES automation beging...
2023-01-09 16:43:15 stop kingbasecluster [192.168.1.101] ...

......
now we check again
=======================================================================
|             ip |                       program|              [status]
[  192.168.1.101]|             [kingbasecluster]|              [active]
[  192.168.1.102]|             [kingbasecluster]|              [active]
[  192.168.1.101]|                    [kingbase]|              [active]
[  192.168.1.102]|                    [kingbase]|              [active]
=======================================================================

#查看数据库服务端口
[kingbase@node101 bin]$ netstat -an |grep 543|grep -i listen |head -1
tcp        0      0 0.0.0.0:54323           0.0.0.0:*               LISTEN

#连接数据库访问
[kingbase@node101 bin]$ ./ksql -U SYSTEM -W 123456 TEST -p 54323
ksql (V008R003C002B0370)
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
------+----------+---------+------------------+---------------+-----------------+-------------+-------------------------------+--------------+-----------+---------------+----------------+----------------+-----------------+---------------+------------
 8986 |       10 | SYSTEM  | node2            | 192.168.1.102 |                 |       64305 | 2023-01-09 16:43:30.566336+08 |              | streaming | 0/B7000B50    | 0/B7000B50     | 0/B7000B50     | 0/B7000B50      |             2 | sync
(1 row)

---如上所示，数据库服务端口修改成功。

七、修改sys_backup.sh备份端口

Tips：
如果集群使用了，sys_backup.sh脚本执行物理备份，在修改数据库端口后，需要修改备份配置文件sys_backup.conf。

[kingbase@node101 bin]$ cat ../share/sys_backup.conf|grep -i port
# database port of single
_single_db_port="54321"