KingbaseES 如何开启并进入数据库

2021-12-11 02:06:09

关键字：

KingbaseES、sys_ctl、ksql

一、数据库启动前环境检测

1.1 查看kingbase用户环境变量配置

图1-1 查看.bashrc环境变量配置

1.2 应用环境变量

[kingbase@node1 ~]$ source .bashrc

[kingbase@node1 ~]$ which ksql

/opt/Kingbase/ES/V8R6_021/Server/bin/ksql

[kingbase@node1 ~]$ which sys_ctl

/opt/Kingbase/ES/V8R6_021/Server/bin/sys_ctl

1.3 查看数据库数据存储路径

在数据库软件包安装目录的Logs目录下查看相关的日志，可以获取到数据库数据存储路径，如下图1-2所示：

图1-2 查看数据库数据存储路径

1.4 查看数据库配置文件信息

根据实际的业务场景，调整数据库的默认配置。

图1-3 kingbase.conf 配置信息

二、启动数据库服务

2.1 查看启动工具(sys_ctl)帮助信息

[kingbase@node1 ~]$ sys_ctl --help

sys_ctl is a utility to initialize, start, stop, or control a Kingbase server.

Usage:

sys_ctl start [-D DATADIR] [-l FILENAME] [-W] [-t SECS] [-s]

[-o OPTIONS] [-p PATH] [-c]

sys_ctl stop [-D DATADIR] [-m SHUTDOWN-MODE] [-W] [-t SECS] [-s]

sys_ctl restart [-D DATADIR] [-m SHUTDOWN-MODE] [-W] [-t SECS] [-s]

[-o OPTIONS] [-c]

sys_ctl reload [-D DATADIR] [-s]

sys_ctl status [-D DATADIR]

sys_ctl promote [-D DATADIR] [-W] [-t SECS] [-s]

sys_ctl logrotate [-D DATADIR] [-s]

sys_ctl kill SIGNALNAME PID

Common options:

-D, --kingbase-data=DATADIR location of the database storage area

-s, --silent only print errors, no informational messages

-t, --timeout=SECS seconds to wait when using -w option

-V, --version output version information, then exit

-w, --wait wait until operation completes (default)

-W, --no-wait do not wait until operation completes

-?, --help show this help, then exit

If the -D option is omitted, the environment variable KINGBASE_DATA is used.

Options for start or restart:

-c, --core-files allow kingbase to produce core files

-l, --log=FILENAME write (or append) server log to FILENAME

-o, --options=OPTIONS command line options to pass to kingbase

(Kingbase server executable) or initdb

-p PATH-TO-KINGBASE normally not necessary

Options for stop or restart:

-m, --mode=MODE MODE can be "smart", "fast", or "immediate"

Shutdown modes are:

smart quit after all clients have disconnected

fast quit directly, with proper shutdown (default)

immediate quit without complete shutdown; will lead to recovery on restart

Allowed signal names for kill:

ABRT HUP INT KILL QUIT TERM USR1 USR2

Report bugs to <kingbase-bugs@kingbase.com.cn>.

2.2 启动数据库服务

2.2.1 启动数据库服务

[kingbase@node1 data]$ sys_ctl start -D /data/kingbase/v8r6_021/data

waiting for server to start....2021-03-01 14:16:55.069 CST [26141] LOG: sepapower extension initialized

2021-03-01 14:16:55.130 CST [26141] LOG: starting KingbaseES V008R006C004B0021 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-46), 64-bit

2021-03-01 14:16:55.130 CST [26141] LOG: listening on IPv4 address "0.0.0.0", port 54321

2021-03-01 14:16:55.130 CST [26141] LOG: listening on IPv6 address "::", port 54321

2021-03-01 14:16:55.290 CST [26141] LOG: listening on Unix socket "/tmp/.s.KINGBASE.54321"

2021-03-01 14:16:55.457 CST [26141] LOG: redirecting log output to logging collector process

2021-03-01 14:16:55.457 CST [26141] HINT: Future log output will appear in directory "sys_log".

done

server started

2.2.2 查看数据库服务进程

[kingbase@node1 data]$ ps -ef |grep kingbase

/opt/Kingbase/ES/V8R6_021/Server/bin/kingbase -D /data/kingbase/v8r6_021/data

kingbase 26142 26141 0 14:16 ? 00:00:00 kingbase: logger

kingbase 26144 26141 0 14:16 ? 00:00:00 kingbase: checkpointer

kingbase 26145 26141 0 14:16 ? 00:00:00 kingbase: background writer

kingbase 26146 26141 0 14:16 ? 00:00:00 kingbase: walwriter

kingbase 26147 26141 0 14:16 ? 00:00:00 kingbase: autovacuum launcher

kingbase 26148 26141 0 14:16 ? 00:00:00 kingbase: stats collector

kingbase 26149 26141 0 14:16 ? 00:00:00 kingbase: ksh writer

kingbase 26150 26141 0 14:16 ? 00:00:00 kingbase: ksh collector

kingbase 26151 26141 0 14:16 ? 00:00:00 kingbase: kwr collector

kingbase 26152 26141 0 14:16 ? 00:00:00 kingbase: logical replication launcher

2.2.3 查看数据库服务端口状态

[kingbase@node1 data]$ netstat -an |grep :54321

tcp 0 0 0.0.0.0:54321 0.0.0.0:* LISTEN

tcp6 0 0 :::54321 :::* LISTEN

===从数据库服务进程状态及端口状态可知，数据库服务启动成功！===

三、连接数据库服务访问

3.1 数据库连接工具(ksql)帮助信息

[kingbase@node1 data]$ ksql --help

ksql is the Kingbase interactive terminal.

Usage:

ksql [OPTION]... [DBNAME [USERNAME]]

General options:

-c, --command=COMMAND run only single command (SQL or internal) and exit

-d, --dbname=DBNAME database name to connect to (default: "kingbase")

-f, --file=FILENAME execute commands from file, then exit

-l, --list list available databases, then exit

-v, --set=, --variable=NAME=VALUE

set ksql variable NAME to VALUE

(e.g., -v ON_ERROR_STOP=1)

-V, --version output version information, then exit

-X, --no-ksqlrc do not read startup file (~/.ksqlrc)

-1 ("one"), --single-transaction

execute as a single transaction (if non-interactive)

-?, --help[=options] show this help, then exit

--help=commands list backslash commands, then exit

--help=variables list special variables, then exit

Input and output options:

-a, --echo-all echo all input from script

-b, --echo-errors echo failed commands

-e, --echo-queries echo commands sent to server

-E, --echo-hidden display queries that internal commands generate

-L, --log-file=FILENAME send session log to file

-n, --no-readline disable enhanced command line editing (readline)

-o, --output=FILENAME send query results to file (or |pipe)

-q, --quiet run quietly (no messages, only query output)

-s, --single-step single-step mode (confirm each query)

-S, --single-line single-line mode (end of line terminates SQL command)

Output format options:

-A, --no-align unaligned table output mode

--csv CSV (Comma-Separated Values) table output mode

-F, --field-separator=STRING

field separator for unaligned output (default: "|")

-H, --html HTML table output mode

-P, --pset=VAR[=ARG] set printing option VAR to ARG (see \pset command)

-R, --record-separator=STRING

record separator for unaligned output (default: newline)

-t, --tuples-only print rows only

-T, --table-attr=TEXT set HTML table tag attributes (e.g., width, border)

-x, --expanded turn on expanded table output

-z, --field-separator-zero

set field separator for unaligned output to zero byte

-0, --record-separator-zero

set record separator for unaligned output to zero byte

Connection options:

-h, --host=HOSTNAME database server host or socket directory (default: "local socket")

-p, --port=PORT database server port (default: "54321")

-U, --username=USERNAME database user name (default: "kingbase")

-w, --no-password never prompt for password

-W, --password force password prompt (should happen automatically)

For more information, type "\?" (for internal commands) or "\help" (for SQL

commands) from within ksql, or consult the ksql section in the Kingbase

documentation.

Report bugs to <kingbase-bugs@kingbase.com.cn>.

3.2 连接数据库

[kingbase@node1 data]$ ksql -U system test

ksql (V8.0)

Type "help" for help.

test=# \l

List of databases

-----------+--------+----------+-------------+-------------+-------------------

(4 rows)

3.3 创建测试数据

test=# create database prod;

CREATE DATABASE

test=# \c prod

You are now connected to database "prod" as user "system".

prod=# create table t1 (id int ,name varchar(10));

CREATE TABLE

prod=# insert into t1 values (10,'tom'),(20,'jerry');

INSERT 0 2

prod=# \d t1

Table "public.t1"

Column | Type | Collation | Nullable | Default

--------+----------------------------+-----------+----------+---------

id | integer | | |

name | character varying(10 char) | | |

prod=# select * from t1;

id | name

----+-------

10 | tom

20 | jerry

(2 rows)

四、总结

KingbaseES数据库服务启动比较简单，对于Linux系统可以通过sys_ctl工具启动数据库服务；对于Windows环境可以通过Windows服务启动，也可以通过Command界面通过sys_ctl启动。

参考文档：

《[安装与升级]基于Linux系统的数据库软件安装指南（单机版）》

码农公寓