通过 OBD Demo 体验 OceanBase 4.3 社区版

本文作者：马顺华

1718185148

引言

OceanBase 4.3 是一个专为实时分析 AP 业务设计的重大更新版本。它基于LSM-Tree架构，引入了列存引擎，实现了行存与列存数据存储的无缝整合。这一版本不仅显著提升了AP场景的查询性能，同时也确保了TP业务场景的高效运行，使其适用于包括复杂分析、实时报表、实时数仓以及联机交易等在内的混合负载场景。

TP & AP一体化产品形态：OceanBase 4.3 实现了行存和列存数据的完美共存，使得TP（联机交易）和AP（实时分析）业务能够同时在一个系统中运行，大大提高了系统的灵活性和效率。
全面增强的内核特性：该版本引入了列存引擎、新版向量化引擎和基于列存的代价模型，这些技术的引入使得OceanBase在处理大宽表等复杂场景时具有更高的性能。
显著增强的计算性能：OceanBase 4.3 在同等硬件环境下，大宽表场景下的查询性能达到了业内主流列存大宽表数据库的水平。
持续提升的易用性：新版本内核扩展了 Online DDL、支持了租户克隆等功能，优化性能和资源使用，提升了系统易用性。

背景信息

OceanBase 数据库自 V4.0.0 版本起，提供了一站式的 all-in-one 安装包。这个安装包包含了 OBD、OceanBase 数据库、ODP、OBAgent、Grafana、Prometheus 等组件，自 V4.1.0 版本起，还新增支持了 OCP Express 的安装。您可以根据实际需求选择安装所需组件。想要快速上手体验 OceanBase 社区版吗？本文将指导您通过三种部署方案——演示环境、集群环境和容器环境，轻松体验 OceanBase 的强大功能。请注意，以下部署方案仅适用于测试环境，不适用于生产环境。

前提条件

在开始部署之前，请确保您的软硬件环境满足 OceanBase 的基本要求。以下指南以 x86 架构的 CentOS Linux 7.4 为例，其他环境请参照官方文档进行相应调整。

项目	描述
系统	CentOS Linux 7.X 版本
CPU	最低要求 2 核，推荐 4 核及以上。
内存	最低要求 8 GB，推荐设置在 16 GB 至 1024 GB 范围内。
磁盘类型	使用 SSD 存储。
磁盘存储空间	最低要求 19 GB。
文件系统	EXT4 戓 XFS，当数据超过 16T 时，使用 XFS。
all-in-one 安装包	all-in-one 安装包需选择 V4.1.0 及以上版本。
Docker	使用 Docker 部署 OceanBase 数据库时需提前安装 Docker 并启动 Docker 服务，详细操作请参考 Docker 文档。

以下内容以 x86 架构的 CentOS Linux 7.4 镜像作为环境，其他环境可能略有不同。

[root@worker2 ~]# cat /etc/redhat-release 
CentOS Linux release 7.4.1708 (Core) 
[root@worker2 ~]# free -htotal        used        free      shared  buff/cache   available
Mem:            27G        3.5G        552M         15G         23G        8.3G
Swap:          8.0G          0B        8.0G
[root@worker2 ~]# df -Th
Filesystem              Type      Size  Used Avail Use% Mounted on
/dev/mapper/centos-root ext4       91G   49G   38G  57% /
devtmpfs                devtmpfs   14G     0   14G   0% /dev
tmpfs                   tmpfs      14G     0   14G   0% /dev/shm
tmpfs                   tmpfs      14G  1.4G   13G  11% /run
tmpfs                   tmpfs      14G     0   14G   0% /sys/fs/cgroup
/dev/sda2               ext4      190M  141M   35M  81% /boot
/dev/sdb1               xfs       200G   10G  190G   5% /data

1718184241

一、准备 OceanBase 部署环境

当您仅拥有一台可用机器时，您可参考本节内容使用 obd demo 命令快速部署单机 OceanBase 数据库。

1、下载并安装 all-in-one 安装包

请从 OceanBase 软件下载中心下载最新版本 all-in-one 安装包，并将其上传到机器任一目录下。

https://www.oceanbase.com/softwarecenter

1718184251

2、上传安装包

[root@worker2 ob4-3]# ls
oceanbase-all-in-one-4.3.1.0-100000032024051615.el7.x86_64.tar.gz
[root@worker2 ob4-3]#

1718184266

3、解压安装包

在安装包所在目录下执行如下命令解压安装包并安装。

[root@worker2 ob4-3]# ls
oceanbase-all-in-one-4.3.1.0-100000032024051615.el7.x86_64.tar.gz
[root@worker2 ob4-3]# tar -xzf oceanbase-all-in-one-4.3.1.0-100000032024051615.el7.x86_64.tar.gz 
[root@worker2 ob4-3]# ls
oceanbase-all-in-one  oceanbase-all-in-one-4.3.1.0-100000032024051615.el7.x86_64.tar.gz
[root@worker2 ob4-3]# cd oceanbase-all-in-one/
[root@worker2 oceanbase-all-in-one]# ls
bin  obclient  obd  README.md  rpms  VERSION
[root@worker2 oceanbase-all-in-one]#

1718184276

4、安装 all-in-one

[root@worker2 oceanbase-all-in-one]# ls
bin  obclient  obd  README.md  rpms  VERSION
[root@worker2 oceanbase-all-in-one]# cd bin/
[root@worker2 bin]# ls
env.sh  install.sh  uninstall.sh
[root@worker2 bin]# ./install.sh 
name: grafana
version: 7.5.17
release:1
arch: x86_64
md5: 1bf1f338d3a3445d8599dc6902e7aeed4de4e0d6
size: 177766248
add /opt/ob4-3/oceanbase-all-in-one/rpms/grafana-7.5.17-1.el7.x86_64.rpm to local mirror
name: obagent
version: 4.2.2
release:100000042024011120.el7
arch: x86_64
md5: 19739a07a12eab736aff86ecf357b1ae660b554e
size: 72919140
add /opt/ob4-3/oceanbase-all-in-one/rpms/obagent-4.2.2-100000042024011120.el7.x86_64.rpm to local mirror
name: ob-configserver
version: 1.0.0
release:2.el7
arch: x86_64
md5: feca6b9c76e26ac49464f34bfa0780b5a8d3f4a0
size: 24259515
add /opt/ob4-3/oceanbase-all-in-one/rpms/ob-configserver-1.0.0-2.el7.x86_64.rpm to local mirror
name: ob-deploy
version: 2.8.0
release:4.el7
arch: x86_64
md5: cd73c47e348ccdb71455b1a671e95f77ef818e0c
size: 154417687
add /opt/ob4-3/oceanbase-all-in-one/rpms/ob-deploy-2.8.0-4.el7.x86_64.rpm to local mirror
name: obproxy-ce
version: 4.2.3.0
release:3.el7
arch: x86_64
md5: 0490ebc04220def8d25cb9cac9ac61a4efa6d639
size: 688373235
add /opt/ob4-3/oceanbase-all-in-one/rpms/obproxy-ce-4.2.3.0-3.el7.x86_64.rpm to local mirror
name: oceanbase-ce
version: 4.3.1.0
release:100000032024051615.el7
arch: x86_64
md5: f3cd399aa780d23fbb60faed68e32a7dbd4e6a3c
size: 589267409
add /opt/ob4-3/oceanbase-all-in-one/rpms/oceanbase-ce-4.3.1.0-100000032024051615.el7.x86_64.rpm to local mirror
name: oceanbase-ce-libs
version: 4.3.1.0
release:100000032024051615.el7
arch: x86_64
md5: 68f0b5f988bd5fb80d44ac29afad0c2b2f2d3763
size: 468528
add /opt/ob4-3/oceanbase-all-in-one/rpms/oceanbase-ce-libs-4.3.1.0-100000032024051615.el7.x86_64.rpm to local mirror
name: oceanbase-diagnostic-tool
version: 2.0.0
release:32024041111.el7
arch: x86_64
md5: 5deeab8e3359ebf933898136090f72ebb3729f8a
size: 36010369
add /opt/ob4-3/oceanbase-all-in-one/rpms/oceanbase-diagnostic-tool-2.0.0-32024041111.el7.x86_64.rpm to local mirror
name: ocp-express
version: 4.2.2
release:100000022024011120.el7
arch: x86_64
md5: 09ffcf156d1df9318a78af52656f499d2315e3f7
size: 78426196
add /opt/ob4-3/oceanbase-all-in-one/rpms/ocp-express-4.2.2-100000022024011120.el7.x86_64.rpm to local mirror
name: openjdk-jre
version: 1.8.0_322
release:b09.el7
arch: x86_64
md5: 051aa69c5abb8697d15c2f0dcb1392b3f815f7ed
size: 69766947
add /opt/ob4-3/oceanbase-all-in-one/rpms/openjdk-jre-1.8.0_322-b09.el7.x86_64.rpm to local mirror
name: prometheus
version: 2.37.1
release:10000102022110211.el7
arch: x86_64
md5: 58913c7606f05feb01bc1c6410346e5fc31cf263
size: 211224073
add /opt/ob4-3/oceanbase-all-in-one/rpms/prometheus-2.37.1-10000102022110211.el7.x86_64.rpm to local mirror
Trace ID: 7cd5ad98-24a7-11ef-b503-005056818c51
If you want to view detailed obd logs, please run: obd display-trace 7cd5ad98-24a7-11ef-b503-005056818c51
Disable remote ok
Trace ID: 7dcb601c-24a7-11ef-a5bd-005056818c51
If you want to view detailed obd logs, please run: obd display-trace 7dcb601c-24a7-11ef-a5bd-005056818c51add auto set env logic to profile: /root/.bash_profile#########################################################################################Install Finished 
=========================================================================================
Setup Environment:              source ~/.oceanbase-all-in-one/bin/env.sh 
Quick Start:                    obd demo 
Use Web Service to install:     obd web 
Use Web Service to upgrade:     obd web upgrade 
More Details:                   obd -h 
=========================================================================================
[root@worker2 bin]#

1718184295

1718184300

[root@worker2 bin]# 
[root@worker2 bin]# source ~/.oceanbase-all-in-one/bin/env.sh
[root@worker2 bin]#

1718184313

二、单机部署 OceanBase 数据库

执行 obd demo 命令，OceanBase 将默认在当前用户家目录下以最小规格部署并启动数据库及相关组件，部署名为 demo。如有定制化需求，请参考官方文档中的快速部署命令。

1、执行 obd demo 部署 OceanBase 数据库

obd demo

[root@worker2 ~]# obd demo
Found a higher version
name: oceanbase-ce
version: 4.3.1.0
release:100000032024051615.el7
arch: x86_64
md5: f3cd399aa780d23fbb60faed68e32a7dbd4e6a3c
size: 589267409
Do you want to use it? [y/n]: y
Package oceanbase-ce-4.3.1.0-100000032024051615.el7 is available.
Found a higher version
name: obproxy-ce
version: 4.2.3.0
release:3.el7
arch: x86_64
md5: 0490ebc04220def8d25cb9cac9ac61a4efa6d639
size: 688373235
Do you want to use it? [y/n]: y
Package obproxy-ce-4.2.3.0-3.el7 is available.
Found a higher version
name: obagent
version: 4.2.2
release:100000042024011120.el7
arch: x86_64
md5: 19739a07a12eab736aff86ecf357b1ae660b554e
size: 72919140
Do you want to use it? [y/n]: y
Package obagent-4.2.2-100000042024011120.el7 is available.
install oceanbase-ce-4.3.1.0 for local ok
install obproxy-ce-4.2.3.0 for local ok
install grafana-7.5.17 for local ok
install prometheus-2.37.1 for local ok
install obagent-4.2.2 for local ok
Cluster param config check ok
Open ssh connection ok
Generate obagent configuration ok
Generate prometheus configuration ok
Generate grafana configuration ok
Generate obproxy configuration ok
Generate observer configuration ok
+--------------------------------------------------------------------------------------------+
|                                          Packages                                          |
+--------------+---------+------------------------+------------------------------------------+
| Repository   | Version | Release                | Md5                                      |
+--------------+---------+------------------------+------------------------------------------+
| oceanbase-ce | 4.3.1.0 | 100000032024051615.el7 | f3cd399aa780d23fbb60faed68e32a7dbd4e6a3c |
| obproxy-ce   | 4.2.3.0 | 3.el7                  | 0490ebc04220def8d25cb9cac9ac61a4efa6d639 |
| grafana      | 7.5.17  | 1                      | 1bf1f338d3a3445d8599dc6902e7aeed4de4e0d6 |
| prometheus   | 2.37.1  | 10000102022110211.el7  | 58913c7606f05feb01bc1c6410346e5fc31cf263 |
| obagent      | 4.2.2   | 100000042024011120.el7 | 19739a07a12eab736aff86ecf357b1ae660b554e |
+--------------+---------+------------------------+------------------------------------------+
Repository integrity check ok
Load param plugin ok
Open ssh connection ok
Parameter check ok
Cluster status check ok
Initializes observer work home ok
Initializes obproxy work home ok
Initializes grafana work home ok
Initializes prometheus work home ok
Initializes obagent work home ok
Remote oceanbase-ce-4.3.1.0-100000032024051615.el7-f3cd399aa780d23fbb60faed68e32a7dbd4e6a3c repository install ok
Remote oceanbase-ce-4.3.1.0-100000032024051615.el7-f3cd399aa780d23fbb60faed68e32a7dbd4e6a3c repository lib check !!
Remote obproxy-ce-4.2.3.0-3.el7-0490ebc04220def8d25cb9cac9ac61a4efa6d639 repository install ok
Remote obproxy-ce-4.2.3.0-3.el7-0490ebc04220def8d25cb9cac9ac61a4efa6d639 repository lib check ok
Remote grafana-7.5.17-1-1bf1f338d3a3445d8599dc6902e7aeed4de4e0d6 repository install ok
Remote grafana-7.5.17-1-1bf1f338d3a3445d8599dc6902e7aeed4de4e0d6 repository lib check ok
Remote prometheus-2.37.1-10000102022110211.el7-58913c7606f05feb01bc1c6410346e5fc31cf263 repository install ok
Remote prometheus-2.37.1-10000102022110211.el7-58913c7606f05feb01bc1c6410346e5fc31cf263 repository lib check ok
Remote obagent-4.2.2-100000042024011120.el7-19739a07a12eab736aff86ecf357b1ae660b554e repository install ok
Remote obagent-4.2.2-100000042024011120.el7-19739a07a12eab736aff86ecf357b1ae660b554e repository lib check ok
Try to get lib-repository
Package oceanbase-ce-libs-4.3.1.0-100000032024051615.el7 is available.
install oceanbase-ce-libs-4.3.1.0 for local ok
Remote oceanbase-ce-libs-4.3.1.0-100000032024051615.el7-68f0b5f988bd5fb80d44ac29afad0c2b2f2d3763 repository install ok
Remote oceanbase-ce-4.3.1.0-100000032024051615.el7-f3cd399aa780d23fbb60faed68e32a7dbd4e6a3c repository lib check ok
demo deployed
Get local repositories ok
Search plugins ok
Load cluster param plugin ok
Open ssh connection ok
Check before start observer ok
[WARN] OBD-1007: (127.0.0.1) The recommended number of stack size is unlimited (Current value: 20480)
[WARN] OBD-1017: (127.0.0.1) The value of the "fs.file-max" must be greater than 6573688 (Current value: 655350, Recommended value: 6573688)
[WARN] OBD-2000: (127.0.0.1) not enough memory. (Free: 263M, Need: 6144M)
[WARN] OBD-1012: (127.0.0.1) clog and data use the same disk (/)Check before start obproxy ok
Check before start obagent ok
Check before start prometheus ok
Check before start grafana ok
cluster scenario: express_oltp
Start observer ok
observer program health check ok
Connect to observer 127.0.0.1:2881 ok
Initialize oceanbase-ce ok
Start obproxy ok
obproxy program health check ok
Connect to obproxy ok
Initialize obproxy-ce ok
Start obagent ok
obagent program health check ok
Connect to Obagent ok
Start promethues ok
prometheus program health check ok
Connect to Prometheus ok
Initialize prometheus ok
Start grafana ok
grafana program health check ok
Connect to grafana ok
Initialize grafana ok
Wait for observer init ok
+---------------------------------------------+
|                   observer                  |
+-----------+---------+------+-------+--------+
| ip        | version | port | zone  | status |
+-----------+---------+------+-------+--------+
| 127.0.0.1 | 4.3.1.0 | 2881 | zone1 | ACTIVE |
+-----------+---------+------+-------+--------+
obclient -h127.0.0.1 -P2881 -uroot -Doceanbase -Acluster unique id: ba0792c2-f3a5-5f3f-a1c5-c969744780fd-18ff2acdab4-00010304+---------------------------------------------+
|                   obproxy                   |
+-----------+------+-----------------+--------+
| ip        | port | prometheus_port | status |
+-----------+------+-----------------+--------+
| 127.0.0.1 | 2883 | 2884            | active |
+-----------+------+-----------------+--------+
obclient -h127.0.0.1 -P2883 -uroot -Doceanbase -A +-----------------------------------------------------------------+
|                             obagent                             |
+--------------+--------------------+--------------------+--------+
| ip           | mgragent_http_port | monagent_http_port | status |
+--------------+--------------------+--------------------+--------+
| 127.0.0.1    | 8089               | 8088               | active |
+--------------+--------------------+--------------------+--------+
+-----------------------------------------------------+
|                      prometheus                     |
+--------------------------+------+----------+--------+
| url                      | user | password | status |
+--------------------------+------+----------+--------+
| http://127.0.0.1:9090    |      |          | active |
+--------------------------+------+----------+--------+
+--------------------------------------------------------------------+
|                              grafana                               |
+--------------------------------------+-------+------------+--------+
| url                                  | user  | password   | status |
+--------------------------------------+-------+------------+--------+
| http://127.0.0.1:3000/d/oceanbase    | admin | VlxvOiQdfw | active |
+--------------------------------------+-------+------------+--------+
demo running
Trace ID: 3cc9e300-24c9-11ef-9f40-0050568197df
If you want to view detailed obd logs, please run: obd display-trace 3cc9e300-24c9-11ef-9f40-0050568197df

1718184327

1718184333

obd demo 命令默认在当前用户家目录下以最小规格部署并启动 OceanBase 数据库及相关组件（包括 ODP、OBAgent、Grafana 和 Prometheus），固定部署名为 demo。如需更多定制化的部署形式，参见快速部署命令。

2、查看部署状态

[root@worker2 ~]# obd cluster list
+----------------------------------------------------------+
|                       Cluster List                       |
+----------+-----------------------------+-----------------+
| Name     | Configuration Path          | Status (Cached) |
+----------+-----------------------------+-----------------+
| demo     | /root/.obd/cluster/demo     | running         |
| ob41test | /root/.obd/cluster/ob41test | destroyed       |
+----------+-----------------------------+-----------------+
Trace ID: d69691f8-2592-11ef-a9b9-0050568197df
If you want to view detailed obd logs, please run: obd display-trace d69691f8-2592-11ef-a9b9-0050568197df
[root@worker2 ~]#

1718184344

状态 running 为运行正常您也可以使用 OBD 命令管理该 OceanBase 数据库，详细命令介绍请参考集群命令组。

三、访问与配置

当安装 grafana 或 prometheus 时，会输出 grafana 或 prometheus 的访问地址。在阿里云或其他云环境下，可能出现因无法获取公网 IP 而输出内网地址的情况，此 IP 非公网地址，您需要使用正确的地址。

部署成功后，根据输出信息中的连接命令，使用 OBClient 客户端连接到 OceanBase 数据库。执行输出中的连接命令连接数据库

obd demo 命令成功执行后会输出通过 OBClient 连接 OceanBase 数据库的命令，示例如下。

1、通过 2881 端口直连数据库

[root@worker2 ~]# obclient -h127.0.0.1 -P2881 -uroot@sys -Doceanbase -A
Welcome to the OceanBase.  Commands end with ; or \g.
Your OceanBase connection id is 3221487833
Server version: OceanBase_CE 4.3.1.0 (r100000032024051615-bad90e897a7f0f56b8ce5c43e186aa8f4bab03c4) (Built May 16 2024 17:21:43)Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.obclient [oceanbase]>

1718184353

2、通过 ODP 代理访问数据库

[root@worker2 ~]# obclient -h127.0.0.1 -P2883 -uroot@sys -Doceanbase -A
Welcome to the OceanBase.  Commands end with ; or \g.
Your OceanBase connection id is 1251475458
Server version: OceanBase_CE 4.3.1.0 (r100000032024051615-bad90e897a7f0f56b8ce5c43e186aa8f4bab03c4) (Built May 16 2024 17:21:43)Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.obclient [oceanbase]>

1718184361

使用 OBClient 客户端连接 OceanBase 集群的详细操作可参见通过 OBClient 连接 OceanBase 租户。连接 OceanBase 数据库更多方法请参见连接方式概述。

3、（可选）配置密码

使用 obd demo 命令快速部署 OceanBase 数据库后，您可参考如下步骤为 demo 集群配置密码。

修改配置文件obd cluster edit-config demo执行上述命令打开配置文件后，在配置文件中 oceanbase-ce（社区版）/oceanbase（企业版）组件下添加 root_password: xxxx，添加完成后保存退出。示例如下：

[root@worker2 ~]# obd cluster edit-config demo
Search param plugin and load okoceanbase-ce:servers:- 127.0.0.1global:home_path: /root/oceanbase-ceappname: demoroot_password: ******cluster_id: 1717763299scenario: express_oltpenable_syslog_recycle: trueenable_syslog_wf: falsemax_syslog_file_count: 4memory_limit: 6144Mproduction_mode: false__min_full_resource_pool_memory: 1073741824system_memory: 1024Mcpu_count: 16datafile_size: 2048Mdatafile_maxsize: 8192Mdatafile_next: 2048Mlog_disk_size: 14336M
obproxy-ce:servers:- 127.0.0.1global:home_path: /root/obproxy-ceskip_proxy_sys_private_check: trueenable_strict_kernel_release: falseenable_cluster_checkout: falseproxy_mem_limited: 500M127.0.0.1:proxy_id: 4774client_session_id_version: 2depends:- oceanbase-ce
grafana:servers:- 127.0.0.1global:home_path: /root/grafanalogin_password: VlxvOiQdfwdepends:- prometheus
prometheus:servers:- 127.0.0.1global:home_path: /root/prometheusdepends:- obagent
obagent:servers:- 127.0.0.1global:home_path: /root/obagentob_monitor_status: activedepends:- oceanbase-ce

1718184373

4、重启集群

修改并保存配置文件后，OBD 会输出待执行的重启命令，直接复制执行即可，示例如下。

[root@worker2 ~]# obd cluster edit-config demo
Search param plugin and load ok
Search param plugin and load ok
Parameter check ok
Save deploy "demo" configuration
Use `obd cluster reload demo` to make changes take effect.
Trace ID: 8cb3948c-24ca-11ef-9f19-0050568197df
If you want to view detailed obd logs, please run: obd display-trace 8cb3948c-24ca-11ef-9f19-0050568197df
[root@worker2 ~]#

1718184384

从输出可以看出，修改配置文件中 root@sys 用户密码后，需执行 obd cluster reload demo 命令重启 demo 集群。

[root@worker2 ~]# obd cluster reload demo
Get local repositories and plugins ok
Load cluster param plugin ok
Open ssh connection ok
Cluster status check ok
Connect to observer 127.0.0.1:2881 ok
Reload observer ok
Connect to obproxy ok
Connect to Obagent ok
Reload obagent ok
Connect to Prometheus ok
Reload prometheus ok
Connect to grafana ok
Reload Grafana ok
demo reload
Trace ID: 50b9c09a-24cb-11ef-8401-0050568197df
If you want to view detailed obd logs, please run: obd display-trace 50b9c09a-24cb-11ef-8401-0050568197df
[root@worker2 ~]#

1718184394

说明：您也可使用 root 用户登录数据库的 sys 租户后，通过 ALTER USER 命令修改用户密码。详细操作可参见 ALTER USER。

5、测试带密码登录

[root@worker2 ~]# obclient -h127.0.0.1 -P2883 -uroot@sys -Doceanbase -A
ERROR 1045 (42000): Access denied for user 'root'@'xxx.xxx.xxx.xxx' (using password: NO)
[root@worker2 ~]# obclient -h127.0.0.1 -P2883 -uroot@sys -Doceanbase -A -p
Enter password: 
Welcome to the OceanBase.  Commands end with ; or \g.
Your OceanBase connection id is 1251475463
Server version: OceanBase_CE 4.3.1.0 (r100000032024051615-bad90e897a7f0f56b8ce5c43e186aa8f4bab03c4) (Built May 16 2024 17:21:43)Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.obclient [oceanbase]>

1718184415

不带密码登录已经报错了，-p加上密码后正常登录。

四、SQL基础操作（MySQL 模式）的增删改查

本节主要介绍 OceanBase 数据库 MySQL 模式下的一些 SQL 基本操作。

1、创建数据库

使用 CREATE DATABASE 语句创建数据库。示例：创建数据库 obdb1，指定字符集为 utf8mb4，并创建读写属性。


obclient [oceanbase]> 
obclient [oceanbase]> CREATE DATABASE obdb1 DEFAULT CHARACTER SET utf8mb4 READ WRITE;
Query OK, 1 row affected (0.060 sec)obclient [oceanbase]>

1718184433

更多 CREATE DATABASE 语句相关的语法说明，请参见 CREATE DATABASE 章节。

1.1查看数据库

创建完成后，可以通过 SHOW DATABASES 命令查看当前数据库服务器中所有的数据库。

obclient [oceanbase]> 
obclient [oceanbase]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| LBACSYS            |
| mysql              |
| obdb1              |
| oceanbase          |
| ocs                |
| ORAAUDITOR         |
| SYS                |
| test               |
+--------------------+
9 rows in set (0.004 sec)obclient [oceanbase]>

1718184444

2、创建表

使用 CREATE TABLE 语句在数据库中创建新表。

示例：在数据库 oddb1 中创建表 test。

obclient [oceanbase]> 
obclient [oceanbase]> USE obdb1;
Database changed
obclient [obdb1]> CREATE TABLE obtest (c1 INT PRIMARY KEY, c2 VARCHAR(3));
Query OK, 0 rows affected (0.156 sec)obclient [obdb1]>

1718184457

更多 CREATE TABLE 语句相关的语法说明，请参见 CREATE TABLE 章节。

2.1 查看表

使用 SHOW CREATE TABLE 语句查看建表语句。

示例：

2.2 查看表 obtest 的建表语句。

obclient [obdb1]> 
obclient [obdb1]> SHOW CREATE TABLE obtest\G
*************************** 1. row ***************************Table: obtest
Create Table: CREATE TABLE `obtest` (`c1` int(11) NOT NULL,`c2` varchar(3) DEFAULT NULL,PRIMARY KEY (`c1`)
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
1 row in set (0.032 sec)obclient [obdb1]>

1718184474

2.3 使用 SHOW TABLES 语句查看 db1 数据库中的所有表。

obclient [obdb1]> SHOW TABLES FROM obdb1;
+-----------------+
| Tables_in_obdb1 |
+-----------------+
| obtest          |
+-----------------+
1 row in set (0.005 sec)obclient [obdb1]>

1718184482

3、修改表

使用 ALTER TABLE 语句来修改已存在的表的结构，包括修改表及表属性、新增列、修改列及属性、删除列等。

示例：

3.1 将表 test 的字段 c2 改名为 c3，并同时修改其字段类型。

obclient [obdb1]> DESCRIBE obtest;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| c1    | int(11)    | NO   | PRI | NULL    |       |
| c2    | varchar(3) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.005 sec)obclient [obdb1]> ALTER TABLE obtest CHANGE COLUMN c2 c3 CHAR(10);
Query OK, 0 rows affected (0.681 sec)obclient [obdb1]> DESCRIBE obtest;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| c1    | int(11)  | NO   | PRI | NULL    |       |
| c3    | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.027 sec)obclient [obdb1]>

1718184492

3.2 在表 obtest 中增加、删除列。

obclient [obdb1]> 
obclient [obdb1]> DESCRIBE obtest;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| c1    | int(11)  | NO   | PRI | NULL    |       |
| c3    | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.004 sec)obclient [obdb1]> ALTER TABLE obtest ADD c4 int;
Query OK, 0 rows affected (0.113 sec)obclient [obdb1]> DESCRIBE obtest;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| c1    | int(11)  | NO   | PRI | NULL    |       |
| c3    | char(10) | YES  |     | NULL    |       |
| c4    | int(11)  | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.005 sec)obclient [obdb1]> ALTER TABLE obtest DROP c3;
Query OK, 0 rows affected (0.516 sec)obclient [obdb1]> DESCRIBE obtest;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c1    | int(11) | NO   | PRI | NULL    |       |
| c4    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.027 sec)obclient [obdb1]>

1718184502

更多 ALTER TABLE 语句相关的语法说明，请参见 ALTER TABLE 章节。

4、删除表

使用 DROP TABLE 语句删除表。

示例：删除表 obtest。

obclient [obdb1]> 
obclient [obdb1]> DROP TABLE obtest;
Query OK, 0 rows affected (0.075 sec)obclient [obdb1]>

1718184511

更多 DROP TABLE 语句相关的语法说明,请参见 DROP TABLE 章节。

5、插入数据

使用 INSERT 语句在已经存在的表中插入数据。

示例：创建表 t1 并插入一行数据。

obclient [obdb1]> CREATE TABLE obt1(c1 INT PRIMARY KEY, c2 int) PARTITION BY KEY(c1) PARTITIONS 4;
Query OK, 0 rows affected (0.121 sec)obclient [obdb1]> SELECT * FROM obt1;
Empty set (0.065 sec)obclient [obdb1]> INSERT obt1 VALUES(1,1);
Query OK, 1 row affected (0.007 sec)obclient [obdb1]> SELECT * FROM obt1;
+----+------+
| c1 | c2   |
+----+------+
|  1 |    1 |
+----+------+
1 row in set (0.005 sec)obclient [obdb1]>

1718184523

6、删除数据

使用 DELETE 语句删除数据，支持单表删除和多表删除数据。

示例：

6.1 通过 CREATE TABLE 创建表 t2 和 t3。删除 c1=2 的行，其中 c1 列为表 t2 中的 PRIMARY KEY。

/表 t3 为 KEY 分区表，且分区名由系统根据分区命令规则自动生成，即分区名为 p0、p1、p2、p3/

obclient [obdb1]> 
obclient [obdb1]> CREATE TABLE obt2(c1 INT PRIMARY KEY, c2 INT);
Query OK, 0 rows affected (0.095 sec)obclient [obdb1]> INSERT obt2 VALUES(1,1),(2,2),(3,3),(5,5);
Query OK, 4 rows affected (0.005 sec)
Records: 4  Duplicates: 0  Warnings: 0obclient [obdb1]> SELECT * FROM obt2;
+----+------+
| c1 | c2   |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
|  5 |    5 |
+----+------+
4 rows in set (0.007 sec)obclient [obdb1]> CREATE TABLE obt3(c1 INT PRIMARY KEY, c2 INT) PARTITION BY KEY(c1) PARTITIONS 4;
Query OK, 0 rows affected (0.142 sec)obclient [obdb1]> INSERT INTO obt3 VALUES(5,5),(1,1),(2,2),(3,3);
Query OK, 4 rows affected (0.035 sec)
Records: 4  Duplicates: 0  Warnings: 0obclient [obdb1]> SELECT * FROM obt3;
+----+------+
| c1 | c2   |
+----+------+
|  5 |    5 |
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
+----+------+
4 rows in set (0.028 sec)obclient [obdb1]> DELETE FROM obt2 WHERE c1 = 2;
Query OK, 1 row affected (0.005 sec)obclient [obdb1]> SELECT * FROM obt2;
+----+------+
| c1 | c2   |
+----+------+
|  1 |    1 |
|  3 |    3 |
|  5 |    5 |
+----+------+
3 rows in set (0.003 sec)obclient [obdb1]>

1718184532

6.2 删除表 t2 中按照 c2 列排序之后的第一行数据。

obclient [obdb1]> SELECT * FROM obt2;
+----+------+
| c1 | c2   |
+----+------+
|  1 |    1 |
|  3 |    3 |
|  5 |    5 |
+----+------+
3 rows in set (0.003 sec)obclient [obdb1]> DELETE FROM obt2 ORDER BY c2 LIMIT 1;
Query OK, 1 row affected (0.005 sec)obclient [obdb1]> SELECT * FROM obt2;
+----+------+
| c1 | c2   |
+----+------+
|  3 |    3 |
|  5 |    5 |
+----+------+
2 rows in set (0.001 sec)obclient [obdb1]>

1718184542

7、更新数据

使用 UPDATE 语句修改表中的字段值。

示例：通过 CREATE TABLE 创建表 t4 和 t5，将表 t4 中 t4.c1=10 对应的那一行数据的 c2 列值修改为 100。

obclient [obdb1]> 
obclient [obdb1]> CREATE TABLE obt4(c1 INT PRIMARY KEY, c2 INT);
Query OK, 0 rows affected (0.113 sec)obclient [obdb1]> INSERT obt4 VALUES(10,10),(20,20),(30,30),(40,40);
Query OK, 4 rows affected (0.033 sec)
Records: 4  Duplicates: 0  Warnings: 0obclient [obdb1]> SELECT * FROM obt4;
+----+------+
| c1 | c2   |
+----+------+
| 10 |   10 |
| 20 |   20 |
| 30 |   30 |
| 40 |   40 |
+----+------+
4 rows in set (0.005 sec)obclient [obdb1]> CREATE TABLE obt5(c1 INT PRIMARY KEY, c2 INT) PARTITION BY KEY(c1) PARTITIONS 4;
Query OK, 0 rows affected (0.118 sec)obclient [obdb1]> INSERT obt5 VALUES(50,50),(10,10),(20,20),(30,30);
Query OK, 4 rows affected (0.025 sec)
Records: 4  Duplicates: 0  Warnings: 0obclient [obdb1]> SELECT * FROM obt5;
+----+------+
| c1 | c2   |
+----+------+
| 20 |   20 |
| 10 |   10 |
| 50 |   50 |
| 30 |   30 |
+----+------+
4 rows in set (0.015 sec)obclient [obdb1]> UPDATE obt4 SET obt4.c2 = 100 WHERE obt4.c1 = 10;
Query OK, 1 row affected (0.004 sec)
Rows matched: 1  Changed: 1  Warnings: 0obclient [obdb1]> SELECT * FROM obt4;
+----+------+
| c1 | c2   |
+----+------+
| 10 |  100 |
| 20 |   20 |
| 30 |   30 |
| 40 |   40 |
+----+------+
4 rows in set (0.003 sec)obclient [obdb1]>

1718184556

8、查询数据

使用 SELECT 语句查询表中的内容。

示例：

8.1 通过 CREATE TABLE 创建表 t6。从表 t6 中读取 name 的数据。

obclient [obdb1]> 
obclient [obdb1]> CREATE TABLE obt6 (id INT, name VARCHAR(50), num INT);
Query OK, 0 rows affected (0.205 sec)obclient [obdb1]> INSERT INTO obt6 VALUES(1,'a',100),(2,'b',200),(3,'a',50);
Query OK, 3 rows affected (0.042 sec)
Records: 3  Duplicates: 0  Warnings: 0obclient [obdb1]> SELECT * FROM obt6;
+------+------+------+
| id   | name | num  |
+------+------+------+
|    1 | a    |  100 |
|    2 | b    |  200 |
|    3 | a    |   50 |
+------+------+------+
3 rows in set (0.007 sec)obclient [obdb1]> SELECT name FROM obt6;
+------+
| name |
+------+
| a    |
| b    |
| a    |
+------+
3 rows in set (0.003 sec)

1718184567

9、在查询结果中对 name 进行去重处理。

obclient [obdb1]> SELECT DISTINCT name FROM obt6;
+------+
| name |
+------+
| a    |
| b    |
+------+
2 rows in set (0.004 sec)obclient [obdb1]>

1718184576

五、部署体验建议

熟悉文档：在开始体验之前，建议用户仔细阅读 OceanBase 官方文档，了解 odb demo 的使用方法和注意事项。

多场景测试：为了更全面地了解 OceanBase 4.3 的性能和功能，建议用户在不同的业务场景下进行测试，如高并发读写、大数据量查询等。

关注社区：OceanBase 社区是一个活跃的开发者社区，用户可以在其中交流经验、寻求帮助、参与讨论等。

总结

OceanBase 4.3 社区版以其强大的功能、优秀的性能和易用性，为开发者提供了一个高效的分布式数据库解决方案。通过 odb demo 命令快速体验 OceanBase 4.3 社区版是一个便捷的方式，用户可以在短时间内了解该版本的各项功能和优势。无论是对于数据库初学者还是专业开发人员来说，这都是一个值得尝试的体验过程。希望以上部署测试能对您有所帮助！