金倉數據庫Mysql版本安裝測試順暢,兼容性方面很棒,已從功能兼容階段過渡到強性能兼容、生態全面兼容階段
金倉體驗官來了,第一期、第二期、第三期 ···· 沒寫的加緊入局 ~

1. 金倉mysql兼容版本安裝配置
1.1 簡單準備工作
PS : 安裝具體細節參照之前寫的:十分鐘內搞定金倉數據庫V8靜默安裝(新手完全上手)
https://bbs.kingbase.com.cn/blogDetail?postsId=9a0cfd3b13dad2b395ee0e2df4a1b3dd
# 新建安裝及數據目錄
[kingbase@dba236 mysql]$ mkdir -p /data/Kingbase/kmysql/{kbinstall,data}
# 掛載ISO安裝文件
[root@dba236 Kingbase]# mount /data/Kingbase/KingbaseES_V009R003C011B0003_Lin64_install.iso /data/Kingbase/kmysql/kbinstall
mount: /dev/loop1 is write-protected, mounting read-only
# 下載license文件,并放置到如下目錄,并授權
[root@dba236 mysql]# chown -R kingbase:kingbase /data/Kingbase/license_mysql.dat
1.2 校驗安裝包
[root@dba236 Kingbase]# md5sum KingbaseES_V009R003C011B0003_Lin64_install.iso
a5d73025035c5616f8b696423f7d0bf8 KingbaseES_V009R003C011B0003_Lin64_install.iso
1.3 復制并配置靜默安裝的配置文件
[root@dba236 setup]# cat silent.cfg > /data/Kingbase/silent.cfg
vi /data/Kingbase/silent.cfg
KB_LICENSE_PATH=/data/Kingbase/license_mysql.dat
USER_INSTALL_DIR=/data/Kingbase/kmysql
USER_SELECTED_DATA_FOLDER=/data/cd -Kingbase/kmysql/data
DB_PORT=54322
DB_PASS2=kbdb#134
DATABASE_MODE_PARAM=MySQL # 留意下就好,配置文件包含
1.4 靜默安裝
PS:進入安裝程序所在目錄,以kingbase用戶執行如下命令,否則會報錯提示需要非root用戶安裝
cd /data/Kingbase/V9/mysql/kbinstall
[root@dba236 kbinstall]# ll
total 6
dr-xr-xr-x 2 root root 2048 Feb 14 2025 setup
-r-xr-xr-x 1 root root 3932 Feb 14 2025 setup.sh
[kingbase@dba236 kbinstall]$ ./setup.sh -i silent -f/data/Kingbase/silent.cfg
Now launch installer...
.Complete.
1.5 查看安裝日志下
cd /data/Kingbase/kmysql/install/Logs

1.6 啟動服務
cd /data/Kingbase/kmysql/Server/bin
./sys_ctl -w start -D /data/Kingbase/kmysql/data -l /data/Kingbase/kmysql/data/sys_log/startup.log
# 輸出:
waiting for server to start.... done
server started
# 查看端口
[kingbase@dba236 bin]$ netstat -ntpl|grep 54322
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
tcp 0 0 0.0.0.0:54322 0.0.0.0:* LISTEN 7166/kingbase
tcp6 0 0 :::54322 :::* LISTEN 7166/kingbase
1.7 查看版本
test=# show database_mode;
test=# select version();

1.8 快捷命令及免密登錄
編輯環境變量
# 增加 ~/.bash_profile
alias ks2='ksql -p 54322 -d test -U system'
# 馬上生效
source $ cat ~/.bash_profile
編輯sys_hba.conf配置文件(如圖)
/data/Kingbase/kmysql/data/sys_hba.conf
# 重新加載配置
./Server/bin/sys_ctl reload

2. Mysql兼容性測試
KingbaseES以內核兼容為基礎,通過初始化參數控制,兼容MySQL 數據庫,支持MySQL常用功能。KingbaseES對MySQL的兼容性,已從功能兼容階段過渡到強性能兼容、生態全面兼容階段,做到在遷移過程中對上層應用透明,實現低成本,低難度,低風險的平滑遷移。
在基礎能力方面,KingbaseES兼容SQL語法及PL/SQL過程化語言的語法基礎,完成了對數據類型、常用表達式和條件、系統視圖、內置函數、DML、DQL語句,以及控制語句、存儲過程、函數、觸發器、游標,靜態SQL、動態SQL等各方面的兼容。在高級能力方面,KingbaseES支持BIT,ENUM類型,SET類型等特殊類型,支持INTERVAL表達式,MySQL的用戶變量,REPLACE,INSERT ON DUPLICATE KEY子句,INSERT IGNORE INTO子句,DELETE和UPDATE語句支持LIMIT子句,建表兼容CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP子句,COMOMENT子句,多表更新等能力。
2.1 導入導出測試(mysql導出csv,導入到kes mysql兼容版本)
PS:注意導出的CSV文件,分隔符默認是逗號,需要將逗號換成tab符,不然會當成一個字段處理;字符用雙引號,若是單引號會默認為是數據的一部分
經常維護mysql的DBA來說,簡單的數據導出,還是navicat工具更快,免得再登錄到服務器
直接通過navicat導出csv格式,并上傳到服務器

(1) 在金倉執行導入
test=# \dt
public | cus | table | system
public | customer | table | system
public | t | table | system
# 執行導入(3216行記錄,秒入,超級棒)
test=# load data infile '/data/cus.csv' into table cus;
COPY 3216
test=# select count(1) from cus;
3216

(2) 建表語句不完全兼容的部分


去除之后,再執行返回成功

(3) 導入的CSV若用逗號分隔及導入錯誤的表報錯如下,請欣賞
test=# load data infile '/data/cus.csv' into table mysql.film;
ERROR: relation "mysql.film" does not exist
test=# load data infile '/data/cus.csv' into table cus.film;
ERROR: schema or package "cus" does not exist
test=#
test=# load data infile '/data/cus.csv' into table cus;
WARNING: data truncated for type numeric: "'id','reccode','custname'"
ERROR: missing data for column "reccode"
CONTEXT: COPY cus, line 1: "'id','reccode','custname'"
2.2 多表聯合更新(完美支持)
在金倉數據庫(KingbaseES)的 MySQL 兼容模式下進行多表更新,確實能讓熟悉 MySQL 的你更方便地操作數據
# 更新前查詢
test=# select * from cus e, cus_acc d
test-# WHERE e.id = d.cid
test-# and e.reccode = 'CU202101000019' ;
1441289030321831958 | CU202101000019 | 廣州 | 1447846574276214861 | 1441289030321831958 | 10244230150
1441289030321831958 | CU202101000019 | 廣州 | 1447846574276214862 | 1441289030321831958 | 10244230151
1441289030321831958 | CU202101000019 | 廣州 | 1447846574276214863 | 1441289030321831958 | 1024401100107
1441289030321831958 | CU202101000019 | 廣州 | 1447846574276214864 | 1441289030321831958 | 10244230152
# 多表聯合更新,由于cus 1條數據,cus_acc 4條數據,共計更新5條數據
test=# UPDATE cus e, cus_acc d
test-# SET e.custname = CONCAT('GZ2025_',e.custname) ,
test-# d.code = CONCAT('GZ_',d.code)
test-# WHERE e.id = d.cid
test-# and e.reccode = 'CU202101000019';
UPDATE 5
# 更新前查詢
test=# select * from cus e, cus_acc d
test-# WHERE e.id = d.cid
test-# and e.reccode = 'CU202101000019' ;
1441289030321831958 | CU202101000019 | GZ2025_廣州 | 1447846574276214861 | 1441289030321831958 | GZ_10244230150
1441289030321831958 | CU202101000019 | GZ2025_廣州 | 1447846574276214862 | 1441289030321831958 | GZ_10244230151
1441289030321831958 | CU202101000019 | GZ2025_廣州 | 1447846574276214863 | 1441289030321831958 | GZ_1024401100107
1441289030321831958 | CU202101000019 | GZ2025_廣州 | 1447846574276214864 | 1441289030321831958 | GZ_10244230152
3. 遇見問題
3.1 若是使用root安裝,會報錯
[root@dba236 kbinstall]# ./setup.sh -i silent -f /data/Kingbase/V9/mysql/silent.cfg
Current user is ROOT.
Please re-run the installer as Non-Root user.
3.2 安裝命令錯誤示范
[kingbase@dba236 kbinstall]$ ./setup.sh -i silent -f/data/Kingbase/silent.cfg
Now launch installer...
.Usage: install [-f <path_to_installer_properties_file> | -options]
(to execute the installer)
Where options include:
-? Show this help text
-h Show this help text
-help Show this help text
--help Show this help text
-i [gui | console | silent]
Specify the user interface mode for the installer
-D<name>=<value>
Specify installer properties
-r <path_to_generate_response_file>
Generates response file.
JVM heap size options are only applicable to Installers
-jvmxms <size>
Specify JVM initial heap size.
-jvmxmx <size>
Specify JVM maximum heap size.
The options field may also include the following in case of uninstaller
if it is enabled for Maintenance Mode
-add <feature_name_1> [<feature_name_2 ...]
Add Specified Features
-remove <feature_name_1> [<feature_name_2 ...]
Remove Specified Features
-repair
Repair Installation
-uninstall
Uninstall
Notes:
1. The path to the installer properties file may be either absolute,
or relative to the directory in which the installer resides.
2. If an installer properties file is specified and exists, all other
command line options will be ignored.
3. If a properties file named either 'installer.properties' or
<NameOfInstaller>.properties resides in the same directory as the
installer, it will automatically be used, overriding all other command
line options, unless the '-f' option is used to point to another valid
properties file.
4. If an installer properties file is specified but does not exist, the
default properties file, if present, will be used. Otherwise, any
supplied command line options will be used, or if no additional
options were specified, the installer will be run using the default
settings.
Complete.
參考文檔
金倉官方文檔
https://bbs.kingbase.com.cn/documentGuide?recId=ef7404928de44316a3e72c84e64af712
金倉軟件下載
https://www.kingbase.com.cn/download.html
金倉mysql序列下載
https://www.kingbase.com.cn/download.html#authorization?authorcurrV=V9R3C11%EF%BC%88MySQL%E5%85%BC%E5%AE%B9%E7%89%88%EF%BC%89




