kingbase
# mysql
```
-- 查看组件id和链路节点id是否正常
select a.* from levelinfo_view a inner join (
select chooseId ,count(1) as n from levelinfo_view group by chooseId having n>1) b
on a.chooseId=b.chooseId
order by a.chooseId
```
```sql
-- mysql
INSERT INTO easyviews.`sys_holiday` ( `name`, `date`, `workday`) VALUES( '元旦', '2024-01-01', 0);
-- kingbase
INSERT INTO sys_holiday ( name, "date", workday) VALUES( '元旦', '2024-01-01', 0);
-- dm
SET IDENTITY_INSERT sys_holiday ON;
INSERT INTO sys_holiday ( name, "date", workday) VALUES( '元旦', '2024-01-01', 0);
SET IDENTITY_INSERT sys_holiday OFF;
commit;
print("取消勾选所有组件")
sql = "DELETE FROM bpm_component_dimension WHERE dimensionId IN (-5,-6,-7,-8,-9,-10,-11,-12,-13,-14);"
# 功能号放第一位
update bpm_component_dimension set sort=-1 where dimensionId in (select id from bpm_component_field where fieldMapping='dimension7')
```
```bash
mkdir /mnt/kingbase-latest/opt -p
chmod 777 /mnt/kingbase-latest/opt
mkdir /mnt/kingbase-latest/opt/license
docker pull godmeowicesun/kingbase
wget https://kingbase.oss-cn-beijing.aliyuncs.com/KESV8R3/V8R6/license-%E4%BC%81%E4%B8%9A%E7%89%88.zip -O /mnt/kingbase-latest/opt/license/license.zip
unzip /mnt/kingbase-latest/opt/license/license.zip
mv license_29296/license_29296_0.dat /mnt/kingbase-latest/opt/license/license.dat
docker run -d -it --privileged=true -p 54321:54321 -v /mnt/kingbase-latest/opt:/opt --name kingbase godmeowicesun/kingbase
docker exec -it kingbase bash
```
### dbeaver链接人大金仓
数据库 —> 驱动管理器
```
com.kingbase8.Driver
jdbc:kingbase8://{host}[:{port}]/[{database}]
```
引入人大金仓驱动包(驱动位置下载：阿里云盘：文件/easyviews/数据库驱动)
### 命令行登录
```
ksql -U system -d easyviews -c "select * from deploy;"
./ksql test system
./ksql -U system -h localhost -p 54321 easyviews
# 查看数据库列表
[root@hadoop101 bin]# ./ksql -U system -l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+--------+----------+----------+-------------+-------------------
easyviews | system | UTF8 | ci_x_icu | zh_CN.UTF-8 |
security | system | UTF8 | ci_x_icu | zh_CN.UTF-8 |
template0 | system | UTF8 | ci_x_icu | zh_CN.UTF-8 | =c/system +
| | | | | system=CTc/system
template1 | system | UTF8 | ci_x_icu | zh_CN.UTF-8 | =c/system +
| | | | | system=CTc/system
test | system | UTF8 | ci_x_icu | zh_CN.UTF-8 |
(5 rows)
# 连接指定的数据库
[root@hadoop101 bin]# ./ksql -U system -d easyviews
ksql (V8.0)
Type "help" for help.
easyviews=#
# 执行指定的sql脚本
[kingbase@s166 bin]$ ./ksql -U system -f /tmp/test.sql easyviews
-------------7(1 row)
```
### 更换 license
```
# 查看授权还有多久到期
SELECT GET_LICENSE_VALIDDAYS();
通用机服务器替换license步骤：
特别说明：①、如果是集群先停掉集群：su - kingbase(切换到kingbase用户) ，kingbase_monitor.sh stop（停掉集群）
②、如果是单机停掉单机：：su - kingbase(切换到kingbase用户) ，sys_ctl stop -D data完整路径 （停掉单机数据库）
ps -ef | grep kingbase可以看到数据库的安装路径，下面以/home/kingbase安装路径介绍
第一步，执行：find /home/kingbase -name license.dat ，看license.dat在哪些路径。（数据库如果是装在windows上的话直接找到替换，重启数据库即可，不用执行下面的步骤，windows只能用标准版授权）
第二步：把上一步找到的路径记录下来。
第三步：根据上一步的路径把原有license.dat重命名为license.dat_old或者其他名字也行
第四步：把我给的授权上传到服务器，改为license.dat，并且执行chown -R kingabse:kingbase license.dat（用什么用户安装的数据库用户权限给到哪个用户，这一步建议用root用户进行授权）
第五步，执行su - kingbase切换到kingbase用户，把最新的license拷贝到第二步记录下来的路径。
最后一步，启动数据库①、集群：kingbase_monitor.sh start (sys_monitor.sh start)
②、单机：sys_ctl start -D /home/kingbase/KingbaseES/V8/data
[root@captor-101 ~]# find / -name kingbase.conf
/dbdata/data/kingbase.conf
[kingbase@captor-101 V008R006C006B0013PS003]$ sys_ctl stop -D /dbdata/data
waiting for server to shut down.... done
server stopped
[kingbase@captor-101 V008R006C006B0013PS003]$
[root@captor-101 ~]# find /home/kingbase -name license.dat
/home/kingbase/KingbaseES/V8/license.dat
/home/kingbase/KingbaseES/V8/KESRealPro/V008R006C006B0013PS003/license.dat
[kingbase@captor-101 V008R006C006B0013PS003]$ chown -R kingbase:kingbase license.dat
[kingbase@captor-101 V008R006C006B0013PS003]$ sys_ctl start -D /dbdata/data
```
### 备份重启服务
```
1、确认应用是否已经停止
2、进行手工逻辑全量备份:
su - kingbase
sys_dumpall -Usystem > XXXX/db_fullbackup.sql (XXXX路径为kingbase用户属主的存储路径)
3、ksql test system 登录数据库，查询活动用户连接情况：
select usename,count(*) from sys_stat_activity where state !='idle' group by usename;
4、\q退出数据库登录，sys_monitor.sh stop停止数据库集群
5、确认数据库服务器正常启动的情况下：sys_monitor.sh start启动数据库集群
```
### 备份和数据恢复
```sql
-- rename 数据库
test=# ALTER DATABASE easyviews RENAME TO easyviews_bak;
ALTER DATABASE
-- rename 表
ALTER TABLE my_table RENAME TO new_table;
```
[sys_dump](https://help.kingbase.com.cn/v8.6.7.12/admin/reference/ref-client/app-pgdump.html#id7)
```sql
-- 要把一个数据库 mydb 转储到一个 SQL 脚本文件
sys_dump -U system easyviews > 0224.sql
-- 脚本载入
ksql -f 0224.sql -U SYSTEM easyviews
-- 要转储一个名为 mytab 的表
sys_dump -U system -t mytab easyviews > db.sql
-- 要转储一个数据库到一个自定义格式归档文件：
sys_dump -Fc easyviews -U system > db.dump
-- 要把一个归档文件重新载入到一个（新创建的）名为 newdb 的数据库：
sys_restore -U system -d newdb db.dump
-- 把一个归档文件重新装载到同一个数据库（该归档正是从这个数据库中转储得来）中，丢掉那个数据库中的当前内容：
sys_restore -d easyviews -U system --clean --create db.dump
```
[sys-dumpall](https://help.kingbase.com.cn/v8.6.7.12/admin/reference/ref-client/app-pg-dumpall.html#sys-dumpall)
```
#备份
sys_dumpall -U system > 20240223kb.sql
#恢复
ksql -f 20240223kb.sql -U system easyviews
```
如果你要清空人大金仓数据库中的所有数据，可以执行以下 SQL 语句：
```sql
DROP SCHEMA public CASCADE;
CREATE SCHEMA public;
```
这两条语句的作用如下：
`DROP SCHEMA public CASCADE;`：删除数据库中的 public 模式以及该模式下的所有表、视图和其他对象。CASCADE 选项指定了如果有依赖于该模式的其他对象，则也会被删除。
`CREATE SCHEMA public;`：重新创建一个空的 public 模式，可以用来存储新的数据表和其他对象。
执行以上两条语句后，你的数据库将被清空，并且只剩下一个空的 public 模式。请注意，在执行这些操作之前，务必备份好原始数据，以防止不可逆的数据丢失。
### 乱七杂八
```
;[root@FAS-BigData6 ~]# su - kingbase
Last login: Sat Dec 16 10:34:38 CST 2023 on pts/0
[kingbase@FAS-BigData6 ~]$ repmgr cluster show
ID | Name | Role | Status | Upstream | Location | Priority | Timeline | LSN_Lag | Connection string
----+-------+---------+-----------+----------+----------+----------+----------+---------+----------------------------------------------------------------------------------------------------------------------------------------------------
1 | node1 | primary | * running | | default | 100 | 1 | | host=192.168.80.172 user=esrep dbname=esrep port=54321 connect_timeout=10 keepalives=1 keepalives_idle=10 keepalives_interval=1 keepalives_count=3
2 | node2 | standby | running | node1 | default | 100 | 1 | 0 bytes | host=192.168.80.171 user=esrep dbname=esrep port=54321 connect_timeout=10 keepalives=1 keepalives_idle=10 keepalives_interval=1 keepalives_count=3
[kingbase@FAS-BigData6 ~]$ ksql test system
ksql (V8.0)
Type "help" for help.
test=# \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-----------+--------+----------+----------+-------------+-------------------+---------+-------------+-----------------------------------------
---
easyviews | system | UTF8 | ci_x_icu | en_US.UTF-8 | | 65 MB | sys_default |
esrep | system | UTF8 | ci_x_icu | en_US.UTF-8 | | 13 MB | sys_default |
security | system | UTF8 | ci_x_icu | en_US.UTF-8 | | 12 MB | sys_default |
template0 | system | UTF8 | ci_x_icu | en_US.UTF-8 | =c/system +| 12 MB | sys_default | unmodifiable empty database
| | | | | system=CTc/system | | |
template1 | system | UTF8 | ci_x_icu | en_US.UTF-8 | =c/system +| 12 MB | sys_default | default template for new databases
| | | | | system=CTc/system | | |
test | system | UTF8 | ci_x_icu | en_US.UTF-8 | | 3400 MB | sys_default | default administrative connection databa
se
(6 rows)
test=# \c easyviews
You are now connected to database "easyviews" as user "system".
easyviews=# \d+ sys_holiday
Table "public.sys_holiday"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Descriptio
n
---------+-----------------------------+-----------+----------+-----------------------------------------+----------+--------------+-----------
--
id | integer | | not null | nextval('sys_holiday_id_seq'::regclass) | plain | |
name | character varying(128 char) | default | not null | | extended | |
date | pg_catalog.date | | not null | | plain | |
workday | smallint | | not null | | plain | |
Indexes:
"sys_holiday_pkey" PRIMARY KEY, btree (id)
Access method: heap
easyviews=# \d+
List of relations
Schema | Name | Type | Owner | Size | Description
--------+-------------------------------------------------+----------+--------+------------+----------------------------------------------------------------------------------------------
----------------------
public | ai_report_daily | table | system | 0 bytes | 每日报表主表
public | ai_report_daily_compare_data | table | system | 8192 bytes | 每日报表自定义对比数据 存对比日期的数据
public | ai_report_daily_compare_data_id_seq | sequence | system | 8192 bytes |
public | ai_report_daily_data | table | system | 8192 bytes | 每日报表主体数据
public | ai_report_daily_data_id_seq | sequence | system | 8192 bytes |
public | ai_report_daily_id_seq | sequence | system | 8192 bytes |
public | ai_report_daily_json | table | system | 8192 bytes | 每日报表json
public | ai_report_daily_json_id_seq | sequence | system | 8192 bytes |
public | ai_report_task | table | system | 8192 bytes | 报表定时任务表
public | ai_report_task_id_seq | sequence | system | 8192 bytes |
public | ai_report_task_scene | table | system | 0 bytes | 报表定时任务存储表
public | ai_report_task_scene_id_seq | sequence | system | 8192 bytes |
public | ai_report_uuid | table | system | 8192 bytes | 三维报表主体
public | ai_report_uuid_dimension | table | system | 8192 bytes | 三维报表维度
public | ai_report_uuid_dimension_id_seq | sequence | system | 8192 bytes |
public | ai_report_uuid_filter | table | system | 0 bytes | 三维报表筛选条件
```