kingbase部署主从(原始主从,非读写分离集群)
环境:
OS:Centos 7
DB:kingbaseV8R6
说明:
kingbase读写分离集群是部署了repmgr、kbha、自带vip转移功能.
1.主从节点都采用full模式安装
full模式安装会初始化数据库(initdb)
数据库配置:
a.大小写不敏感
b.采用utf8字符集
c.兼容oracle模式
d.system账号的密码设置为:kingbase
2.创建复制账号
su - kingbase
cd /kingbase/app/Server/bin
[kingbase@localhost bin]$./sys_ctl start -D /kingbase/data
主库创建流复制的用户
[kingbase@localhost bin]$ ./ksql -p 54321 -U system -d test -W
test=# CREATE ROLE replica login replication encrypted password 'replica';
CREATE ROLE
test=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
hxl | | {}
kcluster | Cannot login | {}
replica | Replication | {}
sao | No inheritance | {}
sso | No inheritance | {}
system | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
3.主库修改 sys_hba.conf 文件,允许备库IP通过复制用户访问数据库(好像不需要修改)
# IPv4 本地连接:
host all all 127.0.0.1/32 scram-sha-256
host all all 0.0.0.0/0 scram-sha-256
##添加如下部分
host replication all 0.0.0.0/0 scram-sha-256
修改后需要重新加载
重新加载sys_hba.conf：
test=# select sys_reload_conf();
sys_reload_conf
-----------------
t
(1 row)
4.主备流复制参数配置
修改kingbase.conf参数
archive_mode = on
archive_command = 'DATE=`date +%Y%m%d`;DIR="/kingbase/arch/$DATE";(test -d $DIR || mkdir -p $DIR)&& cp %p $DIR/%f
wal_level = replica
##synchronous_commit = on 我这里没有修改,保留默认的
5.主库创建replication slot(我这里没有操作)
test=# select sys_create_physical_replication_slot('slot_node1');
test=# select sys_create_physical_replication_slot('slot_node2');
6.备库采用full模式安装了数据库软件和初始化数据库
步骤参考主库的安装
7.停掉备库并将data目录置空
删除data目录的目的是,下面对主库进行备份,然后在备库进行恢复
su - kingbase
cd /kingbase/app/Server/bin
[kingbase@localhost bin]$./sys_ctl stop -D /kingbase/data
[kingbase@localhost kingbase]$ mv data bakdata
[kingbase@localhost kingbase]$ mkdir data
8.备库创建归档目录保持与主库一致
[kingbase@localhost kingbase]$ su - kingbase
[kingbase@localhost kingbase]$ mkdir -p /kingbase/arch
9.在备库上执行对主库的基础备份
注意:这里是在备库上执行
[kingbase@localhost bin]$su - kingbase
[kingbase@localhost bin]$cd /kingbase/app/Server/bin
[kingbase@localhost bin]$ ./sys_basebackup -h 192.168.1.101 -p 54321 -U replica --password -X stream -Fp --progress -D /kingbase/data -R
Password:
85660/85660 kB (100%), 1/1 tablespace
这里我们使用上面创建的账号replica进行操作
命令执行完成后,可以看到备库的data目录下的文件
[kingbase@localhost data]$ ls
backup_label kingbase.auto.conf sys_commit_ts sys_log sys_serial sys_twophase
base kingbase.conf sys_csnlog sys_logical sys_snapshots SYS_VERSION
current_logfiles standby.signal sys_dynshmem sys_multixact sys_stat sys_wal
global sys_aud sys_hba.conf sys_notify sys_stat_tmp sys_xact
initdb.conf sys_bulkload sys_ident.conf sys_replslot sys_tblspc
kingbase.conf和sys_hba.conf也会自动拷贝过来了,主库的kingbase.conf配置了归档目录,备库也必须有保持一致的归档目录(上面步骤已经创建)
同时备库自动生成了standby.signal和kingbase.auto.conf文件
kingbase.auto.conf文件自动生成如下内容,不需要进行修改
[kingbase@localhost data]$ more kingbase.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
primary_conninfo = 'user=replica password=replica host=192.168.1.101 port=54321 application_name=internal_backup sslmode=prefer sslcompression=0 gssencmode=disable target_session_attrs=any loadbalance=off enable_ce=0'
发现这里replica账号的密码是明文的,若不想在这里出现密码，可以在sys_hba.conf进行修改配置，如下
host replication all 0.0.0.0/0 trust
10.启动备库
[kingbase@localhost bin]#su - kingbase
[kingbase@localhost bin]$cd /kingbase/app/Server/bin
[kingbase@localhost bin]$./sys_ctl start -D /kingbase/data
11.查看备库数据库进程
[kingbase@localhost bin]$ ps -ef |grep kingbase
root 2594 1975 0 09:21 pts/0 00:00:00 su - kingbase
kingbase 2595 2594 0 09:21 pts/0 00:00:00 -bash
root 5648 2016 0 09:32 pts/1 00:00:00 su - kingbase
kingbase 5649 5648 0 09:32 pts/1 00:00:00 -bash
kingbase 6220 1 0 10:22 ? 00:00:00 /kingbase/app/KESRealPro/V008R006C008B0014/Server/bin/kingbase -D /kingbase/data
kingbase 6221 6220 0 10:22 ? 00:00:00 kingbase: logger
kingbase 6222 6220 0 10:22 ? 00:00:00 kingbase: startup recovering 00000001000000000000000D
kingbase 6223 6220 0 10:22 ? 00:00:00 kingbase: checkpointer
kingbase 6224 6220 0 10:22 ? 00:00:00 kingbase: background writer
kingbase 6225 6220 0 10:22 ? 00:00:00 kingbase: stats collector
kingbase 6226 6220 0 10:22 ? 00:00:00 kingbase: walreceiver streaming 0/D000130
kingbase 6235 2595 0 10:22 pts/0 00:00:00 ps -ef
kingbase 6236 2595 0 10:22 pts/0 00:00:00 grep --color=auto kingbase
12.验证主备流复制
查看replication slots(取消)
select * from sys_replication_slots;
查看主备流复制状态
主库查询
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
------+----------+---------+------------------+---------------+-----------------+-------------+----------------------
---------+--------------+-----------+----------+-----------+-----------+------------+-----------+-----------+--------
----+---------------+------------+-------------------------------
3381 | 16396 | replica | internal_backup | 192.168.1.103 | | 13189 | 2024-02-29 10:22:07.6
34315+08 | | streaming | | 0/D000130 | 0/D000130 | 0/D000130 | | |
| 0 | async | 2024-02-29 10:25:17.612748+08
(1 row)
13.测试数据同步
主库写入数据
[kingbase@localhost bin]$ pwd
/kingbase/app/Server/bin
[kingbase@localhost bin]$ ./ksql -p 54321 -U hxl -d db_hxl -W
Password:
Type "help" for help.
db_hxl=> \dt
List of relations
Schema | Name | Type | Owner
--------+---------+-------+-------
public | tb_test | table | hxl
(1 row)
insert into tb_test(name1) values('name1');
insert into tb_test(name1) values('name2');
insert into tb_test(name1) values('name3');
insert into tb_test(name1) values('name4');
insert into tb_test(name1) values('name5');
db_hxl=> select * from tb_test;
id | name1 | name2 | name3 | name4 | name5 | name6 | createtime | modifytime
----+-------+-------+-------+-------+-------+-------+----------------------------+----------------------------
1 | name1 | | | | | | 2024-02-29 10:30:53.913008 | 2024-02-29 10:30:53.913008
2 | name2 | | | | | | 2024-02-29 10:30:53.956264 | 2024-02-29 10:30:53.956264
3 | name3 | | | | | | 2024-02-29 10:30:53.981133 | 2024-02-29 10:30:53.981133
4 | name4 | | | | | | 2024-02-29 10:30:53.994459 | 2024-02-29 10:30:53.994459
5 | name5 | | | | | | 2024-02-29 10:30:54.903705 | 2024-02-29 10:30:54.903705
(5 rows)
登录从库查询
[kingbase@localhost bin]$ pwd
/kingbase/app/Server/bin
[kingbase@localhost bin]$ ./ksql -p 54321 -U hxl -d db_hxl -W
db_hxl=> \dt
List of relations
Schema | Name | Type | Owner
--------+---------+-------+-------
public | tb_test | table | hxl
(1 row)
db_hxl=> select * from tb_test;
id | name1 | name2 | name3 | name4 | name5 | name6 | createtime | modifytime
----+-------+-------+-------+-------+-------+-------+----------------------------+----------------------------
1 | name1 | | | | | | 2024-02-29 10:30:53.913008 | 2024-02-29 10:30:53.913008
2 | name2 | | | | | | 2024-02-29 10:30:53.956264 | 2024-02-29 10:30:53.956264
3 | name3 | | | | | | 2024-02-29 10:30:53.981133 | 2024-02-29 10:30:53.981133
4 | name4 | | | | | | 2024-02-29 10:30:53.994459 | 2024-02-29 10:30:53.994459
5 | name5 | | | | | | 2024-02-29 10:30:54.903705 | 2024-02-29 10:30:54.903705
(5 rows)
db_hxl=> select sys_is_in_recovery();
sys_is_in_recovery
--------------------
t
(1 row)
