- 库集群安装文档
- 关闭防火墙及操作系统参数调整
注:以下操作所有节点执行
- 、关闭防火墙
systemctl stop firewalld
systemctl disable firewalld
vim /etc/selinux/config
- 、操作系统参数调整
vim /etc/systemd/logind.conf
RemoveIPC=no(需要修改的参数)
vim /etc/systemd/system.conf
DefaultTasksAccounting=no(需要修改的参数)
vim /etc/security/limits.conf 追加写入以下参数
root hard core unlimited
root soft nproc unlimited
root hard nproc unlimited
root soft nofile 65535
root hard nofile 65535
kingbase soft nproc unlimited
kingbase hard nproc unlimited
kingbase soft nofile 65535
kingbase hard nofile 65535
vim /etc/sysctl.conf,追加在后面
kernel.sem = 50100 64128000 50100 1280
net.ipv4.tcp_syncookies = 1 net.ipv4.tcp_tw_reuse = 1 net.ipv4.tcp_tw_recycle = 1 net.ipv4.tcp_fin_timeout = 30
net.ipv4.tcp_keepalive_time = 1200
net.ipv4.tcp_keepalive_probes = 3
net.ipv4.tcp_keepalive_intvl = 30
net.ipv4.ip_local_port_range = 10000 65000
net.ipv4.tcp_max_tw_buckets = 6000
net.ipv4.tcp_max_syn_backlog = 65536
net.core.netdev_max_backlog = 32768
net.core.wmem_default = 8388608
net.core.rmem_default = 8388608
net.core.rmem_max = 16777216
net.core.wmem_max = 16777216
net.ipv4.tcp_synack_retries = 2
net.ipv4.tcp_syn_retries = 2
net.ipv4.route.gc_timeout = 100
net.ipv4.tcp_wmem = 8192 436600 873200
net.ipv4.tcp_rmem = 32768 436600 873200
net.ipv4.tcp_mem = 94500000 91500000 92700000
net.ipv4.tcp_max_orphans = 3276800
vm.swappiness=1
保存退出之后使用命令systemctl -p生效
- 创建用户、安装目录以及上传安装包
(1)、创建用户
useradd -U -m kingbase
passwd kingbase 密码根据需求设定
(2)、创建上传目录和安装目录
mkdir -p /home/kingbase/install
mkdir -p /home/kingbase/KingbaseES/V8
(3)、上传安装包及解压
将安装包上传到/home/kingbase/install目录下
授权chown -R kingbase. /home/kingbase/*
如果安装包是tar.gz直接使用kingbase用户解压安装即可
如果安装包是iso文件,需要mount到一个空目录下
- 、安装
如果是tar.gz文件解压之后直接进入解压目录使用命令./setup.sh -i console
如果是iso文件进入挂在目录./setup.sh -i console
没有填写的地方回车即可
(4)、解压db.zip包
unzip /home/kingbase/KingbaseES/V8/DeployTools/zip/Lin64/db.zip
mkdir -p /home/kingbase/R6_install/
cp /home/kingbase/KingbaseES/V8/DeployTools/zip/Lin64/bin/V8R6_cluster_install.sh install.conf trust_cluster.sh /home/kingbase/R6_install/
Cp /home/kingbase/install/license*.dat /home/kingbase/R6_install/
(5)、编辑安装配置文件
cd /home/kingbase/R6_install/
vi install.conf
脚本内容:
on_bmj=0
all_ip=() #所有节点ip写在这里,并且使用空格隔开
install_dir="/home/kingbase/cluster"(cluster后面不要加/,否则配置物理备份的时候会有问题)
zip_package="/home/kingbase/KingbaseES/V8/DeployTools/zip/Lin64/db.zip"
license_file=(license.dat) #此处的license必须与安装配置文件同级目录下,否则找不到license安装失败
db_user="system"
#db_password="" (密码取消注释则可以直接换成想要密码,加注释表示默认密码为123456)
db_port="54321"
db_mode="oracle"
db_auth="scram-sha-256"
db_case_sensitive="yes"
trusted_servers=""
data_directory="/data/data"(这里的data后面不要写/,负责就会变成/data/dada/data样式,或者可以写为/data/)
net_device=(eth0 eth0)网卡名称
net_device_ip=()与all_ip配置的ip地址相同
ipaddr_path="/usr/sbin" #在操作系统上使用which ip命令查看路径即可
arping_path="/usr/sbin"
ping_path="/usr/bin"
super_user="root"
execute_user="kingbase"
reconnect_attempts="10"
reconnect_interval="6"
recovery="standby"
ssh_port="22"
auto_cluster_recovery_level='1'
配置完成保存退出
切换到root用户下
cd /home/kingbase/R6_install/
./trust_cluster.sh配置互信
过程中会让输入几遍密码,输入对应用户面面即可
- 、集群部署
su - kingbase
cd R6_install
./V8R6_cluster_install.sh
等待部署完成即可
3.6 配置环境变量
主备节点都需配置
[kingbase@node1 ~]$ vi ~/.bash_profile 追加以下内容
export PATH=$PATH:/home/kingbase/cluster/kingbase/bin
export KINGBASE_DATA=/home/kingbase/cluster/kingbase/data
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/home/kingbase/cluster/kingbase/lib
[kingbase@node1 ~]$ source .bash_profile 执行生效
- 数据库参数优化
vi /data/data/kingbase.conf
Vi /data/data/es_rep.conf
port=54321
max_connections=2000
shared_buffers=64GB
work_mem =10MB
maintenance_work_mem=300MB
effective_cache_size=30GB
max_locks_per_transaction=1024
enable_upper_colname=on
max_wal_size=64GB
checkpoint_timeout=20min
checkpoint_completion_target=0.9
max_parallel_workers_per_gather=0
logging_collector=on
log_destination='stderr'
log_directory='sys_log'
log_filename='kingbase-%d.log'
log_truncate_on_rotation=on
log_rotation_age=1440
log_connections=on
log_disconnections=on
log_statement='ddl'
log_checkpoints=on
log_lock_waits=on
log_autovacuum_min_duration=0
log_temp_files=0
lc_messages='C'
log_min_duration_statement=1000
log_line_prefix='%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h'
参数修改之后记得重启集群生效
注:业务连接数据库使用10.1.56.139
- 物理备份配置
- 配置备份
cd /home/kingbase/cluster/kingbase/share
vi sys_backup.conf
# target db style enum: single/cluster
_target_db_style="single"
# one kingbase node IP
# just provide one IP, script will use 'repmgr cluster show' get other node IP
_one_db_ip="10.1.56.139"
# local repo IP, inner-REPO, must be same as one_db_ip, means repo located in one db node
# outer repo IP, outer-REPO, means repo located in outer node
_repo_ip="10.1.56.135"
# label of this cluster
_stanza_name="kingbase"
# OS user name of database
_os_user_name="kingbase"
# !!!! dir to store the backup files
# should be accessable for the OS user
_repo_path="/data/backup_rman/kbbr_repo"#备份集目录,kbbr_repo这个之前的路径随意,这个是必须存在的
# count of keep, over the count FULL-backup will be remove
_repo_retention_full_count=5
# count of days, interval to do FULL-backup
_crond_full_days=7
# count of days, interval to do DIFF-backup
_crond_diff_days=0
# count of days, interval to do INCR-backup
_crond_incr_days=1
# HOUR to do the FULL-backup
_crond_full_hour=2
# HOUR to do the DIFF-backup
_crond_diff_hour=3
# HOUR to do the INCR-backup
_crond_incr_hour=4
# OS cmd define
_os_ip_cmd="/usr/sbin/ip"
_os_rm_cmd="/bin/rm"
_os_sed_cmd="/bin/sed"
_os_grep_cmd="/bin/grep"
# !!! these follow 4 parameter ONLY for single style
# data dir of single
_single_data_dir="/data/data" #data路径
# bin dir of single
_single_bin_dir="/home/kingbase/cluster/kingbase/bin"在使用sys_rman命令时需要
# database user of single
_single_db_user="system"
# database port of single
_single_db_port="54321"
保存退出
2、初始化备份目录
cd /home/kingbase/cluster/kingbase/bin
./sys_backup.sh init
3、开启物理备份
sys_backup.sh start(在/etc/cron.d/KINGBASECRON定时任务中配置定时任务)
4、关闭物理备份
sys_backup.sh stop(在/etc/cron.d/KINGBASECRON定时任务中删除定时任务)
5、暂时停止定时备份
sys_backup.sh pause 不影响已经在进行中的备份动作
6、恢复被暂停的定时备份
sys_backup.sh unpause
0 2 */7 * * kingbase /home/kingbase/cluster/kingbase/bin/sys_rman --config=/data/backup_rman/kbbr_repo/sys_rman.conf --stanza=kingbase --archive-copy --type=full backup >> /home/kingbase/cluster/kingbase/log/sys_rman_backup_full.log 2>&1
每周第七天凌晨2点进行全被
0 4 */1 * * kingbase /home/kingbase/cluster/kingbase/bin/sys_rman --config=/data/backup_rman/kbbr_repo/sys_rman.conf --stanza=kingbase --archive-copy --type=incr backup >> /home/kingbase/cluster/kingbase/log/sys_rman_backup_incr.log 2>&1
每天凌晨4点进行增量备份
7、逻辑备份说明
0 2 * * * sh /home/kingbase/R6logical/backup8.sh >>/home/kingbase/R6logical/logical_backup.log 2>&1 &
每天凌晨两点进行逻辑全备(可根据运维情况进行调整)
crontab -l查看备份策略
备份脚本路径:/home/kingbase/R6logical/
备份集路径:/data/backup_dump
- 常用维护命令
1、基本命令
- 集群停止
sys_monitor.sh stop
- 集群启动
sys_monitor.sh stop
- 集群重启
sys_monitor.sh restart
- 集群状态查看
repmgr cluster show
- 数据库登陆
ksql -Usystem -dtest(test为你所有连接的,这里随意)
- 数据库切换
登陆之后在ksql里切换数据库
\c database_name
- 查看数据库
\l
- 查看模式
\dn
- 查看表
\dt scheam_name.*(或者scheam_name.table_name)
- 查看表结构
\d+ scheam_name.table_name
- 创建业务用户
create user xxx password ‘password’;
alter user user_name superuser;
- 创建业务数据库
Create database database_name owner user_name;
- 集群切换
集群切换需要在备节点执行
repmgr standby switchover
- 数据库及集群日志
数据库日志路径:
/data/data/sys_log
集群日志:
/home/kingbase/cluster/kingbase/log
两个文件:
hamgr.log(集群日志)
kbha.log(集群存活检测信息)