人大金仓kingbase部署&测试
人大金仓KingBase
安装&部署
为了方便，我们这里使用docker方式进行kingbase部署，其中kingbase使用的版本为v8r6
# 1. 下载docker镜像
docker pull warm3snow/kingbase:v8r6
# 2. 创建本地数据目录
mkdir -p /opt/kingbase/data
# 3. 启动kingbase
docker run -d --name kingbasev8r6 -p 54321:54321 -e SYSTEM_USER=kingbase -e SYSTEM_PWD=kingbase \
-v /opt/kingbase/data:/opt/kingbase/data \
warm3snow/kingbase:v8r6
# 4. 查看日志, 等待启动完成
docker logs `docker ps -a | grep kingbase | awk '{print $1}'`
waiting for server to start.... done
server started
2024-06-11 10:00:20.047 GMT [14] HINT: the value of max_connect is set 10
2024-06-11 10:00:20.050 GMT [14] WARNING: max_connections should be less than or equal than 10 (restricted by license)
2024-06-11 10:00:20.050 GMT [14] HINT: the value of max_connect is set 10
2024-06-11 10:00:20.057 UTC [14] LOG: sepapower extension initialized
2024-06-11 10:00:20.058 UTC [14] LOG: starting KingbaseES V008R006C005B0013 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-46), 64-bit
2024-06-11 10:00:20.058 UTC [14] LOG: listening on IPv4 address "0.0.0.0", port 54321
2024-06-11 10:00:20.058 UTC [14] LOG: listening on IPv6 address "::", port 54321
2024-06-11 10:00:20.064 UTC [14] LOG: listening on Unix socket "/tmp/.s.KINGBASE.54321"
2024-06-11 10:00:20.096 UTC [14] LOG: redirecting log output to logging collector process
2024-06-11 10:00:20.096 UTC [14] HINT: Future log output will appear in directory "sys_log".
如果提示license expired，可以从官网下载最新的license文件，替换license.dat即可。步骤如下：
- 下载地址如下：https://www.kingbase.com.cn/xzzx/index.htm
- 启动docker时进行挂载：
docker run -d --name kingbasev8r6 -p 54321:54321 -e SYSTEM_USER=kingbase -e SYSTEM_PWD=kingbase \
-v /opt/kingbase/data:/opt/kingbase/data \
-v ./license.dat:/opt/kingbase/Server/bin/license.dat \
warm3snow/kingbase:v8r6
登录&测试
# 进入docker容器
docker exec -it `docker ps -a | grep kingbase | awk '{print $1}'` /bin/sh
# 切换工作目录
cd Server/bin/
# 登录test数据库
./ksql -Ukingbase test #注test为默认安装的测试数据库
# 创建测试表
test=# CREATE TABLE mytest(
id INT,
name VARCHAR(20)
);
# 查询测试表
test=# \dt
List of relations
Schema | Name | Type | Owner
--------+--------+-------+------------
public | mytest | table | kingbase
(1 row)
# 插入测试数据
test=# INSERT INTO mytest VALUES(1, 'zhangsan');
test=# INSERT INTO mytest VALUES(2, 'lisi');
test=# INSERT INTO mytest VALUES(3, 'wangwu');
# 查询测试数据
test=# SELECT * FROM mytest;
id | name
----+----------
1 | zhangsan
2 | lisi
3 | wangwu
(3 rows)
常用DDL
# 创建数据库
test=# CREATE DATABASE mydb;
# 删除数据库
test=# DROP DATABASE mydb;
# 创建表
test=# CREATE TABLE mytest(
id INT,
name VARCHAR(20)
);
# 删除表
test=# DROP TABLE mytest;
# 修改表: 添加列, 删除列, 修改列名, 修改表名, 清空表, 创建索引, 删除索引
test=# ALTER TABLE mytest ADD COLUMN age INT;
test=# ALTER TABLE mytest DROP COLUMN age;
test=# ALTER TABLE mytest RENAME COLUMN name TO username;
test=# ALTER TABLE mytest RENAME TO mytest2;
test=# TRUNCATE TABLE mytest;
test=# CREATE INDEX mytest_index ON mytest(id);
test=# DROP INDEX mytest_index;
# 创建用户
test=# CREATE USER myuser WITH PASSWORD '<PASSWORD>';
# 授权用户
test=# GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;
# 删除用户
test=# DROP USER myuser;
常用命令
# 数据库列表
test=# \l
# 表列表
test=# \dt
# 表结构
test=# \d mytest
# 切换数据库
test=# \c mydb
# 查看所有存储过程
test=# \df
# 查看所有用户
test=# \du
常见问题
1. 如何查看kingbase版本
docker exec -it `docker ps -a | grep kingbase | awk '{print $1}'` /opt/kingbase/Server/bin/ksql -V
2. 如何查看kingbase日志
docker logs `docker ps -a | grep kingbase | awk '{print $1}'`
docker logs `docker ps -a | grep kingbase | awk '{print $1}'`
3. 如何查看kingbase进程
docker exec -it `docker ps -a | grep kingbase | awk '{print $1}'` ps -ef
4. kingbase服务启动失败，提示license expired
# 1. 下载最新license文件
# 2. 启动docker时进行挂载
docker run -d --name kingbasev8r6 -p 54321:54321 -e SYSTEM_USER=kingbase -e SYSTEM_PWD=kingbase \
-v /opt/kingbase/data:/opt/kingbase/data \
-v ./license.dat:/opt/kingbase/Server/bin/license.dat \
warm3snow/kingbase:v8r6
5. kingbase服务启动失败，提示Operation not permitted
错误详情如下：
chown: changing ownership of '/opt/kingbase/data': Operation not permitted
The files belonging to this database system will be owned by user "kingbase".
This user must also own the server process.
The database cluster will be initialized with locale "C".
The default text search configuration will be set to "english".
The comparision of strings is case-insensitive.
Data page checksums are disabled.
fixing permissions on existing directory /opt/kingbase/data ... initdb: error: could not change permissions of directory "/opt/kingbase/data": Operation not permitted
initdb failed
错误原因：docker挂载目录权限问题，导致kingbase无法修改目录权限
解决方案：修改挂载目录权限
# 1. 本地创建kingbase用户和用户组（注意：用户ID和组ID必须和docker内部的一致，docker内部为10001）
# 在创建前需要排除已经占用该ID的用户和组（如果被占用，则需要同步修改docker内部的用户和组ID）
groupadd -g 1001 kingbase
useradd -u 1001 -g 1001 kingbase
# 2. 修改挂载目录权限
chown -R 1001:1001 /opt/kingbase/data
6. 镜像warm3snow/kingbase:v8r6不满足需求，如何定制化
# 1. 下载Dockerfile和镜像build仓库
git clone https://github.com/warm3snow/practical-crypto.git
cd practical-crypto/xin_chuang/kingbase
# 定制化内容
# 修改license：将license.dat放到当前目录，并覆盖
# 修改安装包：将kingbase安装包放到当前目录，并修改Dockerfile中的安装包，从本地ADD
# 修改启动脚本：修改Dockerfile中的启动脚本，从本地ADD
# 修改kingbase用户和组ID：修改Dockerfile中的kingbase用户和组ID，源码中默认为10001
