系列文章目录
第一章 oceanbase学习之docker方式部署
第二章 oceanbase学习之手动部署
第三章 oceanbase学习之迁移MySQL数据到oceanbase
第四章 oceanbase学习之查看oceanbase执行计划
前言
在国产化数据库需求日益增多的浪潮中，oceanbase作为一款分布式数据库脱颖而出，多次问鼎TPC基准测试，并完美支撑阿里体系下淘宝、天猫、支付宝等相关业务，在多次双11的洗礼中稳如磐石。本系列文章主要就oceanbase相关基础知识进行学习部署，并备考obcp作为记录。
一、服务器环境
本次实验采用单台服务器部署，服务器信息如下：
| 服务器IP | 系统 | cpu | 内存 | 磁盘 | 安装目录 |
|---|---|---|---|---|---|
| 10.40.204.170 | centos 7 | 8 | 16 | 170G | 根目录 |
修改服务器相关配置
#修改主机名
[root@localhost ~]# hostnamectl set-hostname oceanbase
#增加服务器dns
[root@oceanbase ~]# echo "nameserver 8.8.8.8" >> /etc/resolv.conf
#优化linux内核参数
[root@oceanbase ~]# echo "fs.file-max = 65536
> net.ipv4.tcp_fin_timeout= 30
> net.ipv4.tcp_tw_reuse = 1
> net.ipv4.tcp_syncookies = 1
> net.ipv4.tcp_timestamps = 0
> net.ipv4.tcp_max_syn_backlog = 16384
> net.ipv4.tcp_max_tw_buckets = 360000
> net.ipv4.route.gc_timeout = 100
> net.ipv4.tcp_syn_retries = 2
> net.ipv4.tcp_synack_retries = 1
> net.core.somaxconn = 32768
> net.core.netdev_max_backlog = 32768
> net.ipv4.tcp_max_orphans = 327680
> net.ipv4.tcp_syncookies = 1
>
> net.nf_conntrack_max = 102400
> net.netfilter.nf_conntrack_max = 102400
>
> #net.ipv4.tcp_tw_recycle = 1 " >> /etc/sysctl.conf
[root@oceanbase ~]# sysctl -p
fs.file-max = 65536
net.ipv4.tcp_fin_timeout = 30
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_syncookies = 1
net.ipv4.tcp_timestamps = 0
net.ipv4.tcp_max_syn_backlog = 16384
net.ipv4.tcp_max_tw_buckets = 360000
net.ipv4.route.gc_timeout = 100
net.ipv4.tcp_syn_retries = 2
net.ipv4.tcp_synack_retries = 1
net.core.somaxconn = 32768
net.core.netdev_max_backlog = 32768
net.ipv4.tcp_max_orphans = 327680
net.ipv4.tcp_syncookies = 1
net.nf_conntrack_max = 102400
net.netfilter.nf_conntrack_max = 102400
[root@oceanbase ~]#
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
二、docker环境安装
#安装docker环境依赖
[root@oceanbase ~]# sudo yum install -y yum-utils device-mapper-persistent-data lvm2 wget
#根据你的发行版下载repo文件
[root@oceanbase ~]# wget -O /etc/yum.repos.d/docker-ce.repo https://download.docker.com/linux/centos/docker-ce.repo
#把软件仓库地址替换为 清华源:
[root@oceanbase ~]# sudo sed -i 's+download.docker.com+mirrors.tuna.tsinghua.edu.cn/docker-ce+' /etc/yum.repos.d/docker-ce.repo
#开始安装docker
[root@oceanbase ~]# sudo yum makecache fast
[root@oceanbase ~]# sudo yum install docker-ce
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
* base: mirrors.cn99.com
* extras: ftp.sjtu.edu.cn
* updates: mirrors.ustc.edu.cn
Resolving Dependencies
--> Running transaction check
---> Package docker-ce.x86_64 3:20.10.14-3.el7 will be installed
--> Processing Dependency: container-selinux >= 2:2.74 for package: 3:docker-ce-20.10.14-3.el7.x86_64
--> Processing Dependency: containerd.io >= 1.4.1 for package: 3:docker-ce-20.10.14-3.el7.x86_64
--> Processing Dependency: libseccomp >= 2.3 for package: 3:docker-ce-20.10.14-3.el7.x86_64
. . . . .
. . . . .
. . . . .
Total 966 kB/s | 97 MB 00:01:42
Retrieving key from https://mirrors.tuna.tsinghua.edu.cn/docker-ce/linux/centos/gpg
Importing GPG key 0x621E9F35:
Userid : "Docker Release (CE rpm) <docker@docker.com>"
Fingerprint: 060a 61c5 1b55 8a7f 742b 77aa c52f eb6b 621e 9f35
From : https://mirrors.tuna.tsinghua.edu.cn/docker-ce/linux/centos/gpg
Is this ok [y/N]: y #这里需要确认导入gpg key
. . . . .
. . . . .
Installed:
docker-ce.x86_64 3:20.10.14-3.el7
Dependency Installed:
audit-libs-python.x86_64 0:2.8.5-4.el7 checkpolicy.x86_64 0:2.5-8.el7 container-selinux.noarch 2:2.119.2-1.911c772.el7_8 containerd.io.x86_64 0:1.5.11-3.1.el7
docker-ce-cli.x86_64 1:20.10.14-3.el7 docker-ce-rootless-extras.x86_64 0:20.10.14-3.el7 docker-scan-plugin.x86_64 0:0.17.0-3.el7 fuse-overlayfs.x86_64 0:0.7.2-6.el7_8
fuse3-libs.x86_64 0:3.6.1-4.el7 libcgroup.x86_64 0:0.41-21.el7 libseccomp.x86_64 0:2.3.1-4.el7 libsemanage-python.x86_64 0:2.5-14.el7
policycoreutils-python.x86_64 0:2.5-34.el7 python-IPy.noarch 0:0.75-6.el7 setools-libs.x86_64 0:3.3.8-4.el7 slirp4netns.x86_64 0:0.4.3-4.el7_8
Dependency Updated:
audit.x86_64 0:2.8.5-4.el7 audit-libs.x86_64 0:2.8.5-4.el7 policycoreutils.x86_64 0:2.5-34.el7
Complete!
#提示complete！ 表示安装成功
#查看docker版本
[root@oceanbase ~]# docker version
Client: Docker Engine - Community
Version: 20.10.14
API version: 1.41
Go version: go1.16.15
Git commit: a224086
Built: Thu Mar 24 01:49:57 2022
OS/Arch: linux/amd64
Context: default
Experimental: true
Cannot connect to the Docker daemon at unix:///var/run/docker.sock. Is the docker daemon running?
#启动docker并设置为开机自启动
[root@oceanbase ~]# systemctl start docker
[root@oceanbase ~]# systemctl enable docker
Created symlink from /etc/systemd/system/multi-user.target.wants/docker.service to /usr/lib/systemd/system/docker.service.
#再次查看docker版本号
[root@oceanbase ~]# docker version
Client: Docker Engine - Community
Version: 20.10.14
API version: 1.41
Go version: go1.16.15
Git commit: a224086
Built: Thu Mar 24 01:49:57 2022
OS/Arch: linux/amd64
Context: default
Experimental: true
Server: Docker Engine - Community
Engine:
Version: 20.10.14
API version: 1.41 (minimum version 1.12)
Go version: go1.16.15
Git commit: 87a90dc
Built: Thu Mar 24 01:48:24 2022
OS/Arch: linux/amd64
Experimental: false
containerd:
Version: 1.5.11
GitCommit: 3df54a852345ae127d1fa3092b95168e4a88e2f8
runc:
Version: 1.0.3
GitCommit: v1.0.3-0-gf46b6ba
docker-init:
Version: 0.19.0
GitCommit: de40ad0
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
三、oceanbase使用docker部署
#查看当前oceanbase可部署的docker版本
[root@oceanbase ~]# docker search oceanbase
NAME DESCRIPTION STARS OFFICIAL AUTOMATED
oceanbase/oceanbase-xe OceanBase Database 2.2 Express Edition 4
oceanbase/oceanbase-ce OceanBase is open source now. This is the do… 4
oceanbase/obce-mini obce-mini is a mini standalone test image fo… 3
obpilot/oceanbase-ce 3 steps to run an OceanBase-CE docker in you… 3
oceanbase/obce-operator obce-operator 1
oceanbase/miniob miniob database competition 1
zibuyu886/oceanbase-ce-cluster OceanBase ce cluster 1
huweijie/oceanbase-ce-deploy 0
superbigfu/oceanbase 0
oceanbase/centos7 0
20220121/oceanbase 0
jimmyzhou623/oceanbase 0
hongweiqin/anolisos-oceanbase A tentative deploy of oceanbase. 0
stutiredboy/centos_ob Build environment for OceanBase 3.1 CE. Crea… 0
ggzzzzzzz/obce-mini oceanbase community edition 3.1.3 mini image 0
#根据官方文档安装oceanbase镜像
[root@oceanbase ~]# docker pull oceanbase/obce-mini
Using default tag: latest
Error response from daemon: manifest for oceanbase/obce-mini:latest not found: manifest unknown: manifest unknown
#提示无法找到镜像包并报错
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
在docker网站上未找到相应信息，按提示需要使用oceanbae-ce的standalone模式
https://hub.docker.com/r/oceanbase/obce-mini
#拉取oceanbase-ce镜像
[root@oceanbase ~]# docker pull oceanbase/oceanbase-ce
Using default tag: latest
latest: Pulling from oceanbase/oceanbase-ce
13add961a70d: Pull complete
1cf396138e36: Pull complete
de207e7387d3: Pull complete
Digest: sha256:22c2d82e5e7223ee510b1c53400edd05e622d9a09bc33987bd85ce560bb0781a
Status: Downloaded newer image for oceanbase/oceanbase-ce:latest
docker.io/oceanbase/oceanbase-ce:latest
#查看本机镜像
[root@oceanbase ~]# docker images
REPOSITORY TAG IMAGE ID CREATED SIZE
oceanbase/oceanbase-ce latest 66c986c2d478 3 weeks ago 783MB
#启动docker镜像官方提供两个方案其中MINI_MODE表示以模拟模式启动
# deploy an instance of the largest size according to the current container
docker run -p 2881:2881 --name obstandalone -d oceanbase/oceanbase-ce
# deploy mini standalone instance
docker run -p 2881:2881 --name obstandalone -e MINI_MODE=1 -d oceanbase/oceanbase-ce
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
由于本服务器配置较低，当前以模拟模式启动
[root@oceanbase ~]# docker run -p 2881:2881 --name obstandalone -e MINI_MODE=1 -d oceanbase/oceanbase-ce
5437adba18818ca5e31a36161642f85377063420034c00db636baab271dcba9a
#查看镜像状态
[root@oceanbase ~]# docker ps -a
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
5437adba1881 oceanbase/oceanbase-ce "/bin/sh -c _boot" About a minute ago Exited (1) 55 seconds ago obstandalone
#检查docker日志
[root@oceanbase ~]# docker logs obstandalone
generate boot.yaml ...
/root/boot/_boot: line 11: 1: command not found
create boot dirs and deploy ob cluster ...
/root/boot/_boot: line 25: 1: command not found
Package oceanbase-ce-3.1.3 is available.
install oceanbase-ce-3.1.3 for local ok
Cluster param config check ok
Open ssh connection ok
Generate observer configuration x
[ERROR] (127.0.0.1) / not enough disk space. (Avail: 20.7G, Need: 65.6G). Use `redo_dir` to set other disk for clog
See https://open.oceanbase.com/docs/obd-cn/V1.2.0/10000000000017237.
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
查看启动状态后发现启动失败根目录最少需要65.6G磁盘，需要扩容根据本机磁盘情况将已挂载未使用的磁盘挂载到根目录
[root@oceanbase ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/centos-root00 24G 2.6G 21G 11% /
devtmpfs 7.8G 0 7.8G 0% /dev
tmpfs 7.8G 0 7.8G 0% /dev/shm
tmpfs 7.8G 8.9M 7.8G 1% /run
tmpfs 7.8G 0 7.8G 0% /sys/fs/cgroup
/dev/sda1 1014M 145M 870M 15% /boot
/dev/mapper/centos-oceanbase 48G 33M 48G 1% /oceanbase
tmpfs 1.6G 0 1.6G 0% /run/user/0
[root@oceanbase ~]# ls /oceanbase/
[root@oceanbase ~]# umount /oceanbase/
[root@oceanbase ~]# lvremove /dev/centos/oceanbase
Do you really want to remove active logical volume centos/oceanbase? [y/n]: y
Logical volume "oceanbase" successfully removed
[root@oceanbase ~]# vgdisplay
--- Volume group ---
VG Name centos
System ID
Format lvm2
Metadata Areas 1
Metadata Sequence No 5
VG Access read/write
VG Status resizable
MAX LV 0
Cur LV 2
Open LV 2
Max PV 0
Cur PV 1
Act PV 1
VG Size <79.00 GiB
PE Size 4.00 MiB
Total PE 20223
Alloc PE / Size 7989 / <31.21 GiB
Free PE / Size 12234 / <47.79 GiB
VG UUID GScsEO-CxJl-tqq2-vk73-QAnA-fj4s-DapxRg
[root@oceanbase ~]# lvdisplay
--- Logical volume ---
LV Path /dev/centos/swap
LV Name swap
VG Name centos
LV UUID YTdaEo-XfWU-m0ww-wQD1-2zXz-JRI0-XBUz9H
LV Write Access read/write
LV Creation host, time localhost, 2022-04-26 22:34:31 +0800
LV Status available
# open 2
LV Size <7.88 GiB
Current LE 2016
Segments 1
Allocation inherit
Read ahead sectors auto
- currently set to 8192
Block device 253:1
--- Logical volume ---
LV Path /dev/centos/root00
LV Name root00
VG Name centos
LV UUID piA1di-WY4K-HTzr-yPWE-xcVZ-tjdD-CpGnOV
LV Write Access read/write
LV Creation host, time localhost, 2022-04-26 22:34:31 +0800
LV Status available
# open 1
LV Size 23.33 GiB
Current LE 5973
Segments 1
Allocation inherit
Read ahead sectors auto
- currently set to 8192
Block device 253:0
[root@oceanbase ~]# lvextend -l +12234 /dev/centos/root00
Size of logical volume centos/root00 changed from 23.33 GiB (5973 extents) to 71.12 GiB (18207 extents).
Logical volume centos/root00 successfully resized.
[root@oceanbase ~]# xfs_growfs /dev/centos/root00
meta-data=/dev/mapper/centos-root00 isize=512 agcount=4, agsize=1529088 blks
= sectsz=512 attr=2, projid32bit=1
= crc=1 finobt=0 spinodes=0
data = bsize=4096 blocks=6116352, imaxpct=25
= sunit=0 swidth=0 blks
naming =version 2 bsize=4096 ascii-ci=0 ftype=1
log =internal bsize=4096 blocks=2986, version=2
= sectsz=512 sunit=0 blks, lazy-count=1
realtime =none extsz=4096 blocks=0, rtextents=0
data blocks changed from 6116352 to 18643968
[root@oceanbase ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/centos-root00 72G 2.6G 69G 4% /
devtmpfs 7.8G 0 7.8G 0% /dev
tmpfs 7.8G 0 7.8G 0% /dev/shm
tmpfs 7.8G 8.9M 7.8G 1% /run
tmpfs 7.8G 0 7.8G 0% /sys/fs/cgroup
/dev/sda1 1014M 145M 870M 15% /boot
tmpfs 1.6G 0 1.6G 0% /run/user/0
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
根目录扩容后，重新启动docker镜像并观察日志
#再次启动发现问题依然存在
[root@oceanbase ~]# docker logs obstandalone
generate boot.yaml ...
/root/boot/_boot: line 11: 1: command not found
create boot dirs and deploy ob cluster ...
/root/boot/_boot: line 25: 1: command not found
Package oceanbase-ce-3.1.3 is available.
install oceanbase-ce-3.1.3 for local ok
Cluster param config check ok
Open ssh connection ok
Generate observer configuration x
[ERROR] (127.0.0.1) / not enough disk space. (Avail: 68.5G, Need: 77.1G). Use `redo_dir` to set other disk for clog
See https://open.oceanbase.com/docs/obd-cn/V1.2.0/10000000000017237.
#再次扩容磁盘后镜像启动成功
[root@oceanbase ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/centos-root00 172G 73G 99G 43% /
devtmpfs 7.8G 0 7.8G 0% /dev
tmpfs 7.8G 0 7.8G 0% /dev/shm
tmpfs 7.8G 9.1M 7.8G 1% /run
tmpfs 7.8G 0 7.8G 0% /sys/fs/cgroup
/dev/sda1 1014M 145M 870M 15% /boot
tmpfs 1.6G 0 1.6G 0% /run/user/0
overlay 172G 73G 99G 43% /var/lib/docker/overlay2/a443cf898c6e09a3919a444fc2179287e5544c4647e54c0261183618f90a0057/merged
[root@oceanbase ~]# docker ps -a
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
cd87ac2364dd oceanbase/oceanbase-ce "/bin/sh -c _boot" 7 minutes ago Up 2 minutes 0.0.0.0:2881->2881/tcp, :::2881->2881/tcp obstandalone
[root@oceanbase ~]# docker logs obstandalone| tail -10
/root/boot/_boot: line 11: 1: command not found
/root/boot/_boot: line 25: 1: command not found
/root/boot/_boot: line 11: 1: command not found
/root/boot/_boot: line 25: 1: command not found
Open ssh connection ok
Connect to observer ok
Create tenant test ok
start ob cluster ...
Get local repositories and plugins ok
Open ssh connection ok
Load cluster param plugin ok
Cluster status check ok
Deploy "obcluster" is running
boot success!
[root@oceanbase ~]#
#本镜像共使用磁盘71G
#排查镜像使用磁盘率过高问题
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
可以看到block_file占用磁盘过高，通过排查配置文件发现在配置文件中默认大小设置为70G。
四、obd启停数据库
#查看集群状态
[root@cd87ac2364dd /]# obd cluster list
+------------------------------------------------------------+
| Cluster List |
+-----------+------------------------------+-----------------+
| Name | Configuration Path | Status (Cached) |
+-----------+------------------------------+-----------------+
| obcluster | /root/.obd/cluster/obcluster | running |
+-----------+------------------------------+-----------------+
#关闭ob集群
[root@cd87ac2364dd /]# obd cluster stop obcluster
Get local repositories and plugins ok
Open ssh connection ok
Stop observer ok
obcluster stopped
[root@cd87ac2364dd /]# obd cluster list
+------------------------------------------------------------+
| Cluster List |
+-----------+------------------------------+-----------------+
| Name | Configuration Path | Status (Cached) |
+-----------+------------------------------+-----------------+
| obcluster | /root/.obd/cluster/obcluster | stopped |
+-----------+------------------------------+-----------------+
#启动ob集群
[root@cd87ac2364dd /]# obd cluster start obcluster
Get local repositories and plugins ok
Open ssh connection ok
Load cluster param plugin ok
Check before start observer ok
[WARN] (127.0.0.1) The recommended value of fs.aio-max-nr is 1048576 (Current value: 65536)
[WARN] (127.0.0.1) The recommended number of open files is 655350 (Current value: 65536)
Start observer ok
observer program health check ok
Connect to observer ok
Wait for observer init ok
+---------------------------------------------+
| observer |
+-----------+---------+------+-------+--------+
| ip | version | port | zone | status |
+-----------+---------+------+-------+--------+
| 127.0.0.1 | 3.1.3 | 2881 | zone1 | active |
+-----------+---------+------+-------+--------+
obcluster running
#登陆ob数据库
[root@cd87ac2364dd /]# obclient -h127.1 -uroot -P2881
Welcome to the OceanBase. Commands end with ; or \g.
Your MySQL connection id is 3221487678
Server version: 5.7.25 OceanBase 3.1.3 (r10000292022032916-3d79cacb37012cf61b7cb8faf00d9a6bb152bcd1) (Built Mar 29 2022 08:20:39)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| oceanbase |
| information_schema |
| mysql |
| SYS |
| LBACSYS |
| ORAAUDITOR |
| test |
+--------------------+
7 rows in set (0.002 sec)
MySQL [(none)]>
#
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
五、创建基础数据信息
先创建unti在创建资源池，基于资源池创建用户
#创建用户
MySQL [(none)]> alter resource unit sys_unit_config min_cpu=5;
Query OK, 0 rows affected (0.010 sec)
MySQL [(none)]> CREATE resource unit S4C1G max_cpu=4, min_cpu=4, max_memory='1G', min_memory='1G', max_iops=10000, min_iops=1000, max_session_num=1000000, max_disk_size='1024G';
Query OK, 0 rows affected (0.005 sec)
MySQL [(none)]> CREATE resource pool my_pool unit = 'S4C1G', unit_num = 1;
ERROR 4624 (HY000): machine resource 'zone1' is not enough to hold a new unit
#提示创建资源池失败，查看当前已经创建的unit资源分配情况
MySQL [(none)]> select * from oceanbase.__all_unit_config;
+----------------------------+----------------------------+----------------+-----------------+---------+---------+------------+------------+----------+----------+---------------+---------------------+
| gmt_create | gmt_modified | unit_config_id | name | max_cpu | min_cpu | max_memory | min_memory | max_iops | min_iops | max_disk_size | max_session_num |
+----------------------------+----------------------------+----------------+-----------------+---------+---------+------------+------------+----------+----------+---------------+---------------------+
| 2022-04-27 00:00:16.423323 | 2022-04-27 00:31:19.445209 | 1 | sys_unit_config | 5 | 2 | 2254857830 | 1879048192 | 10000 | 5000 | 75161927680 | 9223372036854775807 |
| 2022-04-27 00:00:21.823451 | 2022-04-27 00:00:21.823451 | 1001 | test_unit | 9 | 9 | 5261334938 | 5261334938 | 128 | 128 | 75161927680 | 64 |
| 2022-04-27 00:30:06.224322 | 2022-04-27 00:30:06.224322 | 1002 | S4C1G | 4 | 4 | 1073741824 | 1073741824 | 10000 | 1000 | 1099511627776 | 1000000 |
+----------------------------+----------------------------+----------------+-----------------+---------+---------+------------+------------+----------+----------+---------------+---------------------+
3 rows in set (0.001 sec)
MySQL [(none)]> select tenant_id,tenant_name,max_cpu,min_cpu,max_memory/1024/1024/1024 from oceanbase.v$unit;
+-----------+-------------+---------+---------+---------------------------+
| tenant_id | tenant_name | max_cpu | min_cpu | max_memory/1024/1024/1024 |
+-----------+-------------+---------+---------+---------------------------+
| 1 | sys | 5 | 2 | 2.099999999627 |
| 1001 | test | 9 | 9 | 4.900000000372 |
+-----------+-------------+---------+---------+---------------------------+
2 rows in set (0.006 sec)
MySQL [(none)]> select zone,cpu_total,cpu_assigned,mem_total/1024/1024/1024,mem_assigned/1024/1024/1024 from oceanbase.__all_virtual_server_stat;
+-------+-----------+--------------+--------------------------+-----------------------------+
| zone | cpu_total | cpu_assigned | mem_total/1024/1024/1024 | mem_assigned/1024/1024/1024 |
+-------+-----------+--------------+--------------------------+-----------------------------+
| zone1 | 14 | 11 | 7.000000000000 | 6.650000000372 |
+-------+-----------+--------------+--------------------------+-----------------------------+
1 row in set (0.002 sec)
MySQL [(none)]> select unit_config_name,resource_pool_name,tenant_name from oceanbase.v$unit;
+------------------+--------------------+-------------+
| unit_config_name | resource_pool_name | tenant_name |
+------------------+--------------------+-------------+
| sys_unit_config | sys_pool | sys |
| test_unt | test_pool | |
+------------------+--------------------+-------------+
1 row in set (0.005 sec)
#从动态视图可以看到test_pool资源池使用了test_unit，占用资源较多，因为是测试库，我们将test_pool删除后重新创建新的资源池
MySQL [(none)]> drop resource pool test_pool;
Query OK, 0 rows affected (0.004 sec)
#如果此时tenant_name中已分配用户，需要先将用户删除
MySQL [(none)]> drop tenant obmysql;
ERROR 1235 (0A000): should drop tenant force, delay drop tenant not supported
#删除tenant语法需增加force
MySQL [(none)]> drop tenant obmysql force;
Query OK, 0 rows affected (0.017 sec)
#重新创建资源池
MySQL [(none)]> CREATE resource pool my_pool unit = 'S4C1G', unit_num = 1;
Query OK, 0 rows affected (0.011 sec)
#创建tenant
MySQL [(none)]> create tenant obmysql resource_pool_list=('my_pool'), primary_zone='RANDOM',comment 'mysql tenant/instance', charset='utf8' set ob_tcp_invited_nodes='%', ob_compatibility_mode='mysql';
Query OK, 0 rows affected (0.501 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
使用新tenant创建业务账户与数据表
#使用新用户登陆
[root@cd87ac2364dd log]# obclient -h 127.1 -uroot@obmysql -P2881
Welcome to the OceanBase. Commands end with ; or \g.
Your MySQL connection id is 3221488244
Server version: 5.7.25 OceanBase 3.1.3 (r10000292022032916-3d79cacb37012cf61b7cb8faf00d9a6bb152bcd1) (Built Mar 29 2022 08:20:39)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| oceanbase |
| information_schema |
| mysql |
| test |
+--------------------+
4 rows in set (0.002 sec)
#删除并创建数据库
MySQL [(none)]> drop database test;
Query OK, 0 rows affected (0.017 sec)
MySQL [(none)]> create database test;
Query OK, 1 row affected (0.015 sec)
#新建业务账户
MySQL [(none)]> create user 'test' identified by 'test';
Query OK, 0 rows affected (0.019 sec)
MySQL [(none)]> grant all on test.* to 'test' with grant option;
Query OK, 0 rows affected (0.010 sec)
#通过新的业务账户创建表
[root@cd87ac2364dd init_sql]# obclient -h 127.1 -utest@obmysql -P2881 -ptest
Welcome to the OceanBase. Commands end with ; or \g.
Your MySQL connection id is 3221488300
Server version: 5.7.25 OceanBase 3.1.3 (r10000292022032916-3d79cacb37012cf61b7cb8faf00d9a6bb152bcd1) (Built Mar 29 2022 08:20:39)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| test |
+--------------------+
2 rows in set (0.002 sec)
MySQL [(none)]> use test
Database changed
MySQL [test]> create table t (id int);
Query OK, 0 rows affected (0.047 sec)
MySQL [test]> insert into t values (10);
Query OK, 1 row affected (0.008 sec)
MySQL [test]> commit;
Query OK, 0 rows affected (0.000 sec)
MySQL [test]> select * from t;
+------+
| id |
+------+
| 10 |
+------+
1 row in set (0.002 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
总结
官方镜像使用磁盘较大，对资源紧张的同学有点不友好，通过排查猜测是镜像中配置文件初始化大小太大，而且测试镜像中资源池配置过高导致新建资源池无法成功，需要官方优化下镜像。
文章知识点与官方知识档案匹配，可进一步学习相关知识