案例说明:
默认KingbaseES R3集群部署后,数据存储目录(data)在/home/kingbase下,部署时不能更改;本案例是在部署完成后,迁移data目录到其他指定的存储位置。
数据库版本:
test=# select version();
version
-------------------------------------------------------------------------------------------------------------------------
Kingbase V008R003C002B0270 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-46), 64-bit
(1 row)
集群节点信息:
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
一、创建新的data路径(所有节点)
[kingbase@node1 ~]$ mkdir -p /data/kingbase/cluster/r3/data
[kingbase@node1 ~]$ chmod 700 /data/kingbase/cluster/r3/data
二、修改配置文件中data路径
1、修改HAmodule.conf参数(所有节点)
=== 以下两个目录中文件的都要修改===
[kingbase@node3 etc]$ pwd
/home/kingbase/cluster/kha/db/etc
[kingbase@node1 etc]$ pwd
/home/kingbase/cluster/kha/kingbasecluster/etc
****
修改前:
[kingbase@node3 etc]$ cat HAmodule.conf |grep -i data
#location of the db data directory.example:KB_DATA_PATH="./cluster/clusterName/db/data"
KB_DATA_PATH="/home/kingbase/cluster/kha/db/data"
#database instance built-in database.example:KB_DATANAME="TEST"
KB_DATANAME="TEST"
#database listening port,default 54321.example:KB_PORT="54321"
#temporary files to query database status in clusster.example:KB_CLUSTER_STATUS="./log/pool_nodes"
修改后:
[kingbase@node3 etc]$ cat HAmodule.conf |grep -i kb_data
#location of the db data directory.example:KB_DATA_PATH="./cluster/clusterName/db/data"
#KB_DATA_PATH="/home/kingbase/cluster/kha/db/data"
KB_DATA_PATH="/data/kingbase/cluster/r3/data"
2、修改kingbase.conf 文件(所有节点)
=== 以下两个目录中文件的都要修改===
[kingbase@node3 etc]$ pwd
/home/kingbase/cluster/kha/db/etc
[kingbase@node3 data]$ pwd
/home/kingbase/cluster/kha/db/data
修改前:
[kingbase@node1 etc]$ cat kingbase.conf |grep -i data
# option or KBDATA environment variable, represented here as ConfigDir.
#data_directory = 'ConfigDir' # use data in another directory
#fsync = on # flush data to disk for crash safety
# unrecoverable data corruption)
# open_datasync
# fdatasync (default on Linux)
# Set these on the master and on any standby that will send replication data.
# can be absolute or relative to KBDATA
# %d = database name
#client_encoding = sql_ascii # actually, defaults to database
log_directory='/home/kingbase/cluster/kha/db/data/sys_log/'
修改后:
[kingbase@node1 etc]$ cat kingbase.conf |grep -i data
# option or KBDATA environment variable, represented here as ConfigDir.
#data_directory = 'ConfigDir' # use data in another directory
#fsync = on # flush data to disk for crash safety
# unrecoverable data corruption)
# open_datasync
# fdatasync (default on Linux)
# Set these on the master and on any standby that will send replication data.
# can be absolute or relative to KBDATA
# %d = database name
#client_encoding = sql_ascii # actually, defaults to database
#log_directory='/home/kingbase/cluster/kha/db/data/sys_log/'
log_directory='/data/kingbase/cluster/r3/data/sys_log/'
3、修改kingbasecluster.conf文件(所有节点)
[kingbase@node1 etc]$ pwd
/home/kingbase/cluster/kha/kingbasecluster/etc
修改前:
[kingbase@node3 etc]$ cat kingbasecluster.conf|grep -i data
backend_data_directory0='/home/kingbase/cluster/kha/db/data'
# Data directory for backend 0
# with INSERT statements to keep SERIAL data
# that don't write to database
# that write to database
backend_data_directory1='/home/kingbase/cluster/kha/db/data'
修改后:
[kingbase@node3 etc]$ cat kingbasecluster.conf|grep -i data
#backend_data_directory0='/home/kingbase/cluster/kha/db/data'
backend_data_directory0='/data/kingbase/cluster/r3/data'
# Data directory for backend 0
# with INSERT statements to keep SERIAL data
# that don't write to database
# that write to database
#backend_data_directory1='/home/kingbase/cluster/kha/db/data'
backend_data_directory1='/data/kingbase/cluster/r3/data'
三、拷贝本节点数据文件到新的data路径下:(所有节点)
=== 注意:拷贝前,保证主备库节点数据库都是正常关闭===
[kingbase@node3 db]$ du -sh data
621M data
[kingbase@node3 data]$ scp -r * /data/kingbase/cluster/r3/data/
[kingbase@node3 data]$ du -sh /data/kingbase/cluster/r3/data/
621M /data/kingbase/cluster/r3/data/
重新对data授权:
[kingbase@node3 bin]$ chmod 700 /data/kingbase/cluster/r3/data
四、主备流复制测试
** 1、主备流复制测试**
启动主库数据库服务:
[kingbase@node1 bin]$ ./sys_ctl start -D /data/kingbase/cluster/r3/data
server starting
[kingbase@node1 bin]$ LOG: sysaudit extension initialized
LOG: redirecting log output to logging collector process
HINT: Future log output will appear in directory "/data/kingbase/cluster/r3/data/sys_log".
[kingbase@node1 bin]$ ps -ef |grep kingbase
kingbase 5009 1 0 17:31 pts/0 00:00:00 /home/kingbase/cluster/kha/db/bin/kingbase -D /data/kingbase/cluster/r3/data
kingbase 5026 5009 0 17:31 ? 00:00:00 kingbase: logger process
kingbase 5028 5009 0 17:31 ? 00:00:00 kingbase: checkpointer process
kingbase 5029 5009 0 17:31 ? 00:00:00 kingbase: writer process
kingbase 5030 5009 0 17:31 ? 00:00:00 kingbase: wal writer process
kingbase 5031 5009 0 17:31 ? 00:00:00 kingbase: autovacuum launcher process
kingbase 5032 5009 0 17:31 ? 00:00:00 kingbase: archiver process
kingbase 5033 5009 0 17:31 ? 00:00:00 kingbase: stats collector process
kingbase 5034 5009 0 17:31 ? 00:00:00 kingbase: bgworker: syslogical supervisor
kingbase 5041 5009 0 17:31 ? 00:00:00 kingbase: wal sender process SYSTEM 192.168.7.243(60676) streaming 0/28000098
启动备库数据库服务:
[kingbase@node3 bin]$ ./sys_ctl start -D /data/kingbase/cluster/r3/data
server starting
[kingbase@node3 bin]$ LOG: redirecting log output to logging collector process
HINT: Future log output will appear in directory "/data/kingbase/cluster/r3/data/sys_log".
[kingbase@node3 bin]$ ps -ef |grep kingbase
root 7125 7014 0 16:52 pts/0 00:00:00 su - kingbase
kingbase 7126 7125 0 16:52 pts/0 00:00:00 -bash
root 8214 8171 0 16:54 pts/1 00:00:00 su - kingbase
kingbase 8215 8214 0 16:54 pts/1 00:00:00 -bash
kingbase 11336 1 0 17:31 pts/0 00:00:00 /home/kingbase/cluster/kha/db/bin/kingbase -D /data/kingbase/cluster/r3/data
kingbase 11337 11336 0 17:31 ? 00:00:00 kingbase: logger process
kingbase 11338 11336 0 17:31 ? 00:00:00 kingbase: startup process recovering 000000020000000000000027
kingbase 11342 11336 0 17:31 ? 00:00:00 kingbase: checkpointer process
kingbase 11343 11336 0 17:31 ? 00:00:00 kingbase: writer process
kingbase 11344 11336 0 17:31 ? 00:00:00 kingbase: stats collector process
查看流复制状态:
[kingbase@node1 bin]$ ./ksql -U system -W 123456 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 | s
tate | sent_location | write_location | flush_location | replay_location | sync_priority | sync_state
------+----------+---------+------------------+---------------+-----------------+-------------+-------------------------------+--------------+----
-------+---------------+----------------+----------------+-----------------+---------------+------------
5041 | 10 | SYSTEM | node243 | 192.168.7.243 | | 60676 | 2021-03-01 17:31:19.905944+08 | | str
eaming | 0/280000D0 | 0/280000D0 | 0/280000D0 | 0/280000D0 | 0 | async
(1 row)
2、测试数据同步
主库DML操作:
test=# \c prod
You are now connected to database "prod" as user "system".
prod=# create table dept1 as select * from dept;
SELECT 2
prod=# select * from dept1;
deptno | dname | loc
--------+-------+----------
10 | CAIWU | BEIJING
20 | JISHU | SHANGHAI
(2 rows)
备库查询:
test=# \c prod
You are now connected to database "prod" as user "system".
prod=# select * from dept1;
deptno | dname | loc
--------+-------+----------
10 | CAIWU | BEIJING
20 | JISHU | SHANGHAI
(2 rows)
五、重启集群测试
1、启动集群
[kingbase@node1 bin]$ ./kingbase_monitor.sh restart
-----------------------------------------------------------------------
2021-03-01 17:35:32 KingbaseES automation beging...
2021-03-01 17:35:32 stop kingbasecluster [192.168.7.243] ...
DEL VIP NOW AT 2021-03-01 17:35:44 ON enp0s3
No VIP on my dev, nothing to do.
2021-03-01 17:35:34 Done...
2021-03-01 17:35:34 stop kingbasecluster [192.168.7.248] ...
DEL VIP NOW AT 2021-03-01 17:35:35 ON enp0s3
No VIP on my dev, nothing to do.
2021-03-01 17:35:35 Done...
2021-03-01 17:35:35 stop kingbase [192.168.7.243] ...
set /data/kingbase/cluster/r3/data down now...
2021-03-01 17:35:39 Done...
2021-03-01 17:35:40 Del kingbase VIP [192.168.7.245/24] ...
DEL VIP NOW AT 2021-03-01 17:35:51 ON enp0s3
No VIP on my dev, nothing to do.
2021-03-01 17:35:41 Done...
2021-03-01 17:35:41 stop kingbase [192.168.7.248] ...
set /data/kingbase/cluster/r3/data down now...
2021-03-01 17:35:46 Done...
2021-03-01 17:35:47 Del kingbase VIP [192.168.7.245/24] ...
DEL VIP NOW AT 2021-03-01 17:35:47 ON enp0s3
No VIP on my dev, nothing to do.
2021-03-01 17:35:47 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 : [1]
Redirecting to /bin/systemctl restart crond.service
start crontab kingbase position : [1]
Redirecting to /bin/systemctl restart crond.service
ADD VIP NOW AT 2021-03-01 17:36:03 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]
wait kingbase recovery 5 sec...
start crontab kingbasecluster line number: [2]
Redirecting to /bin/systemctl restart crond.service
start crontab kingbasecluster line number: [2]
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]
=======================================================================
2、查看流复制状态
[kingbase@node1 bin]$ ./ksql -U SYSTEM -W 123456 -p 9999 test
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 | s
tate | sent_location | write_location | flush_location | replay_location | sync_priority | sync_state
------+----------+---------+------------------+---------------+-----------------+-------------+-------------------------------+--------------+----
-------+---------------+----------------+----------------+-----------------+---------------+------------
8963 | 10 | SYSTEM | node243 | 192.168.7.243 | | 60749 | 2021-03-01 17:37:04.121560+08 | | str
eaming | 0/2901EDD8 | 0/2901EDD8 | 0/2901EDD8 | 0/2901EDD8 | 0 | async
(1 row)
=== 如上所示,data路径修改成功!===
六、总结
kingbaseES R3集群修改data路径修改,操作比较繁琐,修改的配置文件较多,必须保证所有节点的对应配置文件都做了修改,才能保证集群正常启动和运行。