Centos7上安装人大金仓Kingbase ES V7数据库

环境信息：
OS: Centos7.8-x86_64

软件包: 
kdb-7.1.2.0935-release-Linux-x86_64-installer-build0036.zip (安装 ok)
kdb-7.1.2.0250-release-Linux-x86_64-installer-build0029.zip (安装 failed)

刚开始下载的 kdb-7.1.2.0250-release-Linux-x86_64-installer-build0029.zip 这个软件包，执行Root.sh脚本总是报错：
[root@localhost ~]# /opt/Kingbase/ES/V7/Install/Root.sh
/opt/Kingbase/ES/V7/Install/Root.sh: line 20: $/Install/root.log: No such file or directory
/opt/Kingbase/ES/V7/Install/Root.sh: line 21: $/Install/root.log: No such file or directory
/opt/Kingbase/ES/V7/Install/Root.sh: line 22: $/Install/root.log: No such file or directory
cp: cannot stat ‘$/Uninstall/rmln.sh’: No such file or directory
/opt/Kingbase/ES/V7/Install/Root.sh: line 247: cd: $/Install/startup-desktop/icons: No such file or directory
find: ‘./hicolor’: No such file or directory
tee: $/Install/root.log: No such file or directory

chown: invalid user: ‘$.’
chmod: cannot access ‘$/Install/root.log’: No such file or directory
Complete.
[root@localhost ~]# 
后来重新下载另外一个软件包安装：kdb-7.1.2.0250-release-Linux-x86_64-installer-build0029.zip


1. 安装Kingbase ES V7数据库

关闭防火墙
systemctl stop firewalld
systemctl disable firewalld

vim /etc/selinux/config

SELINUX=disabled


创建kingbase用户
由于不支持以root执行安装脚本，必须非root用户执行（Current user is ROOT. Please re-run the installer as Non-Root user.），所以先创建一个普通用户用来执行安装脚本，
还要求改用户必须有标准的home目录
# useradd -m kingbase
# passwd kingbase

创建数据库的安装目录&赋权
# mkdir -p /opt/Kingbase/ES/V7
# chmod 777 /opt/Kingbase/ES/V7

拷贝软件包
# mkdir -p /opt/software/kingbase #解压目录
# chown -R kingbase /opt/software/kingbase
# cp kdb-7.1.2.0250-release-Linux-x86_64-installer-build0029.zip /opt/software
# cd /opt/software

解压软件包
# su - kingbase
$ cd /opt/software/
$ unzip kdb-7.1.2.0935-release-Linux-x86_64-installer-build0036.zip  -d /opt/software/kingbase/
$ cd /opt/software/kingbase
$ chmod +x -R ./


执行安装脚本
$ cd /opt/software/kingbase/
$ sh setup.sh -i console   #我采用命令行交互安装方式

几种安装方式说明：
图形界面安装执行 sh setup.sh 或 sh setup.sh -i swing
命令行安装执行   sh setup.sh -i console 
静默安装执行     sh setup.sh -i silent (需要配置好应答文件 installer.properties，注意：Linux的路径符号\需要转移"\\" & 不能使用中文 & #注释仅支持位于行首)


回车   #连续多次回车 for welcome & License Agreement
Y      # DO YOU ACCEPT THE TERMS OF THIS LICENSE AGREEMENT? (Y/N): 
1      #1 for 完全安装(1- Full  2- Client)。安装KingbaseES所有组件，推荐大多数用户使用，是默认的安装方式。
       #2 for 客户端安装。只安装KingbaseES的所有客户端工具、编程接口和扩展文件。
回车   #Dependence Check，依赖检测结果，全部pass

/opt/software/license_ee_7.1.2_xxProject-x86-64.dat    #Choose License File 指定License文件路径，如果License正确，会显示相关信息

Detail Information
-----------------------------------------------------
Effective Date: 2021-02-08
Distribution Type: EE
Max Connections: unlimited
MAC Address: unlimited
IPV4 Address: unlimited

===============================================================================
Choose Install Folder
---------------------

Please choose a destination folder for this installation.
You must have write permission in the folder

Default Folder:/opt/Kingbase/ES/V7

Enter an absolute path, or press <ENTER> to accept the default: 

选择安装文件夹位置，默认为 /opt/Kingbase/ES/V7 。
注意：如果指定的安装目录没有读、写、执行权限(chmod 777 /opt/Kingbase/ES/V7)，会提示让你回车后再重新指定安装路径

Warning
-------

You do not have read, write or execute permissions to the chosen installation 
destination.
Please choose a different location for installation.

PRESS <ENTER> TO CONTINUE:       #重新给目录赋权后再继续，显示安装配置的简要信息

===============================================================================
Pre-Installation Summary
------------------------

Please Review the Following Before Continuing:

Product Name:
    KingbaseES V7

Install Folder:
    /opt/Kingbase/ES/V7

Product Features:
    SERVER,
    HELP,
    SERVER TOOLS,
    PLUGIN,
    INTERFACE,
    CLIENT TOOLS

Disk space information
    Required Space:507M                             Available Space:95270M

PRESS <ENTER> TO CONTINUE:   #确认安装配置信息，回车继续


===============================================================================
Ready To Install
----------------

InstallAnywhere is now ready to install KingbaseES V7 onto your system at the 
following location:

   /opt/Kingbase/ES/V7

PRESS <ENTER> TO INSTALL: #确认安装，回车

===============================================================================
Create KRMS Account
-------------------

Create KRMS Account of KingbaseES. Default username is krms and password is krms.

Username Rule: 
1. Valid characters: (A-Z), (a-z), (0-9), (_), (#), ($) 
2. Can only begin with a letter, the maximum length is 30.
3. Case-sensitive.

Password Rule:
1. Valid characters: Visible input character except ('), (") and (\). 
2. The min minimum length is 1 and the maximum length is 16.
3. Case-sensitive.

Username (DEFAULT: krms): system      #安装过程中会创建远程管理账号，提示输入账号密码，如果不指定默认为 krms/krms

Password (DEFAULT: krms): 1q2w3e

Confirm Password (DEFAULT: krms): 1q2w3e

===============================================================================
Completing the installation...
------------------------------

Please run /opt/Kingbase/ES/V7/Install/Root.sh script as root to complete the 
installation process in a new shell after exit this installer.
 
PRESS <ENTER> TO CONTINUE:   #安装完成会提示完成并退出安装后，再重新打开一个终端shell窗口以root身份执行Root.sh脚本，先不操作，直接回车。

注意：出现该提示时先不要执行，需要等安装结束退出后再执行，否则执行Root.sh会报错。
 
===============================================================================
The method to initialize the database
-----------
  ->1- Initialize database when executing Root.sh
    2- Manual initialization

Please select the database initialization method(or press <ENTER> to accept 
   the default): 2    #我选择后面再手动初始化数据库，执行Root.sh脚本的时候不进行自动初始化数据库操作
 
===============================================================================
If there is a database instance activated, you can access the instance in the 
following ways:
 
KingbaseES Universal Data Management Platform can be accessed via the following
URL, Owner can also access directly via the start menu [ Web Management 
Platform ].
https://<hostname>:54328/webstudio

KingbaseES isql can be accessed as follow, or be accessed directly via the 
start menu [ ISQL ]. See help documents of isql for detials.
"/opt/Kingbase/ES/V7/bin/isql" [OPTIONS]... [DBNAME [USERNAME]]

KingbaseES Query Analyzer can be accessed as follow, or be accessed directly 
via the start menu [ Query Analyzer ].
"/opt/Kingbase/ES/V7/bin/javatools" JSQL


Please press <ENTER> to exit the installer.: 
 
Complete.
[kingbase@localhost kingbase]$ 


安装结束后，再以root身份执行Root.sh脚本
# /opt/Kingbase/ES/V7/Install/Root.sh

执行Root.sh脚本所完成的功能包含：
1)安装ODBC驱劢
2)注册服务：krms7d、kingbase7d、jobmgmt7d、kdbws7d
3)若安装过程中选择“初始化数据库”，则启动初始化数据库工具

执行结果如下：
[root@localhost ~]# /opt/Kingbase/ES/V7/Install/Root.sh
Installing ODBC driver...
ODBC Driver is installed successfully!

Installing krms7d...
krms7d is installed successfully!

krms7d is starting...
starting krms                                              [  OK  ]
Succeed to start the krms service.
krms7d start successfully!

Installing kingbase7d...
kingbase7d is installed successfully!

Installing jobmgmt7d...
jobmgmt7d is installed successfully!

Installing kdbws7d...
kdbws7d is installed successfully!

kdbws7d is starting...
kdbws7d is starting.
kdbws7d start successfully!

cp: cannot create regular file ‘/usr/share/icons/hicolor/64x64/apps/kdb_help.png’: No such file or directory
chmod: cannot access ‘/usr/share/icons/hicolor/64x64/apps/kdb_help.png’: No such file or directory
cp: cannot create regular file ‘/usr/share/icons/hicolor/64x64/apps/kdb_isql.png’: No such file or directory
chmod: cannot access ‘/usr/share/icons/hicolor/64x64/apps/kdb_isql.png’: No such file or directory
cp: cannot create regular file ‘/usr/share/icons/hicolor/64x64/apps/kdb_jagent.png’: No such file or directory
chmod: cannot access ‘/usr/share/icons/hicolor/64x64/apps/kdb_jagent.png’: No such file or directory
cp: cannot create regular file ‘/usr/share/icons/hicolor/64x64/apps/kdb_jbackup.png’: No such file or directory
chmod: cannot access ‘/usr/share/icons/hicolor/64x64/apps/kdb_jbackup.png’: No such file or directory
cp: cannot create regular file ‘/usr/share/icons/hicolor/64x64/apps/kdb_jcontrol.png’: No such file or directory
chmod: cannot access ‘/usr/share/icons/hicolor/64x64/apps/kdb_jcontrol.png’: No such file or directory
cp: cannot create regular file ‘/usr/share/icons/hicolor/64x64/apps/kdb_jdts.png’: No such file or directory
chmod: cannot access ‘/usr/share/icons/hicolor/64x64/apps/kdb_jdts.png’: No such file or directory
cp: cannot create regular file ‘/usr/share/icons/hicolor/64x64/apps/kdb_jdump.png’: No such file or directory
chmod: cannot access ‘/usr/share/icons/hicolor/64x64/apps/kdb_jdump.png’: No such file or directory
cp: cannot create regular file ‘/usr/share/icons/hicolor/64x64/apps/kdb_jinitdb.png’: No such file or directory
chmod: cannot access ‘/usr/share/icons/hicolor/64x64/apps/kdb_jinitdb.png’: No such file or directory
cp: cannot create regular file ‘/usr/share/icons/hicolor/64x64/apps/kdb_jmanager.png’: No such file or directory
chmod: cannot access ‘/usr/share/icons/hicolor/64x64/apps/kdb_jmanager.png’: No such file or directory
cp: cannot create regular file ‘/usr/share/icons/hicolor/64x64/apps/kdb_jsql.png’: No such file or directory
chmod: cannot access ‘/usr/share/icons/hicolor/64x64/apps/kdb_jsql.png’: No such file or directory
cp: cannot create regular file ‘/usr/share/icons/hicolor/64x64/apps/kdb_kem.png’: No such file or directory
chmod: cannot access ‘/usr/share/icons/hicolor/64x64/apps/kdb_kem.png’: No such file or directory
cp: cannot create regular file ‘/usr/share/icons/hicolor/64x64/apps/kdb_kingbasehome.png’: No such file or directory
chmod: cannot access ‘/usr/share/icons/hicolor/64x64/apps/kdb_kingbasehome.png’: No such file or directory
cp: cannot create regular file ‘/usr/share/icons/hicolor/64x64/apps/kdb_start.png’: No such file or directory
chmod: cannot access ‘/usr/share/icons/hicolor/64x64/apps/kdb_start.png’: No such file or directory
cp: cannot create regular file ‘/usr/share/icons/hicolor/64x64/apps/kdb_stop.png’: No such file or directory
chmod: cannot access ‘/usr/share/icons/hicolor/64x64/apps/kdb_stop.png’: No such file or directory
cp: cannot create regular file ‘/usr/share/icons/hicolor/64x64/apps/kdb_support.png’: No such file or directory
chmod: cannot access ‘/usr/share/icons/hicolor/64x64/apps/kdb_support.png’: No such file or directory
cp: cannot create regular file ‘/usr/share/icons/hicolor/64x64/apps/kdb_uninstall.png’: No such file or directory
chmod: cannot access ‘/usr/share/icons/hicolor/64x64/apps/kdb_uninstall.png’: No such file or directory

Complete.
[root@localhost ~]# 

执行Root.sh脚本提示创建 /usr/share/icons/hicolor/64x64/ 相关文件失败，不知道是否有影响，先忽略该错误。
查找了一下几个目录的相关文件：
# find /usr/share/icons/ -name kdb_help.png
/usr/share/icons/hicolor/16x16/apps/kdb_help.png
/usr/share/icons/hicolor/32x32/apps/kdb_help.png

# find /opt/Kingbase/ES/V7/ -name kdb_help.png
/opt/Kingbase/ES/V7/Install/startup-desktop/icons/hicolor/16x16/apps/kdb_help.png
/opt/Kingbase/ES/V7/Install/startup-desktop/icons/hicolor/32x32/apps/kdb_help.png
/opt/Kingbase/ES/V7/Install/startup-desktop/icons/hicolor/64x64/apps/kdb_help.png

# find /home/kingbase/ -name kdb_help.png
/home/kingbase/.local/share/icons/hicolor/16x16/apps/kdb_help.png
/home/kingbase/.local/share/icons/hicolor/32x32/apps/kdb_help.png
/home/kingbase/.local/share/icons/hicolor/64x64/apps/kdb_help.png


查看服务
# /etc/init.d/krms7d status
# /etc/init.d/kingbase7d status 
# /etc/init.d/jobmgmt7d status 
# /etc/init.d/kdbws7d status
# ps aux |grep Kingbase
# netstat -an |grep 54328

[root@localhost ~]# /etc/init.d/krms7d status
krms of kingbase: running                                  [  OK  ]
[root@localhost ~]# /etc/init.d/kingbase7d status 
[root@localhost ~]# /etc/init.d/jobmgmt7d status 
[root@localhost ~]# /etc/init.d/kdbws7d status
kdbws7d (pid 9685) is running.......
[root@localhost ~]# ps aux |grep Kingbase
kingbase  9542  0.0  0.0 232640  2104 ?        Sl   19:48   0:00 /opt/Kingbase/ES/V7/bin/krms
kingbase  9685  0.3  3.1 2658452 124120 ?      Sl   19:48   0:09 /opt/Kingbase/ES/V7/jre/bin/java -Djava.util.logging.config.file=/opt/Kingbase/ES/V7/tomcat/conf/logging.properties -Djava.util.logging.manager=org.apache.juli.ClassLoaderLogManager -Djava.endorsed.dirs=/opt/Kingbase/ES/V7/tomcat/endorsed -classpath /opt/Kingbase/ES/V7/tomcat/bin/bootstrap.jar -Dcatalina.base=/opt/Kingbase/ES/V7/tomcat -Dcatalina.home=/opt/Kingbase/ES/V7/tomcat -Djava.io.tmpdir=/opt/Kingbase/ES/V7/tomcat/temp org.apache.catalina.startup.Bootstrap start
root     20298  0.0  0.0 112808   964 pts/1    S+   20:35   0:00 grep --color=auto Kingbase
[root@localhost ~]# netstat -an |grep 54328
tcp6       0      0 :::54328                :::*                    LISTEN     


查看版本信息
# su - kingbase
$ kingbase -V
kingbase (Kingbase) 7.1.2.0935 release 64 bit


如果初始化数据库和实例后，也可以在交互式终端（查询分析器或isql）中执行select version()
$ isql -h 127.0.0.1 -U SYSTEM -W 1q2w3e -p 54321 -d testdb
select version()

如果要卸载数据库，操作如下：
1) 进入安装目录下的 Uninstall 目录，以 root 执行  Uninstall.sh 卸载脚本：Uninstall.sh -i console
2) 卸载完成时会提示 以 root 执行 UninstallRoot.sh 脚本：/opt/Kingbase/ES/V7/Uninstall/UninstallRoot.sh
3) 删除安装目录下的所有文件： rm -rf /opt/Kingbase/ES/V7/*
如果卸载安装时提示已经安装过了，则以root身份删除 /etc/kingbasees 中有冲突的注册项


2. 手动初始化数据库（要求以非root操作）

1) 初始化数据库
# su - kingbase
$ cd /opt/Kingbase/ES/V7/bin/
$ initdb --help    #查看详细帮助信息
[kingbase@localhost bin]$ initdb --help
initdb initializes a Kingbase database cluster.

Usage:
  initdb [OPTION]... [DATADIR]

Options:
 [-D, --data=]DATADIR           location for this database cluster #数据目录位置，参数后指定具体的初始化数据库数据目录名称
  --case-insensitive            initialize database cluster with case-insensitive #大小写不敏感，默认是敏感，添加此选项，表示大小写不敏感
  --ignore-trailing-blanks      initialize database cluster with ignore-trailing-blanks
  -a, --alloc-space-for-null    allocate space for null values
  -E, --encoding=ENCODING       set default encoding for new databases #初始化数据库所用的数据库编码，支持四种编码选择：ASCII、GBK、GB18030、UNICODE
  --locale=LOCALE               initialize database cluster with given locale #指定 LOCAL，如不指定，默认是“C”，等效与指定“--no-locale”
  --lc-collate, --lc-ctype, --lc-messages=LOCALE
  --lc-monetary, --lc-numeric, --lc-time=LOCALE
                                initialize database cluster with given locale
                                in the respective category (default taken from
                                environment)
  --no-locale                   equivalent to --locale=C
  --wal-dir= DIRECTORY          
                                location for redo log files created during initdb #REDO 日志文件所在目录，可以通过此选项，把REDO日志文件和数据文件分开存放，有利于提高数据库系统的性能。
                                # 如果不指定本参数，则 REDO 日志文件同数据文件存放在相同的父目录中。如果给定本参数，且路径信息是相对路径，则此路径相对于通过“-D”指定的数据目录。
                                # REDO 日志文件默认有三个，用户可以在初始化后通过SQL 语句增加新的 REDO 日志文件
  --wal-file-size=SIZE          size(in megabytes) for redo log files created during initdb #REDO 日志文件大小，日志文件大小区间是：[16, 2048]；单位是：M，有性能要求的环境建议设置大一点
  --database=DBNAME             the user database created during initdb #数据库名称。初始化时，将为用户初始化一个用户数据库，必须指定
  -b, --blocksize=SIZE          database block size(in kilobytes), might be 4, 8, 16 or 32  #数据页面大小（单位：KB），默认值为：8KB
  -T, --text-search-config=CFG
                                default text search configuration
  -A, --auth=METHOD             default authentication method for local connections  #对于用户连接的验证方式。缺省时，默认的验证方式是“MD5”
  -U, --username=NAME           database superuser name, must be specified when initdb #用户名，初始化数据库后的数据库管理员的名称
  -W, --password=PASSWORD       database superuser password #初始化数据库后的数据库管理员的口令
  --ssousername=SSOUSERNAME     database ssouser name, default name is SYSSSO  #审计管理员账号
  --ssopassword=SSOPASSWORD     database ssouser password
  --saousername=SAOUSERNAME     database saouser name, default name is SYSSAO  #安全管理员账号
  --saopassword=SAOPASSWORD     database saouser password
  -Z, --empty-password          database superuser password is empty #当指定的用户没有口令时，使用“-Z”表示口令为空
  --pwfile=FILE                 read password for the new superuser from file #数据库管理员对应的口令文件，初始化时指定的数据库管理员对应的口令位于指定的文件中
 --encrypt-device               specify the encrypt device name
 --mac-writedown                MAC use wirtedown
  -?, --help                    show this help, then exit
  -V, --version                 output version information, then exit #版本信息

Less commonly used options:
  -d, --debug                   generate lots of debugging output
  -s, --show                    show internal settings  #显示设置信息。输出设置信息，如数据库管理员的名称等（要配合“-U”等参数使用）。如果指定本选项，则只是显式设置信息，不进行初始化工作
  -L DIRECTORY                  where to find the input files #共享目录。初始化过程中，需要读一些配置文件，如KingbaseES.conf、kls.conf等文件，可以通过本选项指定这些文件所在的目录。默认不金仓数据库 KingbaseES，需要指定，初始化工具会自动查找
  -n, --noclean                 do not clean up after errors #不删除初始化失败后的目录。数据库初始化过程失败，初始化工具自动删除初始化指定的目录。
  -f                            if the new data directory is not empty
                                remove it and create a new database 
  --guid-type=name|bytea         set the return type for sys_guid()

If the data directory is not specified, the environment variable KINGBASE_DATA
is used.

Report bugs to <support@kingbase.com.cn>.
[kingbase@localhost bin]$ 

手动初始化数据库
$ initdb -U system -W 1q2w3e -b 16 --wal-file-size=1024 -E UNICODE --ssousername=SYSSSO --ssopassword=1q2w3e --saousername=SYSSAO --saopassword=1q2w3e --case-insensitive --database=testdb -D /opt/Kingbase/ES/V7/data


The files belonging to this database system will be owned by user "kingbase".
This user must also own the server process.

The superuser of this database cluster is system.

The database cluster will be initialized with locales
  COLLATE:  C
  CTYPE:    C
  MESSAGES: en_US.UTF-8
  MONETARY: C
  NUMERIC:  C
  TIME:     C
The comparision of strings is case-insensitive.
The default text search configuration will be set to "english".

creating directory /opt/Kingbase/ES/V7/data ... ok
creating subdirectories ... ok
creating configuration files ... ok
creating audit template files ... ok
creating TEMPLATE1 database in /opt/Kingbase/ES/V7/data/DB ... ok
initializing the encrypt device ... ok
initializing sys_authid ... ok
setting superuser password ... ok
setting ssouser password ... ok
setting saouser password ... ok
initializing dual ... ok
initializing sys_guid ... return type is BYTEA ... ok
initializing file_type ... ok
initializing utl_file_internal ... ok
initializing dependencies ... ok
creating system views ... ok
creating package: DBMS_SQL ... ok
creating package: DBMS_ALERT ... ok
creating package: DBMS_DDL ... ok
creating package: DBMS_JOB ... ok
creating package: DBMS_GDAECDC ... ok
creating package: DBMS_DEFER_PACK ... ok
creating package: UTL_INADDR ... ok
creating package: UTL_FILE ... ok
creating package: DBMS_LOB ... ok
creating package: DBMS_METADATA ... ok
creating package: DBMS_OBFUSCATION_TOOLKIT ... ok
creating package: DBMS_RANDOM ... ok
creating compatibility views ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
add dependencies ... ok
vacuuming database TEMPLATE1 ... ok
copying TEMPLATE1 to TEMPLATE0 ... ok
copying TEMPLATE1 to TEMPLATE2 ... ok
creating user defined database testdb ... ok
creating SAMPLES database ... ok
loading SAMPLES database ... ok
loading Kingbase systools plugins ... ok
saving database user/password ...ok

DONE: Success. You can now start the database server using:

    kingbase -D /opt/Kingbase/ES/V7/data

[kingbase@localhost bin]$ 

查看数据库数据目录：
[kingbase@localhost bin]$ ls -l /opt/Kingbase/ES/V7/data/
total 36
drwx------ 6 kingbase kingbase   235 Aug 14 01:33 CTL
drwx------ 2 kingbase kingbase  4096 Aug 14 01:33 DB
-rw------- 1 kingbase kingbase  1049 Aug 14 01:33 high.sql
-rw------- 1 kingbase kingbase 11438 Aug 14 01:33 kingbase.conf    #数据库服务配置文件
-rw------- 1 kingbase kingbase   630 Aug 14 01:33 low.sql
-rw------- 1 kingbase kingbase   941 Aug 14 01:33 middle.sql
drwx------ 3 kingbase kingbase    88 Aug 14 01:33 REDOLOG
-rw------- 1 kingbase kingbase   485 Aug 14 01:33 sys_hba.conf     #数据库服务访问控制配置文件
-rw------- 1 kingbase kingbase  1476 Aug 14 01:33 sys_ident.conf

修改数据库服务配置文件

$ vim /opt/Kingbase/ES/V7/data/kingbase.conf
#max_connections = 25
max_connections = 100


启动数据库服务
kingbase -D /opt/Kingbase/ES/V7/data&
或
sys_ctl start -D /opt/Kingbase/ES/V7/data
查看数据库服务监听端口
netstat -an |grep 54321

如果要停止数据库服务
sys_ctl stop -D /opt/Kingbase/ES/V7/data

查看数据库服务状态
sys_ctl status -D /opt/Kingbase/ES/V7/data

数据库服务状态也可以通过该查看监听端口
netstat -an |grep 54321


进入数据库shell
$ isql -h 127.0.0.1 -U SYSTEM -W 1q2w3e -p 54321 -d testdb
2020-08-14 02:59:07.655 CST  [unknown] 5 [unknown]LOG:  connection received: host=127.0.0.1 port=39612
2020-08-14 02:59:07.655 CST 127.0.0.1 testdb 5 SYSTEMLOG:  connection authorized: user=SYSTEM database=testdb
2020-08-14 02:59:07.657 CST 127.0.0.1 testdb 5 SYSTEMLOG:  connection authorized: user=SYSTEM database=testdb
Welcome to isql 7.1.2.0935 release 64 bit, the Kingbase interactive terminal.

Type:  \h for help with SQL commands
       \? for help with isql commands
       \g or terminate with semicolon to execute query
       \q to quit

testdb=# select version();
                 version                  
------------------------------------------
 Kingbase 7.1.2.0935 release 64 bit SALES
(1 row)

testdb=# 
testdb=# \l
           List of databases
   Name    | Owner  | Size  | Encoding 
-----------+--------+-------+----------
 GLOBAL    | system | 40 MB | UNICODE
 SAMPLES   | system | 67 MB | GBK
 TEMPDB    | system | 20 MB | UNICODE
 TEMPLATE0 | system | 67 MB | UNICODE
 TEMPLATE1 | system | 67 MB | UNICODE
 TEMPLATE2 | system | 67 MB | UNICODE
 testdb    | system | 67 MB | UNICODE
(7 rows)

testdb=# 

2) 创建(注册)数据库实例(instance)
只要创建并启动了数据库(database)，就可以远程连接访问了，不注册数据库实例也可以。
注册数据库实例以后，也可以通过实例的方式来启动/停止，启动/停止实例时会自动同步启动/停止数据库服务，操作起来比较方便。
$  kingbase --install inst01 -U system -p 54321 -D /opt/Kingbase/ES/V7/data
Succeed to set instance "inst01" as system service.

参数说明：
–install kingbase_inst : 创建(注册)的实例名
-U SYSTEM : 实例用户
-p 54321 : 实例端口
-D /opt/Kingbase/ES/V7/data : 本实例中数据存储位置
如果有需要，也可以创建多个实例（但一个数据库只能对应注册1个实例）


查看数据库实例
$ kingbase --list
2020-08-14 03:24:52.117 CST  [unknown] 5 [unknown]LOG:  connection received: host=[local]
2020-08-14 03:24:52.117 CST [local] TEMPLATE2 5 systemLOG:  connection authorized: user=system database=TEMPLATE2
2020-08-14 03:24:52.118 CST [local] TEMPLATE2 5 systemLOG:  connection authorized: user=system database=TEMPLATE2
2020-08-14 03:24:52.119 CST [local] TEMPLATE2 5 systemLOG:  disconnection: session time: 0:00:00.002 user=system database=TEMPLATE2 host=[local]
inst01                          started         kdbinst01d                              54321           /opt/Kingbase/ES/V7/data
[kingbase@localhost bin]$ 

通过实例名来启动和停止实例和数据库

kingbase --stop  inst01 
sys_ctl  status -D /opt/Kingbase/ES/V7/data/
netstat -an |grep 54321

kingbase --start inst01
sys_ctl  status -D /opt/Kingbase/ES/V7/data/
netstat -an |grep 54321


3) 数据库连接测试

本地连接
isql -h 127.0.0.1 -U SYSTEM -W 1q2w3e -p 54321 -d testdb

远程连接
Kingbase jdbc 连接和 MySQL 类似
jdbc驱动文件路径： /opt/Kingbase/ES/V7/jdbc

MySQL jdbc 连接：
driverClassName = com.mysql.jdbc.Driver
url = jdbc:mysql://192.168.208.100:3306/testdb
username = test
password = 1q2w3e

Kingbase jdbc 连接：
driverClassName = com.kingbase.Driver
url = jdbc:kingbase://192.168.208.100:54321/testdb
username = SYSTEM
password = 1q2w3e


创建表
CREATE TABLE PUBLIC.TEST01 (
    ID INTEGER NOT NULL,
    NAME VARCHAR(255),
    CONSTRAINT TEST01_PK PRIMARY KEY (ID)
);

INSERT INTO PUBLIC.TEST01(ID, NAME) VALUES(1, 'aaa');
INSERT INTO PUBLIC.TEST01(ID, NAME) VALUES(2, 'bbb'),(3, 'ccc');


CREATE TABLE PUBLIC.TEST02 (
    ID INTEGER NOT NULL,
    NAME VARCHAR(255),
    CONSTRAINT TEST02_PK PRIMARY KEY (ID)
);

INSERT INTO PUBLIC.TEST02(ID, NAME) VALUES(1, 'aaa');
INSERT INTO PUBLIC.TEST02(ID, NAME) VALUES(2, 'bbb'),(3, 'ccc');


Kingbase ES V7下可创建多个连接，每个连接指定到一个初始化的实例，每个连接下可创建多个数据库，每个数据库下包含多种模式（也可以自定义模式，默认模式为 PUBLIC），每个模式下可以创建多张表。
使用Kingbase数据库时，建议每个业务系统对应一个数据库，相关表格全部放在 PUBLIC 模式下，这样就可以支持通用SQL的操作、不用指定模式名。如果放在其他模式下，对某表格进行SQL语句操作，就必须使用 “模式名.表名”，不能省略模式名。

添加开机自启动：

# vim /etc/rc.local
/bin/su kingbase -c "PATH=$PATH:$HOME/.local/bin:$HOME/bin; export PATH; . ~/.kbrc; /opt/Kingbase/ES/V7/bin/kingbase --start inst01"

# chmod u+x /etc/rc.local


-------------------------------


Kingbase ES V7 常用命令

kingbase --help                          #查看帮助
kingbase --list                          #查看所有实例
kingbase -D /opt/Kingbase/ES/V7/data&    #启动数据库服务，-D 后面指定数据库数据目录
kingbase --start inst01                  #启动数据库实例，inst01为指定的instance name
kingbase --stop inst01                   #停止数据库实例，inst01为指定的instance name

sys_ctl --help                               #查看帮助
sys_ctl status -D /opt/Kingbase/ES/V7/data   #查看数据库服务状态
sys_ctl start -D /opt/Kingbase/ES/V7/data    #启动数据库服务
sys_ctl stop -D /opt/Kingbase/ES/V7/data     #停止数据库服务
sys_ctl restart -D /opt/Kingbase/ES/V7/data  #重启数据库服务
sys_ctl reload -D /opt/Kingbase/ES/V7/data
sys_ctl kill 

isql --help  #查看帮助
isql -h 127.0.0.1 -U SYSTEM -W 1q2w3e -p 54321 -d testdb    #进入数据库命令行

数据库备份
备份的路径在配置文件中(backup_path in /opt/Kingbase/ES/V7/data/kingbase.conf)

数据库全量备份
isql -h 127.0.0.1 -U SYSTEM -W 1q2w3e -d testdb -p 54321 -c "backup name back01 type full;"

数据库增量备份
isql -h 127.0.0.1 -U SYSTEM -W 1q2w3e -d testdb -p 54321 -c "backup name back02 type differential increment;"

开启归档日志
vim /opt/Kingbase/ES/V7/data/kingbase.conf
修改参数
log_archive_start = on
log_archive_dest = '/opt/kingbase/ES/V7/archive'

重启数据库实例
kingbase --stop inst01
kingbase --start inst01
 

查看所有用户
\du

创建用户
create user frank password '1q2w3e';
grant all on database testdb to frank; 

如果要删除授权：
revoke all on database testdb on frank;

创建超级用户
create user frank with superuser password '1q2w3e';

删除用户
drop user frank;


授权
查看帮助
#\help grant
Command:     GRANT
Description: define access privileges
Syntax:
GRANT {ObjectPrivilege[,...n] | ALL }
{
    [ ON [ TABLE ] TableName
    | ON [ VIEW ] ViewName
    | ON [ SEQUENCE ] SeqName
    | ON DATABASE DbName
    | ON TABLESPACE tablespace 
    | ON PROCEDURE ProcName ([type, ...n]) [, ...n]
    | ON FUNCTION FuncName ([type, ...n]) [, ...n]
    | ON PACKAGE PkgName
    | ON SCHEMA SchemaName
}
TO { { UserName | PUBLIC } [ WITH GRANT OPTION ]
   }

GRANT {ColumnPrivilege( column[, ...] )[,...n] 
       | ALL [ PRIVILEGES ] ( column[, ...] )}
{
    ON [ [ TABLE ] TableName [,...]
         |[ VIEW ] ViewName  [,...]
}
TO { { UserName | PUBLIC } [ WITH GRANT OPTION ]
   }

GRANT Role [, ...n] 
TO { UserName | RoleName [, ...n] } [ WITH ADMIN OPTION ]

grant all on database testdb to frank;
grant all on database testdb to public;  //public表示授权给所有用户


对数据库模式的授权由DBA创建用户时授予
create user <用户名> [with] [DBA|RESOURCE|CONNECT];
1.数据库的超级用户才有权创建一个新的数据库用户
2.创建的数据库用户有三责权限：DBA|RESOURCE|CONNECT
3.如果不指定权限，则默认为CONNECT权限。

DBA       数据库管理员权限，包括：创建用户、角色、视图、表、授权等DBA权限
RESOURCE  有创建基本表、视图的权限，但不能创建模式、不能创建用户
CONNECT   只有连接数据库权限、没有创建用户、模式、表的权限


删除权限
# \help revoke
Command:     REVOKE
Description: remove access privileges
Syntax:
REVOKE SystemPrivilege[,...n]
FROM { UserName | RoleName} [, ...n]


REVOKE [ GRANT OPTION FOR ] {ObjectPrivilege[,...n] | ALL [ PRIVILEGES ]}
{
    ON [ TABLE ] TableName
    | ON [ VIEW ] ViewName
    | ON [ SEQUENCE ] SeqName
    | ON DATABASE  DbName
    | ON PROCEDURE ProcName ([type, ...n]) [, ...n]
    | ON FUNCTION  FuncName ([type, ...n]) [, ...n]
    | ON PACKAGE PkgName
    | ON SCHEMA    SchemaName
    | ON TABLESPACE tablespace
}
FROM { UserName | RoleName | PUBLIC } [, ...n] [RESTRICT | CASCADE]


REVOKE [ ADMIN OPTION FOR ] Role [, ...n]
FROM { UserName | RoleName [, ...n] }  [ CASCADE | RESTRICT ]

revoke all on database testdb from frank;


创建数据库
create database testdb02;

删除数据库
drop database testdb02;

修改数据库用户密码
ksql -U SYSTEM -d TEST
alter user SYSTEM password '123456';

查看大小写敏感设置
show case_sensitive;

初始化数据库命令帮助
./initdb --help
注意：有些数据库不支持Oracle/PG模式的设置，例如：KingbaseES V8R3版本的数据库，initdb --help 查看帮助时，就没有 -m pg 参数选项

# 数据库
Logo
华为云开发者联盟

为开发者提供学习成长、分享交流、生态实践、资源工具等服务，帮助开发者快速成长。

更多推荐

cover

对接HiveMetaStore，拥抱开源大数据

avatar 华为云开发者联盟
cover

HCDG北京站精彩回顾｜鸿蒙千帆起，开启万物互联新纪元

avatar 华为云开发者联盟
cover

GaussDB 数据导入导出工具介绍

avatar 华为云开发者联盟