「OceanBase 4.1 体验」OceanBase 4.1社区版的部署及使用体验

一、前言

1.1 本次实践介绍

本次部署OceanBase 4.1社区版采用的是使docker单机快速部署方式，使用的是dockerhub中oceanbase/oceanbase-ce镜像，版本为4.1.0.0版本。该镜像仅供学习或测试使用，可以快速部署一个MINI_MODE OceanBase数据库实例，极大的提高了部署速度，方便快捷。

1.2 本次实践目的

1.本次实践环境为个人测试环境，生产环境请根据官方文档指导部署；
2.本次OceanBase 4.1社区版部署为docker单机快速部署方式；
3.体验OceanBase 4.1社区版的部署及基本使用；

二、准备环境资源

2.1 部署前需准备工作

• 确保机器已经部署docker环境
• Docker服务状态运行正常
• 宿主机2881端口没有被占用
• 防火墙已放行2881端口
• 确保宿主机内存大于8G
• 宿主机cpu内核总数大于2
• 宿主机磁盘空间大于54G

2.2 本地环境规划

本次实践的个人环境规划如下，已经部署好docker环境，使用的是一台在PVE虚拟化平台的虚拟机作为实践环境。

hostname	IP地址	docker版本	操作系统版本	CPU核数	内存	硬盘	虚拟化平台	服务器类型	OceanBase版本
docker	192.168.3.127	23.0.5	centos 7.6	6	12G	500G	PVE虚拟化平台	虚拟机	OceanBase 4.1社区版

三、部署Docker环境

3.1 安装Docker

执行以下两条命令，使用docker快速安装脚本，一键快速部署docker。

curl -fsSL get.docker.com -o get-docker.sh
sh get-docker.sh --mirror Aliyun

启动Docker服务，并设置开机自启。

systemctl --now enbale docker

3.2 配置Docker镜像加速

修改/etc/docker/daemon.json文件，配置Docker镜像加速。

echo '{ "registry-mirrors": ["https://sp4mg57h.mirror.aliyuncs.com"] }'   > /etc/docker/daemon.json 

3.3 开启路由转发

开启路由转发，并重启网络服务。

[root@docker ~]# echo "net.ipv4.ip_forward=1" >>  /etc/sysctl.conf
[root@docker ~]#  systemctl restart network
[root@docker ~]#  sysctl net.ipv4.ip_forward
net.ipv4.ip_forward = 1

3.4 重启Docker服务

重启Docker服务。

systemctl daemon-reload  && systemctl restart docker

四、检查本地Docker环境

4.1 检查docker版本

检查本机安装的docker版本

[root@docker ~]# docker -v 
Docker version 23.0.5, build bc4487a

4.2 检查Docker服务

检查本机的docker服务状态

[root@docker ~]# systemctl status docker
● docker.service - Docker Application Container Engine
   Loaded: loaded (/usr/lib/systemd/system/docker.service; disabled; vendor preset: disabled)
   Active: active (running) since Thu 2023-04-27 15:40:54 CST; 24min ago
     Docs: https://docs.docker.com
 Main PID: 5092 (dockerd)
    Tasks: 12
   Memory: 549.2M
   CGroup: /system.slice/docker.service
           └─5092 /usr/bin/dockerd -H fd:// --containerd=/run/containerd/containerd.sock

4.3 检查磁盘大小

检查docker宿主机的磁盘大小，确保满足部署要求。

[root@docker ~]# df -h 
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda6       360G   26G  334G   8% /
devtmpfs        5.8G     0  5.8G   0% /dev
tmpfs           5.8G     0  5.8G   0% /dev/shm
tmpfs           5.8G  8.6M  5.8G   1% /run
tmpfs           5.8G     0  5.8G   0% /sys/fs/cgroup
/dev/sda2       100G   33M  100G   1% /home
/dev/sda3        20G  3.0G   18G  15% /var
/dev/sda1        12G  137M   12G   2% /boot
overlay          20G  3.0G   18G  15% /var/lib/docker/overlay2/c493da6000055915b42d2bec2e169c522122ecd9559a92f1f41f104dd4508b25/merged
tmpfs           1.2G     0  1.2G   0% /run/user/0
[root@docker ~]# lsblk 
NAME   MAJ:MIN RM  SIZE RO TYPE MOUNTPOINT
sda      8:0    0  500G  0 disk 
├─sda1   8:1    0   12G  0 part /boot
├─sda2   8:2    0  100G  0 part /home
├─sda3   8:3    0   20G  0 part /var
├─sda4   8:4    0    1K  0 part 
├─sda5   8:5    0    8G  0 part [SWAP]
└─sda6   8:6    0  360G  0 part /
sr0     11:0    1  4.3G  0 rom  

4.4 检查内存大小

检查docker宿主机的内存大小，确保满足部署要求。

[root@docker ~]# free -m
              total        used        free      shared  buff/cache   available
Mem:          11852        5537         345           8        5970        5878
Swap:          8191           7        8184

4.5 检查cpu核数

检查cpu的核心数，确保大于2核。

[root@docker ~]# cat /proc/cpuinfo| grep "cpu cores"| uniq
cpu cores	: 6

4.6 检查2881端口

检查2881端口是否被占用，确保没有服务占用2881端口。

ss -tunlp |grep 2881

检查防火墙是否放行2881端口

[root@docker ~]# firewall-cmd --list-ports 
2881/tcp

如果防火墙未放行2881端口，则执行以下放行命令：

[root@docker ~]# firewall-cmd --permanent --add-port=2881/tcp
success
[root@docker ~]# firewall-cmd --reload 
success

五、部署OceanBase 4.1社区版

5.1 下载OceanBase 4.1镜像

在dockerhub下载oceanbase/oceanbase-ce:4.1.0.0容器镜像

[root@docker ~]# docker pull oceanbase/oceanbase-ce:4.1.0.0
4.1.0.0: Pulling from oceanbase/oceanbase-ce
2d473b07cdd5: Pull complete 
c21cdfe7a94b: Pull complete 
7433dd4c409c: Pull complete 
00a0e26f2005: Pull complete 
Digest: sha256:18c4055f80ec312fc618ef6356ba0da6379c93c45a01b1b5af46e4667c327135
Status: Downloaded newer image for oceanbase/oceanbase-ce:4.1.0.0
docker.io/oceanbase/oceanbase-ce:4.1.0.0

5.2 创建OceanBase容器

创建容器挂载目录/data/ob.

mkdir -p /data/ob/  &&  mkdir -p /data/obd/

在本地系统环境，创建一个OceanBase容器。

docker run -d -p 2881:2881 -v /data/ob:/root/ob -v /data/obd:/root/.obd --name oceanbase oceanbase/oceanbase-ce:4.1.0.0

5.3 检查OceanBase容器状态

检查OceanBase容器状态，查看容器是否正常启动。

[root@docker data]# docker ps
CONTAINER ID   IMAGE                            COMMAND              CREATED         STATUS         PORTS                                       NAMES
32d28cd6b407   oceanbase/oceanbase-ce:4.1.0.0   "/bin/sh -c _boot"   9 minutes ago   Up 9 minutes   0.0.0.0:2881->2881/tcp, :::2881->2881/tcp   oceanbase

5.4 检查OceanBase容器的运行日志

查看OceanBase容器的运行日志，确保OceanBase服务正常运行。

[root@docker data]# docker logs oceanbase
generate boot.yaml ...
oceanbase-ce docker in mini mode
create boot dirs and deploy ob cluster ...
name: obagent
version: 1.3.0
release:22.el7
arch: x86_64
md5: d57fbb4962b2fbecb6282358c59295fdfba4d6ac
add /root/pkg/obagent-1.3.0-22.el7.x86_64.rpm to local mirror
name: oceanbase-ce
version: 4.1.0.0
release:100000192023032010.el7
arch: x86_64
md5: 8439ecf8db5e0649bd49671b41ea9e8c85756b63
add /root/pkg/oceanbase-ce-4.1.0.0-100000192023032010.el7.x86_64.rpm to local mirror
name: oceanbase-ce-libs
version: 4.1.0.0
release:100000192023032010.el7
arch: x86_64
md5: a83b1dd1cab44d3f610d439931322be7a08555f2
add /root/pkg/oceanbase-ce-libs-4.1.0.0-100000192023032010.el7.x86_64.rpm to local mirror
Trace ID: 5b38756a-e4d6-11ed-a300-0242ac110002
If you want to view detailed obd logs, please run: obd display-trace 5b38756a-e4d6-11ed-a300-0242ac110002
+----------------------------------------------------------------------------------------------------------+
|                                            local Package List                                            |
+-------------------+---------+------------------------+--------+------------------------------------------+
| name              | version | release                | arch   | md5                                      |
+-------------------+---------+------------------------+--------+------------------------------------------+
| obagent           | 1.3.0   | 22.el7                 | x86_64 | d57fbb4962b2fbecb6282358c59295fdfba4d6ac |
| oceanbase-ce      | 4.1.0.0 | 100000192023032010.el7 | x86_64 | 8439ecf8db5e0649bd49671b41ea9e8c85756b63 |
| oceanbase-ce-libs | 4.1.0.0 | 100000192023032010.el7 | x86_64 | a83b1dd1cab44d3f610d439931322be7a08555f2 |
+-------------------+---------+------------------------+--------+------------------------------------------+
Trace ID: 5bb1765e-e4d6-11ed-b7d4-0242ac110002
If you want to view detailed obd logs, please run: obd display-trace 5bb1765e-e4d6-11ed-b7d4-0242ac110002
Local deploy is empty
Trace ID: 5c25bab4-e4d6-11ed-8844-0242ac110002
If you want to view detailed obd logs, please run: obd display-trace 5c25bab4-e4d6-11ed-8844-0242ac110002
Dev Mode: ON
Trace ID: 5dd12ede-e4d6-11ed-88c3-0242ac110002
If you want to view detailed obd logs, please run: obd display-trace 5dd12ede-e4d6-11ed-88c3-0242ac110002
Package oceanbase-ce-4.1.0.0-100000192023032010.el7 is available.
Package obagent-1.3.0-22.el7 is available.
install oceanbase-ce-4.1.0.0 for local ok
install obagent-1.3.0 for local ok
Cluster param config check ok
Open ssh connection ok
Generate observer configuration ok
Generate obagent configuration ok
+--------------------------------------------------------------------------------------------+
|                                          Packages                                          |
+--------------+---------+------------------------+------------------------------------------+
| Repository   | Version | Release                | Md5                                      |
+--------------+---------+------------------------+------------------------------------------+
| oceanbase-ce | 4.1.0.0 | 100000192023032010.el7 | 8439ecf8db5e0649bd49671b41ea9e8c85756b63 |
| obagent      | 1.3.0   | 22.el7                 | d57fbb4962b2fbecb6282358c59295fdfba4d6ac |
+--------------+---------+------------------------+------------------------------------------+
Repository integrity check ok
Parameter check ok
Open ssh connection ok
Cluster status check ok
Initializes observer work home ok
Initializes obagent work home ok
Remote oceanbase-ce-4.1.0.0-100000192023032010.el7-8439ecf8db5e0649bd49671b41ea9e8c85756b63 repository install ok
Remote oceanbase-ce-4.1.0.0-100000192023032010.el7-8439ecf8db5e0649bd49671b41ea9e8c85756b63 repository lib check !!
Remote obagent-1.3.0-22.el7-d57fbb4962b2fbecb6282358c59295fdfba4d6ac repository install ok
Remote obagent-1.3.0-22.el7-d57fbb4962b2fbecb6282358c59295fdfba4d6ac repository lib check ok
Try to get lib-repository
Package oceanbase-ce-libs-4.1.0.0-100000192023032010.el7 is available.
install oceanbase-ce-libs-4.1.0.0 for local ok
Remote oceanbase-ce-libs-4.1.0.0-100000192023032010.el7-a83b1dd1cab44d3f610d439931322be7a08555f2 repository install ok
Remote oceanbase-ce-4.1.0.0-100000192023032010.el7-8439ecf8db5e0649bd49671b41ea9e8c85756b63 repository lib check ok
obcluster deployed
Get local repositories ok
Search plugins ok
Open ssh connection ok
Load cluster param plugin ok
Check before start observer ok
[WARN] OBD-1011: (127.0.0.1) The recommended value of fs.aio-max-nr is 1048576 (Current value: 65536)
[WARN] OBD-1007: (127.0.0.1) The recommended number of open files is 655350 (Current value: 65536)
[WARN] OBD-1012: (127.0.0.1) clog and data use the same disk (/root/ob)

Check before start obagent ok
Start observer ok
observer program health check ok
Connect to observer ok
Initialize oceanbase-ce ok
Start obagent ok
obagent program health check ok
Connect to Obagent ok
Wait for observer init ok
+---------------------------------------------+
|                   observer                  |
+-----------+---------+------+-------+--------+
| ip        | version | port | zone  | status |
+-----------+---------+------+-------+--------+
| 127.0.0.1 | 4.1.0.0 | 2881 | zone1 | ACTIVE |
+-----------+---------+------+-------+--------+
obclient -h127.0.0.1 -P2881 -uroot -Doceanbase -A

+---------------------------------------------------------------+
|                            obagent                            |
+------------+--------------------+--------------------+--------+
| ip         | mgragent_http_port | monagent_http_port | status |
+------------+--------------------+--------------------+--------+
| 172.17.0.2 | 8089               | 8088               | active |
+------------+--------------------+--------------------+--------+
obcluster running
Trace ID: 5e3444ba-e4d6-11ed-a7b1-0242ac110002
If you want to view detailed obd logs, please run: obd display-trace 5e3444ba-e4d6-11ed-a7b1-0242ac110002
Get local repositories and plugins ok
Open ssh connection ok
Connect to observer ok
Create tenant test ok
Trace ID: d3706e3e-e4d6-11ed-82e4-0242ac110002
If you want to view detailed obd logs, please run: obd display-trace d3706e3e-e4d6-11ed-82e4-0242ac110002
deploy success!
boot success!

直接通过以下命令查看最后的安装结果，出现boot success就表示部署成功。

[root@docker data]# docker logs oceanbase | tail -1
boot success!

六、连接到 OceanBase 实例

6.1 连接sys租户的root 用户

连接sys租户的root 用户

[root@docker data]# docker exec -it oceanbase ob-mysql sys
login as root@sys
Command is: obclient -h127.1 -uroot@sys -A -Doceanbase -P2881 
Welcome to the OceanBase.  Commands end with ; or \g.
Your OceanBase connection id is 3221487689
Server version: OceanBase_CE 4.1.0.0 (r100000192023032010-0265dfc6d00ff4f0ff4ad2710504a18962abaef6) (Built Mar 20 2023 10:12:57)

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]> 

6.2 连接test租户root用户

连接test租户root用户

[root@docker data]# docker exec -it oceanbase ob-mysql root
login as root@test
Command is: obclient -h127.1 -uroot@test -A -Doceanbase -P2881 
Welcome to the OceanBase.  Commands end with ; or \g.
Your OceanBase connection id is 3221487695
Server version: OceanBase_CE 4.1.0.0 (r100000192023032010-0265dfc6d00ff4f0ff4ad2710504a18962abaef6) (Built Mar 20 2023 10:12:57)

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]> 

6.3 连接test租户的test用户

连接test租户的test用户

[root@docker data]# docker exec -it oceanbase ob-mysql test
login as test@test
Command is: obclient -h127.1 -utest@test -A -Dtest -P2881 
Welcome to the OceanBase.  Commands end with ; or \g.
Your OceanBase connection id is 3221487706
Server version: OceanBase_CE 4.1.0.0 (r100000192023032010-0265dfc6d00ff4f0ff4ad2710504a18962abaef6) (Built Mar 20 2023 10:12:57)

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 [test]> 

6.4 创建wordpress数据库

在 root@root用户权限下，新建wordpress数据库。

[root@docker ~]# docker exec -it oceanbase ob-mysql root
login as root@test
Command is: obclient -h127.1 -uroot@test -A -Doceanbase -P2881 
Welcome to the OceanBase.  Commands end with ; or \g.
Your OceanBase connection id is 3221487904
Server version: OceanBase_CE 4.1.0.0 (r100000192023032010-0265dfc6d00ff4f0ff4ad2710504a18962abaef6) (Built Mar 20 2023 10:12:57)

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]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| oceanbase          |
| test               |
+--------------------+
4 rows in set (0.496 sec)

obclient [oceanbase]> create database wordpress;
Query OK, 1 row affected (5.961 sec)

obclient [oceanbase]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| oceanbase          |
| test               |
| wordpress          |
+--------------------+
5 rows in set (0.604 sec)

6.5 在test租户下创建用户

使用test租户下的root用户连接OceanBase数据库，创建admin@test用户。

[root@docker ~]# docker exec -it oceanbase ob-mysql root
login as root@test
Command is: obclient -h127.1 -uroot@test -A -Doceanbase -P2881 
Welcome to the OceanBase.  Commands end with ; or \g.
Your OceanBase connection id is 3221487913
Server version: OceanBase_CE 4.1.0.0 (r100000192023032010-0265dfc6d00ff4f0ff4ad2710504a18962abaef6) (Built Mar 20 2023 10:12:57)

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]>  create user admin  identified by "admin123";
Query OK, 0 rows affected (1.590 sec)

obclient [oceanbase]> GRANT ALL ON wordpress.* TO admin with GRANT OPTION;
Query OK, 0 rows affected (0.764 sec)

6.6 连接test租户下admin用户

进入OceanBase容器内

[root@docker ~]# docker exec -it oceanbase /bin/bash
[root@32d28cd6b407 ~]# 

测试连接test租户下admin用户

[root@32d28cd6b407 ~]#  obclient -h127.1 -uadmin@test -A -Dwordpress -P2881 -padmin123
Welcome to the OceanBase.  Commands end with ; or \g.
Your OceanBase connection id is 3221487933
Server version: OceanBase_CE 4.1.0.0 (r100000192023032010-0265dfc6d00ff4f0ff4ad2710504a18962abaef6) (Built Mar 20 2023 10:12:57)

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 [wordpress]> 

6.7 使用mysql客户端连接

在局域网内一台mysql客户端，连接test租户下admin用户

[root@server ~]# mysql -h 192.168.3.127  -P 2881 -u admin@test -padmin123 -D wordpress -Ac --prompt "OceanBase(\u@\d)> " 
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3221487997
Server version: 5.7.25 OceanBase_CE 4.1.0.0 (r100000192023032010-0265dfc6d00ff4f0ff4ad2710504a18962abaef6) (Built Mar 20 2023 10:12:57)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

OceanBase(admin@wordpress)> 

七、在OceanBase数据库创建数据表

7.1 使用mysql客户端连接OceanBase

使用mysql客户端远程连接OceanBase数据库

[root@server ~]# mysql -h 192.168.3.127  -P 2881 -u admin@test -padmin123  -Ac --prompt "OceanBase(\u@\d)> " 
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3221488894
Server version: 5.7.25 OceanBase_CE 4.1.0.0 (r100000192023032010-0265dfc6d00ff4f0ff4ad2710504a18962abaef6) (Built Mar 20 2023 10:12:57)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

OceanBase(admin@(none))> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| oceanbase          |
| test               |
| wordpress          |
| wordpress_db       |
+--------------------+
6 rows in set (0.37 sec)

OceanBase(admin@(none))> 

7.2 创建数据库

在OceanBase内创建一个数据库school

OceanBase(admin@(none))>  create database school;
Query OK, 1 row affected (0.44 sec)

OceanBase(admin@(none))> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| oceanbase          |
| school             |
| test               |
| wordpress          |
| wordpress_db       |
+--------------------+
7 rows in set (0.35 sec)

OceanBase(admin@(none))> 

7.3 进入数据库内

进入新创建的school数据库内

OceanBase(admin@(none))> use school;
Database changed
OceanBase(admin@school)> show tables;
Empty set (0.00 sec)

OceanBase(admin@school)> 

7.4 新建数据表

创建一个空数据表

 CREATE TABLE IF NOT EXISTS `student`(
     `id` INT UNSIGNED AUTO_INCREMENT,
      `name` VARCHAR(100) NOT NULL,
    `gender` TINYINT NOT NULL,
     `age` INT UNSIGNED,
    `class` INT UNSIGNED,
      `score` INT UNSIGNED,
      PRIMARY KEY ( `id` )
    )ENGINE=InnoDB DEFAULT CHARSET=utf8;

7.5 写入数据

向数据表student写入数据。

OceanBase(admin@school)> insert into student ( name, gender, age, class,  score ) values ( "李萌萌", "0", "17", "3", "98" );
Query OK, 1 row affected (1.30 sec)

OceanBase(admin@school)> insert into student ( name, gender, age, class,  score ) values ( "王依依", "0", "14", "2", "68" );
Query OK, 1 row affected (0.57 sec)

OceanBase(admin@school)> insert into student ( name, gender, age, class,  score ) values ( "张强", "1", "17", "3", "78" );
Query OK, 1 row affected (0.21 sec)

7.6 查看数据表内容

查看数据表内容

OceanBase(admin@school)> select * from student;
+----+-----------+--------+------+-------+-------+
| id | name      | gender | age  | class | score |
+----+-----------+--------+------+-------+-------+
|  1 | 李萌萌    |      0 |   17 |     3 |    98 |
|  2 | 王依依    |      0 |   14 |     2 |    68 |
|  3 | 张强      |      1 |   17 |     3 |    78 |
+----+-----------+--------+------+-------+-------+
3 rows in set (0.01 sec)

八、使用DBeaver连接OceanBase 4.1社区版

8.1 打开DBeaver软件

打开本地DBeaver软件

8.2 选择连接数据库类型

选择连接的数据库为OceanBase

8.3 填写数据库连接信息

数据库连接信息如下：
host: 192.168.3.127
port： 2881
database: school
Tenant: test
用户名：admin
密码：admin123

8.4 测试连接及下载相关驱动

点击测试连接选项，自动下载驱动程序，测试连接正常。

8.5 查看数据表内容

查看OceanBase中student数据表内容。