Kingbase 产品体系

• KingbaseES
  中电科金仓数据库管理系统 KingbaseES（KES） 是面向全行业、全客户关键应用的企业级大型通用数据库管理系统，适用于联机事务处理、查询密集型数据仓库、要求苛刻的互联网应用等场景，提供全部应用开发及系统管理功能，提供性能增强特性，可支持主备集群、读写分离集群、多活共享存储集群等全集群架构，具有高性能、高安全、高可用、易使用、易管理、易维护的特点，支持所有国内外主流CPU、操作系统与云平台部署。
  中电科金仓数据库管理系统 KingbaseES（KES）于2022年7月升级到了V9版本。当前KingbaseES（KES）的最新发布版本为V009R001C001B0030。
• KingbaseAnalyticsDB
  金仓分析型数据库系统KingbaseAnalyticsDB（以下简称为 KADB）是中电科金仓信息技术股份有限公司推出的MPP数据库产品。产品应大数据时代海量数据分析处理的需求，采用shared-nothing分布式架构，具有高性能，高扩展性能力，承载了中电科金仓在大规模并行计算和数据库管理领域先进的研发成果，它定位于数据分析类应用市场，可以处理TB-PB级别的数据，并能集成多种异构数据源进行数据挖掘和分析。在处理复杂查询，如：多表连接、聚合等，也体现出了很好的性能。适用于数据仓库、决策支持、高级分析等分析类应用场景。
• KSOne
  KSOne是电科金仓自主研发的一款面向交易型业务、 实时分析、时间序列等场景的 HTAP 分布式关系型数据库系统。产品具备企业级复杂事务混合负载能力，同时支持跨AZ部署，数据0丢失，支持大规模横向扩展，PB级海量数据存储等企业级数据库特性。拥有高可靠，高安全，弹性伸缩，高效备份恢复等关键能力，能为企业提供功能全面，稳定可靠，扩展性强，性能优越的企业级数据库服务。
• Kingbase FlySync
  FlySync是面向同城/异地灾备、数据库平滑升级替换、数据集中共享与分发、应用上云迁移、数据库负载均衡等场景的数据同步产品。该产品基于增量日志解析技术，性能高、时延低、资源占用极少，能够实现异构数据源之间大规模增量数据的任意方向流转和实时同步，帮助用户打破数据孤岛，轻松共享分发数据。Kingbase FlySync支持丰富的软硬件平台和数据源，同步拓扑可以一对一、一对多、多对一、级联等方式任意组合延伸，并可在数据同步过程中保证端到端的事务级数据完整性和高可用性。

KingbaseES（KES）单机部署

KES 软件下载

https://www.kingbase.com.cn/xzzx/index.htm

license下载

license文件版本差异说明：

license文件下载：这里下载企业版

安装前准备

主机、目录规划

软件包：/soft/KingbaseES_V009R001C001B0030_Lin64_install.iso
license文件：/soft/license_企业版.zip
软件安装目录 /opt/kes/v9
数据存放目录 /data

关闭SELINUX

# vi /etc/selinux/config
修改SELINUX=disabled
或者
sed -i 's/^SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config

关闭防火墙

# systemctl disable firewalld.service
# systemctl stop firewalld.service
# systemctl status firewalld

关闭RemoveIPC

在各数据库节点上，关闭RemoveIPC。

 (1) 修改/etc/systemd/logind.conf文件中的“RemoveIPC”值为“no”
vim  /etc/systemd/logind.conf
RemoveIPC=no

(2) 修改/usr/lib/systemd/system/systemd-logind.service文件中的“RemoveIPC”值为“no”
vim /usr/lib/systemd/system/systemd-logind.service
RemoveIPC=no

(3) 重启服务
systemctl daemon-reload
systemctl restart systemd-logind.service

(4) 结果验证确认
loginctl show-session | grep RemoveIPC
systemctl show systemd-logind | grep RemoveIPC

配置sudo权限

cat  >>/etc/sudoers << EOF
kingbase ALL=(ALL) NOPASSWD:ALL
EOF

配置时间同步

# yum install -y ntpdate

--每天凌晨2点进行同步
# crontab -e
0 2 * * * /usr/sbin/ntpdate  time.windows.com >> /var/log/ntpdate.log 2>&1

设置/etc/hosts

# cat >> /etc/hosts <<EOF
192.*.*.100 node1       #注意*要根据实际情况替换
EOF

设置内核参数

# cat >> /etc/sysctl.conf <<EOF
#add by kingbase
kernel.sem = 250 162500 250 650	 
kernel.shmall = 3774873								
kernel.shmmax = 8589934592 
kernel.shmmni = 4096		
vm.dirty_background_ratio=2 
vm.dirty_ratio = 40			
vm.overcommit_memory = 2	
vm.overcommit_ratio = 90 	
vm.swappiness = 0 				
fs.aio-max-nr = 1048576		
fs.file-max = 6815744		
fs.nr_open = 20480000       
net.ipv4.ip_local_port_range = 10000 65000
net.ipv4.tcp_keepalive_time = 1200
net.ipv4.tcp_keepalive_probes = 3
net.ipv4.tcp_keepalive_intvl = 30
net.ipv4.tcp_max_syn_backlog = 8192
net.ipv4.tcp_max_tw_buckets = 6000
net.ipv4.tcp_max_syn_backlog = 65536
net.core.somaxconn=1024
net.core.netdev_max_backlog = 32768
net.core.wmem_default = 8388608
net.core.wmem_max = 1048576
net.core.rmem_default = 8388608
net.core.rmem_max = 16777216
net.ipv4.tcp_synack_retries = 2
net.ipv4.tcp_fin_timeout = 30
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
EOF

sysctl -p 生效

设置资源限制

# cat >> /etc/security/limits.conf <<EOF
#add by kingbase
kingbase soft  nproc   65536
kingbase  hard  nproc   65536
kingbase  soft  nofile  65536
kingbase  hard  nofile  65536
kingbase  soft  stack   10240
kingbase  hard  stack   32768
kingbase soft core unlimited
kingbase hard core unlimited
EOF

创建用户及用户组

# groupadd -g 1101 kingbase
# useradd -g kingbase -u 1101 -m kingbase
# passwd kingbase

创建软件安装目录并授权

# mkdir -p /opt/kes/v9
# chown -R kingbase:kingbase /opt/kes/v9

创建数据目录并授权

# mkdir -p /data
# chown -R kingbase:kingbase /data

KES安装包挂载

# mount -o loop /soft/KingbaseES_V009R001C001B0030_Lin64_install.iso /mnt/

解压license文件

# unzip /soft/license_企业版.zip -d /soft
Archive:  license_企业版.zip
   creating: /soft/license_41248/
  inflating: /soft/license_41248/license_41248_0.dat 

命令行安装

主要安装过程如下：

# su - kingbase
$ cd /mnt
$ ./setup.sh 
1、接受许可协议
2、选择安装方式
  ->1- Full
    2- Client
    3- Custom
3、选择license文件：
   File Path: /soft/license_41248/license_41248_0.dat
4、选择软件安装文件夹
   ENTER AN ABSOLUTE PATH, OR PRESS <ENTER> TO ACCEPT THE DEFAULT
      : /opt/kes/v9 
5、选择数据目录文件夹
   Data folder (Default: /opt/kes/v9/data): /data
6、输入端口
   Please enter database service listened port， default 54321.
   Port (Default: 54321): 
5、输入数据库管理用户和密码
   Please enter database administrator user name.
   User: (Default: system): 
   Please Enter the Password: Please Enter the Password:******
6、输入服务器字符集编码
   Please enter server character set encoding.
    1- default
  ->2- UTF8
    3- GBK
    4- GB2312
    5- GB18030
ENTER THE NUMBER FOR YOUR CHOICE, OR PRESS <ENTER> TO ACCEPT THE DEFAULT: 2
7、输入数据库的区域设置
Please enter the Database Locale.
    1- C
  ->2- zh_CN.UTF-8
    3- en_US.UTF-8
ENTER THE NUMBER FOR YOUR CHOICE, OR PRESS <ENTER> TO ACCEPT THE DEFAULT: 2
8、输入数据库模式
   Please enter database mode.
    1- PG
  ->2- ORACLE
    3- MySQL
   ENTER THE NUMBER FOR YOUR CHOICE, OR PRESS <ENTER> TO ACCEPT THE DEFAULT: 2
9、输入大小写敏感性
   Please enter the case sensitivity.
  ->1- YES
    2- NO
ENTER THE NUMBER FOR YOUR CHOICE, OR PRESS <ENTER> TO ACCEPT THE DEFAULT: 2  
10、输入数据库block大小
   Please enter block size used in storing data.
  ->1- 8k
    2- 16k
    3- 32k
   ENTER THE NUMBER FOR YOUR CHOICE, OR PRESS <ENTER> TO ACCEPT THE DEFAULT: 1
11、输入加密方法
   Please enter the encrypt method.
  ->1- scram-sha-256
    2- scram-sm3
    3- sm4
    4- sm3
 ENTER THE NUMBER FOR YOUR CHOICE, OR PRESS <ENTER> TO ACCEPT THE DEFAULT: 1
12、自定义参数
   Please enter database custom parameters.
   Custom (Default:  ): 
13、初始化数据库
   The database will be initialized, which may take some time. Please be patient.
   PRESS <ENTER> TO CONTINUE: 
14、安装完成
Congratulations. KingbaseES V9 has been successfully installed to:
/opt/kes/v9
If you want to register KingbaseES V9 as OS service, please run
    /opt/kes/v9/install/script/root.sh
PRESS <ENTER> TO EXIT THE INSTALLER: 
Complete.
15、root用户注册服务
root用户执行 /opt/kes/v9/install/script/root.sh
# /opt/kes/v9/install/script/root.sh
Starting KingbaseES V9: 
waiting for server to start.... done
server started
KingbaseES V9 started successfully

静默安装

静默安装也是非常方便，配置/mnt/setup/silent.cfg文件即可

查看silent.cfg模版文件的内容： 
[root@node1 setup]# cat silent.cfg 
# Mon Jan 10 11:06:56 CST 2022
# Replay feature output
# ---------------------
# This file was built by the Replay feature of InstallAnywhere.
# It contains variables that were set by Panels, Consoles or Custom Code.

#license file path, for example /home/kingbase/license_1309_0-sales.dat for linux or C:\\license_1309_0-sales.dat for windows
KB_LICENSE_PATH=

#install set, options are Full, Client, Custom
CHOSEN_INSTALL_SET=Full
#component list, options are SERVER, KSTUDIO, KDTS, INTERFACE, DEPLOY, KINGBASEHA
CHOSEN_FEATURE_LIST=SERVER,KSTUDIO,KDTS,INTERFACE,DEPLOY,KINGBASEHA

#install dir path, for example /opt/Kingbase/ES/V9 for linux or C:\\Program Files\\Kingbase\\ES\\V9 for windows
USER_INSTALL_DIR=/opt/Kingbase/ES/V9

#data dir path, for example /opt/Kingbase/ES/V9/data for linux or C:\\Program Files\\Kingbase\\ES\\V9\\data for windows
USER_SELECTED_DATA_FOLDER=
 
#init db params
#server port, requied, between 1 and 65535
DB_PORT=54321
#user name, required, length less than 63 bytes
DB_USER=system
#password, required
DB_PASS=
#password confirm, required
DB_PASS2=
#encoding, required, options are UTF8, GBK, GB18030, GB2312, default
ENCODING_PARAM=UTF8

#locale, required, based on encoding
#options are ______zh_CN.UTF-8, en_US.UTF-8, C     (encoding = UTF-8)
#             \____zh_CN.GBK, C                    (encoding = GBK)
#              \___zh_CN.GB18030, C                (encoding = GB18030)
#               \__zh_CN.GB2312, C                 (encoding = GB2312)
#                \_C, default                      (encoding = default)
LOCALE_PARAM=zh_CN.UTF-8

#init db custom parameters.
INITCUSTOM=

#compatibility mode, required, options are ORACLE, PG, MySQL
DATABASE_MODE_PARAM=ORACLE
#case sensitivity, required, options are YES, NO
CASE_SENSITIVE_PARAM=YES
#block size, required, options are 8k, 16k, 32k
BLOCK_SIZE_PARAM=8k

#authentication method, required, options are scram-sha-256, scram-sm3, sm4, sm3
AUTHENTICATION_METHOD_PARAM=scram-sha-256

启停KES服务

# systemctl start kingbased
# systemctl status kingbased
# systemctl stop kingbased

配置kingbase用户环境变量

cat >>/home/kingbase/.bashrc<<EOF
export KINGBASE_DATA=/data
export PATH=$PATH:/opt/kes/v9/KESRealPro/V009R001C001B0030/Server/bin
export LANG=zh_CN.UTF8
export KINGBASE_PORT=54321
EOF

环境变量生效：

source /home/kingbase/.bashrc

KingbaseES（KES）使用

ksql连接测试

[kingbase@node1 ~]$ ksql -h 192.*.*.50 -U system -d kingbase
用户 system 的口令：
输入 "help" 来获取帮助信息.

# 查看数据库连接信息
kingbase=# \conninfo
以用户 "system" 的身份，通过套接字"/tmp"在端口"54321"连接到数据库 "kingbase"

# 查看数据库版本
kingbase=# select version();
                                                       version                                                        
----------------------------------------------------------------------------------------------------------------------
 KingbaseES V009R001C001B0030 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit
(1 行记录)

# 查看数据库列表
kingbase=# \l
                                  数据库列表
   名称    | 拥有者 | 字元编码 |  校对规则   |    Ctype    |     存取权限      
-----------+--------+----------+-------------+-------------+-------------------
 kingbase  | system | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | 
 security  | system | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | 
 template0 | system | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/system        +
           |        |          |             |             | system=CTc/system
 template1 | system | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/system        +
           |        |          |             |             | system=CTc/system
 test      | system | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | 
(5 行记录)

# 查看数据库自带的用户列表
kingbase=# \du
                             角色列表
 角色名称 |                    属性                    | 成员属于 
----------+--------------------------------------------+----------
 kcluster | 无法登录                                   | {}
 sao      | 没有继承                                   | {}
 sso      | 没有继承                                   | {}
 system   | 超级用户, 建立角色, 建立 DB, 复制, 绕过RLS | {}

# 创建数据库
kingbase=# create database testdb;
CREATE DATABASE

# 给用户授予在数据库上创建schema的权限
testdb=# grant create on database testdb to testusr;       
GRANT

# 切换数据库
kingbase=# \c testdb
您现在以用户名"system"连接到数据库"testdb"。

# 创建用户
testdb=# create user testusr with password '******';
CREATE ROLE

testdb=# \c testdb testusr                                 
用户 testusr 的口令：
您现在以用户名"testusr"连接到数据库"testdb"。

# 创建schema（不要在public schema下创建自定义对象）
testdb=> create schema testns;
CREATE SCHEMA

# 设置search_path
testdb=> set search_path to testns;
SET

# 创建表
testdb=> create table testtab(id int);
CREATE TABLE

# 查看表
testdb=> \dt
               关联列表
 架构模式 |  名称   |  类型  | 拥有者  
----------+---------+--------+---------
 testns   | testtab | 数据表 | testusr

dbeaver连接测试

KES软件自带jdbc驱动，位置在KES软件安装目录的Interface/jdbc目录下。本例中KES软件安装目录是/opt/kes/v9，自带的驱动如下：

[root@node1 ~]# cd /opt/kes/v9/Interface/jdbc
[root@node1 jdbc]# ll
total 13M
-rw-rw-r-- 1 kingbase kingbase  5.6M Apr 29 16:20 bcprov-jdk15on-1.70.jar
-rw-rw-r-- 1 kingbase kingbase  1.2M Apr 29 16:20 kingbase8-9.0.0.jar
-rw-rw-r-- 1 kingbase kingbase 1021K Apr 29 16:20 kingbase8-9.0.0.jre6.jar
-rw-rw-r-- 1 kingbase kingbase  1.2M Apr 29 16:20 kingbase8-9.0.0.jre7.jar
-rw-rw-r-- 1 kingbase kingbase  1.2M Apr 29 16:20 postgresql-42.2.9.jar
-rw-rw-r-- 1 kingbase kingbase 1022K Apr 29 16:20 postgresql-42.2.9.jre6.jar
-rw-rw-r-- 1 kingbase kingbase  1.2M Apr 29 16:20 postgresql-42.2.9.jre7.jar

dbeaver通过jdbc连接数据库：

python连接测试

查找python驱动包ksycopg2

KES软件自带python驱动，位置在KES软件安装目录的Interface/Python目录下。本例中KES软件安装目录是/opt/kes/v9，自带的驱动如下：

[root@node1 ~]# cd /opt/kes/v9/Interface/Python
[root@node1 Python]# ls -l ksycopg2_linux_x86_64_python*
-rw-rw-r-- 1 kingbase kingbase 3242583 Apr 29 16:19 ksycopg2_linux_x86_64_python2.7.tar.gz
-rw-rw-r-- 1 kingbase kingbase 3266637 Apr 29 16:20 ksycopg2_linux_x86_64_python3.5.tar.gz

配置ksycopg2

# tar -xvf ksycopg2_linux_x86_64_python3.5.tar.gz 
# cp -rp ksycopg2 /usr/lib/python3.7/site-packages/
# chmod 755 /usr/lib/python3.7/site-packages/ksycopg2

--加载库文件
# cat >> /etc/ld.so.conf << EOF
/usr/lib/python3.7/site-packages/ksycopg2
EOF
# ldconfig

使用ksycopg2访问KES数据库

# python3
import ksycopg2
conn = ksycopg2.connect(database="kingbase", user="system", password="******", host="192.*.*.50", port="54321")
cur = conn.cursor()
cur.execute("select * from test")
rows = cur.fetchall()
for row in rows:
    print("ID = ",row[0],"NAME = ",row[1])

KingbaseES（KES）卸载

删除服务

# sh /opt/kes/v9/install/script/rootuninstall.sh

卸载KES

# su - kingbase
$ /opt/kes/v9/Uninstall/Uninstaller

碰到的问题

python访问数据库时，报错：

import ksycopg2
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/lib/python3.7/site-packages/ksycopg2/__init__.py", line 51, in <module>
    from ksycopg2._ksycopg import (                     # noqa
ModuleNotFoundError: No module named 'ksycopg2._ksycopg'
>>> 

解决办法：

cd /usr/lib/python3.7/site-packages/ksycopg2
mv _ksycopg.cpython-35m-x86_64-linux-gnu.so  _ksycopg.cpython-37m-x86_64-linux-gnu.so

总结

金仓KES V9数据库单机版的安装非常丝滑~~~