目录
- 一、 [实践练习一（必选）：OceanBase Docker 体验]
- 二、 [实践练习二（必选）：手动部署 OceanBase 集群（单副本）]
- 三、 [实践练习三（可选）]
- 四、[实践练习四（必选）：迁移 MySQL 数据到 OceanBase 集群]
- 五、 [实践练习五（可选）]
- 六、[实践练习六（必选）：查看 OceanBase 执行计划]
一、 [实践练习一（必选）：OceanBase Docker 体验]
（https://ask.oceanbase.com/t/topic/13700695)
1.1 个人环境信息
| 项目 | 描述 |
|---|---|
| 系统 | [root@OS3 ~]# lsb_release -d Description: CentOS Linux release 7.9.2009 (Core) [root@OS3 ~]# uname -a Linux OS3 3.10.0-1160.el7.x86_64 #1 SMP Mon Oct 19 16:18:59 UTC 2020 x86_64 x86_64 x86_64 GNU/Linux |
| CPU | 4 C |
| 内存 | 10 GB |
| 磁盘存储空间 | 50 GB |
| 文件系统 | XFS |
1.2 前置条件
1.2.1 下载安装OBD
- 安装 yum 附加工具 yum-utils
sudo yum install -y yum-utils
- 向 yum 添加新的仓库
sudo yum-config-manager --add-repo https://mirrors.aliyun.com/oceanbase/OceanBase.repo
- 安装 ob-deploy
sudo yum install -y ob-deploy
1.2.2 docker 部署
- yum 安装 docker
yum install -y docker
- 检查 docker 安装情况
yum list installed |grep docker
- 查看 docker 版本
docker -v
- 配置 docker 阿里云镜像加速
vim /etc/docker/daemon.json
{
"registry-mirrors": [
"https://ung2thfc.mirror.aliyuncs.com"
]
}
- 重载 deamon
systemctl daemon-reload
- 重启并查看 docker 服务
systemctl restart docker
systemctl status docker
1.3 使用 docker 部署 OceanBase 数据库
1.3.1 搜索、拉取 OceanBase 镜像
docker search oceanbase|head -n 4 --搜索
docker pull oceanbase/oceanbase-ce --拉取最新镜像
1.3.2 启动 OceannBase 数据库实例
- 部署 mini 独立实例
docker run -p 2881:2881 --name obstandalone -e MINI_MODE=1 -d oceanbase/oceanbase-ce
查看日志
docker logs obstandalone |tail -1
1.4 使用 OBD 完成 OceanBase 集群部署
1.4.1 下载安装 all-in-one
- 下载 all-in-one 安装包
wget https://obbusiness-private.oss-cn-shanghai.aliyuncs.com/download-center/opensource/oceanbase-all-in-one/7/x86_64/oceanbase-all-in-one-4.2.2.0-100010012024022719.el7.x86_64.tar.gz
- 安装 all-in-one
tar -xzf oceanbase-all-in-one-*.tar.gz
cd oceanbase-all-in-one/bin/
./install.sh
source ~/.oceanbase-all-in-one/bin/env.sh
1.4.2 部署单机 OceanBase 数据库
部署社区版 OB
obd demo
1.5 连接 OceanBase 数据库实例
1.5.1 连接方式 1（OBClient，ob-mysql）
1.5.1.1 docker
拉取的 docker oceanbase-ce 镜像安装了 Oceanbase 数据库客户端 OBClient ，并提供了默认的连接脚本 ob-mysql
使用 root 用户登录集群的 sys 租户
docker exec -it obstandalone ob-mysql sys
使用 root 用户登录集群的 root 租户
docker exec -it obstandalone ob-mysql root
使用 test 用户登录集群的 test 租户
docker exec -it obstandalone ob-mysql test
1.5.1.2 obd demo
2881 直连
obclient -h 127.0.0.1 -P 2881 -uroot@sys -Doceanbase -A -e 'show databases;'
ODP 代理访问
obclient -h 127.0.0.1 -P 2883 -uroot@sys -Doceanbase -A -e 'show databases;'
1.5.2 连接方式 2（mysql）
1.5.2.1 docker
mysql -uroot@sys -h127.1 -P2881 -e "show databases;"
mysql -uroot -h127.1 -P2881 -e "show databases;"
mysql -uroot@test -h127.1 -P2881 -e "show databases;"
1.5.2.2 obd demo
2881 直连
mysql -h 127.0.0.1 -P 2881 -uroot@sys -Doceanbase -A -e 'show databases;'
ODP 代理访问
mysql -h 127.0.0.1 -P 2883 -uroot@sys -Doceanbase -A -e 'show databases;'
1.6 创建业务租户、业务数据库、表(操作均在 demo 下完成)
1.6.1 创建租户
1.6.1.1 创建资源规格
# root 用户登录sys租户
[root@localhost ~]# obclient -h 127.0.0.1 -P 2881 -uroot@sys -A
Warning: World-writable config file '/etc/my.cnf' is ignored
Welcome to the OceanBase. Commands end with ; or \g.
Your OceanBase connection id is 3221705255
Server version: OceanBase_CE 4.2.2.0 (r100010012024022719-c984fe7cb7a4cef85a40323a0d073f0c9b7b8235) (Built Feb 27 2024 19:20:54)
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.
# 进入 oceanbase 数据库
obclient [(none)]> use oceanbase;
Database changed
# 查看 DBA_OB_UNIT_CONFIGS 资源规格信息相关视图
obclient [oceanbase]> select * from oceanbase.DBA_OB_UNIT_CONFIGS;
+----------------+-----------------+----------------------------+----------------------------+---------+---------+-------------+---------------+---------------------+---------------------+-------------+
| UNIT_CONFIG_ID | NAME | CREATE_TIME | MODIFY_TIME | MAX_CPU | MIN_CPU | MEMORY_SIZE | LOG_DISK_SIZE | MAX_IOPS | MIN_IOPS | IOPS_WEIGHT |
+----------------+-----------------+----------------------------+----------------------------+---------+---------+-------------+---------------+---------------------+---------------------+-------------+
| 1 | sys_unit_config | 2024-03-12 11:02:16.053862 | 2024-03-12 11:02:16.053862 | 3 | 3 | 1073741824 | 2147483648 | 9223372036854775807 | 9223372036854775807 | 3 |
+----------------+-----------------+----------------------------+----------------------------+---------+---------+-------------+---------------+---------------------+---------------------+-------------+
1 row in set (0.002 sec)
# 创建资源规格 OBRU
obclient [oceanbase]> create resource unit OBRU memory_size = '4G', MAX_CPU = 1, MIN_CPU = 1, LOG_DISK_SIZE = '2G';
Query OK, 0 rows affected (0.006 sec)
obclient [oceanbase]> select * from oceanbase.DBA_OB_UNIT_CONFIGS;
+----------------+-----------------+----------------------------+----------------------------+---------+---------+-------------+---------------+---------------------+---------------------+-------------+
| UNIT_CONFIG_ID | NAME | CREATE_TIME | MODIFY_TIME | MAX_CPU | MIN_CPU | MEMORY_SIZE | LOG_DISK_SIZE | MAX_IOPS | MIN_IOPS | IOPS_WEIGHT |
+----------------+-----------------+----------------------------+----------------------------+---------+---------+-------------+---------------+---------------------+---------------------+-------------+
| 1 | sys_unit_config | 2024-03-12 11:02:16.053862 | 2024-03-12 11:02:16.053862 | 3 | 3 | 1073741824 | 2147483648 | 9223372036854775807 | 9223372036854775807 | 3 |
| 1003 | OBRU | 2024-03-12 14:38:56.994323 | 2024-03-12 14:38:56.994323 | 1 | 1 | 4294967296 | 2147483648 | 9223372036854775807 | 9223372036854775807 | 1 |
+----------------+-----------------+----------------------------+----------------------------+---------+---------+-------------+---------------+---------------------+---------------------+-------------+
2 rows in set (0.001 sec)
1.6.1.2 创建资源池
# 查看 DBA_OB_RESOURCE_POOLS 资源池配置信息相关视图
obclient [oceanbase]> select * from oceanbase.DBA_OB_RESOURCE_POOLS;
+------------------+----------+-----------+----------------------------+----------------------------+------------+----------------+-----------+--------------+
| RESOURCE_POOL_ID | NAME | TENANT_ID | CREATE_TIME | MODIFY_TIME | UNIT_COUNT | UNIT_CONFIG_ID | ZONE_LIST | REPLICA_TYPE |
+------------------+----------+-----------+----------------------------+----------------------------+------------+----------------+-----------+--------------+
| 1 | sys_pool | 1 | 2024-03-12 11:02:16.056709 | 2024-03-12 11:02:16.061550 | 1 | 1 | zone1 | FULL |
+------------------+----------+-----------+----------------------------+----------------------------+------------+----------------+-----------+--------------+
1 row in set (0.002 sec)
# 创建资源池 OBRP
obclient [oceanbase]> create resource pool OBRP UNIT='OBRU', UNIT_NUM=1, ZONE_LIST=('zone1');
Query OK, 0 rows affected (0.016 sec)
obclient [oceanbase]> select * from oceanbase.DBA_OB_RESOURCE_POOLS;
+------------------+----------+-----------+----------------------------+----------------------------+------------+----------------+-----------+--------------+
| RESOURCE_POOL_ID | NAME | TENANT_ID | CREATE_TIME | MODIFY_TIME | UNIT_COUNT | UNIT_CONFIG_ID | ZONE_LIST | REPLICA_TYPE |
+------------------+----------+-----------+----------------------------+----------------------------+------------+----------------+-----------+--------------+
| 1 | sys_pool | 1 | 2024-03-12 11:02:16.056709 | 2024-03-12 11:02:16.061550 | 1 | 1 | zone1 | FULL |
| 1001 | OBRP | NULL | 2024-03-12 14:42:55.650878 | 2024-03-12 14:42:55.650878 | 1 | 1003 | zone1 | FULL |
+------------------+----------+-----------+----------------------------+----------------------------+------------+----------------+-----------+--------------+
2 rows in set (0.000 sec)
1.6.1.3 创建租户
# 查看 DBA_OB_TENANTS 所有租户信息视图
obclient [oceanbase]> select * from oceanbase.DBA_OB_TENANTS;
+-----------+-------------+-------------+----------------------------+----------------------------+--------------+---------------+-------------------+--------------------+--------+---------------+--------+-------------+-------------------+------------------+----------+----------------+--------------+--------------------+--------------+----------------------------+----------+------------+-----------+
| TENANT_ID | TENANT_NAME | TENANT_TYPE | CREATE_TIME | MODIFY_TIME | PRIMARY_ZONE | LOCALITY | PREVIOUS_LOCALITY | COMPATIBILITY_MODE | STATUS | IN_RECYCLEBIN | LOCKED | TENANT_ROLE | SWITCHOVER_STATUS | SWITCHOVER_EPOCH | SYNC_SCN | REPLAYABLE_SCN | READABLE_SCN | RECOVERY_UNTIL_SCN | LOG_MODE | ARBITRATION_SERVICE_STATUS | UNIT_NUM | COMPATIBLE | MAX_LS_ID |
+-----------+-------------+-------------+----------------------------+----------------------------+--------------+---------------+-------------------+--------------------+--------+---------------+--------+-------------+-------------------+------------------+----------+----------------+--------------+--------------------+--------------+----------------------------+----------+------------+-----------+
| 1 | sys | SYS | 2024-03-12 11:02:16.071289 | 2024-03-12 11:02:16.071289 | RANDOM | FULL{1}@zone1 | NULL | MYSQL | NORMAL | NO | NO | PRIMARY | NORMAL | 0 | NULL | NULL | NULL | NULL | NOARCHIVELOG | DISABLED | 1 | 4.2.2.0 | 1 |
+-----------+-------------+-------------+----------------------------+----------------------------+--------------+---------------+-------------------+--------------------+--------+---------------+--------+-------------+-------------------+------------------+----------+----------------+--------------+--------------------+--------------+----------------------------+----------+------------+-----------+
1 row in set (0.005 sec)
# 创建租户 OBTN
obclient [oceanbase]> create tenant if not exists OBTN PRIMARY_ZONE='zone1', RESOURCE_POOL_LIST=('OBRP') set OB_TCP_INVITED_NODES='%';
Query OK, 0 rows affected (25.890 sec)
obclient [oceanbase]> select * from oceanbase.DBA_OB_TENANTS;
+-----------+-------------+-------------+----------------------------+----------------------------+--------------+---------------+-------------------+--------------------+--------+---------------+--------+-------------+-------------------+------------------+---------------------+---------------------+---------------------+---------------------+--------------+----------------------------+----------+------------+-----------+
| TENANT_ID | TENANT_NAME | TENANT_TYPE | CREATE_TIME | MODIFY_TIME | PRIMARY_ZONE | LOCALITY | PREVIOUS_LOCALITY | COMPATIBILITY_MODE | STATUS | IN_RECYCLEBIN | LOCKED | TENANT_ROLE | SWITCHOVER_STATUS | SWITCHOVER_EPOCH | SYNC_SCN | REPLAYABLE_SCN | READABLE_SCN | RECOVERY_UNTIL_SCN | LOG_MODE | ARBITRATION_SERVICE_STATUS | UNIT_NUM | COMPATIBLE | MAX_LS_ID |
+-----------+-------------+-------------+----------------------------+----------------------------+--------------+---------------+-------------------+--------------------+--------+---------------+--------+-------------+-------------------+------------------+---------------------+---------------------+---------------------+---------------------+--------------+----------------------------+----------+------------+-----------+
| 1 | sys | SYS | 2024-03-12 11:02:16.071289 | 2024-03-12 11:02:16.071289 | RANDOM | FULL{1}@zone1 | NULL | MYSQL | NORMAL | NO | NO | PRIMARY | NORMAL | 0 | NULL | NULL | NULL | NULL | NOARCHIVELOG | DISABLED | 1 | 4.2.2.0 | 1 |
| 1001 | META$1002 | META | 2024-03-12 14:53:58.676873 | 2024-03-12 14:54:12.225587 | zone1 | FULL{1}@zone1 | NULL | MYSQL | NORMAL | NO | NO | PRIMARY | NORMAL | 0 | NULL | NULL | NULL | NULL | NOARCHIVELOG | DISABLED | 1 | 4.2.2.0 | 1 |
| 1002 | OBTN | USER | 2024-03-12 14:53:58.681824 | 2024-03-12 14:54:12.257827 | zone1 | FULL{1}@zone1 | NULL | MYSQL | NORMAL | NO | NO | PRIMARY | NORMAL | 0 | 1710226536293219002 | 1710226536293219002 | 1710226536293219001 | 4611686018427387903 | NOARCHIVELOG | DISABLED | 1 | 4.2.2.0 | 1001 |
+-----------+-------------+-------------+----------------------------+----------------------------+--------------+---------------+-------------------+--------------------+--------+---------------+--------+-------------+-------------------+------------------+---------------------+---------------------+---------------------+---------------------+--------------+----------------------------+----------+------------+-----------+
3 rows in set (0.025 sec)
1.6.1.4 登录租户 OBTN 的 ROOT 用户
# 登录 OBTN 的 ROOT 用户
# 区分大小写
obclient -h 127.0.0.1 -P 2881 -uroot@OBTN -A
Warning: World-writable config file '/etc/my.cnf' is ignored
Welcome to the OceanBase. Commands end with ; or \g.
Your OceanBase connection id is 3221745508
Server version: OceanBase_CE 4.2.2.0 (r100010012024022719-c984fe7cb7a4cef85a40323a0d073f0c9b7b8235) (Built Feb 27 2024 19:20:54)
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 [(none)]>
1.6.2 创建数据库、表
obclient -h 127.0.0.1 -P 2881 -uroot@OBTN -A
Warning: World-writable config file '/etc/my.cnf' is ignored
Welcome to the OceanBase. Commands end with ; or \g.
Your OceanBase connection id is 3221745508
Server version: OceanBase_CE 4.2.2.0 (r100010012024022719-c984fe7cb7a4cef85a40323a0d073f0c9b7b8235) (Built Feb 27 2024 19:20:54)
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 [(none)]> create database testtest;
Query OK, 1 row affected (0.034 sec)
obclient [(none)]> create table testtest.t1 (id int);
Query OK, 0 rows affected (0.110 sec)
obclient [(none)]> insert into testtest.t1 values(1);
Query OK, 1 row affected (0.013 sec)
obclient [(none)]> select * from testtest.t1;
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.000 sec)
1.7 踩坑
在 《部署 mini 独立实例》 步骤提示，最终重新配置虚拟机存储空间才得以解决
报错，提示空间不足
新加磁盘并挂载到 /OB 目录下，然后铲掉旧容器
docker run -p 2881:2881 --name obstandalone -e MINI_MODE=1 -d oceanbase/oceanbase-ce
[root@OS3 ~]# docker logs obstandalone |tail -1
deploy failed!
--铲掉旧容器
docker ps -a
docker stop obstandalone
docker rm obstandalone 或者 docker rename obstandalone new_obstandalone
重新部署 mini 独立实例，并指定容器所在目录为 /OB
- oceanbase-ce容器有两个主要目录：
- /root/ob
OB的启动目录，内有log、datafile以及clog文件； - /root/boot
存放配置文件，容器启动时会在/root/boot下找配置文件，如果没有，就会认为还没有部署过。第一次启动不能挂载/root/boot。
- /root/ob
docker run -p 2881:2881 --name obstandalone -e MINI_MODE=1 -v /OB:/root/ob -d oceanbase/oceanbase-ce
还是报错
提示
[ERROR] OBD-1002: Fail to init 127.0.0.1 home path: /root/ob is not empty.
不知道咋处理了 。直接要清理Docker数据并重新部署
-
停止所有运行中的容器:
docker stop $(docker ps -aq) -
删除所有容器 (包括运行中和停止的):
docker rm $(docker ps -aq) -
删除所有Docker镜像:
docker rmi $(docker images -q) -
删除所有Docker卷 (请确保备份重要数据):
docker volume rm $(docker volume ls -q) -
清理未使用的Docker资源:
docker system prune
第三次部署 mini 独立实例
docker run -p 2881:2881 --name obstandalone -e MINI_MODE=1 -v /OB:/root/ob -d oceanbase/oceanbase-ce
同样的错误
[ERROR] OBD-1002: Fail to init 127.0.0.1 home path: /root/ob is not empty.
二、 [实践练习二（必选）：手动部署 OceanBase 集群（单副本）]
(https://ask.oceanbase.com/t/topic/13700696)
参考文章:实战教程第二章2.11：（高级）如何手动部署 OceanBase 集群
2.1 个人环境信息
练习二、四、六均为此环境
| 项目 | 描述 |
|---|---|
| 系统 | [root@OS3 ~]# lsb_release -d Description: CentOS Linux release 7.9.2009 (Core) |
| CPU | 4 C |
| 内存 | 10 GB |
由于报错 SLOG and datafile must be on the same disk，已将所有路径设在/redo盘上 | |
| 日志 /redo | /dev/sdb1 80 GB日志盘(最低要求内存的4倍以上) |
| 文件系统 | XFS |
2.2 前置条件
2.2.1 下载数据库、依赖库
# 创建rpm包存放目录
mkdir /root/ob-ce
cd /root/ob-ce
# OceanBase 数据库
[root@localhost ob-ce]# wget https://obbusiness-private.oss-cn-shanghai.aliyuncs.com/download-center/opensource/observer/v4.2.2_CE_HF1/oceanbase-ce-4.2.2.0-100010012024022719.el7.x86_64.rpm
--2024-03-12 04:16:20-- https://obbusiness-private.oss-cn-shanghai.aliyuncs.com/download-center/opensource/observer/v4.2.2_CE_HF1/oceanbase-ce-4.2.2.0-100010012024022719.el7.x86_64.rpm
Resolving obbusiness-private.oss-cn-shanghai.aliyuncs.com (obbusiness-private.oss-cn-shanghai.aliyuncs.com)... 47.101.83.171
Connecting to obbusiness-private.oss-cn-shanghai.aliyuncs.com (obbusiness-private.oss-cn-shanghai.aliyuncs.com)|47.101.83.171|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 105720340 (101M) [application/x-redhat-package-manager]
Saving to: ‘oceanbase-ce-4.2.2.0-100010012024022719.el7.x86_64.rpm’
100%[=================================================================================================================================================================================================>] 105,720,340 2.56MB/s in 31s
2024-03-12 04:16:51 (3.29 MB/s) - ‘oceanbase-ce-4.2.2.0-100010012024022719.el7.x86_64.rpm’ saved [105720340/105720340]
# OceanBase Libs 依赖库
[root@localhost ob-ce]# wget https://obbusiness-private.oss-cn-shanghai.aliyuncs.com/download-center/opensource/observer/v4.2.2_CE_HF1/oceanbase-ce-libs-4.2.2.0-100010012024022719.el7.x86_64.rpm
--2024-03-12 04:17:12-- https://obbusiness-private.oss-cn-shanghai.aliyuncs.com/download-center/opensource/observer/v4.2.2_CE_HF1/oceanbase-ce-libs-4.2.2.0-100010012024022719.el7.x86_64.rpm
Resolving obbusiness-private.oss-cn-shanghai.aliyuncs.com (obbusiness-private.oss-cn-shanghai.aliyuncs.com)... 47.101.83.171
Connecting to obbusiness-private.oss-cn-shanghai.aliyuncs.com (obbusiness-private.oss-cn-shanghai.aliyuncs.com)|47.101.83.171|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 158336 (155K) [application/x-redhat-package-manager]
Saving to: ‘oceanbase-ce-libs-4.2.2.0-100010012024022719.el7.x86_64.rpm’
100%[=================================================================================================================================================================================================>] 158,336 --.-K/s in 0.05s
2024-03-12 04:17:13 (2.92 MB/s) - ‘oceanbase-ce-libs-4.2.2.0-100010012024022719.el7.x86_64.rpm’ saved [158336/158336]
2.3 安装 OceanBase 软件包
直接安装 ob 数据库缺少依赖库
安装 lib 提示缺少 jq
安装 jq 提示 nothing to do
需要先安装 epel-release
然后再装 jq
因为我是 root 来操作，最后安装 ob 数据库时，会提示没有 admin 。
且软件默认的安装目录依旧是 /home/admin/oceanbase
[root@localhost ob-ce]# rpm -ivh oceanbase-ce-4.2.2.0-100010012024022719.el7.x86_64.rpm
error: Failed dependencies:
jq is needed by oceanbase-ce-4.2.2.0-100010012024022719.el7.x86_64
libmariadb.so.3()(64bit) is needed by oceanbase-ce-4.2.2.0-100010012024022719.el7.x86_64
libmariadb.so.3(libmysqlclient_18)(64bit) is needed by oceanbase-ce-4.2.2.0-100010012024022719.el7.x86_64
[root@localhost ob-ce]# rpm -ivh oceanbase-ce-libs-4.2.2.0-100010012024022719.el7.x86_64.rpm
error: Failed dependencies:
jq is needed by oceanbase-ce-libs-4.2.2.0-100010012024022719.el7.x86_64
# yum -y install epel-release
...
Downloading packages:
epel-release-7-11.noarch.rpm | 15 kB 00:00:00
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : epel-release-7-11.noarch 1/1
Verifying : epel-release-7-11.noarch 1/1
Installed:
epel-release.noarch 0:7-11
Complete!
# yum -y install jq
...
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : oniguruma-6.8.2-2.el7.x86_64 1/2
Installing : jq-1.6-2.el7.x86_64 2/2
Verifying : oniguruma-6.8.2-2.el7.x86_64 1/2
Verifying : jq-1.6-2.el7.x86_64 2/2
Installed:
jq.x86_64 0:1.6-2.el7
Dependency Installed:
oniguruma.x86_64 0:6.8.2-2.el7
Complete!
[root@localhost ob-ce]# rpm -ivh oceanbase-ce-libs-4.2.2.0-100010012024022719.el7.x86_64.rpm
Preparing... ################################# [100%]
Updating / installing...
1:oceanbase-ce-libs-4.2.2.0-1000100warning: user admin does not exist - using root
warning: group admin does not exist - using root
warning: user admin does not exist - using root
################################# [100%]
[root@localhost ob-ce]# rpm -ivh oceanbase-ce-4.2.2.0-100010012024022719.el7.x86_64.rpm
Preparing... ################################# [100%]
Updating / installing...
1:oceanbase-ce-4.2.2.0-100010012024warning: user admin does not exist - using root
warning: group admin does not exist - using root
warning: user admin does not exist - using root
################################# [100%]
warning: user admin does not exist - using root
warning: group admin does not exist - using root
execute post install script
2.4清理目录数据
初次部署不用执行这一步
用于部署失败后重新部署时，需要清空目录
kill -9 `pidof observer`
/bin/rm -rf /home/admin/oceanbase/store/obdemo/*/*
2.5 初始化数据目录
初次部署需要创建，重复部署时，如果目录已存在则不用在执行这一步、
手动部署需要手动创建目录
mkdir -p /home/admin/oceanbase/store/obdemo /redo/obdemo/{sstable,etc3,clog,ilog,slog,etc2}
for f in {clog,ilog,slog,etc2}; do ln -s /redo/obdemo/$f /home/admin/oceanbase/store/obdemo/$f ; done
[root@localhost oceanbase]# tree /home/admin/oceanbase/store/ /data/ /redo/
/home/admin/oceanbase/store/
└── obdemo
├── clog -> /redo/obdemo/clog
├── etc2 -> /redo/obdemo/etc2
├── etc3 -> /redo/obdemo/etc3
├── ilog -> /redo/obdemo/ilog
├── slog -> /redo/obdemo/slog
└── sstable -> /redo/obdemo/sstable
/data/
/redo/
└── obdemo
├── clog
├── etc2
├── etc3
├── ilog
├── slog
└── sstable
14 directories, 0 files
下面的目录结构有问题，需要改成上面的
mkdir -p /home/admin/oceanbase/store/obdemo /data/obdemo/{sstable,etc3} /redo/obdemo/{clog,ilog,slog,etc2}
for f in {sstable,etc3}; do ln -s /data/obdemo/$f /home/admin/oceanbase/store/obdemo/$f; done
[root@localhost obdemo]# tree /home/admin/oceanbase/store/ /data/ /redo/
/home/admin/oceanbase/store/
└── obdemo
├── clog -> /redo/obdemo/clog
├── etc2 -> /redo/obdemo/etc2
├── etc3 -> /data/obdemo/etc3
├── ilog -> /redo/obdemo/ilog
├── slog -> /redo/obdemo/slog
└── sstable -> /data/obdemo/sstable
/data/
└── obdemo
├── etc3
└── sstable
/redo/
└── obdemo
├── clog
├── etc2
├── ilog
└── slog
15 directories, 0 files
2.6 启动 OBSERVER 进程
正常多副本时， -r 要写所有 observer 进程服务器的ip:端口，
-z 每个服务器配置都不一样，如：
服务器1：zone1
服务器2：zone2
服务器3：zone3
本次部署为单副本，就一个节点，所以这俩参数写都只写一个就行-z zone1，-r '192.168.56.107:2882:2881'
echo 'export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/home/admin/oceanbase/lib' >> ~/.bash_profile
. ~/.bash_profile
cd /home/admin/oceanbase/
bin/observer -i enp0s8 -p 2881 -P 2882 -z zone1 -d /home/admin/oceanbase/store/obdemo -r '192.168.56.107:2882:2881' -c 20240312 -n obdemo -o "memory_limit=8G,cache_wash_threshold=1G,system_memory=3G,memory_chunk_cache_size=128M,cpu_count=4,net_thread_count=2,datafile_size=30G,stack_size=1536K,config_additional_dir=/data/obdemo/etc3;/redo/obdemo/etc2"
2.6.1 确认进程正常
- 查看日志
# 无ERR即可
tail -f observer.log|grep ERR
- 查看进程
# 有observer即可
[root@localhost ~]# ps -ef|grep ob
root 11035 1 8 22:14 ? 00:01:54 bin/observer -i enp0s8 -p 2881 -P 2882 -z zone1 -d /home/admin/oceanbase/store/obdemo -r 192.168.56.107:2882:2881 -c 20240312 -n obdemo -o memory_limit=8G,cache_wash_threshold=1G,system_memory=3G,memory_chunk_cache_size=128M,cpu_count=4,net_thread_count=2,datafile_size=30G,stack_size=1536K,config_additional_dir=/data/obdemo/etc3;/redo/obdemo/etc2
root 11525 5505 0 22:37 pts/2 00:00:00 grep --color=auto ob
- 查看端口
# 有2881，2882即可
[root@localhost oceanbase]# netstat -ntlp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 1089/sshd
tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 1402/master
tcp 0 0 0.0.0.0:2881 0.0.0.0:* LISTEN 11035/bin/observer
tcp 0 0 0.0.0.0:2882 0.0.0.0:* LISTEN 11035/bin/observer
tcp6 0 0 :::22 :::* LISTEN 1089/sshd
tcp6 0 0 ::1:25 :::* LISTEN 1402/master
2.7 集群自举（初始化）
[root@localhost oceanbase]# mysql -h192.168.56.107 -uroot -P2881 -p -c -A
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 3221228440
Server version: 5.7.25 OceanBase_CE 4.2.2.0 (r100010012024022719-c984fe7cb7a4cef85a40323a0d073f0c9b7b8235) (Built Feb 27 2024 19:20:54)
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)]> set session ob_query_timeout=1000000000; alter system bootstrap ZONE 'zone1' SERVER '192.168.56.107:2882';
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (43.94 sec)
MySQL [(none)]> exit
Bye
[root@localhost oceanbase]# mysql -h192.168.56.107 -uroot@sys -P2881 -p -c -A
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 3221490387
Server version: 5.7.25 OceanBase_CE 4.2.2.0 (r100010012024022719-c984fe7cb7a4cef85a40323a0d073f0c9b7b8235) (Built Feb 27 2024 19:20:54)
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 |
| LBACSYS |
| mysql |
| oceanbase |
| ORAAUDITOR |
| SYS |
| test |
+--------------------+
2.8 安装 OBPROXY
# 下载 OBPROXY （没找到 4.2.2 的 社区版 proxy ，下载个 4.2.3 的试试 ）
wget https://mirrors.aliyun.com/oceanbase/community/stable/el/7/x86_64/obproxy-ce-4.2.3.0-3.el7.x86_64.rpm?spm=a2c6h.25603864.0.0.19a5482402R50U
[root@localhost ~]# ls
anaconda-ks.cfg ob-ce obproxy-ce-4.2.3.0-3.el7.x86_64.rpm?spm=a2c6h.25603864.0.0.19a5482402R50U oceanbase-all-in-one oceanbase-all-in-one-4.2.2.0-100010012024022719.el7.x86_64.tar.gz
[root@localhost ~]# mv obproxy-ce-4.2.3.0-3.el7.x86_64.rpm\?spm\=a2c6h.25603864.0.0.19a5482402R50U obproxy-ce-4.2.3.0-3.el7.x86_64.rpm
# 安装 OBPROXY
[root@localhost ~]# rpm -ivh /root/obproxy-ce-4.2.3.0-3.el7.x86_64.rpm
warning: /root/obproxy-ce-4.2.3.0-3.el7.x86_64.rpm: Header V4 RSA/SHA1 Signature, key ID e9b4a7aa: NOKEY
Preparing... ################################# [100%]
Updating / installing...
1:obproxy-ce-4.2.3.0-3.el7 warning: user admin does not exist - using root
warning: group admin does not exist - using root
warning: user admin does not exist - using root
warning: group admin does not exist - using root
warning: user admin does not exist - using root
warning: group admin does not exist - using root
warning: user admin does not exist - using root
warning: group admin does not exist - using root
################################# [100%]
chown: invalid user: ‘admin:admin’
# 默认安装在 /home/admin 下
[root@localhost admin]# tree -L 1 /home/admin/
/home/admin/
├── obproxy-4.2.3.0
└── oceanbase
[root@localhost admin]# tree /home/admin/obproxy-4.2.3.0/
/home/admin/obproxy-4.2.3.0/
├── bin
│ ├── obproxy
│ └── obproxyd.sh
└── lib
└── libstdc++.so.6
2 directories, 3 files
2.9 启动 OBPROXY 进程
cd /home/admin/obproxy-4.2.3.0/
bin/obproxy -r "192.168.56.107:2881" -p 2883 -o "enable_strict_kernel_release=false,enable_cluster_checkout=false,enable_metadb_used=false" -c obdemo
2.9.1 确认 OBPROXY 进程正常
[root@localhost obproxy-4.2.3.0]# ps ef|grep obproxy
11812 pts/3 S+ 0:00 \_ tail -f obproxy.log XDG_SESSION_ID=4 HOSTNAME=localhost SELINUX_ROLE_REQUESTED= TERM=xterm SHELL=/bin/bash HISTSIZE=1000 SSH_CLIENT=192.168.56.1 63913 22 SELINUX_USE_CURRENT_RANGE= OBD_INSTALL_PRE=/root/.oceanbase-all-in-one/obd OLDPWD=/home/admin/obproxy-4.2.3.0 SSH_TTY=/dev/pts/3 USER=root LD_LIBRARY_PATH=:/home/admin/oceanbase/lib LS_COLORS=rs=0:di=01;34:ln=01;36:mh=00:pi=40;33:so=01;35:do=01;35:bd=40;33;01:cd=40;33;01:or=40;31;01:mi=01;05;37;41:su=37;41:sg=30;43:ca=30;41:tw=30;42:ow=34;42:st=37;44:ex=01;32:*.tar=01;31:*.tgz=01;31:*.arc=01;31:*.arj=01;31:*.taz=01;31:*.lha=01;31:*.lz4=01;31:*.lzh=01;31:*.lzma=01;31:*.tlz=01;31:*.txz=01;31:*.tzo=01;31:*.t7z=01;31:*.zip=01;31:*.z=01;31:*.Z=01;31:*.dz=01;31:*.gz=01;31:*.lrz=01;31:*.lz=01;31:*.lzo=01;31:*.xz=01;31:*.bz2=01;31:*.bz=01;31:*.tbz=01;31:*.tbz2=01;31:*.tz=01;31:*.deb=01;31:*.rpm=01;31:*.jar=01;31:*.war=01;31:*.ear=01;31:*.sar=01;31:*.rar=01;31:*.alz=01;31:*.ace=01;31:*.zoo=01;31:*.cpio=01;31:*.7z=01;31:*.rz=01;31:*.cab=01;31:*.jpg=01;35:*.jpeg=01;35:*.gif=01;35:*.bmp=01;35:*.pbm=01;35:*.pgm=01;35:*.ppm=01;35:*.tga=01;35:*.xbm=01;35:*.xpm=01;35:*.tif=01;35:*.tiff=01;35:*.png=01;35:*.svg=01;35:*.svgz=01;35:*.mng=01;35:*.pcx=01;35:*.mov=01;35:*.mpg=01;35:*.mpeg=01;35:*.m2v=01;35:*.mkv=01;35:*.webm=01;35:*.ogm=01;35:*.mp4=01;35:*.m4v=01;35:*.mp4v=01;35:*.vob=01;35:*.qt=01;35:*.nuv=01;35:*.wmv=01;35:*.asf=01;35:*.rm=01;35:*.rmvb=01;35:*.flc=01;35:*.avi=01;35:*.fli=01;35:*.flv=01;35:*.gl=01;35:*.dl=01;35:*.xcf=01;35:*.xwd=01;35:*.yuv=01;35:*.cgm=01;35:*.emf=01;35:*.axv=01;35:*.anx=01;35:*.ogv=01;35:*.ogx=01;35:*.aac=01;36:*.au=01;36:*.flac=01;36:*.mid=01;36:*.midi=01;36:*.mka=01;36:*.mp3=01;36:*.mpc=01;36:*.ogg=01;36:*.ra=01;36:*.wav=01;36:*.axa=01;36:*.oga=01;36:*.spx=01;36:*.xspf=01;36: MAIL=/var/spool/mail/root PATH=/root/.oceanbase-all-in-one/obd/usr/bin:/root/.oceanbase-all-in-one/obclient/u01/obclient/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin PWD=/home/admin/obproxy-4.2.3.0/log LANG=en_US.UTF-8 SELINUX_LEVEL_REQUESTED= HISTCONTROL=ignoredups SHLVL=1 HOME=/root LOGNAME=root SSH_CONNECTION=192.168.56.1 63913 192.168.56.107 22 LESSOPEN=||/usr/bin/lesspipe.sh %s OBCLIENT_HOME=/root/.oceanbase-all-in-one/obclient XDG_RUNTIME_DIR=/run/user/0 _=/usr/bin/tail
11820 pts/2 R+ 0:00 \_ ps ef XDG_SESSION_ID=4 HOSTNAME=localhost SELINUX_ROLE_REQUESTED= TERM=xterm SHELL=/bin/bash HISTSIZE=1000 SSH_CLIENT=192.168.56.1 63913 22 SELINUX_USE_CURRENT_RANGE= OBD_INSTALL_PRE=/root/.oceanbase-all-in-one/obd SSH_TTY=/dev/pts/2 USER=root LD_LIBRARY_PATH=:/home/admin/oceanbase/lib LS_COLORS=rs=0:di=01;34:ln=01;36:mh=00:pi=40;33:so=01;35:do=01;35:bd=40;33;01:cd=40;33;01:or=40;31;01:mi=01;05;37;41:su=37;41:sg=30;43:ca=30;41:tw=30;42:ow=34;42:st=37;44:ex=01;32:*.tar=01;31:*.tgz=01;31:*.arc=01;31:*.arj=01;31:*.taz=01;31:*.lha=01;31:*.lz4=01;31:*.lzh=01;31:*.lzma=01;31:*.tlz=01;31:*.txz=01;31:*.tzo=01;31:*.t7z=01;31:*.zip=01;31:*.z=01;31:*.Z=01;31:*.dz=01;31:*.gz=01;31:*.lrz=01;31:*.lz=01;31:*.lzo=01;31:*.xz=01;31:*.bz2=01;31:*.bz=01;31:*.tbz=01;31:*.tbz2=01;31:*.tz=01;31:*.deb=01;31:*.rpm=01;31:*.jar=01;31:*.war=01;31:*.ear=01;31:*.sar=01;31:*.rar=01;31:*.alz=01;31:*.ace=01;31:*.zoo=01;31:*.cpio=01;31:*.7z=01;31:*.rz=01;31:*.cab=01;31:*.jpg=01;35:*.jpeg=01;35:*.gif=01;35:*.bmp=01;35:*.pbm=01;35:*.pgm=01;35:*.ppm=01;35:*.tga=01;35:*.xbm=01;35:*.xpm=01;35:*.tif=01;35:*.tiff=01;35:*.png=01;35:*.svg=01;35:*.svgz=01;35:*.mng=01;35:*.pcx=01;35:*.mov=01;35:*.mpg=01;35:*.mpeg=01;35:*.m2v=01;35:*.mkv=01;35:*.webm=01;35:*.ogm=01;35:*.mp4=01;35:*.m4v=01;35:*.mp4v=01;35:*.vob=01;35:*.qt=01;35:*.nuv=01;35:*.wmv=01;35:*.asf=01;35:*.rm=01;35:*.rmvb=01;35:*.flc=01;35:*.avi=01;35:*.fli=01;35:*.flv=01;35:*.gl=01;35:*.dl=01;35:*.xcf=01;35:*.xwd=01;35:*.yuv=01;35:*.cgm=01;35:*.emf=01;35:*.axv=01;35:*.anx=01;35:*.ogv=01;35:*.ogx=01;35:*.aac=01;36:*.au=01;36:*.flac=01;36:*.mid=01;36:*.midi=01;36:*.mka=01;36:*.mp3=01;36:*.mpc=01;36:*.ogg=01;36:*.ra=01;36:*.wav=01;36:*.axa=01;36:*.oga=01;36:*.spx=01;36:*.xspf=01;36: MAIL=/var/spool/mail/root PATH=/root/.oceanbase-all-in-one/obd/usr/bin:/root/.oceanbase-all-in-one/obclient/u01/obclient/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin PWD=/home/admin/obproxy-4.2.3.0 LANG=en_US.UTF-8 SELINUX_LEVEL_REQUESTED= HISTCONTROL=ignoredups SHLVL=1 HOME=/root LOGNAME=root SSH_CONNECTION=192.168.56.1 63913 192.168.56.107 22 LESSOPEN=||/usr/bin/lesspipe.sh %s OBCLIENT_HOME=/root/.oceanbase-all-in-one/obclient XDG_RUNTIME_DIR=/run/user/0 _=/usr/bin/ps OLDPWD=/home/admin/obproxy-4.2.3.0
11821 pts/2 S+ 0:00 \_ grep --color=auto obproxy XDG_SESSION_ID=4 HOSTNAME=localhost SELINUX_ROLE_REQUESTED= TERM=xterm SHELL=/bin/bash HISTSIZE=1000 SSH_CLIENT=192.168.56.1 63913 22 SELINUX_USE_CURRENT_RANGE= OBD_INSTALL_PRE=/root/.oceanbase-all-in-one/obd SSH_TTY=/dev/pts/2 USER=root LD_LIBRARY_PATH=:/home/admin/oceanbase/lib LS_COLORS=rs=0:di=01;34:ln=01;36:mh=00:pi=40;33:so=01;35:do=01;35:bd=40;33;01:cd=40;33;01:or=40;31;01:mi=01;05;37;41:su=37;41:sg=30;43:ca=30;41:tw=30;42:ow=34;42:st=37;44:ex=01;32:*.tar=01;31:*.tgz=01;31:*.arc=01;31:*.arj=01;31:*.taz=01;31:*.lha=01;31:*.lz4=01;31:*.lzh=01;31:*.lzma=01;31:*.tlz=01;31:*.txz=01;31:*.tzo=01;31:*.t7z=01;31:*.zip=01;31:*.z=01;31:*.Z=01;31:*.dz=01;31:*.gz=01;31:*.lrz=01;31:*.lz=01;31:*.lzo=01;31:*.xz=01;31:*.bz2=01;31:*.bz=01;31:*.tbz=01;31:*.tbz2=01;31:*.tz=01;31:*.deb=01;31:*.rpm=01;31:*.jar=01;31:*.war=01;31:*.ear=01;31:*.sar=01;31:*.rar=01;31:*.alz=01;31:*.ace=01;31:*.zoo=01;31:*.cpio=01;31:*.7z=01;31:*.rz=01;31:*.cab=01;31:*.jpg=01;35:*.jpeg=01;35:*.gif=01;35:*.bmp=01;35:*.pbm=01;35:*.pgm=01;35:*.ppm=01;35:*.tga=01;35:*.xbm=01;35:*.xpm=01;35:*.tif=01;35:*.tiff=01;35:*.png=01;35:*.svg=01;35:*.svgz=01;35:*.mng=01;35:*.pcx=01;35:*.mov=01;35:*.mpg=01;35:*.mpeg=01;35:*.m2v=01;35:*.mkv=01;35:*.webm=01;35:*.ogm=01;35:*.mp4=01;35:*.m4v=01;35:*.mp4v=01;35:*.vob=01;35:*.qt=01;35:*.nuv=01;35:*.wmv=01;35:*.asf=01;35:*.rm=01;35:*.rmvb=01;35:*.flc=01;35:*.avi=01;35:*.fli=01;35:*.flv=01;35:*.gl=01;35:*.dl=01;35:*.xcf=01;35:*.xwd=01;35:*.yuv=01;35:*.cgm=01;35:*.emf=01;35:*.axv=01;35:*.anx=01;35:*.ogv=01;35:*.ogx=01;35:*.aac=01;36:*.au=01;36:*.flac=01;36:*.mid=01;36:*.midi=01;36:*.mka=01;36:*.mp3=01;36:*.mpc=01;36:*.ogg=01;36:*.ra=01;36:*.wav=01;36:*.axa=01;36:*.oga=01;36:*.spx=01;36:*.xspf=01;36: MAIL=/var/spool/mail/root PATH=/root/.oceanbase-all-in-one/obd/usr/bin:/root/.oceanbase-all-in-one/obclient/u01/obclient/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin PWD=/home/admin/obproxy-4.2.3.0 LANG=en_US.UTF-8 SELINUX_LEVEL_REQUESTED= HISTCONTROL=ignoredups SHLVL=1 HOME=/root LOGNAME=root SSH_CONNECTION=192.168.56.1 63913 192.168.56.107 22 LESSOPEN=||/usr/bin/lesspipe.sh %s OBCLIENT_HOME=/root/.oceanbase-all-in-one/obclient XDG_RUNTIME_DIR=/run/user/0 _=/usr/bin/grep OLDPWD=/home/admin/obproxy-4.2.3.0
[root@localhost obproxy-4.2.3.0]# netstat -nltp|grep obproxy
tcp 0 0 0.0.0.0:2883 0.0.0.0:* LISTEN 11783/bin/obproxy
tcp 0 0 0.0.0.0:2884 0.0.0.0:* LISTEN 11783/bin/obproxy
2.9.2 登录 OBPROXY 修改密码
# 先登录 root@sys 创建 OBPROXY 所需用户(proxyro)，OBPROXY 连接OceanBase 使用的用户就是 proxyro
mysql -h192.168.56.107 -uroot@sys -P2881 -c -A
MySQL [(none)]> create user proxyro;
Query OK, 0 rows affected (0.03 sec)
MySQL [(none)]> grant select on oceanbase.* to proxyro;
Query OK, 0 rows affected (0.04 sec)
# 再登录 root@proxysys ，修改 proxysys 和 proxyro 的密码
mysql -h192.168.56.107 -uroot@proxysys -P2883
# 修改 proxysys 和 proxyro 的密码，用alter proxyconfig set 的方式修改
# obproxy_sys_password ，这个修改的是 OBPROXY 即 proxysys的密码
alter proxyconfig set obproxy_sys_password = '' ;
# observer_sys_password ， 这个修改的为 proxyro 的密码，要与上面的保持一致
alter proxyconfig set observer_sys_password = '' ;
# 退出，通过 OBPROXY 连接 Oceanbase 集群，确认 OBPROXY 是否部署成功。
# 能查到数据即为成功部署
mysql -h192.168.56.107 -uroot@sys -P2883 -c -A oceanbase
MySQL [oceanbase]> show processlist;
+------+--------+------+----------------------+-----------+-------------+-------------------+-------------------+-------+-------+
| Id | Tenant | User | Host | db | trans_count | svr_session_count | state | tid | pid |
+------+--------+------+----------------------+-----------+-------------+-------------------+-------------------+-------+-------+
| 2 | sys | root | 192.168.56.107:50544 | oceanbase | 0 | 1 | MCS_ACTIVE_READER | 11783 | 11783 |
+------+--------+------+----------------------+-----------+-------------+-------------------+-------------------+-------+-------+
1 row in set (0.00 sec)
MySQL [oceanbase]> show full processlist;
+------------+---------+--------+----------------------+-----------+---------+------+--------+-----------------------+----------------+------+----------------------+
| Id | User | Tenant | Host | db | Command | Time | State | Info | Ip | Port | Proxy_sessid |
+------------+---------+--------+----------------------+-----------+---------+------+--------+-----------------------+----------------+------+----------------------+
| 3221522860 | proxyro | sys | 192.168.56.107:35802 | oceanbase | Sleep | 4 | SLEEP | NULL | 192.168.56.107 | 2881 | 13882407883771150338 |
| 3221522859 | root | sys | 192.168.56.107:35798 | oceanbase | Query | 0 | ACTIVE | show full processlist | 192.168.56.107 | 2881 | 13882407883771150339 |
| 3221514028 | root | sys | 192.168.56.107:35786 | NULL | Sleep | 567 | SLEEP | NULL | 192.168.56.107 | 2881 | NULL |
+------------+---------+--------+----------------------+-----------+---------+------+--------+-----------------------+----------------+------+----------------------+
3 rows in set (0.00 sec)
# 三次连接数据库的区别
# 2881 链接 OceanBase 数据库
mysql -h192.168.56.107 -uroot@sys -P2881 -c -A
# 2883 链接 PROXY
mysql -h192.168.56.107 -uroot@proxysys -P2883
# 2883 通过 PROXY 链接 OceanBase 数据库
mysql -h192.168.56.107 -uroot@sys -P2883 -c -A oceanbase
2.10 创建业务租户、业务数据库、表(实践练习做过，此处仅为创建命令及成功截图)
命令汇总
# 创建租户
mysql -h192.168.56.107 -uroot@sys -P2881 -A oceanbase
ALTER RESOURCE UNIT sys_unit_config memory_size = 1073741824, MAX_CPU = 1, MIN_CPU = 1, LOG_DISK_SIZE = 5368709120;
drop resource unit OBRU;
create resource unit OBRU memory_size = '1G', MAX_CPU = 1, MIN_CPU = 1, LOG_DISK_SIZE = 5368709120;
select * from oceanbase.DBA_OB_UNIT_CONFIGS;
drop resource pool OBRP ;
create resource pool OBRP UNIT='OBRU', UNIT_NUM=1, ZONE_LIST=('zone1');
select * from oceanbase.DBA_OB_RESOURCE_POOLS;
# 一定是先创建完 resource pool 之后 再把 resource unit 从 1g 改为 4g 。不然会报错。详见本章的踩坑
ALTER RESOURCE UNIT OBRU memory_size = '4G', MAX_CPU = 1, MIN_CPU = 1, LOG_DISK_SIZE = 5368709120;
drop tenant OBTN;
create tenant if not exists OBTN PRIMARY_ZONE='zone1', RESOURCE_POOL_LIST=('OBRP') set OB_TCP_INVITED_NODES='%';
select * from oceanbase.DBA_OB_TENANTS;
# 创建数据库表
mysql -h192.168.56.107 -uroot@OBTN -P2881
create database obtn;
create table t1 (id int);
insert into t1 values(1);
select * from t1;
2.10 .1 资源规格、创建资源池、 创建租户
2.10 .2 登录租户 OBTN 的 ROOT 用户、 创建数据库、表
2.11 踩坑
2.11.1 启动 OBServer 报错
- 在《启动 OBSERVER 进程》步骤，启动进程的配置有问题一直error
并且使用下面第一种方式启动不产生日志，必须用第二种方式才产生日志。
cd /home/admin/oceanbase/bin
/observer -i enp0s8 -p 2881 -P 2882 -z zone1 -d /home/admin/oceanbase/store/obdemo -r '192.168.56.107:2882:2881' -c 20240312 -n obdemo -o "memory_limit=8G,cache_wash_threshold=1G,__min_full_resource_pool_memory=268435456,system_memory=3G,memory_chunk_cache_size=128M,cpu_count=4,net_thread_count=2,datafile_size=30G,stack_size=1536K,config_additional_dir=/data/obdemo/etc3;/redo/obdemo/etc2"
cd /home/admin/oceanbase/
bin/observer -i enp0s8 -p 2881 -P 2882 -z zone1 -d /home/admin/oceanbase/store/obdemo -r '192.168.56.107:2882:2881' -c 20240312 -n obdemo -o "memory_limit=8G,cache_wash_threshold=1G,__min_full_resource_pool_memory=268435456,system_memory=3G,memory_chunk_cache_size=128M,cpu_count=4,net_thread_count=2,datafile_size=30G,stack_size=1536K,config_additional_dir=/data/obdemo/etc3;/redo/obdemo/etc2"
- 上一步不产生日志的问题解决，但是启动报错，修改过很多参数 ，最后删除
__min_full_resource_pool_memory后才不报这个错了。(报新的错)
[2024-03-12 05:58:18.221081] ERROR issue_dba_error (ob_log.cpp:1875) [5623][observer][T0][Y0-0000000000000000-0-0] [lt=56][errcode=-4388] Unexpected internal error happen, please checkout the internal errcode(errcode=-4147, file="ob_sever.cpp", line_no=1826, info="invalid config from cmdline options")
[2024-03-12 05:58:18.222714] ERROR issue_dba_error (ob_log.cpp:1875) [5623][observer][T0][Y0-0000000000000000-0-0] [lt=9][errcode=-4388] Unexpected internal error happen, please checkout the internal errcode(errcode=-4147, file="ob_serer.cpp", line_no=264, info="init config failed")
[2024-03-12 05:58:18.233314] ERROR issue_dba_error (ob_log.cpp:1875) [5623][observer][T0][Y0-0000000000000000-0-0] [lt=3][errcode=-4388] Unexpected internal error happen, please checkout the internal errcode(errcode=-4147, file="ob_serer.cpp", line_no=514, info="[OBSERVER_NOTICE] fail to init observer")
[2024-03-12 05:58:18.233343] ERROR init (ob_server.cpp:515) [5623][observer][T0][Y0-0000000000000000-0-0] [lt=19][errcode=-4393] observer start process failure(msg="observer init() has failure", ret=-4147, ret="OB_INVALID_CONFIG")
[2024-03-12 05:58:18.233351] ERROR issue_dba_error (ob_log.cpp:1875) [5623][observer][T0][Y0-0000000000000000-0-0] [lt=7][errcode=-4388] Unexpected internal error happen, please checkout the internal errcode(errcode=-4147, file="main.cp", line_no=591, info="observer init fail")
- 删掉
__min_full_resource_pool_memory之后，上一步错误是不报了，但是报SLOG and datafile must be on the same disk，根据实战教程步骤，创建的目录有问题。我把这两个路径合到同一个磁盘下才解决（记得删掉启动失败，但是生成的无用文件）。
[2024-03-12 22:06:09.482335] WDIAG [SERVER] nonblock_get_leader (ob_inner_sql_connection.cpp:1888) [10931][observer][T0][Y0-0000000000000000-0-0] [lt=4][errcode=-4014] user tenant has been dropped(ret=-4014, ret="OB_INNER_STAT_ERROR", tenant_id=1)
[2024-03-12 22:06:09.495124] ERROR issue_dba_error (ob_log.cpp:1875) [10931][observer][T0][Y0-0000000000000000-0-0] [lt=4][errcode=-4388] Unexpected internal error happen, please checkout the internal errcode(errcode=-4016, file="ob_server_utils.cpp", line_no=175, info="SLOG and datafile must be on the same disk")
[2024-03-12 22:06:09.495186] ERROR issue_dba_error (ob_log.cpp:1875) [10931][observer][T0][Y0-0000000000000000-0-0] [lt=48][errcode=-4388] Unexpected internal error happen, please checkout the internal errcode(errcode=-4016, file="ob_server.cpp", line_no=2171, info="fail to check need reserved space")
[2024-03-12 22:06:09.495223] ERROR issue_dba_error (ob_log.cpp:1875) [10931][observer][T0][Y0-0000000000000000-0-0] [lt=31][errcode=-4388] Unexpected internal error happen, please checkout the internal errcode(errcode=-4016, file="ob_server.cpp", line_no=332, info="init io failed")
[2024-03-12 22:06:09.495226] EDIAG [SERVER] init (ob_server.cpp:332) [10931][observer][T0][Y0-0000000000000000-0-0] [lt=3][errcode=-4016] init io failed(ret=-4016, ret="OB_ERR_UNEXPECTED") BACKTRACE:0x12a0688c 0x53dbba5 0x540a68a 0x540a04b 0x5409f8a 0x5528f17 0xa8630ec 0xa857eb9 0x73fe460 0x7f2d7bc80555 0x56c374f
[2024-03-12 21:06:11.121468] ERROR issue_dba_error (ob_log.cpp:1875) [10931][observer][T0][Y0-0000000000000000-0-0] [lt=10][errcode=-4388] Unexpected internal error happen, please checkout the internal errcode(errcode=-4016, file="ob_server.cpp", line_no=514, info="[OBSERVER_NOTICE] fail to init observer")
[2024-03-12 21:06:11.121491] EDIAG [SERVER] init (ob_server.cpp:514) [10931][observer][T0][Y0-0000000000000000-0-0] [lt=22][errcode=-4016] [OBSERVER_NOTICE] fail to init observer(ret=-4016, ret="OB_ERR_UNEXPECTED") BACKTRACE:0x12a0688c 0x53dbba5 0x540a68a 0x540a04b 0x5409f8a 0x5528f17 0xa8689ca 0xa859fe0 0x73fe460 0x7f2d7bc80555 0x56c374f
[2024-03-12 21:06:11.121554] ERROR init (ob_server.cpp:515) [10931][observer][T0][Y0-0000000000000000-0-0] [lt=55][errcode=-4393] observer start process failure(msg="observer init() has failure", ret=-4016, ret="OB_ERR_UNEXPECTED")
[2024-03-12 21:06:11.121588] ERROR issue_dba_error (ob_log.cpp:1875) [10931][observer][T0][Y0-0000000000000000-0-0] [lt=32][errcode=-4388] Unexpected internal error happen, please checkout the internal errcode(errcode=-4016, file="main.cpp", line_no=591, info="observer init fail")
2.11.2 创建租户报错
- 创建 resource pool 资源池报错
MySQL [oceanbase]> create resource unit OBRU memory_size = '4G', MAX_CPU = 1, MIN_CPU = 1, LOG_DISK_SIZE = '2G';
Query OK, 0 rows affected (0.01 sec)
MySQL [oceanbase]> create resource pool OBRP UNIT='OBRU', UNIT_NUM=1, ZONE_LIST=('zone1');
ERROR 1235 (0A000): unit MEMORY_SIZE less than __min_full_resource_pool_memory not supported
调整资源规格的 memory_size 为 5g 后，还是报错
MySQL [oceanbase]> create resource unit OBRU memory_size = '5G', MAX_CPU = 1, MIN_CPU = 1, LOG_DISK_SIZE = '2G';
Query OK, 0 rows affected (0.01 sec)
MySQL [oceanbase]> create resource pool OBRP UNIT='OBRU', UNIT_NUM=1, ZONE_LIST=('zone1');
ERROR 4733 (HY000): zone 'zone1' resource not enough to hold 1 unit. You can check resource info by views: DBA_OB_UNITS, GV$OB_UNITS, GV$OB_SERVERS.
server '"192.168.56.107:2882"' MEMORY resource not enough
调整 __min_full_resource_pool_memory 为最小值，依旧报错
MySQL [oceanbase]> alter system set __min_full_resource_pool_memory='1073741824';
Query OK, 0 rows affected (0.00 sec)
MySQL [oceanbase]> create resource unit OBRU memory_size = 1073741824, MAX_CPU = 1, MIN_CPU = 1, LOG_DISK_SIZE = 5368709120;
Query OK, 0 rows affected (0.01 sec)
MySQL [oceanbase]> create resource pool OBRP UNIT='OBRU', UNIT_NUM=1, ZONE_LIST=('zone1');
ERROR 4733 (HY000): zone 'zone1' resource not enough to hold 1 unit. You can check resource info by views: DBA_OB_UNITS, GV$OB_UNITS, GV$OB_SERVERS.
server '"192.168.56.107:2882"' MEMORY resource not enough
此时操作系统 free -h 为
[root@localhost obproxy-4.2.3.0]# free -h
total used free shared buff/cache available
Mem: 7.6G 2.6G 4.9G 8.5M 228M 4.9G
Swap: 7.9G 264K 7.9G
查看资源规格，发现 sys_unit_config 用的多，调整一下 sys_unit_config ,之后在创建资源池成功
MySQL [oceanbase]> select * from oceanbase.DBA_OB_UNIT_CONFIGS;
+----------------+-----------------+----------------------------+----------------------------+---------+---------+-------------+---------------+---------------------+---------------------+-------------+
| UNIT_CONFIG_ID | NAME | CREATE_TIME | MODIFY_TIME | MAX_CPU | MIN_CPU | MEMORY_SIZE | LOG_DISK_SIZE | MAX_IOPS | MIN_IOPS | IOPS_WEIGHT |
+----------------+-----------------+----------------------------+----------------------------+---------+---------+-------------+---------------+---------------------+---------------------+-------------+
| 1 | sys_unit_config | 2024-03-13 10:44:25.017120 | 2024-03-13 10:44:25.017120 | 1 | 1 | 5368709120 | 5368709120 | 9223372036854775807 | 9223372036854775807 | 1 |
| 1012 | OBRU | 2024-03-13 14:35:05.954585 | 2024-03-13 14:35:05.954585 | 1 | 1 | 1073741824 | 5368709120 | 9223372036854775807 | 9223372036854775807 | 1 |
+----------------+-----------------+----------------------------+----------------------------+---------+---------+-------------+---------------+---------------------+---------------------+-------------+
2 rows in set (0.00 sec)
MySQL [oceanbase]> ALTER RESOURCE UNIT sys_unit_config memory_size = 1073741824, MAX_CPU = 1, MIN_CPU = 1, LOG_DISK_SIZE = 5368709120;
Query OK, 0 rows affected (0.02 sec)
MySQL [oceanbase]> select * from oceanbase.DBA_OB_UNIT_CONFIGS;
+----------------+-----------------+----------------------------+----------------------------+---------+---------+-------------+---------------+---------------------+---------------------+-------------+
| UNIT_CONFIG_ID | NAME | CREATE_TIME | MODIFY_TIME | MAX_CPU | MIN_CPU | MEMORY_SIZE | LOG_DISK_SIZE | MAX_IOPS | MIN_IOPS | IOPS_WEIGHT |
+----------------+-----------------+----------------------------+----------------------------+---------+---------+-------------+---------------+---------------------+---------------------+-------------+
| 1 | sys_unit_config | 2024-03-13 10:44:25.017120 | 2024-03-13 14:51:56.086334 | 1 | 1 | 1073741824 | 5368709120 | 9223372036854775807 | 9223372036854775807 | 1 |
| 1012 | OBRU | 2024-03-13 14:35:05.954585 | 2024-03-13 14:35:05.954585 | 1 | 1 | 1073741824 | 5368709120 | 9223372036854775807 | 9223372036854775807 | 1 |
+----------------+-----------------+----------------------------+----------------------------+---------+---------+-------------+---------------+---------------------+---------------------+-------------+
2 rows in set (0.00 sec)
MySQL [oceanbase]> create resource pool OBRP UNIT='OBRU', UNIT_NUM=1, ZONE_LIST=('zone1');
Query OK, 0 rows affected (0.02 sec)
- 创建 tenant 租户报错
看样子还是内存问题。在创建过程中 free 最低到 3.7 G，也就是说还有 3.7 的富余量。不知道为什么创建失败。
怀疑是给的资源太少了，1G 不足以创建租户？ 所以改了下 OBRU 的内存为 4G 。创建成功。
注：如果刚开始就给 OBRU 4g 的资源，会报ERROR 4733 (HY000): zone 'zone1' resource not enough to hold 1 unit. You can check resource info by views: DBA_OB_UNITS, GV$OB_UNITS, GV$OB_SERVERS. server '"192.168.56.107:2882"' MEMORY resource not enough，在我的环境中，要按照《命令汇总》顺序去做才能成功
MySQL [oceanbase]> create tenant if not exists OBTN PRIMARY_ZONE='zone1', RESOURCE_POOL_LIST=('OBRP') set OB_TCP_INVITED_NODES='%';
ERROR 4013 (HY001): No memory or reach tenant memory limit
MySQL [oceanbase]> ALTER RESOURCE UNIT OBRU memory_size = '4G', MAX_CPU = 1, MIN_CPU = 1, LOG_DISK_SIZE = 5368709120;
Query OK, 0 rows affected (0.01 sec)
MySQL [oceanbase]> select * from oceanbase.DBA_OB_UNIT_CONFIGS;
+----------------+-----------------+----------------------------+----------------------------+---------+---------+-------------+---------------+---------------------+---------------------+-------------+
| UNIT_CONFIG_ID | NAME | CREATE_TIME | MODIFY_TIME | MAX_CPU | MIN_CPU | MEMORY_SIZE | LOG_DISK_SIZE | MAX_IOPS | MIN_IOPS | IOPS_WEIGHT |
+----------------+-----------------+----------------------------+----------------------------+---------+---------+-------------+---------------+---------------------+---------------------+-------------+
| 1 | sys_unit_config | 2024-03-13 10:44:25.017120 | 2024-03-13 14:51:56.086334 | 1 | 1 | 1073741824 | 5368709120 | 9223372036854775807 | 9223372036854775807 | 1 |
| 1012 | OBRU | 2024-03-13 14:35:05.954585 | 2024-03-13 15:03:25.907444 | 1 | 1 | 4294967296 | 5368709120 | 9223372036854775807 | 9223372036854775807 | 1 |
+----------------+-----------------+----------------------------+----------------------------+---------+---------+-------------+---------------+---------------------+---------------------+-------------+
2 rows in set (0.00 sec)
MySQL [oceanbase]>
MySQL [oceanbase]> select * from oceanbase.DBA_OB_RESOURCE_POOLS;
+------------------+----------+-----------+----------------------------+----------------------------+------------+----------------+-----------+--------------+
| RESOURCE_POOL_ID | NAME | TENANT_ID | CREATE_TIME | MODIFY_TIME | UNIT_COUNT | UNIT_CONFIG_ID | ZONE_LIST | REPLICA_TYPE |
+------------------+----------+-----------+----------------------------+----------------------------+------------+----------------+-----------+--------------+
| 1 | sys_pool | 1 | 2024-03-13 10:44:25.021946 | 2024-03-13 10:44:25.029897 | 1 | 1 | zone1 | FULL |
| 1010 | OBRP | NULL | 2024-03-13 14:53:30.165565 | 2024-03-13 14:56:20.521812 | 1 | 1012 | zone1 | FULL |
+------------------+----------+-----------+----------------------------+----------------------------+------------+----------------+-----------+--------------+
2 rows in set (0.01 sec)
MySQL [oceanbase]> create tenant if not exists OBTN PRIMARY_ZONE='zone1', RESOURCE_POOL_LIST=('OBRP') set OB_TCP_INVITED_NODES='%';
Query OK, 0 rows affected (35.43 sec)
三、 [实践练习三（可选）]
略
四、[实践练习四（必选）：迁移 MySQL 数据到 OceanBase 集群]
(https://ask.oceanbase.com/t/topic/20400030)
4.1 前置条件
4.1.1 创建 mysql 数据库
tar -zxvf mysql-5.7.35-linux-glibc2.12-x86_64.tar.gz
mv mysql-5.7.35-linux-glibc2.12-x86_64 /usr/local/mysql
c
groupadd mysql && useradd -r -g mysql mysql
mkdir -p /data/mysql
chown mysql:mysql -R /data/mysql
vim /etc/my.cnf
[mysqld]
bind-address=0.0.0.0
port=3306
user=root
basedir=/usr/local/mysql
datadir=/data/mysql
socket=/tmp/mysql.sock
log-error=/data/mysql/mysql.err
pid-file=/data/mysql/mysql.pid
#character config
character_set_server=utf8mb4
symbolic-links=0
explicit_defaults_for_timestamp=true
cd /usr/local/mysql/bin/
./mysqld --defaults-file=/etc/my.cnf --basedir=/usr/local/mysql/ --datadir=/data/mysql/ --user=mysql --initialize
查看初始密码
[root@OS3 bin]# cat /data/mysql/mysql.err|grep localhost
2022-04-27T08:15:57.451985Z 1 [Note] A temporary password is generated for root@localhost: bwSOyfy4ro;.
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql
service mysql start
service mysql restart
登录mysql
/usr/local/mysql/bin/mysql -u root -p"bwSOyfy4ro"
修改密码为root
alter user root identified by 'root';
4.1.2 mysql 数据库 造数
wget https://codeload.github.com/Percona-Lab/tpcc-mysql/zip/refs/heads/master --no-check-certificate
unzip master
cd tpcc-mysql-master
ls
add_fkey_idx.sql count.sql create_table.sql Dockerfile drop_cons.sql load_multi_schema.sh load.sh README.md schema2 scripts src
yum install -y mysql-devel
yum install -y 'Development Tools'
cd tpcc-mysql-master/src
make
连接mysql
/usr/local/mysql/bin/mysql -u root
创建数据库：
create database tpcc1000;
创建表：
use tpcc1000;
c创建索引和外键（这一步可以在加载数据之后完成）：
加载数据，使用 tpcc_load 工具：
cd /root/tpcc-mysql-master
./tpcc_load -h127.0.0.1 -d tpcc -u root -p "" -w 1
这里的参数代表：
-h 主机名：端口
-d 数据库名
-u 用户
-p 密码
-w 仓库数量
详细见 tpcc_load --help。或者查看readme
4.2 导入导出
4.2.1 mysqldump 导出数据
/usr/local/mysql/bin/mysqldump -h 127.0.0.1 -u root -P 3306 -p -d tpcc --compact > /home/mysql/DDL.sql
/usr/local/mysql/bin/mysqldump -h 127.0.0.1 -u root -P 3306 -p -t tpcc > /home/mysql/DATA.sql
ls -lrth /home/mysql
[mysql@OS3 ~]$ ls -lrth /home/mysql
总用量 81M
-rw-rw-r--. 1 mysql mysql 5.6K 4月 27 17:41 DDL.sql
-rw-rw-r--. 1 mysql mysql 81M 4月 27 17:43 DATA.sql
4.2.2 导入数据
OceanBase设置
登入 OBTN 租户
mysql -h192.168.56.107 -uroot@OBTN -P2881
set global foreign_key_checks=off;
show global variables like '%foreign%';
create database tpcc;
use tpcc
source DDL.sql
source DATA.sql
4.3 datax 迁移数据
一二四六练习，迁移数据这部分最后编辑完成
4.3.1 第二次迁移数据（看这部分就行）
4.3.1.1 Mysql 环境部署
# 除了这部分，其余章节涉及到的 mysql 数据库都在另一台虚机中部署，后由于资源实在不够电脑卡到爆，迁移数据练习用 docker 在OB服务器上部署。
# docker 部署 mysql 数据库
yum intall -y docker
docker pull mysql
docker images
docker run -di --name demo_mysql -p 33306:3306 -e MYSQL_ROOT_PASSWORD=123456 mysql
# 配置宿主机登录 docker mysql
如果不配置，在宿主机使用登录会提示： ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded
#1. 进入 mysql 容器
docker exec -it demo_mysql /bin/bash
#2. 登录 mysql 修改为 native 密码
mysql -u root -p123456 -P3306
alter user 'root' identified with mysql_native_password by 'root';
#3. 回到宿主机登录验证
mysql -h192.168.56.107 -u root -P33306 -proot
4.3.1.2 Mysql 造数
create database tpcc;
# 这张表在 OB 上也要提前创建
create table tpcc.tt (name varchar(10));
insert into tpcc.tt values ('a');
4.3.1.3 datax 迁移数据
# 下载 datax
wget https://datax-opensource.oss-cn-hangzhou.aliyuncs.com/202308/datax.tar.gz
tar -xzvf datax.tar.gz
# 直接写配置文件 mysql2ob.json
vim mysql2ob.json
{
"job": {
"content": [
{
"reader": {
"name": "mysqlreader",
"parameter": {
# 要同步的列
"column": ["*"],
"connection": [
{
# 修改为 mysql 的 IP:PORT、数据库
"jdbcUrl": ["jdbc:mysql://192.168.56.107:33306/tpcc?useUnicode=true&characterEncoding=utf8"],
# 要同步的表名
"table": ["tt"]
}
],
# mysql 的用户名密码
"password": "root",
"username": "root",
"where": ""
}
},
"writer": {
"name": "oceanbasev10writer",
"parameter": {
"column": ["*"],
"connection": [
{
"jdbcUrl": "jdbc:oceanbase://192.168.56.107:2883/tpcc",
"table": ["tt"]
}
],
"obWriteMode": "insert",
"password": "rootroot",
"username": "root@sys#obdemo"
}
}
}
],
"setting": {
"speed": {
"channel":1
}
}
}
}
# 启动任务
python ./datax/bin/datax.py mysql2ob.json
终于迁移成功了
4.3.2 第一次迁移数据（废弃）
# 造表 mysql
create table tpcc.t1 (id int);
# 建表 OceanBase ，目标端需要提前建表
create table tpcc.t1 (id int);
# 下载 datax
wget http://datax-opensource.oss-cn-hangzhou.aliyuncs.com/datax.tar.gz
tar -xzvf datax.tar.gz
find ./datax/plugin -name ".*" | xargs rm -f
# 配置模板
python ./datax/bin/datax.py -r mysqlreader -w oceanbasev10writer
# 配置文件，写入配置文件，并修改
python ./datax/bin/datax.py -r mysqlreader -w oceanbasev10writer > mysql2ob.json
# 修改文件 mysqltoob.json
{
"job": {
"setting": {
"speed": {
"channel": 4
},
"errorLimit": {
"record": 0,
"percentage": 0.1
}
},
"content": [
{
"reader": {
"name": "mysqlreader",
"parameter": {
"username": "root",
"password": "root",
"column": ["*"],
"connection": [
{
"table": ["customer"],
"jdbcUrl": ["jdbc:mysql://192.168.56.103:3306/tpcc?useUnicode=true&characterEncoding=utf8"]
}
]
}
},
"writer": {
"name": "oceanbasev10writer",
"parameter": {
"obWriteMode": "insert",
"column": ["*"],
"preSql": ["truncate table customer"],
"connection": [
{
"jdbcUrl": "jdbc:oceanbase://127.0.0.1:2883/tpcc?",
"table": ["customer"]
}
],
"username": "root",
"password":"rootroot",
"writerThreadCount":10,
"batchSize": 1000,
"memstoreThreshold": "0.9"
}
}
}
]
}
}
# 启动任务
python ./datax/bin/datax.py mysql2ob.json
4.4 踩坑
- 初次安装datax，运行迁移任务报错
[root@localhost ~]# python ./datax/bin/datax.py mysql2ob.json
DataX (DATAX-OPENSOURCE-3.0), From Alibaba !
Copyright (C) 2010-2017, Alibaba Group. All Rights Reserved.
2024-03-13 23:42:33.943 [main] WARN ConfigParser - 插件[mysqlreader,oceanbasev10writer]加载失败，1s后重试... Exception:Code:[Common-00], Describe:[您提供的配置文件存在错误信息，请检查您的作业配置 .] - 配置信息错误，您提供的配置文件[/root/datax/plugin/reader/._drdsreader/plugin.json]不存在. 请检查您的配置文件.
2024-03-13 23:42:34.948 [main] ERROR Engine -
经DataX智能分析,该任务最可能的错误原因是:
com.alibaba.datax.common.exception.DataXException: Code:[Common-00], Describe:[您提供的配置文件存在错误信息，请检查您的作业配置 .] - 配置信息错误，您提供的配置文件[/root/datax/plugin/reader/._drdsreader/plugin.json]不存在. 请检查您的配置文件.
at com.alibaba.datax.common.exception.DataXException.asDataXException(DataXException.java:26)
at com.alibaba.datax.common.util.Configuration.from(Configuration.java:95)
at com.alibaba.datax.core.util.ConfigParser.parseOnePluginConfig(ConfigParser.java:153)
at com.alibaba.datax.core.util.ConfigParser.parsePluginConfig(ConfigParser.java:125)
at com.alibaba.datax.core.util.ConfigParser.parse(ConfigParser.java:63)
at com.alibaba.datax.core.Engine.entry(Engine.java:137)
at com.alibaba.datax.core.Engine.main(Engine.java:204)
需要手动删除plugin下的这些类型的文件
find ./datax/plugin -name ".*" | xargs rm -f
- 启动任务报错
ERROR JobContainer - 运行scheduler 模式[standalone]出错
很多问题都自己处理过了，但是这个我是实在没法子了。已经提问社区，暂无回复，话题链接：
datax迁移从mysql-oceanbase报错：ERROR JobContainer - 运行scheduler 模式[standalone]出错
已自行处理完成，详见 4.3.1
五、 [实践练习五（可选）]
略
六、[实践练习六（必选）：查看 OceanBase 执行计划]
(https://ask.oceanbase.com/t/topic/20400276)
6.1 BenmarkSQL 运行 TPC-C
# 下载 Benchmark
wget https://jaist.dl.sourceforge.net/project/benchmarksql/benchmarksql-5.0.zip
# 下载 JCBD 驱动
https://www.oceanbase.com/softwarecenter-cloud
mv oceanbase-client-2.4.1.jar /root/benchmarksql-5.0/lib/oracle
chmod 777 oceanbase-client-2.4.1.jar
# 安装 ant
yum install -y ant
# 执行 ant BUILD
cd /root/benchmarksql-5.0
[root@localhost benchmarksql-5.0]# ant
Buildfile: /root/benchmarksql-5.0/build.xml
init:
[mkdir] Created dir: /root/benchmarksql-5.0/build
compile:
[javac] Compiling 11 source files to /root/benchmarksql-5.0/build
dist:
[mkdir] Created dir: /root/benchmarksql-5.0/dist
[jar] Building jar: /root/benchmarksql-5.0/dist/BenchmarkSQL-5.0.jar
BUILD SUCCESSFUL
Total time: 3 seconds
# 编辑配置文件
cd /root/benchmarksql-5.0/run
[root@localhost run]# cat props.ob
db=oracle
driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver
conn=jdbc:oceanbase://127.0.0.1:2883/tpcc?useUnicode=true&characterEncoding=utf-8
user=root
password=rootroot
warehouses=2
loadWorkers=1
terminals=5
//To run specified transactions per terminal- runMins must equal zero
runTxnsPerTerminal=0
//To run for specified minutes- runTxnsPerTerminal must equal zero
runMins=5
//Number of total transactions per minute
limitTxnsPerMin=0
//Set to true to run in 4.x compatible mode. Set to false to use the
//entire configured database evenly.
terminalWarehouseFixed=true
//The following five values must add up to 100
newOrderWeight=45
paymentWeight=43
orderStatusWeight=4
deliveryWeight=4
stockLevelWeight=4
// Directory name to create for collecting detailed result data.
// Comment this out to suppress.
resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tS
osCollectorScript=./misc/os_collector_linux.py
osCollectorInterval=1
# 构建数据库
create database tpccob;
./runDatabaseBuild.sh props.ob
[root@localhost run]# ./runDatabaseBuild.sh props.ob
# ------------------------------------------------------------
# Loading SQL file ./sql.common/tableCreates.sql
# ------------------------------------------------------------
create table bmsql_config (
cfg_name varchar(30) primary key,
cfg_value varchar(50)
);
create table bmsql_warehouse (
w_id integer not null,
w_ytd decimal(12,2),
w_tax decimal(4,4),
w_name varchar(10),
w_street_1 varchar(20),
w_street_2 varchar(20),
w_city varchar(20),
w_state char(2),
w_zip char(9)
);
create table bmsql_district (
d_w_id integer not null,
d_id integer not null,
d_ytd decimal(12,2),
d_tax decimal(4,4),
d_next_o_id integer,
d_name varchar(10),
d_street_1 varchar(20),
d_street_2 varchar(20),
d_city varchar(20),
d_state char(2),
d_zip char(9)
);
create table bmsql_customer (
c_w_id integer not null,
c_d_id integer not null,
c_id integer not null,
c_discount decimal(4,4),
c_credit char(2),
c_last varchar(16),
c_first varchar(16),
c_credit_lim decimal(12,2),
c_balance decimal(12,2),
c_ytd_payment decimal(12,2),
c_payment_cnt integer,
c_delivery_cnt integer,
c_street_1 varchar(20),
c_street_2 varchar(20),
c_city varchar(20),
c_state char(2),
c_zip char(9),
c_phone char(16),
c_since timestamp,
c_middle char(2),
c_data varchar(500)
);
create sequence bmsql_hist_id_seq;
create table bmsql_history (
hist_id integer,
h_c_id integer,
h_c_d_id integer,
h_c_w_id integer,
h_d_id integer,
h_w_id integer,
h_date timestamp,
h_amount decimal(6,2),
h_data varchar(24)
);
create table bmsql_new_order (
no_w_id integer not null,
no_d_id integer not null,
no_o_id integer not null
);
create table bmsql_oorder (
o_w_id integer not null,
o_d_id integer not null,
o_id integer not null,
o_c_id integer,
o_carrier_id integer,
o_ol_cnt integer,
o_all_local integer,
o_entry_d timestamp
);
create table bmsql_order_line (
ol_w_id integer not null,
ol_d_id integer not null,
ol_o_id integer not null,
ol_number integer not null,
ol_i_id integer not null,
ol_delivery_d timestamp,
ol_amount decimal(6,2),
ol_supply_w_id integer,
ol_quantity integer,
ol_dist_info char(24)
);
create table bmsql_item (
i_id integer not null,
i_name varchar(24),
i_price decimal(5,2),
i_data varchar(50),
i_im_id integer
);
create table bmsql_stock (
s_w_id integer not null,
s_i_id integer not null,
s_quantity integer,
s_ytd integer,
s_order_cnt integer,
s_remote_cnt integer,
s_data varchar(50),
s_dist_01 char(24),
s_dist_02 char(24),
s_dist_03 char(24),
s_dist_04 char(24),
s_dist_05 char(24),
s_dist_06 char(24),
s_dist_07 char(24),
s_dist_08 char(24),
s_dist_09 char(24),
s_dist_10 char(24)
);
Starting BenchmarkSQL LoadData
driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver
conn=jdbc:oceanbase://127.0.0.1:2883/tpccob?useUnicode=true&characterEncoding=utf-8
user=root
password=***********
warehouses=2
loadWorkers=1
fileLocation (not defined)
csvNullValue (not defined - using default 'NULL')
Worker 000: Loading ITEM
Worker 000: Loading ITEM done
Worker 000: Loading Warehouse 1
Worker 000: Loading Warehouse 1 done
Worker 000: Loading Warehouse 2
Worker 000: Loading Warehouse 2 done
# ------------------------------------------------------------
# Loading SQL file ./sql.common/indexCreates.sql
# ------------------------------------------------------------
alter table bmsql_warehouse add constraint bmsql_warehouse_pkey
primary key (w_id);
alter table bmsql_district add constraint bmsql_district_pkey
primary key (d_w_id, d_id);
alter table bmsql_customer add constraint bmsql_customer_pkey
primary key (c_w_id, c_d_id, c_id);
create index bmsql_customer_idx1
on bmsql_customer (c_w_id, c_d_id, c_last, c_first);
alter table bmsql_oorder add constraint bmsql_oorder_pkey
primary key (o_w_id, o_d_id, o_id);
create unique index bmsql_oorder_idx1
on bmsql_oorder (o_w_id, o_d_id, o_carrier_id, o_id);
alter table bmsql_new_order add constraint bmsql_new_order_pkey
primary key (no_w_id, no_d_id, no_o_id);
alter table bmsql_order_line add constraint bmsql_order_line_pkey
primary key (ol_w_id, ol_d_id, ol_o_id, ol_number);
alter table bmsql_stock add constraint bmsql_stock_pkey
primary key (s_w_id, s_i_id);
alter table bmsql_item add constraint bmsql_item_pkey
primary key (i_id);
# ------------------------------------------------------------
# Loading SQL file ./sql.common/foreignKeys.sql
# ------------------------------------------------------------
alter table bmsql_district add constraint d_warehouse_fkey
foreign key (d_w_id)
references bmsql_warehouse (w_id);
alter table bmsql_customer add constraint c_district_fkey
foreign key (c_w_id, c_d_id)
references bmsql_district (d_w_id, d_id);
alter table bmsql_history add constraint h_customer_fkey
foreign key (h_c_w_id, h_c_d_id, h_c_id)
references bmsql_customer (c_w_id, c_d_id, c_id);
alter table bmsql_history add constraint h_district_fkey
foreign key (h_w_id, h_d_id)
references bmsql_district (d_w_id, d_id);
alter table bmsql_new_order add constraint no_order_fkey
foreign key (no_w_id, no_d_id, no_o_id)
references bmsql_oorder (o_w_id, o_d_id, o_id);
alter table bmsql_oorder add constraint o_customer_fkey
foreign key (o_w_id, o_d_id, o_c_id)
references bmsql_customer (c_w_id, c_d_id, c_id);
alter table bmsql_order_line add constraint ol_order_fkey
foreign key (ol_w_id, ol_d_id, ol_o_id)
references bmsql_oorder (o_w_id, o_d_id, o_id);
alter table bmsql_order_line add constraint ol_stock_fkey
foreign key (ol_supply_w_id, ol_i_id)
references bmsql_stock (s_w_id, s_i_id);
alter table bmsql_stock add constraint s_warehouse_fkey
foreign key (s_w_id)
references bmsql_warehouse (w_id);
alter table bmsql_stock add constraint s_item_fkey
foreign key (s_i_id)
references bmsql_item (i_id);
# ------------------------------------------------------------
# Loading SQL file ./sql.oracle/extraHistID.sql
# ------------------------------------------------------------
-- ----
-- Extra Schema objects/definitions for history.hist_id in Oracle
-- ----
-- ----
-- This is an extra column not present in the TPC-C
-- specs. It is useful for replication systems like
-- Bucardo and Slony-I, which like to have a primary
-- key on a table. It is an auto-increment or serial
-- column type. The definition below is compatible
-- with Oracle 11g, using the sequence in a trigger.
-- ----
-- Adjust the sequence above the current max(hist_id)
alter sequence bmsql_hist_id_seq increment by 30000;
declare
n integer;
i integer;
dummy integer;
begin
select max(hist_id) into n from bmsql_history;
i := 0;
while i <= n loop
select bmsql_hist_id_seq.nextval into dummy from dual;
i := i + 30000;
end loop;
end;
;
(conn=430) You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near 'declare' at line 1
alter sequence bmsql_hist_id_seq increment by 1;
-- Create a trigger that forces hist_id to be hist_id_seq.nextval
create trigger bmsql_history_before_insert
before insert on bmsql_history
for each row
begin
if :new.hist_id is null then
select bmsql_hist_id_seq.nextval into :new.hist_id from dual;
end if;
end;
;
(conn=430) You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near 'select bmsql_hist_id_seq.nextval into :new.hist_id from dual' at line 6
-- Add a primary key history(hist_id)
alter table bmsql_history add primary key (hist_id);
# ------------------------------------------------------------
# Loading SQL file ./sql.common/buildFinish.sql
# ------------------------------------------------------------
-- ----
-- Extra commands to run after the tables are created, loaded,
-- indexes built and extra's created.
'
-- ----
由于是oracle语法，oracle实现自增列用的是序列+触发器。oceanbase语法不支持。我们直接手动创建自增列即可
ALTER TABLE bmsql_history MODIFY hist_id INTEGER AUTO_INCREMENT PRIMARY KEY;
如果不创建在跑runBenchmark.sh时会报如下错误
Caused by: java.sql.SQLException: Field 'hist_id' doesn't have a default value
# 开始 tpcc 测试
./runBenchmark.sh props.ob
6.2 查看TOPSQL、分析执行计划
# 查看 processlist 确认有会话连接到集群
show full process list;
# 查看 TOP SQL
SELECT sql_id, count(*), round(avg(elapsed_time)) avg_elapsed_time, round(avg(execute_time)) avg_exec_time
FROM gv$ob_sql_audit s
WHERE 1=1
and db_name= 'tpccob'
and request_time >= time_to_usec(DATE_SUB(current_timestamp, INTERVAL 30 MINUTE) )
GROUP BY sql_id
order by avg_elapsed_time desc limit 3;
+----------------------------------+----------+------------------+---------------+
| sql_id | count(*) | avg_elapsed_time | avg_exec_time |
+----------------------------------+----------+------------------+---------------+
| F59A700FA168324279B0DBC25E19760F | 9 | 360237 | 358323 |
| FC3FED8CCB2946DE54F1C5BA3656023C | 137 | 24676 | 408 |
| F4585305C4CB9B091C750826A7DEDD13 | 127 | 23061 | 579 |
+----------------------------------+----------+------------------+---------------+
3 rows in set (0.08 sec)
# 根据 sql_id 定位具体的 sql 语句 ，并查询 SVR_IP,SVR_PORT,TENANT_ID,PLAN_ID 用于获取真实实际的执行计划
MySQL [oceanbase]> select SVR_IP,SVR_PORT,TENANT_ID,PLAN_ID,QUERY_SQL from gv$ob_sql_audit where sql_id='F59A700FA168324279B0DBC25E19760F' limit 1;
+----------------+----------+-----------+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| SVR_IP | SVR_PORT | TENANT_ID | PLAN_ID | QUERY_SQL |
+----------------+----------+-----------+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 192.168.56.107 | 2882 | 1 | 40903 | SELECT count(*) AS low_stock FROM ( SELECT s_w_id, s_i_id, s_quantity FROM bmsql_stock WHERE s_w_id = 2 AND s_quantity < 13 AND s_i_id IN ( SELECT ol_i_id FROM bmsql_district JOIN bmsql_order_line ON ol_w_id = d_w_id AND ol_d_id = d_id AND ol_o_id >= d_next_o_id - 20 AND ol_o_id < d_next_o_id WHERE d_w_id = 2 AND d_id = 8 ) ) |
+----------------+----------+-----------+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
MySQL [oceanbase]> select SVR_IP,SVR_PORT,TENANT_ID,PLAN_ID,QUERY_SQL from gv$ob_sql_audit where sql_id='FC3FED8CCB2946DE54F1C5BA3656023C' limit 1;
+----------------+----------+-----------+---------+----------------------------------------------------------------------------------------------------+
| SVR_IP | SVR_PORT | TENANT_ID | PLAN_ID | QUERY_SQL |
+----------------+----------+-----------+---------+----------------------------------------------------------------------------------------------------+
| 192.168.56.107 | 2882 | 1 | 40877 | SELECT d_tax, d_next_o_id FROM bmsql_district WHERE d_w_id = 1 AND d_id = 1 FOR UPDATE |
+----------------+----------+-----------+---------+----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
MySQL [oceanbase]> select SVR_IP,SVR_PORT,TENANT_ID,PLAN_ID,QUERY_SQL from gv$ob_sql_audit where sql_id='F4585305C4CB9B091C750826A7DEDD13' limit 1;
+----------------+----------+-----------+---------+---------------------------------------------------------------------------------------+
| SVR_IP | SVR_PORT | TENANT_ID | PLAN_ID | QUERY_SQL |
+----------------+----------+-----------+---------+---------------------------------------------------------------------------------------+
| 192.168.56.107 | 2882 | 1 | 40891 | UPDATE bmsql_district SET d_ytd = d_ytd + 26.58 WHERE d_w_id = 2 AND d_id = 6 |
+----------------+----------+-----------+---------+---------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
# 查看执行计划
MySQL [tpccob]> explain SELECT count(*) AS low_stock FROM ( SELECT s_w_id, s_i_id, s_quantity FROM bmsql_stock WHERE s_w_id = 2 AND s_quantity < 18 AND s_i_id IN ( SELECT ol_i_id FROM bmsql_district JOIN bmsql_order_line ON ol_w_id = d_w_id AND ol_d_id = d_id AND ol_o_id >= d_next_o_id - 20 AND ol_o_id < d_next_o_id WHERE d_w_id = 2 AND d_id = 10 ) ) ;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ====================================================================== |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| ---------------------------------------------------------------------- |
| |0 |SCALAR GROUP BY | |1 |74 | |
| |1 |└─NESTED-LOOP JOIN | |3 |74 | |
| |2 | ├─SUBPLAN SCAN |VIEW2 |3 |15 | |
| |3 | │ └─HASH DISTINCT | |3 |15 | |
| |4 | │ └─NESTED-LOOP JOIN | |3 |14 | |
| |5 | │ ├─TABLE RANGE SCAN|bmsql_order_line|57 |10 | |
| |6 | │ └─MATERIAL | |1 |3 | |
| |7 | │ └─TABLE GET |bmsql_district |1 |3 | |
| |8 | └─DISTRIBUTED TABLE GET |bmsql_stock |1 |21 | |
| ====================================================================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([T_FUN_COUNT(*)]), filter(nil), rowset=256 |
| group(nil), agg_func([T_FUN_COUNT(*)]) |
| 1 - output(nil), filter(nil), rowset=256 |
| conds(nil), nl_params_([VIEW2.VIEW1.ol_i_id(:3)]), use_batch=false |
| 2 - output([VIEW2.VIEW1.ol_i_id]), filter(nil), rowset=256 |
| access([VIEW2.VIEW1.ol_i_id]) |
| 3 - output([bmsql_order_line.ol_i_id]), filter(nil), rowset=256 |
| distinct([bmsql_order_line.ol_i_id]) |
| 4 - output([bmsql_order_line.ol_i_id]), filter(nil), rowset=256 |
| conds([bmsql_order_line.ol_o_id < bmsql_district.d_next_o_id], [bmsql_order_line.ol_o_id >= bmsql_district.d_next_o_id - 20]), nl_params_(nil), use_batch=false |
| 5 - output([bmsql_order_line.ol_o_id], [bmsql_order_line.ol_i_id]), filter(nil), rowset=256 |
| access([bmsql_order_line.ol_o_id], [bmsql_order_line.ol_i_id]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([bmsql_order_line.ol_w_id], [bmsql_order_line.ol_d_id], [bmsql_order_line.ol_o_id], [bmsql_order_line.ol_number]), range(2,10,MIN,MIN ; |
| 2,10,MAX,MAX), |
| range_cond([bmsql_order_line.ol_w_id = 2], [bmsql_order_line.ol_d_id = 10]) |
| 6 - output([bmsql_district.d_next_o_id], [bmsql_district.d_next_o_id - 20]), filter(nil), rowset=256 |
| 7 - output([bmsql_district.d_next_o_id], [bmsql_district.d_next_o_id - 20]), filter([bmsql_district.d_next_o_id > bmsql_district.d_next_o_id - 20]), rowset=256 |
| access([bmsql_district.d_next_o_id]), partitions(p0) |
| is_index_back=false, is_global_index=false, filter_before_indexback[false], |
| range_key([bmsql_district.d_w_id], [bmsql_district.d_id]), range[2,10 ; 2,10], |
| range_cond([bmsql_district.d_w_id = 2], [bmsql_district.d_id = 10]) |
| 8 - output(nil), filter([bmsql_stock.s_quantity < 18]), rowset=256 |
| access([bmsql_stock.s_quantity]), partitions(p0) |
| is_index_back=false, is_global_index=false, filter_before_indexback[false], |
| range_key([bmsql_stock.s_w_id], [bmsql_stock.s_i_id]), range(MIN ; MAX), |
| range_cond([bmsql_stock.s_w_id = 2], [bmsql_stock.s_i_id = :3]) |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
42 rows in set (0.07 sec)
MySQL [tpccob]> explain SELECT d_tax, d_next_o_id FROM bmsql_district WHERE d_w_id = 1 AND d_id = 6 FOR UPDATE;
+--------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
+--------------------------------------------------------------------------------------------------------------------------------------------+
| ======================================================= |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| ------------------------------------------------------- |
| |0 |MATERIAL | |1 |3 | |
| |1 |└─FOR UPDATE | |1 |3 | |
| |2 | └─TABLE GET|bmsql_district|1 |3 | |
| ======================================================= |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([bmsql_district.d_tax], [bmsql_district.d_next_o_id]), filter(nil), rowset=16 |
| 1 - output([bmsql_district.d_tax], [bmsql_district.d_next_o_id]), filter(nil), rowset=16 |
| lock tables(bmsql_district) |
| 2 - output([bmsql_district.d_w_id], [bmsql_district.d_id], [bmsql_district.d_tax], [bmsql_district.d_next_o_id]), filter(nil), rowset=16 |
| access([bmsql_district.d_w_id], [bmsql_district.d_id], [bmsql_district.d_tax], [bmsql_district.d_next_o_id]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([bmsql_district.d_w_id], [bmsql_district.d_id]), range[1,6 ; 1,6], |
| range_cond([bmsql_district.d_w_id = 1], [bmsql_district.d_id = 6]) |
+--------------------------------------------------------------------------------------------------------------------------------------------+
17 rows in set (0.01 sec)
MySQL [tpccob]> explain UPDATE bmsql_district SET d_ytd = d_ytd + 3736.43 WHERE d_w_id = 2 AND d_id = 9;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ============================================================ |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| ------------------------------------------------------------ |
| |0 |DISTRIBUTED UPDATE| |1 |35 | |
| |1 |└─TABLE GET |bmsql_district|1 |3 | |
| ============================================================ |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output(nil), filter(nil) |
| table_columns([{bmsql_district: ({bmsql_district: (bmsql_district.d_w_id, bmsql_district.d_id, bmsql_district.d_ytd, bmsql_district.d_tax, bmsql_district.d_next_o_id, |
| bmsql_district.d_name, bmsql_district.d_street_1, bmsql_district.d_street_2, bmsql_district.d_city, bmsql_district.d_state, bmsql_district.d_zip)})}]), |
| |
| update([bmsql_district.d_ytd=column_conv(DECIMAL,PS:(12,2),NULL,bmsql_district.d_ytd + 3736.43)]) |
| 1 - output([bmsql_district.d_w_id], [bmsql_district.d_id], [bmsql_district.d_ytd], [bmsql_district.d_tax], [bmsql_district.d_next_o_id], [bmsql_district.d_name], |
| [bmsql_district.d_street_1], [bmsql_district.d_street_2], [bmsql_district.d_city], [bmsql_district.d_state], [bmsql_district.d_zip]), filter(nil), rowset=16 |
| access([bmsql_district.d_w_id], [bmsql_district.d_id], [bmsql_district.d_ytd], [bmsql_district.d_tax], [bmsql_district.d_next_o_id], [bmsql_district.d_name], |
| [bmsql_district.d_street_1], [bmsql_district.d_street_2], [bmsql_district.d_city], [bmsql_district.d_state], [bmsql_district.d_zip]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([bmsql_district.d_w_id], [bmsql_district.d_id]), range[2,9 ; 2,9], |
| range_cond([bmsql_district.d_w_id = 2], [bmsql_district.d_id = 9]) |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
20 rows in set (0.01 sec)
# 查看真实的执行计划
根据上面查到的 SVR_IP,SVR_PORT,TENANT_ID,PLAN_ID 查询 GV$OB_PLAN_CACHE_PLAN_EXPLAIN 以获取真实实际的执行计划
MySQL [oceanbase]> select * from GV$OB_PLAN_CACHE_PLAN_EXPLAIN where SVR_IP = '192.168.56.107' and SVR_PORT=2882 and TENANT_ID=1 and PLAN_ID=40903;
+-----------+----------------+----------+---------+------------+--------------+-------------------------+------------------+------+------+-----------------------------------------------------------------------------------------------------------------------------------------------+
| TENANT_ID | SVR_IP | SVR_PORT | PLAN_ID | PLAN_DEPTH | PLAN_LINE_ID | OPERATOR | NAME | ROWS | COST | PROPERTY |
+-----------+----------------+----------+---------+------------+--------------+-------------------------+------------------+------+------+-----------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | 192.168.56.107 | 2882 | 40903 | 0 | 0 | PHY_SCALAR_AGGREGATE | NULL | 1 | 2709 | NULL |
| 1 | 192.168.56.107 | 2882 | 40903 | 1 | 1 | PHY_HASH_JOIN | NULL | 1000 | 2691 | NULL |
| 1 | 192.168.56.107 | 2882 | 40903 | 2 | 2 | PHY_TABLE_SCAN | bmsql_stock | 1000 | 2079 | table_rows:100049, physical_range_rows:25012, logical_range_rows:25012, index_back_rows:0, output_rows:1000, avaiable_index_name[bmsql_stock] |
| 1 | 192.168.56.107 | 2882 | 40903 | 2 | 3 | PHY_SUBPLAN_SCAN | NULL | 3791 | 33 | NULL |
| 1 | 192.168.56.107 | 2882 | 40903 | 3 | 4 | PHY_NESTED_LOOP_JOIN | NULL | 3791 | 23 | NULL |
| 1 | 192.168.56.107 | 2882 | 40903 | 4 | 5 | PHY_TABLE_SCAN | bmsql_district | 1 | 2 | table_rows:20, physical_range_rows:1, logical_range_rows:1, index_back_rows:0, output_rows:1, avaiable_index_name[bmsql_district] |
| 1 | 192.168.56.107 | 2882 | 40903 | 4 | 6 | PHY_TABLE_SCAN | bmsql_order_line | 1 | 20 | table_rows:379776, physical_range_rows:1, logical_range_rows:1, index_back_rows:0, output_rows:1, avaiable_index_name[bmsql_order_line] |
+-----------+----------------+----------+---------+------------+--------------+-------------------------+------------------+------+------+-----------------------------------------------------------------------------------------------------------------------------------------------+
7 rows in set (0.11 sec)
MySQL [oceanbase]> select * from GV$OB_PLAN_CACHE_PLAN_EXPLAIN where SVR_IP = '192.168.56.107' and SVR_PORT=2882 and TENANT_ID=1 and PLAN_ID=40877;
+-----------+----------------+----------+---------+------------+--------------+------------------+----------------+------+------+-----------------------------------------------------------------------------------------------------------------------------------+
| TENANT_ID | SVR_IP | SVR_PORT | PLAN_ID | PLAN_DEPTH | PLAN_LINE_ID | OPERATOR | NAME | ROWS | COST | PROPERTY |
+-----------+----------------+----------+---------+------------+--------------+------------------+----------------+------+------+-----------------------------------------------------------------------------------------------------------------------------------+
| 1 | 192.168.56.107 | 2882 | 40877 | 0 | 0 | PHY_MATERIAL | NULL | 1 | 2 | NULL |
| 1 | 192.168.56.107 | 2882 | 40877 | 1 | 1 | PHY_LOCK | NULL | 1 | 2 | NULL |
| 1 | 192.168.56.107 | 2882 | 40877 | 2 | 2 | PHY_TABLE_SCAN | bmsql_district | 1 | 2 | table_rows:20, physical_range_rows:1, logical_range_rows:1, index_back_rows:0, output_rows:1, avaiable_index_name[bmsql_district] |
+-----------+----------------+----------+---------+------------+--------------+------------------+----------------+------+------+-----------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
MySQL [oceanbase]> select * from GV$OB_PLAN_CACHE_PLAN_EXPLAIN where SVR_IP = '192.168.56.107' and SVR_PORT=2882 and TENANT_ID=1 and PLAN_ID=40891;
+-----------+----------------+----------+---------+------------+--------------+-----------------+----------------+------+------+-----------------------------------------------------------------------------------------------------------------------------------+
| TENANT_ID | SVR_IP | SVR_PORT | PLAN_ID | PLAN_DEPTH | PLAN_LINE_ID | OPERATOR | NAME | ROWS | COST | PROPERTY |
+-----------+----------------+----------+---------+------------+--------------+-----------------+----------------+------+------+-----------------------------------------------------------------------------------------------------------------------------------+
| 1 | 192.168.56.107 | 2882 | 40891 | 0 | 0 | PHY_UPDATE | NULL | 1 | 34 | NULL |
| 1 | 192.168.56.107 | 2882 | 40891 | 1 | 1 | PHY_TABLE_SCAN | bmsql_district | 1 | 2 | table_rows:20, physical_range_rows:1, logical_range_rows:1, index_back_rows:0, output_rows:1, avaiable_index_name[bmsql_district] |
+-----------+----------------+----------+---------+------------+--------------+-----------------+----------------+------+------+-----------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.01 sec)
6.3 踩坑
4.2.2 版本部分视图已经改名了，但是我参考的官方文档还没更改(截至20240314)。已反馈
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
