KES 如何开启并进入数据库

关键字：

  KingbaseES、sys_ctl、ksql

1、数据库启动前环境检测
1.1 查看kingbase用户环境变量配置

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

1.2 应用环境变量

1. [kingbase@node1 ~]$ source .bashrc
2. [kingbase@node1 ~]$ which ksql
3. /opt/Kingbase/ES/V8R6_021/Server/bin/ksql
4. [kingbase@node1 ~]$ which sys_ctl
5. /opt/Kingbase/ES/V8R6_021/Server/bin/sys_ctl

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

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

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

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

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

图1-3 kingbase.conf 配置信息

2、启动数据库服务
2.1 查看启动工具(sys_ctl)帮助信息

1. [kingbase@node1 ~]$ sys_ctl --help
2. sys_ctl is a utility to initialize, start, stop, or control a Kingbase server.
4. Usage:
5.   sys_ctl start      [-D DATADIR] [-l FILENAME] [-W] [-t SECS] [-s]
6.                     [-o OPTIONS] [-p PATH] [-c]
7.   sys_ctl stop       [-D DATADIR] [-m SHUTDOWN-MODE] [-W] [-t SECS] [-s]
8.   sys_ctl restart    [-D DATADIR] [-m SHUTDOWN-MODE] [-W] [-t SECS] [-s]
9.                     [-o OPTIONS] [-c]
10.   sys_ctl reload     [-D DATADIR] [-s]
11.   sys_ctl status     [-D DATADIR]
12.   sys_ctl promote    [-D DATADIR] [-W] [-t SECS] [-s]
13.   sys_ctl logrotate  [-D DATADIR] [-s]
14.   sys_ctl kill       SIGNALNAME PID
16. Common options:
17.   -D, --kingbase-data=DATADIR   location of the database storage area
18.   -s, --silent           only print errors, no informational messages
19.   -t, --timeout=SECS     seconds to wait when using -w option
20.   -V, --version          output version information, then exit
21.   -w, --wait             wait until operation completes (default)
22.   -W, --no-wait          do not wait until operation completes
23.   -?, --help             show this help, then exit
24. If the -D option is omitted, the environment variable KINGBASE_DATA is used.
26. Options for start or restart:
27.   -c, --core-files       allow kingbase to produce core files
28.   -l, --log=FILENAME     write (or append) server log to FILENAME
29.   -o, --options=OPTIONS  command line options to pass to kingbase
30.                          (Kingbase server executable) or initdb
31.   -p PATH-TO-KINGBASE    normally not necessary
33. Options for stop or restart:
34.   -m, --mode=MODE        MODE can be "smart", "fast", or "immediate"
36. Shutdown modes are:
37.   smart       quit after all clients have disconnected
38.   fast        quit directly, with proper shutdown (default)
39.   immediate   quit without complete shutdown; will lead to recovery on restart
41. Allowed signal names for kill:
42.   ABRT HUP INT KILL QUIT TERM USR1 USR2
44. Report bugs to <kingbase-bugs@kingbase.com.cn>.

2.2 启动数据库服务
2.2.1 启动数据库服务

1. [kingbase@node1 data]$ sys_ctl start -D /data/kingbase/v8r6_021/data
2. waiting for server to start....2021-03-01 14:16:55.069 CST [26141] LOG:  sepapower extension initialized
3. 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
4. 2021-03-01 14:16:55.130 CST [26141] LOG:  listening on IPv4 address "0.0.0.0", port 54321
5. 2021-03-01 14:16:55.130 CST [26141] LOG:  listening on IPv6 address "::", port 54321
6. 2021-03-01 14:16:55.290 CST [26141] LOG:  listening on Unix socket "/tmp/.s.KINGBASE.54321"
7. 2021-03-01 14:16:55.457 CST [26141] LOG:  redirecting log output to logging collector process
8. 2021-03-01 14:16:55.457 CST [26141] HINT:  Future log output will appear in directory "sys_log".
9. done
10. server started

2.2.2 查看数据库服务进程

1. [kingbase@node1 data]$ ps -ef |grep kingbase
2. /opt/Kingbase/ES/V8R6_021/Server/bin/kingbase -D /data/kingbase/v8r6_021/data
3. kingbase 26142 26141  0 14:16 ?        00:00:00 kingbase: logger
4. kingbase 26144 26141  0 14:16 ?        00:00:00 kingbase: checkpointer
5. kingbase 26145 26141  0 14:16 ?        00:00:00 kingbase: background writer
6. kingbase 26146 26141  0 14:16 ?        00:00:00 kingbase: walwriter
7. kingbase 26147 26141  0 14:16 ?        00:00:00 kingbase: autovacuum launcher
8. kingbase 26148 26141  0 14:16 ?        00:00:00 kingbase: stats collector
9. kingbase 26149 26141  0 14:16 ?        00:00:00 kingbase: ksh writer
10. kingbase 26150 26141  0 14:16 ?        00:00:00 kingbase: ksh collector
11. kingbase 26151 26141  0 14:16 ?        00:00:00 kingbase: kwr collector
12. kingbase 26152 26141  0 14:16 ?        00:00:00 kingbase: logical replication launcher   

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

1. [kingbase@node1 data]$ netstat -an |grep :54321
2. tcp        0      0 0.0.0.0:54321           0.0.0.0:*               LISTEN
3. tcp6       0      0 :::54321                :::*                    LISTEN  
6. ===从数据库服务进程状态及端口状态可知，数据库服务启动成功！===

3、连接数据库服务访问
3.1 数据库连接工具(ksql)帮助信息

1. [kingbase@node1 data]$ ksql --help
2. ksql is the Kingbase interactive terminal.
5. Usage:
6.   ksql [OPTION]... [DBNAME [USERNAME]]
9. General options:
10.   -c, --command=COMMAND    run only single command (SQL or internal) and exit
11.   -d, --dbname=DBNAME      database name to connect to (default: "kingbase")
12.   -f, --file=FILENAME      execute commands from file, then exit
13.   -l, --list               list available databases, then exit
14.   -v, --set=, --variable=NAME=VALUE
15.                            set ksql variable NAME to VALUE
16.                            (e.g., -v ON_ERROR_STOP=1)
17.   -V, --version            output version information, then exit
18.   -X, --no-ksqlrc          do not read startup file (~/.ksqlrc)
19.   -1 ("one"), --single-transaction
20.                            execute as a single transaction (if non-interactive)
21.   -?, --help[=options]     show this help, then exit
22.       --help=commands      list backslash commands, then exit
23.       --help=variables     list special variables, then exit
26. Input and output options:
27.   -a, --echo-all           echo all input from script
28.   -b, --echo-errors        echo failed commands
29.   -e, --echo-queries       echo commands sent to server
30.   -E, --echo-hidden        display queries that internal commands generate
31.   -L, --log-file=FILENAME  send session log to file
32.   -n, --no-readline        disable enhanced command line editing (readline)
33.   -o, --output=FILENAME    send query results to file (or |pipe)
34.   -q, --quiet              run quietly (no messages, only query output)
35.   -s, --single-step        single-step mode (confirm each query)
36.   -S, --single-line        single-line mode (end of line terminates SQL command)
39. Output format options:
40.   -A, --no-align           unaligned table output mode
41.       --csv                CSV (Comma-Separated Values) table output mode
42.   -F, --field-separator=STRING
43.                            field separator for unaligned output (default: "|")
44.   -H, --html               HTML table output mode
45.   -P, --pset=VAR[=ARG]     set printing option VAR to ARG (see \pset command)
46.   -R, --record-separator=STRING
47.                            record separator for unaligned output (default: newline)
48.   -t, --tuples-only        print rows only
49.   -T, --table-attr=TEXT    set HTML table tag attributes (e.g., width, border)
50.   -x, --expanded           turn on expanded table output
51.   -z, --field-separator-zero
52.                            set field separator for unaligned output to zero byte
53.   -0, --record-separator-zero
54.                            set record separator for unaligned output to zero byte
57. Connection options:
58.   -h, --host=HOSTNAME      database server host or socket directory (default: "local socket")
59.   -p, --port=PORT          database server port (default: "54321")
60.   -U, --username=USERNAME  database user name (default: "kingbase")
61.   -w, --no-password        never prompt for password
62.   -W, --password           force password prompt (should happen automatically)
65. For more information, type "\?" (for internal commands) or "\help" (for SQL
66. commands) from within ksql, or consult the ksql section in the Kingbase
67. documentation.
70. Report bugs to <kingbase-bugs@kingbase.com.cn>.

3.2 连接数据库

1. [kingbase@node1 data]$ ksql -U system test
2. ksql (V8.0)
3. Type "help" for help.
6. test=# \l
7.                                List of databases
8.    Name       | Owner  | Encoding |   Collate           |    Ctype          | Access privileges
9. -----------+--------+----------+---------------+-------------+-------------------
10. security     | system | UTF8      | zh_CN.UTF-8 | zh_CN.UTF-8 |
11. template0 | system | UTF8      | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/system        +
12.                   |             |              |                       |                      | system=CTc/system
13. template1 | system | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/system        +
14.                  |             |              |                       |                       | system=CTc/system
15. test           | system | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |
16. (4 rows)

3.3 创建测试数据

1. test=# create database prod;
2. CREATE DATABASE
5. test=# \c prod
6. You are now connected to database "prod" as user "system".
7. prod=# create table t1 (id int ,name varchar(10));
8. CREATE TABLE
9. prod=# insert into t1 values (10,'tom'),(20,'jerry');
10. INSERT 0 2
11. prod=# \d t1
12.                           Table "public.t1"
13. Column |            Type                                  | Collation | Nullable | Default
14. ---------+----------------------------+-----------+----------+---------
15. id          | integer                                |                |                |
16. name    | character varying(10 char) |                |                 |
19. prod=# select * from t1;
20. id | name
21. ----+-------
22. 10 | tom
23. 20 | jerry
24. (2 rows)

4、总结

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